imadouzoun

التعامل مع أنواع البيانات. Ntext و Text و image

3 ردود في هذا الموضوع

التعامل مع أنواع البيانات Ntext و Text و image :

هذا درس كامل مع أمثلة رائعة عن كيفية التعامل مع أنواع البيانات .. Ntext و Text و image .. :) :)

أتمنى أن ينال إعجابكم أيها الأخوة .

ملاحظة هامة الإجراءات المستخدمة موجودة في اّخر المشاركة .

...............................................................................................................

مع نمو قواعد البيانات سواء في الحجم أو التعقيد نوعاً ما أضحت عتاديات الحاسب و برمجياته تسمح لنا بأن نخزن حجوم هائلة من البيانات و بمختلف أنواعها من :

• الوسائط المتعددة (كالصوتيات و الرسوميات) و بلواحق عدة : JPG, PNG, MP3

• الوثائق و النصوص الكبيرة : DOC/RTF, HTML, Unicode, and XML

لهذه الأنواع من البيانات تقدم sql server الأنواع التالية : image, text, and ntext

بشكل عام يمكننا القول بأن :

النوع text : لتخزين بيانات من نوع محارف AscII فقط .

النوع Ntext : لتخزين بيانات من نوع محارف UniCode .

النوع Image : لتخزين البيانات الثنائية .

أحياناً ينتابنا بعض القلق فيما يخص حجم التخزين , أليس كذلك ؟

أظن أن الحجوم التالية سترضيك :

النوع text يعطيك (2 ^ 31 - 1) أي 2,147,483,647 من محارف الـ non_unicode أي اّسكي ..

النوع Ntext يعطيك (2 ^ 30 - 1) أي 1,073,741,823 من محارف الـ Unicode .

النوع Image يعطيك (2 ^ 31 - 1) أي 2,147,483,647 بايت .

لاحظ أخي الكريم أن حجم التخزين للنوع Ntext هو ضعف عدد المحارف المدخلة .. طبعاً لأنها محارف

Unicode .

ولكن كيف يمكننا التعامل مع أنواع البيانات هذه ؟

في الحقيقة أن التعامل معها يتم باستخدام مؤشرات إلى البيانات , وبعض الدوال المخصصة التي تسمح للمؤشر بإجراء عمليات الإضافة و القراءة و الحذف منها .الجدول التالي يوضح قوة و إمكانيات هذه الأنواع :

ما يمكنها أن تفعله مالا يمكنها أن تفعله

أن تعمل كبارامتر دخل/ خرج للإجرائيات المخزنة . العمل مع عبارات الـ DECLARE, SET, or FETCH أي لا يمكننا أن نعاملها معاملة بقية أنواع البيانات . بالطريقة التقليدية .

يمكنها أن تكون بارامتر دخل للدوال . لا يمكنها أن تحل محل البيانات المعادة من دوال المستخدم . User Defined Functions

تملك ما يعادل 8000 بايت قابلة للتحويل إلى بقية أنواع البيانات لا يمكن استخدامها مع المؤشرات Cursors في تعليمة Fetch ما لم يجرى لها تعديل .

يمكنها أن تعمل بعبارة Union All . يمكنها أن تعمل مع نوع البيانات sql_variant .

يمكن مقارنتها و تخزينها أو حتى استخدامها في التجميع Group By . الإستثناء الوحيد للعملية

هو عند استخدام Is Null (اختبار فيما إذا كانت القيمة Null) أو مع عبارة Like إلا في حالة استخدام تحويل بسيط لأنواع البيانات إلى أنواع أخرى مثل varchar و Nvarchar و Char.

أن تشترك في عبارة Union و ذلك لأن أنواع البيانات هذه لا يمكن ترتيبها .

الدوال الخاصة بالتعامل مع أنواع البيانات Ntext , Text , Image :

سأشرح بعون الله عدد من الدوال مبيناً الصيغة القواعدية لكل منها مع مثال :

الدالة : TEXTPTR :

تعيد هذه الدالة قيمة ست عشرية من 16 بايت و هي قيمة مؤشر النص الذي يشير إلى البيانات سواء في

النوع Text , Ntext , Image .

Syntax: TEXTPTR ( column )

مثال :

--TEXTPTR sample, create a text-pointer, see its value
create table #t (n ntext)
insert #t values('ArabTeam')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
print @ptrval
drop table #t

الخرج :

0xFFFF6900000000004D00000001000000

الدالة : TEXTVALID :

تعيد الدالة هذه قيمة صحيحة و التي ستكون 1 فيما إذا كان مؤشر صحيح و إلا ستعيد 0 .

Syntax: TEXTVALID ( 'table.column' , text_ptr )

مثال :

--TEXTVALID sample, creates a text-pointer, tests it
create table #t (n ntext)
insert #t values('الفريق العربي للبرمجة')
DECLARE @ptrval binary(16), @ptrval2 binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
if TEXTVALID('#t.n',@ptrval)=1
 print '@ptrval has a valid text pointer.'
else
 print '@ptrval has an invalid text pointer.'
if TEXTVALID('#t.n',@ptrval2)=1
 print '@ptrval2 has a valid text pointer.'
else  print '@ptrval2 has an invalid text pointer.'
drop table #t

الخرج :

@ptrval has a valid text pointer.
@ptrval2 has an invalid text pointer.

الدالة : SET TEXTSIZE

تقوم بتثبيت الحجم المعاد من أنواع البيانات Ntext و Text بقيمة صحيحة عند استخدام عبارة Select .

Syntax: SET TEXTSIZE { number }

مثال :

--SET TEXTSIZE sample
create table #t (n ntext)
insert #t values('T-Sql For ArabTeam')
SET TEXTSIZE 10--ntext is unicode, 2 bytes/character
select * from #t
SET TEXTSIZE 20--ntext is unicode, 2 bytes/character
select * from #t
drop table #t

الخرج :

T-Sql
T-Sql For

أما @@TEXTSIZE :

تعيد قيمة عددية صحيحة تمثل حجم البيانات المعادة من عبارة Select لأنواع البيانات Ntext , Text .

هذه القيمة يمكن تغييرها كما سلف باستخدام العبارة :

  Set TextSize {Number} 
Syntax: @@TEXTSIZE

مثال :

[email protected]@TEXTSIZE sample
SET TEXTSIZE 10--ntext is unicode, 2 bytes/character
print @@TEXTSIZE
SET TEXTSIZE 20--ntext is unicode, 2 bytes/character
print @@TEXTSIZE

الخرج :

10
20

الاّن نأتي إلى مرحلة استخدام دوال الإضافة و الحذف و التعديل في أنواع البيانات هذه :

الدالة : WRITETEXT :

تشبه إلى حد كبير عملية إسناد قيمة إلى حقل ما حيث تكتب هذه الدالة البيانات الجديدة بعد مسح البيانات الموجود

و تستخدم مع الأعمدة من الأنواع الثلاثة : text, ntext, image

Syntax: WRITETEXT { table.column text_ptr } [ WITH LOG ] { data }

مثال :

--WRITETEXT sample
create table #t (n ntext)
insert #t values('abc')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n)
FROM #t
WRITETEXT #t.n @ptrval 'Walcom'
select * from #t
drop table #t

الخرج :

Walcom

الدالة : UPDATETEXT :

تستخدم هذه الدالة لتعديل البيانات في الأعمدة من الأنواع الثلاثة السابقة :

Syntax: UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ]

مثال :

--UPDATETEXT sample insertion only
create table #t (n ntext)
insert #t values('bd')
DECLARE @ptrval binary(16), @i int
SELECT @ptrval = TEXTPTR(n)
FROM #t
UPDATETEXT #t.n @ptrval 0 0 'a'--insert at beginning
select * from #t
UPDATETEXT #t.n @ptrval 2 0 'c'--insert in the middle
select * from #t
set @i=(select DATALENGTH(n) from #t)/2
--/2 only if ntext, 2 bytes/character
print @i
UPDATETEXT #t.n @ptrval @i 0 'e'--insert at the end
select * from #t
drop table #t

الخرج :

abd
abcd
abcde

استخدام الدالة للحذف و الحشر معاً :

--UPDATETEXT sample deletion+insertion
create table #t (n ntext)
insert #t values('abxyef')
DECLARE @ptrval binary(16), @i int
SELECT @ptrval = TEXTPTR(n)
FROM #t
UPDATETEXT #t.n @ptrval 2 2 'cd'--insert 2, delete 2
--chars starting at position 2
select * from #t
drop table #t

الخرج :

abcdef

الدالة : READTEXT

تقرأ هذه الدالة كمية محددة من البيانات من الأنواع الثلاثة (Ntext , Text , Image)

Syntax: READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

مثال :

--READTEXT sample
create table #t (n ntext)
insert #t values('T-sql arabteam2000')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
READTEXT #t.n @ptrval 10 8
--read 8 characters starting at position 3
drop table #t

الخرج :

team2000

الدالة : DATALENGTH

تعيد الحجم (حجم البيانات بالبايت) لأنواع البيانات الثلاثة :

Syntax: DATALENGTH ( expression )

--DATALENGTH sample
create table #t (n ntext)
insert #t values('1234567890')
DECLARE @i int
set @i=(select DATALENGTH(n) from #t)
--it should return the length in bytes=2*UNICODE length
PRINT @i
drop table #t

الخرج :

20

الدالة : CONVERT

تعيد مكان تواجد نص ما في حقل من أحد الأنواع الثلاثة و في حال كان النص غير موجود ضمن الحقل الهدف عندها تعيد الدالة قيمة 0 .

Syntax: PATINDEX ( '%pattern%' , expression )

مثال :

--PATINDEX sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
SELECT PATINDEX('%tim%', n) FROM #t
SELECT PATINDEX('%time%', n) FROM #t
drop table #t

الخرج :

7
17

الدالة : CONVERT

دالة التحويل بين أنواع البيانات المعرفة لدى الكثير من مبرمجي T-Sql :

Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

مثال :

--CONVERT sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @c nvarchar(5)
SET @c=(select convert(nvarchar(5),n) from #t)
print @c
drop table #t

الخرج :

Hello

الدالة : CAST

نفس عمل الدالة السابقة :

Syntax: CAST ( expression AS data_type )

مثال :

--CAST sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @c nvarchar(5)
SET @c=(select CAST ( n  AS nvarchar(5) ) from #t)
print @c
drop table #t

الخرج :

Hello

دعونا الآن أيها الإخوة نستعرض قليلاً بعض الاستخدامات العامة لهذه الدوال :

حفظ بيانات حقول من الأنواع الثلاثة إلى ملفات خارجية :

المثال التالي : ينشأ جدول مؤقت لكل من الأنواع text و Ntext و Image و يحشر قيمة نصية ضمن الحقول النصية ومن ثم يستدعي الإجراءات التالية savetext2file لحشر قيم النص الموجودة في حقل

الـ Text و saventext2file من أجل الحقل من نوع Ntext و saveimage2file من أجل

حقل من نوع Image . علماً أن الإجرائيات المذكورة هي من إنشائنا و سأسرد نص كل منها بعد هذه النص الذي يقوم باستدعائها .

مثال :

--saveText2file sample
create table ##t (n text)
insert ##t values('Hello Tim, long time no see!')
EXEC saveText2file 'c:\test.txt', '##t','n', ''
drop table ##t

--saveNtext2file sample
create table ##t (n ntext)
insert ##t values('Hello Tim, long time no see!')
EXEC saveNtext2file 'c:\test.txt', '##t','n', ''
drop table ##t

--saveImage2file sample
exec saveImage2file 'c:\Category1.bak',
'Northwind..Categories', 'Picture',
'where categoryid=1'

تعديل قيم الحقول لأنواع البيانات Text و Ntext و Image :

لأن الدوال TEXTPTR, WRITETEXT, and UPDATETEXT لا تسمح بتمرير بارامتراتها على صيغة متحولات من نوع جدول أو عمود (Table Or Column) لذا فإن قراءة محتويات ملف تحتاج إلى جعلنا نستخدم Dynamic Sql .

قمنا بكتابة الدالة readImageFromfile لقراءة نوعي البيانات Varchar و صورة Image من ملفات خارجية ذلك لأنها تتعامل مع البيانات بشكل ثنائي و تكتبها في الحقول الهدف بدون استخدام جداول مؤقتة لهذا الغرض . أما بالنسبة لقراءة البيانات من ملف على شكل محارف

Unicode فالدالة readNtextFromfile ستفي بالغرض .

ملاحظة : أخي الكريم تأكد من وجود الملفات التي تتم قرائتها في المثالين التاليين .

مثال 1 :

--readImageFromfile sample 
--reading a text column from a file
create table ##t (n text)
insert ##t values('Hi Tim, long time no see!')
EXEC readImageFromfile 'c:\hello.txt', '##t','n', ''
select * from ##t
drop table ##t

الخرج :

Hello

مثال 2 :

--readNtextfromfile sample
create table ##t (n ntext)
insert ##t values('Hello Tim, long time no see!')
exec readNtextFromfile 'c:\t.txt', '##t','n', ''
select * from ##t
drop table ##t

نفس الكود تماماً مع ملف صورة يمكن استعماله لسحب صورة من ملف إلى حقل من نوع Image .

ولكن عليك أخي الكريم أن تنتبه أخي الكريم للملاحظة التالية :

لا يجب أن يتلقى الحقل من نوع Ntext بياناته من ملف بصيغة الـ Ascii .

وألا يتلقى الحقل من نوع Text بياناته من ملف بصيغة الـ Unicode .

وذلك لأن الدوال المقدمة سيتم إدخالها على حالتها الطبيعية أي بدون أي تحويل عليها .

ملاحظة 2 : الدوال المقدمة في الأمثلة تقوم بإعادة كتابة القيمة في الحقل الذي تذكره في الإستدعاء , لذلك فإن الدوال التالية ستقوم بالإضافة على القيمة الموجودة في الحقول .. وهي :

الدالة : readImageFromfile2 و أترك لك إنشاء دالة مشابهه لنوع البيانات Nvarchar أو Unicode كتمرين ..

ملاحظة 3 : إن عملية ( الإضافة ) Appending على الحقول من الأنواع السابقة تكون أسهل من (التعديل) Updating و ذلك لأن :

• عملية الإضافة تقوم باستبدال أول كتلة من الملف بالـمحتويات الأصلية للحقل و من ثم تتابع بشكل متسلسل عملية الإضافة كتابة الكتل .. الكتلة الثانية فالثالثة .. وهكذا .

• بينما عملية التعديل لا تحتاج أصلاً إلى الخطوة الأولى (استبدال البيانات السابقة) فتبدأ بحشر الكتل الأولى فالثانية في الحقل الهدف .

حفظ نص كائن من قاعدة البيانات إلى ملف :

و للقيام بذلك سنعتمد على الجداول المؤقتة تملك حقولاً من نوع Ntext من الممكن حفظ نص كل من a rule, default, unencrypted stored proc, UDF, trigger, or view إلى ملفات خارجية

حيث سينشأ الجدول المؤقت و بعدها سيتم حشر سجلات فيه باستخدام تقنية INSERT EXEC .

هذا السجل سوف يحتوي على نص الكائن المعاد من جدول الـنظام sysobjects بواسطة الإجراء sp_helptext و سنستخدم الإجراء التي أنشأناها و هي saveobj كما يلي :

create table #temp(s nvarchar(4000))
insert #temp
exec sp_helptext @object

الإجراء الذي قمنا بإنشائه (saveobj) يمكنه أن يقوم بقراءة نص أي من الكائنات التالية :

rule, default, unencrypted stored proc, UDF, trigger, or view إلى ملف نصي خارجي .

و مثل هذا الأمر طبعاً كما تعلمون أيها الأخوة مفيد جداً و خاصة من أجل أغراض النسخ الإحتياطي و توثيق القاعدة .

--saveobj sample, saving object hello
exec saveobj 'c:\hello.sql', 'hello'

الاّن دعونا نتحدث عن الخطوة المعاكسة أقصد :

قراءة نص كائن من ملف :

في الحقيقة قراءة ملف حجمه أكبر من 8000 بايت يتطلب عبارات استعلام ديناميكية (Dynamic SQL) ذلك لأن العديد من الـ Buffers سوف تمتلئ طبقاً لحجم الملف الكبير . لذا قمنا بإنشاء إجراء مخزن اسمه readobj يستخدم حلقة لكي ينشأ استعلام ديناميكياً , علمأ أن الحلقة سوف تقوم بتحديد عدد المتحولات المؤقتة التي سوف تنشئها . كل متحول مؤقت سوف يمسك 8000 بايت من البيانات , وباستخدام أسماء المتحولات المؤقتة مع محرف و زيادة الأعداد لتمييزها عن بعضها سوف يعود بالنتيجة و يحقق الهدف .

--readobj sample, reading object hello
exec readobj 'c:\hello.sql'

هذا الإجراء المخزن سوف يمنحك إمكانية قراءة كائن قاعدة بيانات من ملفScript بحجم أكبر 8000 بايت و يمكننا استخدام الإجراء من أجل:

عبارات INSERT or UPDATE طويلة أو أي من أنواع الكائنات التالية :

rule, default, unencrypted stored procedure, UDF, trigger, or view و إنشائها بعد قراءتها من الملف النصي .

وفي حال كان لديك عبارات T-SQL ذات حجم كبير فإن إجرائية مثل الإجرائية التالية يمكنها أن تنفذها :

CREATE PROCEDURE exec_ntext (@SQL ntext)
--Execute a gigantic SQL statement
AS
EXEC (@SQL)

مقارنة محتويات الحقول من أنواع البيانات السابقة :

سنستخدم لهذه الغاية الدوال (User-Defined Functions) لمقارنة الأنواع text, ntext, or image .

الطريقة الأسهل للمقارنة هي مقارنة أطوال البيانات مع بعضها ولكن هذه الطريقة ليست مضمونة النتائج خاصة في حال تساوي الأطوال لكلا محتويات العمودين اللذذان تتم مقارنتهما :

CREATE FUNCTION  testlength (@a ntext, @b ntext)
--returns true if both inputs have the same length
RETURNS bit AS  
BEGIN
declare @temp_bit bit
if datalength(@a)=datalength(@b)
 set @temp_bit= 1
else
 set @temp_bit=0
return @temp_bit
END

ملاحظة : المثال السابق ليس فقط لنوع البيانات ntext و إنما يمكن تطبيقه على نوعي البيانات text أو image .

الطريقة المثلى للمقارنة هي مقارنة كامل محتويات الحقلين مع بعضهما أو مجموعتين فرعيتين منهما كما يلي :

CREATE FUNCTION  testequality (@a ntext, @b ntext)
--returns 1 if the comparison result is true
RETURNS bit AS  
BEGIN
declare @temp_bit bit
if  @a like @b
 set @temp_bit= 1
else
 set @temp_bit=0
return @temp_bit
END

و لزيادة قوة المقارنة السابقة يمكنك استخدام أحرف المقارنة و المطابقة (wildcard characters) لجعل الدالة بإمكانيات أعلى و إذا لم تكن تعرف أخي الكريم ما هي أحرف المطابقة المستخدمة في SQL Server

فهي :

1. % سلسلة من أي طول حتى لو كان صفري .

2. _ سلسلة من محرف واحد .

3. [] محرف واحد من مجموعة محارف

1. عشوائية [wewe]

2. أو مجموعة من محارف متسلسلة الترتيب [k-t] .

3. محرف واحد لا ينتمي إلى مجموعة محارف [^wewe] أو[^k-t] .

تحديد نوع الصورة :

هناك طريقة سريعة لكي تكشف نوع الصورة هل هي : bitmap, JPEG, PNG, or TIFF هي أن تتفحص البايتات الأولى من الصورة لتحدد فيما إذا كانت تماثل مواصفات ترويسات تلك الأنواع من الصور من حيث الهيئة .

هذه التقنية ليست سيئة و لكنها ليست ممتازة و لكنها الخطوة الأولى لكتابة دالة أكثر إحكاماً للمطابقة .

CREATE FUNCTION  getImageType (@a image)
--returns the type of image format
RETURNS varchar(4) AS  
BEGIN
declare @out varchar(4), @temp varbinary(8)
SET @temp=convert(varbinary(8), @a)
SET @out=CASE WHEN LEFT(@temp,2)=0x424D THEN 'BMP'
WHEN LEFT(@temp,2)=0xFFD8 THEN 'JPG'
WHEN LEFT(@temp,8)=0x89504E470D0A1A0A THEN 'PNG'
WHEN (LEFT(@temp,2)=0x4949)OR(LEFT(@temp,2)=0x4D4D)
THEN 'TIFF'
ELSE '' END
return @out
END

-----------------------------------------------------------------------------------------------------------------------------------

أما شيفرة الأمثلة والإجرائيات المستخدمة في الإجرائيات المخزنة فهي مفصولة عن بعضها بالفاصل هذا (------------------------------) و هي :

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE exec_ntext (@SQL ntext)
--executes a SQL statement of type ntext
AS
EXEC (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION  getImageType (@a image)
--returns the type of image format
RETURNS varchar(4) AS  
BEGIN
declare @out varchar(4), @temp varbinary(8)
SET @temp=convert(varbinary(8), @a)
SET @out=CASE WHEN LEFT(@temp,2)=0x424D THEN 'BMP'
WHEN LEFT(@temp,2)=0xFFD8 THEN 'JPG'
WHEN LEFT(@temp,8)=0x89504E470D0A1A0A THEN 'PNG'
WHEN (LEFT(@temp,2)=0x4949)OR(LEFT(@temp,2)=0x4D4D) THEN 'TIFF'
ELSE '' END
return @out
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE getREADTEXT @column nvarchar(128), @table nvarchar(128),@where nvarchar(4000),@position int,@length int
--given a column, table and filter clause plus a position to start reading the text and length to be read, the selected portion of the text will be returned
AS
declare @sql nvarchar(4000)
set @sql='declare @txtPtr varbinary(16)
select @txtPtr = TEXTPTR('[email protected]+') from '[email protected]+' '[email protected]+
' READTEXT '[email protected]+'.'[email protected]+' @txtPtr '+str(@position)+' '+str(@length)+''
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE hello AS
print 'Hello world'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE readImageFromfile2 @filename nvarchar(128)=NULL, @table nvarchar(128)=NULL,@column nvarchar(128)=NULL, @where nvarchar(2000)=NULL
--Reads an image (binary data) from a file into an image column appending it to the existing data
AS
SET NOCOUNT ON
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'readImageFromfile reads an image from a file into an image column'
PRINT 'Usage:'
PRINT 'EXEC readImageFromfile FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC readImageFromfile2 ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
RETURN
END
DECLARE @i int, @size int, @blocks int, @cmd varchar(8000)
create table #filedetails(altname varchar(30),size int,createdate varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime varchar(32),
lastaccessdt varchar(30),lastaccesstime varchar(32),attributes int)
insert into #filedetails exec master..xp_getfiledetails @filename
set @size=(select size from #filedetails)
if @size=null
BEGIN
PRINT 'File error.'
RETURN
END
drop table #filedetails
SET @[email protected]/8000+1
--start dynamic SQL declaration
SET @cmd='DECLARE @ptrval binary(16), @fso int, @file int, @i int,@j int, @hr int, @buffer varbinary(8000) '
SET @[email protected]+' EXEC @hr = sp_OACreate ''ADODB.Stream'', @file OUT '
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Open'' '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''Type'', 1 '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''LoadFromFile'','''[email protected]+''''
SET @[email protected]+' SELECT @ptrval = TEXTPTR('[email protected]+') FROM '[email protected]+' '[email protected]+' '

SET @[email protected]+'set @j='+str(@blocks)+' while @j>0 begin '
SET @[email protected]+' exec @hr = sp_oamethod @file, ''read'',  @buffer out , 8000 '
SET @[email protected]+'set @i=(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+') '
SET @[email protected]+'UPDATETEXT  '[email protected]+'.'[email protected]+' @ptrval @i 0 @buffer '
SET @[email protected]+'set @[email protected] end '

--execut dynamic SQL
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Close'' '
SET @[email protected]+'EXEC @hr = sp_OADestroy @file EXEC @hr = sp_OADestroy @fso'
exec( @cmd)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE readImageFromfile @filename nvarchar(128)=NULL, @table nvarchar(128)=NULL,@column nvarchar(128)=NULL, @where nvarchar(2000)=NULL
--Reads an image (binary data) from a file into an image column replacing the original contents
AS
SET NOCOUNT ON
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'readImageFromfile reads data from a file into an image column'
PRINT 'Usage:'
PRINT 'EXEC readImageFromfile FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC readImageFromfile ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
RETURN
END
DECLARE @i int, @size int, @blocks int, @cmd varchar(8000)
create table #filedetails(altname varchar(30),size int,createdate varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime varchar(32),
lastaccessdt varchar(30),lastaccesstime varchar(32),attributes int)
insert into #filedetails exec master..xp_getfiledetails @filename
set @size=(select size from #filedetails)
if @size=null
BEGIN
PRINT 'File error.'
RETURN
END
drop table #filedetails
SET @[email protected]/8000+1
--start dynamic SQL declaration
SET @cmd='DECLARE @ptrval binary(16), @fso int, @file int, @i int, @j int, @hr int, @buffer varbinary(8000) '
SET @[email protected]+' EXEC @hr = sp_OACreate ''ADODB.Stream'', @file OUT '
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Open'' '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''Type'', 1 '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''LoadFromFile'','''[email protected]+''''
SET @[email protected]+' SELECT @ptrval = TEXTPTR('[email protected]+') FROM '[email protected]+' '[email protected]+' '

SET @[email protected]+' exec @hr = sp_oamethod @file, ''read'',  @buffer out , 8000 '
if @blocks=1
 SET @[email protected]+' WRITETEXT '[email protected]+'.'[email protected]+' @ptrval @buffer '
else
 BEGIN
 SET @[email protected]+' WRITETEXT '[email protected]+'.'[email protected]+' @ptrval @buffer '
 SET @[email protected]+'set @j='+str(@blocks-1)+' while @j>0 begin '
 SET @[email protected]+' exec @hr = sp_oamethod @file, ''read'',  @buffer out , 8000 '
 SET @[email protected]+'set @i=(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+') '
 SET @[email protected]+'UPDATETEXT  '[email protected]+'.'[email protected]+' @ptrval @i 0 @buffer '
 SET @[email protected]+'set @[email protected] end '
 END

--execute dynamic SQL
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Close'' '
SET @[email protected]+'EXEC @hr = sp_OADestroy @file EXEC @hr = sp_OADestroy @fso'
exec( @cmd)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE readNtextFromfile @filename nvarchar(128)=NULL, @table nvarchar(128)=NULL,@column nvarchar(128)=NULL, @where nvarchar(2000)=NULL
--Reads Ntext from a file into an Ntext column replacing the original contents
AS
SET NOCOUNT ON
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'readImageFromfile reads data from a file into an image column'
PRINT 'Usage:'
PRINT 'EXEC readImageFromfile FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC readImageFromfile ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
RETURN
END
DECLARE @i int, @size int, @blocks int, @cmd varchar(8000)

create table #filedetails(altname varchar(30),size int,createdate varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime varchar(32),
lastaccessdt varchar(30),lastaccesstime varchar(32),attributes int)

insert into #filedetails exec master..xp_getfiledetails @filename
set @size=(select size from #filedetails)
if @size=null
BEGIN
PRINT 'File error.'
RETURN
END
drop table #filedetails
SET @[email protected]/8000+1
--start dynamic SQL declaration
SET @cmd='DECLARE @ptrval binary(16), @fso int, @file int, @i int, @j int, @hr int, @buffer varbinary(8000) '
SET @[email protected]+' EXEC @hr = sp_OACreate ''ADODB.Stream'', @file OUT '
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Open'' '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''Type'', 1 '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''LoadFromFile'','''[email protected]+''''
SET @[email protected]+' SELECT @ptrval = TEXTPTR('[email protected]+') FROM '[email protected]+' '[email protected]+' '

SET @[email protected]+' exec @hr = sp_oamethod @file, ''read'',  @buffer out , 8000 '
if @blocks=1
 BEGIN
 --remove FFFE if necessary
 SET @[email protected]+'if left(@buffer,2)=0xFFFE '
 SET @[email protected]+' begin '
 SET @[email protected]+' declare @t varbinary(8000), @ti int '
 SET @[email protected]+' set @ti=4 '
 SET @[email protected]+' SET @t=SUBSTRING (@buffer,3,1) '
 SET @[email protected]+' WHILE @ti<=len(@buffer) '
 SET @[email protected]+'  BEGIN '
 SET @[email protected]+'  SET @[email protected]+SUBSTRING (@buffer,@ti,1) '
 SET @[email protected]+'  set @[email protected]+1 '
 SET @[email protected]+'  END '
 SET @[email protected]+' set @[email protected] '
 SET @[email protected]+' END '
 SET @[email protected]+' WRITETEXT '[email protected]+'.'[email protected]lumn+' @ptrval @buffer '
 END
else
 BEGIN
 --remove FFFE if necessary
 SET @[email protected]+'if left(@buffer,2)=0xFFFE '
 SET @[email protected]+' begin '
 SET @[email protected]+' declare @t varbinary(8000), @ti int '
 SET @[email protected]+' set @ti=4 '
 SET @[email protected]+' SET @t=SUBSTRING (@buffer,3,1) '
 SET @[email protected]+' WHILE @ti<=len(@buffer) '
 SET @[email protected]+'  BEGIN '
 SET @[email protected]+'  SET @[email protected]+SUBSTRING (@buffer,@ti,1) '
 SET @[email protected]+'  set @[email protected]+1 '
 SET @[email protected]+'  END '
 SET @[email protected]+' set @[email protected] '
 SET @[email protected]+' END '
 SET @[email protected]+' WRITETEXT '[email protected]+'.'[email protected]+' @ptrval @buffer '
 SET @[email protected]+'set @j='+str(@blocks-1)+' while @j>0 begin '
 SET @[email protected]+' set @i=(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+') '
 SET @[email protected]+' UPDATETEXT  '[email protected]+'.'[email protected]+' @ptrval @i 0 @buffer'
 SET @[email protected]+'set @[email protected] end '
END

--execute dynamic SQL
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Close'' '
SET @[email protected]+'EXEC @hr = sp_OADestroy @file EXEC @hr = sp_OADestroy @fso'
exec( @cmd)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE readNtextFromfileBigEndian @filename nvarchar(128)=NULL, @table nvarchar(128)=NULL,@column nvarchar(128)=NULL, @where nvarchar(2000)=NULL
--Reads Unicode Big Endian from a file into an Ntext column replacing the original contents
AS
SET NOCOUNT ON
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'readImageFromfile reads data from a file into an image column'
PRINT 'Usage:'
PRINT 'EXEC readImageFromfile FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC readNtextFromfileBigEndian ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
RETURN
END
DECLARE @i int, @size int, @blocks int, @cmd varchar(8000)
create table #filedetails(altname varchar(30),size int,createdate varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime varchar(32),
lastaccessdt varchar(30),lastaccesstime varchar(32),attributes int)
insert into #filedetails exec master..xp_getfiledetails @filename
set @size=(select size from #filedetails)
if @size=null
BEGIN
PRINT 'File error.'
RETURN
END
drop table #filedetails
SET @[email protected]/8000+1
--start dynamic SQL declaration
SET @cmd='DECLARE @ptrval binary(16), @fso int, @file int, @i int, @j int, @hr int, @buffer varbinary(8000) '
SET @[email protected]+' EXEC @hr = sp_OACreate ''ADODB.Stream'', @file OUT '
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Open'' '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''Type'', 1 '
SET @[email protected]+' EXEC @hr = sp_oasetproperty @file, ''LoadFromFile'','''[email protected]+''''
SET @[email protected]+' SELECT @ptrval = TEXTPTR('[email protected]+') FROM '[email protected]+' '[email protected]+' '

SET @[email protected]+' exec @hr = sp_oamethod @file, ''read'',  @buffer out , 8000 '
if @blocks=1
 BEGIN
 --turn Big Endian into Little Endian
 SET @[email protected]+' declare @t varbinary(8000), @ti int'
 SET @[email protected]+' if left(@buffer,2)=0xFFFE'
 SET @[email protected]+' BEGIN'
 SET @[email protected]+' PRINT ''Error! This Unicode is “Little Endian”!'''
 SET @[email protected]+' return'
 SET @[email protected]+' END '
 SET @[email protected]+' if left(@buffer,2)=0xFEFF'
 SET @[email protected]+' BEGIN'
 SET @[email protected]+' SET @t=SUBSTRING (@buffer,4,1)+SUBSTRING (@buffer,3,1)'
 SET @[email protected]+' set @ti=5'
 SET @[email protected]+' END '
 SET @[email protected]+' else'
 SET @[email protected]+' BEGIN'
 SET @[email protected]+' SET @t=SUBSTRING (@buffer,2,1)+SUBSTRING (@buffer,1,1)'
 SET @[email protected]+' set @ti=3'
 SET @[email protected]+' END'
 SET @[email protected]+' WHILE @ti<=len(@buffer)'
 SET @[email protected]+'  BEGIN'
 SET @[email protected]+'  SET @[email protected]+SUBSTRING (@buffer,@ti+1,1)+SUBSTRING (@buffer,@ti,1)'
 SET @[email protected]+'  set @[email protected]+2'
 SET @[email protected]+'  END'
 SET @[email protected]+' set @[email protected] '
 SET @[email protected]+' WRITETEXT '[email protected]+'.'[email protected]+' @ptrval @buffer '
 END
else
 BEGIN
 --turn Big Endian into Little Endian
 SET @[email protected]+' declare @t varbinary(8000), @ti int'
 SET @[email protected]+' if left(@buffer,2)=0xFFFE'
 SET @[email protected]+' BEGIN'
 SET @[email protected]+' PRINT ''Error! This Unicode is “Little Endian”!'''
 SET @[email protected]+' return'
 SET @[email protected]+' END '
 SET @[email protected]+' if left(@buffer,2)=0xFEFF '
 SET @[email protected]+' BEGIN'
 SET @[email protected]+' SET @t=SUBSTRING (@buffer,4,1)+SUBSTRING (@buffer,3,1)'
 SET @[email protected]+' set @ti=5'
 SET @[email protected]+' END '
 SET @[email protected]+' else '
 SET @[email protected]+' BEGIN'
 SET @[email protected]+' SET @t=SUBSTRING (@buffer,2,1)+SUBSTRING (@buffer,1,1)'
 SET @[email protected]+' set @ti=3'
 SET @[email protected]+' END'
 SET @[email protected]+' WHILE @ti<=len(@buffer)'
 SET @[email protected]+'  BEGIN'
 SET @[email protected]+'  SET @[email protected]+SUBSTRING (@buffer,@ti+1,1)+SUBSTRING (@buffer,@ti,1)'
 SET @[email protected]+'  set @[email protected]+2'
 SET @[email protected]+'  END'
 SET @[email protected]+' set @[email protected]'
 SET @[email protected]+' WRITETEXT '[email protected]+'.'[email protected]+' @ptrval @buffer '
 SET @[email protected]+' set @j='+str(@blocks-1)+' while @j>0 begin '
 SET @[email protected]+' set @i=(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+') '
 SET @[email protected]+' UPDATETEXT  '[email protected]+'.'[email protected]+' @ptrval @i 0 @buffer'
 SET @[email protected]+' set @[email protected] end '
END

--execute dynamic SQL
SET @[email protected]+' EXEC @hr = sp_OAMethod @file, ''Close'' '
SET @[email protected]+'EXEC @hr = sp_OADestroy @file EXEC @hr = sp_OADestroy @fso'
print @cmd
exec( @cmd)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE readobj (@filename nvarchar(128)=NULL)
--Reads the text of a rule, default, unencrypted stored procedure, user-defined function, trigger, or view from a file
as
SET NOCOUNT ON
IF (@filename is NULL)
BEGIN
PRINT 'readobj executes the SQL code contained in a file, allowing INSERT, UPDATE,  CREATE, etc...  statements that can be longer than 8kb'
PRINT 'Usage:'
PRINT 'EXEC readobj FileName'
PRINT ''
PRINT 'For example: EXEC readobj ''C:\sp_hello.sql'''
RETURN
END
DECLARE @i int, @size int, @blocks int, @cmd varchar(8000)
create table #filedetails(altname varchar(30),size int,createdate varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime varchar(32),
lastaccessdt varchar(30),lastaccesstime varchar(32),attributes int)
insert into #filedetails exec master..xp_getfiledetails @filename
set @size=(select size from #filedetails)
drop table #filedetails
SET @[email protected]/4000+1
--create all temporary variables
SET @cmd='DECLARE @fso int, @file int, @hr int, '
SET @i=1
WHILE @i<[email protected]
BEGIN
SET @[email protected]+'@v'+CONVERT(NVARCHAR(9),@i)+' nvarchar(4000), '
SET @[email protected]+1
END
SET @cmd=LEFT(@cmd,len(@cmd)-1)
SET @[email protected]+' EXEC @hr = sp_OACreate ''Scripting.FileSystemObject'', @fso OUT '
SET @[email protected]+'exec sp_oamethod @fso, ''opentextfile'', @file out, '''[email protected]+''', 1 '
SET @i=1
--read data
WHILE @i<[email protected]
BEGIN
SET @[email protected]+'exec @hr = sp_oamethod @file, ''read'', '+'@v'+CONVERT(NVARCHAR(9),@i)+' out , 4000 '
SET @[email protected]+1
END
--execute SQL
SET @[email protected]+'exec('
SET @i=1
WHILE @i<[email protected]
BEGIN
SET @[email protected]+'@v'+CONVERT(NVARCHAR(9),@i)+'+ '
SET @[email protected]+1
END
SET @cmd=LEFT(@cmd,len(@cmd)-1)
SET @[email protected]+') '
SET @[email protected]+'EXEC @hr = sp_OADestroy @file EXEC @hr = sp_OADestroy @fso'
exec( @cmd)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE saveImage2file @filename nvarchar(128), @table nvarchar(128),@column nvarchar(128), @where nvarchar(4000)
--Saves an image from an image column to a file
AS
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'saveImage2file saves an image from an image column to a file'
PRINT 'Usage:'
PRINT 'EXEC saveImage2file FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC saveImage2file ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
RETURN
END
DECLARE @hr int,@fso int,@i int, @j int, @blocks int, @c int, @v varbinary(10), @buffer varbinary(1000), @sql nvarchar(4000)
EXEC @hr = sp_OACreate 'ADODB.Stream', @fso OUT
exec @hr = sp_oasetproperty @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+')'
exec sp_executesql @sql, N'@c int OUTPUT', @c OUTPUT
set @j=0
create table #t ( t image )
SET @[email protected]
--read 1000 bytes at a time
WHILE @i>1000
BEGIN
insert into #t
exec getREADTEXT @column,@table,@where,@j,1000
set @buffer=(select convert(varbinary(1000),t) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
SET @[email protected]
SET @[email protected]+1000
END
--read remaining bytes
if @i>0
BEGIN
insert into #t
exec getREADTEXT @column,@table,@where,@j,@i
set @buffer=(select convert(varbinary(1000),t) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
END
drop table #t
EXEC @hr = sp_oasetproperty @fso, 'SaveToFile', @filename
EXEC @hr = sp_OAMethod @fso, 'Close'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE saveImage2fileSTR @filename nvarchar(128), @table nvarchar(128),@column nvarchar(128), @where nvarchar(4000)
--Saves an image from an image column to a file
AS
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'saveImage2file saves an image from an image column to a file'
PRINT 'Usage:'
PRINT 'EXEC saveImage2file FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC saveImage2fileSTR ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
RETURN
END
DECLARE @hr int,@fso int,@i int, @j int, @blocks int, @c int, @v varbinary(10), @buffer varbinary(1000), @sql nvarchar(4000)
EXEC @hr = sp_OACreate 'ADODB.Stream', @fso OUT
exec @hr = sp_oasetproperty @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+')'
exec sp_executesql @sql, N'@c int OUTPUT', @c OUTPUT
set @j=0
create table #t ( t image )
SET @[email protected]
--read 1000 bytes at a time
WHILE @i>1000
BEGIN
set @sql = N'select SUBSTRING('[email protected]+','+str(@j+1)+',1000) from '[email protected]+'  '[email protected]
insert into #t
exec(@sql)
--exec getREADTEXT @column,@table,@where,@j,1000
set @buffer=(select convert(varbinary(1000),t) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
SET @[email protected]
SET @[email protected]+1000
END
--read remaining bytes
if @i>0
BEGIN
set @sql = N'select SUBSTRING('[email protected]+','+str(@j+1)+','+str(@i)+') from '[email protected]+'  '[email protected]
insert into #t
exec(@sql)
--exec getREADTEXT @column,@table,@where,@j,@i
set @buffer=(select convert(varbinary(1000),t) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
END
drop table #t
EXEC @hr = sp_oasetproperty @fso, 'SaveToFile', @filename
EXEC @hr = sp_OAMethod @fso, 'Close'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE saveNtext2file @filename nvarchar(128), @table nvarchar(128),@column nvarchar(128), @where nvarchar(4000)
--Saves text from an Ntext column to a file
AS
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'saveNtext2file saves text from an Ntext column to a file'
PRINT 'Usage:'
PRINT 'EXEC saveNtext2file FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC saveNtext2file ''C:\test.txt'', ''customers'', ''memo'', ''where customerID=234'''
RETURN
END
DECLARE @hr int,@fso int,@i int, @j int, @blocks int, @c int,  @buffer varbinary(1000), @sql nvarchar(4000)
EXEC @hr = sp_OACreate 'ADODB.Stream', @fso OUT
exec @hr = sp_oasetproperty @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('[email protected]+')/2 from '[email protected]+' '[email protected]+')'
exec sp_executesql @sql, N'@c int OUTPUT', @c OUTPUT
set @j=0
create table #t ( t ntext )
SET @[email protected]
--read 1000 bytes at a time
WHILE @i>1000
BEGIN
insert into #t
exec getREADTEXT @column,@table,@where,@j,1000
set @buffer=(select convert(varbinary(1000),convert(nvarchar(500),t)) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
SET @[email protected]
SET @[email protected]+1000
END
--read remaining bytes
if @i>0
BEGIN
insert into #t
exec getREADTEXT @column,@table,@where,@j,@i
set @buffer=(select convert(varbinary(1000),convert(nvarchar(500),t)) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
END
drop table #t
EXEC @hr = sp_oasetproperty @fso, 'SaveToFile', @filename
EXEC @hr = sp_OAMethod @fso, 'Close'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE saveobj (@filename nvarchar(128)=NULL, @object nvarchar(128)=NULL)
AS
--Saves the text of a rule, default, unencrypted stored procedure, user-defined function, trigger, or view to file
SET NOCOUNT ON
IF (@filename is NULL) OR  (@object is NULL)
BEGIN
PRINT 'saveobj saves the text of a rule, default, unencrypted stored procedure, user-defined function, trigger, or view to a file that can be longer than 8kb'
PRINT 'Usage:'
PRINT 'EXEC saveobj FileName, ObjectName'
PRINT ''
PRINT 'For example: EXEC saveobj ''C:\sp_hello.sql'', ''sp_hello'''
RETURN
END

DECLARE  @current_proc nvarchar(128),
 @current_text nvarchar(4000), @line nvarchar(4000)
DECLARE @fso int
DECLARE @file int
DECLARE @hr int

--location of output file
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
EXEC @hr = sp_OAMethod @fso, 'CreateTextFile', @file OUT, @filename, true

create table #temp(s nvarchar(4000))
insert #temp
exec sp_helptext @object
--get text from object
DECLARE _Cursor CURSOR FOR
select * from #temp
OPEN _Cursor
FETCH NEXT FROM _Cursor
INTO @current_text
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_OAMethod @file, 'Write', null, @current_text
--write to file
FETCH NEXT FROM _Cursor INTO  @current_text
END
CLOSE _Cursor
DEALLOCATE _Cursor
drop table #temp
EXEC @hr = sp_OADestroy @file
EXEC @hr = sp_OADestroy @fso
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE saveText2file @filename nvarchar(128), @table nvarchar(128),@column nvarchar(128), @where nvarchar(4000)
--Saves text from a text column to a file
AS
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
BEGIN
PRINT 'saveText2file saves text from a text column to a file'
PRINT 'Usage:'
PRINT 'EXEC saveText2file FileName, tableName, columnName, WhereCondition'
PRINT ''
PRINT 'For example: EXEC saveText2file ''C:\test.txt'', ''customers'', ''memo'', ''where customerID=234'''
RETURN
END
DECLARE @hr int,@fso int,@i int, @j int, @blocks int, @c int,  @buffer varbinary(1000), @sql nvarchar(4000)
EXEC @hr = sp_OACreate 'ADODB.Stream', @fso OUT
exec @hr = sp_oasetproperty @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('[email protected]+') from '[email protected]+' '[email protected]+')'
exec sp_executesql @sql, N'@c int OUTPUT', @c OUTPUT
set @j=0
create table #t ( t text )
SET @[email protected]
--read 1000 bytes at a time
WHILE @i>1000
BEGIN
insert into #t
exec getREADTEXT @column,@table,@where,@j,1000
set @buffer=(select convert(varbinary(1000),convert(varchar(1000),t)) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
SET @[email protected]
SET @[email protected]+1000
END
--read remaining bytes
if @i>0
BEGIN
insert into #t
exec getREADTEXT @column,@table,@where,@j,@i
set @buffer=(select convert(varbinary(1000),convert(varchar(1000),t)) from #t)
EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
delete #t
END
drop table #t
EXEC @hr = sp_oasetproperty @fso, 'SaveToFile', @filename
EXEC @hr = sp_OAMethod @fso, 'Close'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_hello AS
print 'Hello world'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION  testequality (@a ntext, @b ntext)
--returns 1 if the comparison result is true
RETURNS bit AS  
BEGIN
declare @temp_bit bit
if  @a like @b
set @temp_bit= 1
else
set @temp_bit=0
return @temp_bit
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION  testlength (@a ntext, @b ntext)
--returns true if both inputs have the same length
RETURNS bit AS  
BEGIN
declare @temp_bit bit
if datalength(@a)=datalength(@b)
set @temp_bit= 1
else
set @temp_bit=0
return @temp_bit
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

تم تعديل بواسطه imadouzoun
0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه

أم أمثلة على استخدام هذه الإجرائيات في أمثلة فهي :

--TEXTPTR sample
create table #t (n ntext)
insert #t values('abcdef')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
print @ptrval
drop table #t

--TEXTVALID sample
create table #t (n ntext)
insert #t values('abxyef')
DECLARE @ptrval binary(16), @ptrval2 binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
if TEXTVALID('#t.n',@ptrval)=1
print '@ptrval has a valid text pointer.'
else
print '@ptrval has an invalid text pointer.'
if TEXTVALID('#t.n',@ptrval2)=1
print '@ptrval2 has a valid text pointer.'
else print '@ptrval2 has an invalid text pointer.'
drop table #t

--SET TEXTSIZE sample
create table #t (n ntext)
insert #t values('abcdefghijk')
SET TEXTSIZE 10--ntext is unicode and so each character takes 2 bytes
select * from #t
SET TEXTSIZE 20--ntext is unicode and so each character takes 2 bytes
select * from #t
drop table #t

[email protected]@TEXTSIZE sample
SET TEXTSIZE 10--ntext is unicode and so each character takes 2 bytes
print @@TEXTSIZE
SET TEXTSIZE 20--ntext is unicode and so each character takes 2 bytes
print @@TEXTSIZE

--WRITETEXT sample
create table #t (n ntext)
insert #t values('abc')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n)
FROM #t
WRITETEXT #t.n @ptrval 'def'
select * from #t
drop table #t

--UPDATETEXT sample only insertion
create table #t (n ntext)
insert #t values('bd')
DECLARE @ptrval binary(16), @i int
SELECT @ptrval = TEXTPTR(n)
FROM #t
UPDATETEXT #t.n @ptrval 0 0 'a'--insert at the beggining
select * from #t
UPDATETEXT #t.n @ptrval 2 0 'c'--insert in the middle
select * from #t
set @i=(select DATALENGTH(n) from #t)/2--/2 only if ntext because unicode takes 2 bytes
print @i
UPDATETEXT #t.n @ptrval @i 0 'e'--insert at the end
select * from #t
drop table #t

--UPDATETEXT sample deletion+insertion
create table #t (n ntext)
insert #t values('abxyef')
DECLARE @ptrval binary(16), @i int
SELECT @ptrval = TEXTPTR(n)
FROM #t
UPDATETEXT #t.n @ptrval 2 2 'cd'--insert 2 and delete other 2 characters
--starting at position 2
select * from #t
drop table #t

--READTEXT sample
create table #t (n ntext)
insert #t values('abcdefghijk')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
READTEXT #t.n @ptrval 3 8--read 8 characters starting at position 3
drop table #t

--DATALENGTH sample
create table #t (n ntext)
insert #t values('1234567890')
DECLARE @i int
set @i=(select DATALENGTH(n) from #t)--it should return the lenght in bytes=2*UNICODE length
PRINT @i
drop table #t

--PATINDEX sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @i int
SELECT PATINDEX('%tim%', n) FROM #t
SELECT PATINDEX('%time%', n) FROM #t
drop table #t

--CONVERT sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @c nvarchar(5)
SET @c=(select convert(nvarchar(5),n) from #t)
print @c
drop table #t

--CONVERT sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @c nvarchar(5)
SET @c=(select CAST ( n  AS nvarchar(5) ) from #t)
print @c
drop table #t

--saveobj sample, saving object hello
exec saveobj 'c:\hello.sql', 'hello'

--readobj sample, reading object hello
exec readobj 'c:\hello.sql'

--saveImage2file sample
exec saveImage2file 'c:\Category1.bak', 'Northwind..Categories', 'Picture', 'where categoryid=1'

--saveText2file sample
create table ##t (n text)
insert ##t values('Hello Tim, long time no see!')
EXEC saveText2file 'c:\test.txt', '##t','n', ''
drop table ##t

--saveNtext2file sample
create table ##t (n ntext)
insert ##t values('Hello Tim, long time no see!')
EXEC saveNtext2file 'c:\test.txt', '##t','n', ''
drop table ##t

--readImageFromfile sample
--reading a text column from a file
create table ##t (n text)
insert ##t values('Hello Tim, long time no see!')
EXEC readImageFromfile 'c:\hello.txt', '##t','n', ''
select * from ##t
drop table ##t
-------------
--readNtextfromfile sample
create table ##t (n ntext)
insert ##t values('Hello Tim, long time no see!')
exec readNtextFromfile 'c:\t.txt', '##t','n', ''
select * from ##t
drop table ##t

كلمة أخيرة :

وبذلك نكون قد أنهينا بعون الله تعالى القسم الأول من التعامل مع أنواع البيانات في SqlServer أدعو الله كي يجعل فيه الفائدة لجميع الإخوة .

لا إله إلا الله محمد رسول الله .. لبيك يا رسول الله

نعيب زماننا و العيب فينا و ما لزماننا عيب سوانا

0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه

السلام عليكم ورحمة الله

بارك الله فيك على المجهود المذول في هذا المثال

فعلاً مثال مفيد قمت بتجربة أغلب الإجراءات الموجودة فيه وأود إضافة ملاحظة صغيرة حول الإجراء الخاص بحفظ image في حقل من نوع image

ألا وهي :

يجب تعبئة الحقل من نوع image بأي صورة عن طريق جملة Insert قبل استعمال جملة WRITETEXT وجملة UPDATETEXT

أي يجب أن لاتكون قيمة الحقل Null وإلا سيعطي الخطأ التالي :

Server: Msg 7133, Level 16, State 2, Line 1

NULL textptr (text, ntext, or image pointer) passed to WriteText function.

0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه
زوار
This topic is now closed to further replies.

  • يستعرض القسم حالياً   0 members

    لا يوجد أعضاء مسجلين يشاهدون هذه الصفحة .