Base64_encode

[bjanker77]

Hi.

One of our customers wants to embedd an PDF file in an EHF Invoice XML file. And they need it to be BASE64 encoded. So I've read the file into a blob and are trying to encode it using SQL Anywhere 16's builtin base64_encode(string) function. The PDF file is 92344 bytes, and the blob has that length after this command (PowerBuilder code):

long ll_file,ll_ret
blob lb_file
string ls_enc_string

ll_file = FileOpen('D:\\test\\Invoice_05022013090646.pdf',TextMode!)
ll_ret = FileReadEx(ll_file,lb_file) //ll_ret = 92344
FileClose(ll_file)
ll_ret = len(lb_file) //ll_ret = 92344

Then I try to base64 encode it using this command:

select base64_encode(:lb_file)
into :ls_enc_string
from dummy;

The command executes without error, but the string containing the BASE64 encoded PDF is only 32767 bytes, and should be closer to 120.000 (due to some overhead in the BASE64 encoding). The VARCHAR datatype in SQL Anywhere is 32767 long, but the base64_encode() function is supposed to return a long varchar string, which is approx 2 GB in size. (I've also tried to insert the data into a #temp table, but the the encoded string is still only 32767).

Can anyone see if there is something I'm doing wrong? Or if there is another approach to solving this issue? I've looked into running certutil, which comes with Windows, but I would really like to not have to rely on a commandline-based solution. I'm thinking user rights and so on.

[MarkCulp · Accepted solution]

I'm not a PowerBuilder user but a quick google search pointed me to this post that indicates that PowerBuilder has a limit of 32K when fetching strings. You need to fetch the result using "select blob" (whatever that means?)

HTH

[chris_keating · Accepted solution]

The commands SELECTBLOB and UPDATEBLOB are PowerBuilder embedded SQL that manipulate data that is 32k or larger interacting with the database. I believe that both UPDATEBLOB and SELECTBLOB with an intermediate table in SA will be needed to solve this.

[bjanker77 · Accepted solution]

SELECTBLOB works like a charm. The base64 encoded file matches the files I get from several online base64 encoders.

Thanks a lot!

Bjarne

[Vlad · Accepted solution]

I'd like to see the file that has different base64 presentations 🙂