Problem with saving a binary file in an SQL server database with LotusScrip

HCL Domino Domino Forum
1

Hi Everybody,

I need to write in an SQL Server database from a LotusScript agent.

I use ADO objects to do that and it works fine except for binary data.

The agent needs to save a file in a binary field of the SQL Server database and I used the following code for that :

Const YOUR_FILENAME_HERE = ""



Dim cn As Variant

Dim rs As Variant



' Variable declarations

Dim FileLength As Variant

Dim NumBlocks As Variant

Dim leftover As Variant

Dim inBuff As Variant, outBuff() As Byte



'VB Constants

Const adOpenKeyset = 1

Const adLockOptimistic = 3

Const adTypeBinary = 1

Const adSaveCreateOverWrite = 2

Const adBlocksize = 4096



'Connect to the serve server

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")



' Open provider

Call cn.Open("Provider=SQLOLEDB;data Source=SOURCE;Database=DATABASE;User Id=USER_NAME;Password=PASSWORD")



Dim session As New NotesSession



Dim stream As NotesStream



Set stream = session.CreateStream

Call stream.open( YOUR_FILENAME_HERE )



Dim file_length As Long

file_length = stream.Bytes



If file_length > 0 Then

	Dim num_blocks As integer

	num_blocks = Int(file_length / adBlocksize)

	Dim left_over As Integer

	left_over = file_length Mod adBlocksize



	Call rs.Open("SELECT TOP 1 * FROM Documents", cn, adOpenKeyset, adLockOptimistic)



	rs.AddNew

	rs.Fields("dsize").Value = file_length

	rs.Fields("dfilename").Value = YOUR_FILENAME_HERE



	' Get blocks of the file

	ReDim outBuff(adBlocksize)

	Dim block_num As Long

	Dim counter As Integer

	For block_num = 1 To num_blocks

		' Get data into a variant

		inBuff = stream.Read(adBlocksize)



		' Move to byte array

		counter = 0

		ForAll b In inBuff

			outBuff(counter) = b

			counter = counter + 1

		End ForAll



		' Save to field

		rs.Fields("dimage").AppendChunk(outBuff)

	Next block_num



	' Get the remainder of the file

	If left_over > 0 Then

		inBuff = stream.Read(left_over)



		' Move to byte array

		counter% = 0

		ReDim outBuff(left_over)

		ForAll b In inBuff

			outBuff(counter%) = b

			counter% = counter% + 1

		End ForAll



		' Save to field

		rs.Fields("dimage").AppendChunk(outBuff)

	End If



	rs.Update

	Call rs.Close

End If



Call cn.Close

Set stream = Nothing

Set session = Nothing

This code is not mine. Unfortunatly, I forgot to note where I found it…

The problem is that a Null (0) character is inserted between each Chunk sent to the database. As well as at the end of the file.

Does anybody has an idea of what to do to prevent that problem ?

Thank you for your help,

Pat