Database- insert and export PDF files from an extern Server

[KKOCH]

Hey,

I want to insert PDF in my Database. I've found the possibility to do this with the BLOB File Format. Acutally my test server is an extern PC. 

I can insert files from the lokal PC by using this Query and the the execution VI from Labview:

NSERT INTO [dbo].[PDFTESt]
([PDF])
SELECT BulkColumn
FROM OPENROWSET( BULK 'C:\Users\SERVERCOMPUTER\Downloads\export_20201029-09_30_53.pdf', SINGLE_BLOB) as ExternalFile

but this do not work with the path from my own pc.

is there a way to insert Files from extern PC.

In the future  different user should be able to insert PDFs into the database. 

And it should  be possible download the files .

[pincpanter]

This is not a LabVIEW problem. If I correctly understood, you are submitting the query from the Client PC but the file path must be valid on the Server. Correct?

If the query must refer to a file local to the Server, I can see basically two options:

1) move the file to the server some way (e.g. by means of a FTP server or a read-write shared folder) and then insert it

2) setup a shared folder on the Client, let's suppose 'Shared_files', which could be read-only : '\\MYOWNPCNAME\Shared_files\export_20201029-09_30_53.pdf'

[KKOCH]

ok, but the other question:

I can save the PDF File into the Database as a varbinary File.

I am able to load the varbinary file with the "SELECT" Query. How can I transport this file into a PDF on the ClientPC ot just show the PDF on screen? 

[pincpanter]

I never worked with BLOB format fields. Are you able to get the BLOB binary content into a LabVIEW string?

If yes, the obvious suggestion is to store this string into a newly created file, name it as .pdf and possibly open it by means of the windows shell.

[KKOCH]

how can i save the string as a new file ?

[pincpanter]

Use Write to Binary File in the File I/O palette, wiring your string to the Data input. Wire False to prepend array or string size? (T).

[KKOCH]

If i try to insert the binary file i get theis error:

ADO Error: 0x80040E14
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->writebinarytodb.vi

wich convert function should I use for this?

[pincpanter]

On your first post you said you were able to insert. Did you change something?

Can you show your code?

[KKOCH]

i use this VI the problem is the conversion into a varbinary. if I try it with the CONVERT function i get a syntax error. 

and i cant copy the converted data into my DBMS and try it there. Does anyone have expierences with this?

[Mcarvalho]

Hello. Convert binary to base64.

Save it to varchar field. The lenght of varchar need to be >= lenght of encoded string.

Regards,

Manuel