+- منتدى فيجوال بيسك لكل العرب | منتدى المبرمجين العرب (http://vb4arb.com/vb)
+-- قسم : قسم قواعد البيانات (http://vb4arb.com/vb/forumdisplay.php?fid=40)
+--- قسم : قسم مقالات SQL SERVER (http://vb4arb.com/vb/forumdisplay.php?fid=84)
+--- الموضوع : معلومة --- Openrowset --- هامة للغاية (/showthread.php?tid=6609)
معلومة --- Openrowset --- هامة للغاية - RaggiTech - 21-10-12
كاتب الموضوع : BADRMEDIA
هل احتجت مرة أن تأتى ببيانات من مصدر بيانان لتسجلها فى مصدر أخي
كفكرة بسيطة : عندك بيانات تحصلها عليها من ملف أكسيل موجود فى سيرفر يتيح عن طريق الملف الحصول مثلاً على أسعار أسهم أو أسعار العملات طبعاً لتحركات مؤشر البورصة
كيف نجعل السيرفر يتصل على مصدر البيانات هذا وهو ملف الأكسيل ويحصل منه على القيم
أنها الدالة OPENROWSET
وتكون صيغتها كالتالي
function syntax
كود :
[color=#000000][COLOR=#0000bb]OPENROWSET [/color][color=#007700]( [/color][color=#dd0000]'provider_name'[/color][COLOR=#007700],
{ [/COLOR][color=#dd0000]'datasource'[/color][color=#007700]; [/color][color=#dd0000]'user_id'[/color][color=#007700]; [/color][color=#dd0000]'password' [/color][color=#007700]| [/color][color=#dd0000]'provider_string' [/color][COLOR=#007700]},
{ [ [/COLOR][color=#0000bb]catalog[/color][color=#007700].][[/color][color=#0000bb]schema[/color][color=#007700].][/color][color=#0000bb]object [/color][color=#007700]| [/color][color=#dd0000]'query' [/color][COLOR=#007700]}
)
[/COLOR][color=#0000bb][/color][/COLOR]ولنبسطها هكذا ونشرحها :
كود :
[color=#000000][COLOR=#0000bb]OPENROWSET [/color][color=#007700]( [/color][color=#dd0000]'provider_name'[/color][color=#007700], [/color][color=#dd0000]'provider_string'[/color][color=#007700], [/color][color=#dd0000]'query' [/color][COLOR=#007700])
[/COLOR][color=#0000bb][/color][/COLOR]سيكون تطبيقنا على ملف Excel وبالطبع سيكون provider الخاص بذلك هو OLEDB
كمثال أنسخ أى ملف Excel على القرص C مباشرة بحيث سيكون مسار العمل عليه وأن يكون به بعض البيانات لنستعرضها
كود :
[color=#000000][COLOR=#0000bb]SELECT [/color][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][color=#0000bb][/color][/COLOR]حيث كتبنا
'provider_name' = 'Microsoft.Jet.OLEDB.4.0'
'provider_string' = 'Excel 8.0;Database=C:\1.xls'
'query' = 'SELECT * FROM [Sheet1$]'
حيث Sheet1$ هو اسم الصفحة الأولي من صفحة الأكسيل ويمكن أن يكون اسمها " الصفحة "
لو كان الأوفيس عربي أو يكون لها اسم خاص لو وضعت لها اسم ، إذن تنادي عليها باسمها مع وضع علامة $
ويمكن أن تنادي علي نطاق معين مثلاً من A:Z بدلاً من اسم الصفحة
معلومة --- Openrowset --- هامة للغاية - RaggiTech - 21-10-12
رأينا كيفية تنفيذ الاستعلام واستخدام الدالة للوصول للبيانات بجانب ذلك يمكن أن تلحق بالجملة شرط Condition باستخدام الكلمة Where
كود :
[color=#000000][COLOR=#0000bb]SELECT [/color][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]Where F1 [/color][color=#007700]= [/color][COLOR=#0000bb]2
[/COLOR][/COLOR]يمكنك أن تنادي على اسم العمود لو كان له اسم او تكتب ترتبيه مع حرف F لو كان ليس له اسم
أي ليس له رأس أو خلية كعنوان له فى أوله
==============================
نتطرق لنكتة جميلة أخري وهي HDR=No أو HDR=Yes
وتسند لها قيمة No أو Yes في حالة هل تريد اظهار أسماء الاعمدة أم لا
كود :
[color=#000000][COLOR=#0000bb]SELECT [/color][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=No'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]وكما قلنا من قبل فى حالة عدم اظهار اسماء الأعمدة أو ليس هناك أصلاً أسماء للأعمدة
يضع المحرك OLEDB اسم بديل وهو F ورقم العمود F1,F2,Fn
معلومة --- Openrowset --- هامة للغاية - RaggiTech - 21-10-12
سنتكلم عن شئ هام جداً بالنسبة لجلب البيانات
في حالة أنك تستخدم HDR=NO
لو كان عندك عمود به بيانات هكذا
كود :
[COLOR=#000000][COLOR=#0000bb]10
20
30
50
AA
HH
JK
56
78
[/COLOR][/COLOR]ستجد أن بياناته تظهر هكذا
كود :
[COLOR=#000000][COLOR=#0000bb]10
20
30
50
Null
Null
Null
56
78
[/COLOR][/COLOR]أن أن القيم النصية تم تحويلها لقيمة Null
كيف حدثت هذه المصيبة
ذلك أن المحرك OLEDB يقوم بقراءة أول قيم للعمود فأن وجد أنها رقمية تعامل مع العمود على أنه رقمي وهكذا مع النصي وبالتالي يقوم بعمل تحويل للقيم
لذلك جاءت IMEX=1 لتحل جريمة المحرك OLEDB الكبري
كود :
[COLOR=#000000][COLOR=#0000bb]
SELECT [/COLOR][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=NO;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]معلومة --- Openrowset --- هامة للغاية - RaggiTech - 21-10-12
حتي الآن تكلمنا كيف نحصل على البيانات ، لكن لا ننسى بداية كلامنا فى الموضوع عن فائدة الأمر من حيث الحصول على بيانات وتسجيلها فى جدول على السيرفر
كيف يكون ذلك ؟
كود :
[COLOR=#000000][COLOR=#0000bb]
SELECT [/COLOR][COLOR=#007700]*
[/COLOR][color=#0000bb]INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]Table_Excel[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=NO;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]أضفنا
INTO [dbo].[Table_Excel]
ستقوم مباشرة هي بانشاء جدول اسمه Table_Excel وتضع فيه البيانات
معلومة --- Openrowset --- هامة للغاية - RaggiTech - 21-10-12
فى الرد السابق تحدثنا عن كيفية حفظ البيانات فى جدول يتم انشاءه عن طريق الجملة Into
لكن هناك شئ هام وهو أن البيانات تخزن فى حقول من نوع على مزاج المحرك OLED أو بصورة علمية حسب البيانات المستوردة
لكن كيف نرسل البيانات ونجعل الحقول بهيئة على طريقتنا
الفكرة أننا نقوم بعمل تحويل لنوع الحقل وبالتالى سيكون نوع الحقل بالنوع المحول له
كود :
[color=#000000][COLOR=#0000bb]SELECT CAST[/color][color=#007700]([[/color][color=#0000bb]FirstName[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]20[/color][color=#007700])) AS [[/color][color=#0000bb]FirstName[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]LastName[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]20[/color][color=#007700])) AS [[/color][color=#0000bb]LastName[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]Address[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]50[/color][color=#007700])) AS [[/color][color=#0000bb]Address[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]City[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]30[/color][color=#007700])) AS [[/color][color=#0000bb]City[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]State[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]2[/color][color=#007700])) AS [[/color][color=#0000bb]State[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]ZIP[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]10[/color][color=#007700])) AS [[/color][color=#0000bb]ZIP[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]Table_Excel[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=NO;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]حتي الآن الأمر جيد
لكن هناك مشكلة أن الأمر Into يقوم بانشاء الجدول المرسل له البيانات
لو استخدمت الجملة مرة اخرى سيقول لك أن الجدول موجود بالفعل
ماذا لو أردت أن تنشأ أنت الجدول وترسل له البيانات بصورة طبيعية
إذن صمم جدول كما أنت معتاد
واستخدم جملة
كود :
[COLOR=#000000][COLOR=#0000bb]Insert Into
Select
[/COLOR][/COLOR]المعروفة
كود :
[COLOR=#000000][COLOR=#0000bb]insert into Table_Excel
SELECT [/COLOR][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]نتمنى نكون افدناكم وأوصلنا لكم فائدة ربما كانت غائبة عن الكثير
والله ولي التوفيق