signature.png into binary data

[Gearcam]

This demo to save a signature as signature.png works great but i need to also upload the binary data from the signature into my sql server

I dont want to upload the actual image just the binary representation of the signature.

Any help ?

Steve

 

[Erel]

Are you decoding the base64 string into bytes in the server side?

 

[Gearcam]

Hi erel

Theire is no prossesing on the server it is just stored in a table in a MS SQL server in a column defined as "varbinary" length -1 which is infanite size i think

This is the same place the win mob saves to works fine

Steve

 

[Erel]

In that case you should add apostrophe before and after the string. Otherwise it will try to interpret the string as a number.

 

[Gearcam]

I have tried this before as below

job2.PostString(ServerUrl, "Insert INTO test ([ECOLsignature]) VALUES ('" & datastring & "')")

This is the error log

in (BufferedInputStream) java.io.BufferedInputStream@40e3e9b0
out (ByteArrayOutputStream)
3255
4340
10586667982119.................................
Bin OK
Error occurred. Query=Insert INTO test ([ECOLsignature]) VALUES ('10586667982119487571103111656565657883851046985103656565101656565657210367658965656566574976548665656565667278678386817367651037310265104107105656565687153748269708510174122116511005083111107111978178701051111164747108905076106106112116478610910311967901041149811790105667911610590879868497111050434950101119776510011887474310369651196685747765656966661036565103737765656966661116765656578658173656673676865656665818965657367686565666581896565736768656566658197651036565688169676565836510311965658169716565676510311965658169716565676510311965658169711037365656548666510365691037377656569666610365651037377656569666610365651037377656569666611167656565786581736566736768656566658189656573676865656665818965657367686565666581976510365656881696765658365103119656581697165656765103119656581697165656765103119656581697110373656565486665103656910373776565696666103656510373776565696666103656510373776565696666111676565657865817365667367686565666581896565736768656566658189656573676865656665819765103656568816967656583651031196565816971656567651031196565816971656567651031196565816971103736565654866651036569103737765656966661036565103737765656966661036565103737765656966661116765656578658173656673676865656665818965657367686565666581896565736768656566658197651036565688169676565836510311965658169716565676510311965658169716565676510311965658169711037365656548666510365691037377656569666610365651037377656569666610365651037377656569666611167656565786581736566736768656566658189656573676865656665818965657367686565666581976510365656881696765658365103119656581697165656765103119656581697165656765103119656581697110373656565486665103656910373776565696666103656510373776565696666103656510373776565696666111676565657865817365667367686565666581896565736768656566658189656573676865656665819765103656568816967656583651031196565816971656567651031196565816971656567651031196565816971103736565654866651036569103737765656966661036565103737765656966661036565103737765656966661116765656578658173656673676865656665818965657367686565666581896565736768656566658197651036565688169676565836510311965658169716565676510311965658169716565676510311965658169711037767102431036965474812284577970475150545154746965871207810343778655707143515053100655511886118511207810879659966104109471157210011110884771081196811680788812143561189848100795511276437479717790105651179883122828010210147887783891221031176510190121122104106100110521031207274996599119108881051175210648122817477821216965727811382881068867116121971064751436810952821041097811969120971101154886481064774110115110978510389526599119112110106117521069712212211869905211011074707648651201165599117112111568811147118694371507988112757010465109908997485057904711984101729011612211410387997081671227266991001084910954881121009787501037477657753100885288118881084711512154888887116896756671226666101679743884368431098984103796510198119847147114691096952661037210911573825187487377114102478668119674356481199966717590112101118108541221201048883901036811588851171225711468103486653104666911179101837879122767487104121521101157776107110681181038389108801005410650887443116554711910475119110656784109759874749956684350501168047435673986799575268901101026779901015512099112120103718277119117120761025699119576610590104876997655099508368100112551189999105119114654365878988834380765689103119114691176550100121835712068705757104697178891061197112011310911390901095598119106107796989824867122719811780549812197787787514771737776120791037811069535251511125249891027799108119118659765879049801061005472895210480694365871008799437611490541081199983511057965767797108120113118545599738047808611151665065115119113887298711158986108667410588105834711853107122671197056671226755697043651066510189108995043548910448106847467109897410472103721099743795311411278471027087725211081681227080721004957119118781037271739966115811101228877102767210048122665674103651155310578991204347114988010665717784896690120548810710299437854576510598103117701101024311172119761097355501178766115108541195510477119771225
Error: Internal Server Error
** Activity (datainput) Pause, UserClosed = false **

 

[Gearcam]

Erel

I think i am off track because i can not make this work

So to start again i have saved a signature from the signature example as a png
it is stored here /mnt/sdcard/sign.PNG

Then all i want to do is save this in the sql database on my remote site using

job2.Initialize("Job2", Me)
job2.PostString(ServerUrl, "Insert INTO test ([ECOLsignature]) VALUES ('" & XXXXX & "')")

All other fields i can save ok including text and data so the URL etc is correct

All i need is what replaces XXXXX ??

The field on the server i belive will accept an image directley this is the code from VS 2008 for a win mob device and it works

'Read the binary file from disk
Dim fs As FileStream = New System.IO.FileStream(const_signaturejpg, FileMode.OpenOrCreate, FileAccess.Read)
Dim rawsignatureData() As Byte = New Byte(CInt(fs.Length)) {}
fs.Read(rawsignatureData, 0, rawsignatureData.Length)
fs.Close()

row!ECOLsignature = rawsignatureData



I also tried to make this work as it was from an example to save pictures to local sql
But it also fails

job2.Initialize("Job2", Me)
job2.PostString(ServerUrl, "Insert INTO test ([ECOLsignature]) VALUES ('" & Array As Object(data) & "' )")

This error
Error occurred. Query=Insert INTO test ([ECOLsignature]) VALUES ('[Ljava.lang.Object;@40e5d820' )
System.Data.SqlClient.SqlException: Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

 

[Erel]

PostString sends a string. The correct way to send raw bytes as string is to first convert the array to Base64 (google it for more information about this encoding). However this means that the server should understand that this field is Base64 string and should decode it back to a bytes array.

 

[Gearcam]

Hi erel

THe problem seems to be the databse is already set up on the SQL server as MS SQL server in a column defined as "varbinary" this i can not change as theire are huge number of records that will be lost and some PC apps see this data directley with no problems.

I have as you have seen tried the Base64 conversion but it does not seem to work or is not compatable with "varbinary"

I have tested on a new column of type "signature"

With this
job2.PostString(ServerUrl, "Insert INTO test ([ECOLsignature]) VALUES ('" & Array As Object(data) & "' )")

And it saves data as binatty but i can not read it in the PC applications

Do you see any problems with the following code ?

Dim data() As Byte
data = out.ToBytesArray
out.Close
in.Close
out.Flush
Dim ff As Int
ff=data.Length
Dim b64 As Base64
Dim temp() As Byte
temp=b64.EncodeBtoB(data, 0, data.Length)
job2.Initialize("Job2", Me)
job2.PostString(ServerUrl, "Insert INTO test ([ECOLsignature]) VALUES ('" & Array As Object(temp) & "')")

Steve

 

[Erel]

Do you see any problems with the following code ?

Yes. As I previously wrote you cannot use PostString with an array of bytes.

 

[Gearcam]

Erel

If i can not use poststring what should i use ?

this is the latest code
Dim in As InputStream
in = File.OpenInput(File.DirRootExternal, "sign.png")
Dim out As OutputStream
out.InitializeToBytesArray(1)
File.Copy2(in, out)
Dim data() As Byte
data = out.ToBytesArray
out.Close
in.Close
out.Flush
Dim ff As Int
ff=data.Length
Dim b64 As Base64
Dim temp() As Byte
temp=b64.EncodeBtoB(data, 0, data.Length)
job2.Initialize("Job2", Me)
'job2.Poststring(ServerUrl, "Insert INTO test ([ECOLsignature])VALUES ('" & Array As Object(temp) & "')")
job2.PostString(ServerUrl, "Insert INTO test ([ECOLsignature]) VALUES ('" &temp()& "')")

if you see the last two poststring lines the first one will save data but the sql is set to "signature" but i can not see the picture.

The second one will not work

I am stuck i think, i have tried to use PostBytes but can not get the syntax to work.

Steve

 

[Erel]

It depends on your server configuration. Does it expect raw bytes? Does it expect a string?

 

[Gearcam]

Erel

This is the beginig of the data stored in the field from win pc device
Server is set to "varbinary"
0xFFD8FFE000104A464946000101010060006000......

This is from the code B4A
Server is set to "signature"
b2.Poststring(ServerUrl, "Insert INTO test ([ECOLsignature])VALUES ('" & Array As Object(temp) & "')")
0x5B4C6A6176612E6C616E672E4F626A6563743B...... (much shorter string)

 

[Gearcam]

Erel this is an example of exactley what is needed

Storing Images and BLOB files in SQL Server Part 2 — DatabaseJournal.com

This is the code that inserts
INSERT INTO BLOBTest
(BLOBName, BLOBData)
SELECT 'First test file',
BulkColumn FROM OPENROWSET(
Bulk 'C:\temp\nextup.jpg', SINGLE_BLOB) AS BLOB

And now my version in B4A
job2.PostString(ServerUrl, "INSERT INTO Test([ECOLsignature])Select BulkColumn FROM OPENROWSET(Bulk ' & /mnt/sdcard/sign.PNG & ' , SINGLE_BLOB) As BLOB")

Which i can not get to work any help ?


This is the best i have so far
MM.const_signaturejpg="/mnt/sdcard/sign.PNG"
job2.PostString(ServerUrl, "INSERT INTO Test([ECOLsignature])Select BulkColumn FROM OPENROWSET(Bulk '" & MM.const_signaturejpg & "' , SINGLE_BLOB) As BLOB")

This throwa this sql error ??

Error occurred. Query=INSERT INTO Test([ECOLsignature])Select BulkColumn FROM OPENROWSET(Bulk '/mnt/sdcard/sign.PNG' , SINGLE_BLOB) As BLOB
System.Data.SqlClient.SqlException: You do not have permission to use the bulk load statement.

So it looks lijke the line is correct but how to allow it to work ?

 

[Gearcam]

hi Erel

I can not make this work

Should i be using Postbyte instead of Poststring ?

But i can not get the sytax correct can you help ?

 

[Erel]

I'm sorry but I cannot write the code for you. As I wrote multiple times in this thread you cannot send raw bytes with PostString. You can use PostBytes to send any data you need. You can convert strings to bytes:

Dim s As String
s = "..."
s.GetBytes("UTF8")

I don't think that this will work as your server probably expects a string query. So it will fail to convert the blob bytes to string. As I wrote to you in this thread as well, a simple solution is to create a custom web service that will expect Base64 encoded picture and will then decode it.

 

[Gearcam]

Thanks Erel

I will persue this

"a simple solution is to create a custom web service that will expect Base64 encoded picture and will then decode it"

But its only simple if you know how to write web service, this i know nothing about as i use your example web service with no changes.

Steve

 

[Erel]

Are you the only one reading from this database? Then you can store the base64 string as a long string. You should add apostrophes before and after the string.

 

[Gearcam]

Erel

This is the problem

The sytem works by a number of remote PC based sytems some using Access some VB6 programs reading the data stored on a remote site.

Theire are also 20 win 6 mobile phones that upload data to the remote server.

These are the devices we need to replace with new android devices.

The database is pretty old with 1000's of records which i can not change the structure of as it will destroy so much historic data.

 

[Erel]

In that case I recommend you to read a bit about Asp.Net and build a web service that takes an image and some other strings and add it to the database.

 

[Gearcam]

HI erel

I guess this is a good example

Android: Upload image to Server | SP Technolab Blog

or this

http://www.codeproject.com/Articles/114892/Saving-Files-Into-Database-Using-NET-Web-Services

 

[JDS]

Is it possible to save an image as an BMP? I need to send the image (compressed) as a string to the server.

I don't want to use base64. I have my own encryption.