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