Inserting BLOB into external SQL table

tentacletentacle Member Posts: 27
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

Comments

  • tentacletentacle Member Posts: 27
    Solved.
  • tinoruijstinoruijs Member Posts: 1,226
    tentacle wrote:
    Solved.

    Could you share how you solved it?
    Thanks.

    Tino Ruijs
    Microsoft Dynamics NAV specialist
  • tentacletentacle Member Posts: 27
    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;
  • tinoruijstinoruijs Member Posts: 1,226
    Thanks for sharing! =D>

    Tino Ruijs
    Microsoft Dynamics NAV specialist
  • Rpintoa16Rpintoa16 Member Posts: 1
    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
Sign In or Register to comment.