منتدى فيجوال بيسك لكل العرب | منتدى المبرمجين العرب

منتدى فيجوال بيسك لكل العرب | منتدى المبرمجين العرب > قسم قواعد البيانات > قسم مقالات SQL SERVER > معلومة --- Openrowset --- هامة للغاية
نسخة كاملة : معلومة --- Openrowset --- هامة للغاية
أنت حالياً تتصفح نسخة خفيفة من المنتدى . مشاهدة نسخة كاملة مع جميع الأشكال الجمالية .

RaggiTech

21-10-12, 04:50 PM
كاتب الموضوع : 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 بدلاً من اسم الصفحة





RaggiTech

21-10-12, 04:50 PM
رأينا كيفية تنفيذ الاستعلام واستخدام الدالة للوصول للبيانات بجانب ذلك يمكن أن تلحق بالجملة شرط 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

RaggiTech

21-10-12, 04:51 PM
سنتكلم عن شئ هام جداً بالنسبة لجلب البيانات


في حالة أنك تستخدم 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]

RaggiTech

21-10-12, 04:52 PM
حتي الآن تكلمنا كيف نحصل على البيانات ، لكن لا ننسى بداية كلامنا فى الموضوع عن فائدة الأمر من حيث الحصول على بيانات وتسجيلها فى جدول على السيرفر

كيف يكون ذلك ؟


كود :
[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 وتضع فيه البيانات

RaggiTech

21-10-12, 04:53 PM
فى الرد السابق تحدثنا عن كيفية حفظ البيانات فى جدول يتم انشاءه عن طريق الجملة 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]




نتمنى نكون افدناكم وأوصلنا لكم فائدة ربما كانت غائبة عن الكثير

والله ولي التوفيق



منتدى فيجوال بيسك لكل العرب | منتدى المبرمجين العرب > قسم قواعد البيانات > قسم مقالات SQL SERVER > معلومة --- Openrowset --- هامة للغاية