SQL save and return Base64 image

Asked

Viewed 671 times

2

I am saving an image in SQL Server using the varbinary field(max).

However, when I run the query to return to the front, I can only return by converting the field to sweep.

My question is how to read the returned string, and if this is the best way to do it.

The string saved in my database as varbinary is "0x646174613A696D6167652F6A7065673B6261736536342C2F396A2F346749" and what returns to the front when I convert to varchar is "data:image/jpeg;Base64,/9j/4gI", for example.

Thanks in advance!

I noticed that the problem itself is when I try to return the value using ADVPL, I include the field in select, but when I try to return it using: variable := qry->fieldbase64, it says that it cannot locate the alias "fieldbase64", and if I remove this line the code runs normally.

sql sql-server tsql base64 advpl

  • I changed the way saved to varchar(max), now the image string is stored as follows: "data:image/jpeg;Base64,/9j/4gIcSUNDX1BST0ZJ...", when I try to return giving a select in the column to the front returns a message "invalid field name...", if I remove the select value and blank return goes without problem.

1 answer

2


The Dbaccess - Relational Data Access Gateway -- used by Protheus to read and write data in the approved Databases -- until recently did not allow reading LOB or similar fields directly by Query. A field of the kind varchar(max) MSSQL is used in Advpl to emulate a field of the type "M" Memo Advpl, which allows recording binary content, as is the case with the image.

If I am not mistaken, in the new Build of Protheus 12, named "Lobo Guará", using the EMBEDDED SQL -- feature of writing a Query directly inside an Advpl source -- it is already possible to read directly a field of this type by Query.

For previous builds, the only solution is to recover a search key from the registry in the Query -- such as the field R_E_C_N_O_ table -- and position in the corresponding record using the ALIAS of the open table in ISAM compatibility mode -- traditional mode of table openings of the ERP Microsiga -- and read the "M" Memo field using syntax ALIAS->CAMPO.

Although it is possible to do a Query with CAST(), to bypass Dbaccess protections and return a fixed size string, you usually have Jum size limitation - for example it will not be possible in MSSQL to bring more than 8 KB of data from this field.

  • Thanks for the help! That’s exactly what I needed.

Browser other questions tagged sql sql-server tsql base64 advpl

You are not signed in. Login or sign up in order to post.