Inserting BLOB into external SQL table

tentacle

Hello,

I am trying to do the following, not very successful until now. Maybe somebody can give me a hint.

Basically I want to create a XML file in Navision and store it into an external table.

1. I do not use XML ports, I am building the XML in a codeunit and store it into a Navision BLOB field. This is done already.

2. Now I want to copy / insert the BLOB field into a remote NON-Navision table on a SQL server.
This is not working as intended.

If I read binary from a file and insert it into the SQL table the field contains the binary characters which are not usable.

ADOStream.Type:=1;
ADOStream.Open;
ADOStream.LoadFromFile(idoc2filename);

ADORecSet.Fields.Item('XMLFIELD').Value :=ADOStream.Read;
ADORecSet.Update;

If I read as text, then the text seems to be too long. Error is (The length of the text string exceeds the size of the string buffer.)

ADOStream.Type:=2;
ADORecSet.Fields.Item('XMLFIELD').Value :=ADOStream.ReadText;

Are there other ways to do this?

Christian

tentacle

Solved.

tinoruijs

tentacle wrote:

Solved.

Could you share how you solved it?
Thanks.

tentacle

When I wrote "If I read binary from a file and insert it into the SQL table the field contains the binary characters which are not usable." in my first post I already had the solution.

I was not successful in writing the xml as text into the field on the SQL server, so I sticked to the binary. So the next process who is using this field has to decode the binary.

Here is the code I am using now. I divided it into two steps (insert and modify) and there was a reason for it but I do not remember it.


//Create SQL server entry without Blob

SQLString:='INSERT INTO Purchase_Order_Transfer......
ADORecSet.LockType:=3;
ADORecSet := ADOConnection.Execute(SQLString,RecordsAffected,RSOption);


//Now update Blob field

SQLString:='SELECT * FROM Purchase_Order_Transfer where......
ADORecSet.Open(SQLString,ADOConnection,2,3);

//read from file into stream
ADOStream.Type:=1;
ADOStream.Open;
ADOStream.LoadFromFile(idoc2filename);

//Update SQL entry
ADORecSet.Fields.Item('XMLFIELD').Value :=ADOStream.Read;
ADORecSet.Update;

tinoruijs

Thanks for sharing! =D>

Rpintoa16

Buen día estimado, tengo una consulta estoy tratando de insertar una imagen a unas BD de nav desde el sql y por transact lo realizó, pero al querer visualizarlo en el mismo navision me sale un error, sabes que debo hacer después se hacer la inserción