B4J Question Passing a binary array or blob to SQL Server stored procedure

[aregan]

Hi

I'm using JServer and JSQL connectionpool to connect to and execute stored procedures.
This has been working well so far, however up until now I've just been passing strings or integers as parameters to the stored procedures.

I now need to store a photo in my SQL Server database and would like to pass the photo blob / binaryarray to the stored procedure which will also handle the insertion of the record with the photo file contents in a record in the database.

        If sql.IsInitialized Then
        
            Dim storedproc As CallProc
            storedproc.ProcedureCall = "exec mobileApp_UploadIncidentPhoto ?, ?, ?, ?"
            storedproc.AddInputParameter(1, storedproc.SQLBIGINT, Params.Get("incidentid") )
            storedproc.AddInputParameter(2, storedproc.SQLNCHAR, Params.Get("filename") )
            storedproc.AddInputParameter(3, storedproc.SQLBIGINT, Params.Get("index") )
            storedproc.AddInputParameter(4, storedproc.SQLBINARY, bmpByteArray )
            
            storedproc.ExecProcedure(sql)

        End If

As per the this is what we would typically call, however the 3rd parameter i nthe AddInputParameter call seems to take strings only and therefore doesn't like our bmpBtyeArray.

Do you know how we can pass a byte array to a stored proc?

 

[Erel]

Where does CallProc comes from? Try to use SQL.CreateCallStatement instead.

 

[aregan]

Where does CallProc comes from? Try to use SQL.CreateCallStatement instead.

Thanks Erel

I was using the SQLCallProc library for all my calls to MS Sql server.
https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/#content


I'll take a look at using SQL lib to connect to the MS SQL server and call the CreateCallStatement

 

[Alex_197]

Hi

I'm using JServer and JSQL connectionpool to connect to and execute stored procedures.
This has been working well so far, however up until now I've just been passing strings or integers as parameters to the stored procedures.

I now need to store a photo in my SQL Server database and would like to pass the photo blob / binaryarray to the stored procedure which will also handle the insertion of the record with the photo file contents in a record in the database.

        If sql.IsInitialized Then
       
            Dim storedproc As CallProc
            storedproc.ProcedureCall = "exec mobileApp_UploadIncidentPhoto ?, ?, ?, ?"
            storedproc.AddInputParameter(1, storedproc.SQLBIGINT, Params.Get("incidentid") )
            storedproc.AddInputParameter(2, storedproc.SQLNCHAR, Params.Get("filename") )
            storedproc.AddInputParameter(3, storedproc.SQLBIGINT, Params.Get("index") )
            storedproc.AddInputParameter(4, storedproc.SQLBINARY, bmpByteArray )
           
            storedproc.ExecProcedure(sql)

        End If

As per the this is what we would typically call, however the 3rd parameter i nthe AddInputParameter call seems to take strings only and therefore doesn't like our bmpBtyeArray.

Do you know how we can pass a byte array to a stored proc?

May I ask you - why do you want to store a photo in MS SQL Server table but not just a file name and send a photo itself via FTP?

 

[aregan]

May I ask you - why do you want to store a photo in MS SQL Server table but not just a file name and send a photo itself via FTP?

Hi Alex

To answer your question, the app I'm working on is intending to work with existing systems and other software and reporting systems that are already accessing the tables the images currently stored in the SQL Server using other processes. So, I guess its for legacy reasons I want to store the data where the other systems expect to find it.

On a personal note, I've traditionally always stored images relating to other data in SQL Server. I guess I just like having all the data stored in the same database and backed up together. Maybe I'm wrong too, but I would also be concerned about another process or user accidently deleting the image files from a network share or other (I know I can easily set permissions, but I guess allowing some processes to read or write to those image files opens the door to accidental deletion).