Expand News
IS Administration
Expand MMC
Expand Com Objects
Expand IDA/IDQ
IS Programming
Expand IS Programming
IS TroubleShooting
Expand Indexing
Expand Querying
IS Optimizing
Expand Querying
Expand Indexing
IS Links
Expand MSDN
Expand TechNet
Expand Other MS
Expand Other Links
iFilters
Expand iFilters
SQL 2000 FTS
Expand Loading Data
ADO
TextCopy
Expand Indexing
Expand Querying
Expand SQL FTS Links
SQL 2005 FTS
Expand What's New

Index Server Faq

Loading text data into your database using ADO

For smaller amounts of text data you can insert data using TSQL insert and update statements. Since SQL 7, you do not need to insert data into the text datatype as a two step process; it can now done in a single insert. If you are inserting larger amounts of data into your tables you are FTI you will need to use the Stream Object of ADO or the AppendChunk method. I find the AppendChunk method too clunky to consider using, I prefer to use the stream object.

Here is some sample code to load text data into a text field.

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.RecordSet")

Set objStream=CreateObject("ADODB.Stream")

objConn.Open "Provider=SQLOLEDB;data Source=ServerName;Initial Catalog= DataBaseName;UserId=Account;Password=Password"

Set objFileSystem=createobject("Scripting.FileSystemObject")

Set objDir=objFileSystem.GetFolder("c:\htm")

for each objFile in objDir.Files

count=count+1

wscript.echo objFile.name

objConn.Execute "insert into TextTable (TextCol) values ('jibberish')"

objRs.Open "select textcol from texttable where pk=" &

count, objConn, 1, 3

objStream.Type = 2

objStream.Open

objStream.LoadFromFile objFile.Path

objRs.Fields("TextCol").Value=objStream.ReadText

objRs.Update

objRs.Close

objStream.Close

next

objConn.Close

Set objStream=nothing

Set objShell=nothing

Set objConn=nothing

Set objFileSystem=nothing

Set objDir=nothing

'here is some sample code to populate your image column

'with binary data

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.RecordSet")

Set objStream=CreateObject("ADODB.Stream")

objConn.Open "Provider=SQLOLEDB;data Source=ServerName;Initial Catalog= TEXT;UserId=Account;Password=Password"

Set objFileSystem=createobject("Scripting.FileSystemObject")

Set objDir=objFileSystem.GetFolder("c:\doc")

for each objFile in objDir.Files

count=count+1

wscript.echo objFile.name

objConn.Execute "insert into ImageTable (ImageCol) values ('jibberish')"

objRs.Open "select imageCol from Imagetable where pk=" &

count, objConn, 1, 3

objStream.Type = 1 '1 indicates binary, 2 text

objStream.Open

objStream.LoadFromFile objFile.Path

objRs.Fields("ImageCol").Value=objStream.Read

objRs.Update

objRs.Close

objStream.Close

next

objConn.Close

Set objStream=nothing

Set objShell=nothing

Set objConn=nothing

Set objFileSystem=nothing

Set objDir=nothing

This works well for DOS text files. The default for Win2k OSs and above is ANSI which you will have to convert to the file type to Unicode to have them index correctly. Here is what ANSI text files look like when it is pushed into a text table.

This won't index at all.

Here is a code sample that demonstrates creating the text files as Unicode and then pushing them into the text table.

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.RecordSet")

Set objStream=CreateObject("ADODB.Stream")

objConn.Open "Provider=SQLOLEDB;data Source=servername;Initial Catalog= TEXT;UserId=non_sa;Password=password"

Set objFileSystem=createobject("Scripting.FileSystemObject")

Set objDir=objFileSystem.GetFolder("c:\txt")

for each objFile in objDir.Files

s=objFileSystem.OpenTextFile(objFile.path).ReadAll

objFileSystem.CreateTextFile(objFile.path +".ANSI", TRUE, TRUE).Write s

count=count+1

objConn.Execute "insert into TextTable (TextCol) values ('jibberish')"

objRs.Open "select textcol from texttable where pk=" & count, objConn, 1, 3

objStream.Type=2

objStream.Open

objStream.LoadFromFile objFile.Path+".ansi"

objRs.Fields("TextCol").Value=objStream.ReadText

objRs.Update

objRs.Close

objStream.Close

next

objConn.Close

Set objStream=nothing

Set objShell =nothing

Set objConn=nothing

Set objFileSystem=nothing

Set objDir=nothing

 

designed by :: smilla group