PC SOFT

GRUPOS DE DISCUSSÃO PROFISSIONAL
WINDEVWEBDEV e WINDEV Mobile

Inicio → WINDEV 2024 → WM21 - Binary Memo to SQL Server
WM21 - Binary Memo to SQL Server
Iniciado por Elisha Spicer, mar., 16 2018 10:22 PM - 24 respostas
Publicado em março, 16 2018 - 10:22 PM
Been presented with an situation. thanks to help on this forum i have overcome saving an image to my android database.

however in one of the last phases of my app development, i have been presented an issue.

reading on the doc.windev.com site, in the sqlexec section, ther is a line that expresses to me to use WDBinaryMemo in my sql query if im connecting to a webservice. im not sure if im using it right, or if the data extracted from the hfsql binfile.

my sql string is
sBinTest1 is string = "INSERT INTO BinFile(bin_data) VALUES = {WDBinaryMemo('Temp.jpg')}
Publicado em março, 17 2018 - 6:01 AM
Well, here is an update. an error presented using the above information.

error in Left-Clicking WIN_Action_Tracker..BTN_Sync_to_Backend process.
Internal error of WDJava.Framework.
: Attempt to invoke virtual method 'fr.pcsoft.wdjava.ws.WDWSEntiteXSD
fr.pcsoft.wdjava.ws.WDWSEntiteXSD.c(java.lang.String)' on a null object reference.

Any Suggestions?
Publicado em março, 17 2018 - 10:58 AM
Hi Elisha,

you are getting a JAVA error... this means that you are executing this code in a Java environment, most probably Android.

However, there is no support available for SQL server access from Android...

So either you are not telling us everything, or we found the source of your problem

Best regards.
Publicado em março, 17 2018 - 9:31 PM
Quote

you are getting a JAVA error... this means that you are executing this code in a Java environment, most probably Android.

it is an Android environment, and the app is built in WinDev Mobile 21.

Quote


However, there is no support available for SQL server access from Android...

I am currently able to push text data 100% successfully every time.
the issue im facing is taking a binary memo and pushing that data to sql server. i really have a hard time believing that this is impossible. i have read through the SqlExec function page on doc.windev.com and it expresses me to use the {WDBinaryMemo} keyword in my sql query to make this work.

Quote


So either you are not telling us everything, or we found the source of your problem

i really want to learn where im failing, what information am i not telling?
i tried to put my sql query string in my first post on this topic as it is in my code.
Publicado em março, 18 2018 - 11:52 AM
Hi again

>>>i really want to learn where im failing, what information am i not telling?

You are not showing us your code
You are not telling us in which environment you are working in... WM ? Which version ?
You are not telling us HOW you are trying to access your SQL DB... Direct access (not supported) ? Is it a webservice ? REST ? SOAP ? One you created yourself ? The one provided byt pcsoft ?


Basically, you are not giving us ANYTHING to work with...

Just showing us what YOU THINK is the problem is not going to work... The reasons being:
1. you don't KNOW what is the problem, or you wouldn't be asking your questions here.
2. We don't know what you are doing, and therefore have no way of finding what you are doing wrong, if anything...

So help us help you...
Publicado em março, 18 2018 - 10:32 PM
Thank you for clarifying.
i assumed with my topic header with the "WM21" i was expressing that i am using Windev Mobile 21.

As stated, i am using Windev Mobile 21.

i am accessing my sql database using a webservice, the one provided by pcsoft. i assume its SOAP from the install.

the code i am using is -

sBintst1 is string
connectionnum is string

IF InternetConnected = True THEN
HOpenConnection(AndHFSQLCon1)
IF HExecuteQuery(QRY_BinTest1) = True THEN
FOR EACH BinFile
fExtractResource("/Temp.jpg","")
HExtractMemo(BinFile.bin_data,"/Temp.jpg")
connectionnum = SQLConnectWS("http://ourwebserver.com/WDSOAPDB_WEB/","AndroidApp","","")
IF connectionnum <> 0 THEN
sBinTest1 = "INSERT INTO BinFile VALUES {WDBinaryMemo('Temp.jpg')}"
IF SQLExec(sBinTest1,"Upload2") = False THEN
SQLInfo("Upload2")
Info("SQL ERR: "+SQL.MesError)
SQLDisconnect()
RETURN
END
SQLClose("Upload2")
Info("Data Uploaded")
END
END
END

ELSE
Info("Upload Failed")
END
IF InternetConnected = False THEN
Info("No Internet Connection Available, Please Try Again Later")
END
SQLDisconnect()

Again, thank you for helping me out, and i apologize i was not giving you all the proper information. i hope this is what you were asking about.

the only other things i can imagine that could be known is
-Webserver that webservice runs on in Windows Server 2016 Standard
-Same OS that SQL is running on(is on a separate machine from webserver)
-I do get successful uploading of text to the sql backend
-i have tried the app on both an android 4 and 7 device, along with testing through the IDE.
Publicado em março, 19 2018 - 11:53 AM
From the top of my head :

1. You are doing a fextractresource of '/temp.jpg" (instead of 'temp.jpg' into an empty destination... So you have two incorrect parameters on the two required. I don't know what you are trying to do with that line, as your code is not commented, but it will clearly do nothing good. See help for the correct syntax.

2. You are NOT testing the result of fextractresource. You should ALWAYS test the result of all functions and have some code for error handling. Again, see the help for an example.

3. You are passing TWO parameters to hextractmemo while it needs THREE (see help).. it will NOT work that way.

4. You are NOT giving a ful path to set where the file should be saved, which, in android, is a VERYBAD idea. use fdatadir, or fcurrentdir, but tell the system where you want to work.

5. You are not testing the result of hextractmemo. You should ALWAYS test the result of all functions and have some code for eror handling. Again, see the help for an example.

6. You are using SQLConnectWS INSIDE the loop on the hf file, instead of before... I doubt that the system will be happy with potentially hundred of connections requests in a row.

7. Because of AT LEAST point 3 (and potentially also point 4 and others), there is very little chance that your binary memo was ever extracted, and that would explain why the java code complains that you are pointing to a null reference (no file).

I strongly suggest that you read in detail the help of the functions you are trying to use. Nearly all the points I made above are clearly described in it.

Best regards
Publicado em março, 19 2018 - 11:09 PM
Thank you for pointing out my flaws in code. i have been reading the help on the website and i do see my errors now. the last 2 weeks have been very frustrating in trying to make this work. i have refactored my code, per your pointers, and am still running into errors.
IF InternetConnected = True THEN connectionnum is int connectionnum = SQLConnectWS("http://xxxxxxxxx.com/WDSOAPDB_WEB/","AndroidApp","","") IF connectionnum <> 0 THEN IF fExtractResource("Temp.jpg","/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg") = True THEN //IF fExtractResource("Temp.jpg","c:\ emp\ emp.jpg") = True THEN IF HOpenConnection(AndHFSQLCon1) = True THEN IF HExecuteQuery(QRY_BinTest1) = True THEN FOR EACH BinFile IF HExtractMemo(BinFile,bin_data,"/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg") = True THEN //IF HExtractMemo(BinFile,bin_data,"c:\ emp\ emp.jpg") = True THEN sBinTest1 is string = "INSERT INTO BinFile VALUES {WDBinaryMemo('/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg')}" //sBinTest1 is string = "INSERT INTO BinFile VALUES {WDBinaryMemo('c:\ emp\Temp.jpg')}" IF SQLExec(sBinTest1,"Upload2") = False THEN SQLInfo("Upload2") Info("SQL ERR: "+SQL.MesError) SQLClose("Upload2") SQLDisconnect() RETURN ELSE SQLClose("Upload2") Info("Data Uploaded") END ELSE Info("Failed extracting Memo") END END//for each ELSE Info("HExecuteQuery Failed") END ELSE Info("HOpenConnection Failed") END ELSE Info("Failed to extract Image") END ELSE Info("Cannot Connect to SQl Webservice") END END IF InternetConnected = False THEN Info("No Internet Connection Available, Please Try Again Later") END SQLDisconnect()

i have the commented out portions of code for my local machine testing
as far as i can tell i am extracting the resources correctly as i check for them in the code, during thesting it functioned in the IDE.

I am still presented with the exact same error of WDJava Framework that i expressed in an earlier reply.

but in my IDE i am only getting a false return on my SQLExec line where the SQLInfo and Info(SQL.MesError) return no information.
Publicado em março, 20 2018 - 9:00 AM
Hi Elisha,

can you add an extra check, just to be sure the HExtractMemo did a proper job and check if the file is actually there?

IF fFileExist("/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg") THEN
etc.
Maybe even checking the filesize

Or did you check by a fileexplorer on your device to see if the file is there?
Just to make sure the file is actually there and accesible.
Publicado em março, 20 2018 - 10:42 AM
I did enter the fFileExist check, it returned true but browsing to it in the file explorer of my phone showed the "0" size placeholder image.

i reworked the location a bit changing it to
("/storage/emulated/0/pictures/Temp.jpg")

and that successfully returned the correct memo image. i was able to view it in the gallery and see it in the file explorer. with a file size of 7mb(i know i need to shrink this somehow, but ill tackle that later, unless it is the problem!)

However, The Fatal error of

error in Left-Clicking WIN_Action_Tracker..BTN_Sync_to_Backend process.
Internal error of WDJava.Framework.
: Attempt to invoke virtual method 'fr.pcsoft.wdjava.ws.WDWSEntiteXSD
fr.pcsoft.wdjava.ws.WDWSEntiteXSD.c(java.lang.String)' on a null object reference.

is still presenting.

Here is the code i am currently using in my button.
IF InternetConnected = True THEN Connectionnum is int Connectionnum = SQLConnectWS("http://xxxxx.com/WDSOAPDB_WEB/","AndroidApp","","") IF Connectionnum <> 0 THEN //IF fExtractResource("Temp.jpg","/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg") = True THEN //IF fExtractResource("Temp.jpg","c:\ emp\ emp.jpg") = true THEN IF HOpenConnection(AndHFSQLCon1) = True THEN IF HExecuteQuery(QRY_BinTest1) = True FOR EACH BinFile //IF HExtractMemo(BinFile,bin_data,"/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg") = True THEN IF HExtractMemo(BinFile,bin_data,"/storage/emulated/0/pictures/Temp.jpg") = True THEN IF fFileExist("/storage/emulated/0/pictures/Temp.jpg") THEN //IF HExtractMemo(BinFile,bin_data,"c:\ emp\ emp.jpg") = True THEN //IMG_Test = dLoadImage("C:\Temp\Temp.jpg") IMG_Test = dLoadImage("/storage/emulated/0/pictures/Temp.jpg") sBinTest1 is string = "INSERT INTO BinFile VALUES {WDBinaryMemo('/storage/emulated/0/pictures/Temp.jpg')}" //sBinTest1 is string = "INSERT INTO BinFile(asset_id) VALUES (8675309)" //sBinTest1 is string = "INSERT INTO BinFile VALUES {WDBinaryMemo('c:\ emp\Temp.jpg')}" IF SQLExec(sBinTest1,"Upload2") = True THEN SQLClose("Upload2") SQLDisconnect() fDelete("/storage/emulated/0/pictures/Temp.jpg") Info("Data Uploaded") ELSE SQLInfo("Upload2") Info("SQL ERR: "+SQL.MesError +CR +"SQL ERR Num: " +SQL.Error) SQLClose("Upload2") SQLDisconnect() RETURN END ELSE Info("Failed extracting Memo(fFileExist)") END ELSE Info("Failed extracting Memo") END END //foreach ELSE Info("HExecuteQuery Failed") END ELSE Info("HOpenConnection Failed") END //Else //info("Failed to extract Image") //end ELSE Info("Cannot Connect to SQl Webservice") END END IF InternetConnected = False THEN Info("No Internet Connection Available, Please Try Again Later") END
Publicado em março, 20 2018 - 11:01 AM
I never used the webservice so I can only guess what's wrong.
But shouldn't there be a fieldname in the insert-statement?

"insert into binfile(YOUR_MEMO_FIELDNAME) values {WDBinaryMemo('/storage/emulated/0/pictures/Temp.jpg')}"
Publicado em março, 20 2018 - 11:05 AM
Yes, i should have it there, i copied a query i was making in SSMS and it did not need the field there.

i did add it in after you reminded me, but the wdjava framework error persists.
Publicado em março, 20 2018 - 11:34 AM
Well then I'm stuck too, I wonder if this 'feature' is working in the first place.

To get more info you can have a look in the Android/Compile map on your pc/laptop. It's in your project map of the application.
You will find your windows and so on translated to Java there. Maybe that will shine a light.

Otherwise I would send a ticket to pcsoft support.
Publicado em março, 20 2018 - 12:03 PM
Hi

something else I don't understand

You are doing a query called QRY_BinTest1
However, you are NOT reading the resulting dataset
Instead you are doing a For EACH BINFILE
then you are doing a hextractmemo on that file

so there is no correlation between the query and the file at all...

so, -IF- your query is used to find the records containing a binary memo (I don't know, you are not saying), then your code cannot work, as you are not using the query dataset at all...

Furthermore you need to test with a ffileexist if you DID extract what you think you extracted.

Best regards



HExecuteQuery(QRY_BinTest1) = True
FOR EACH BinFile
//IF HExtractMemo(BinFile,bin_data,"/storage/emulated/0/Android/data/sla.mobile.ActTrack/files/pictures/Temp.jpg") = True THEN
IF HExtractMemo(BinFile,bin_data,
Publicado em março, 20 2018 - 12:14 PM
Good point.
BinFile seems to be used as a reference to a local and remote database at the same time?
Publicado em março, 20 2018 - 10:03 PM
i have done a fFileExist check and it returns true. plus i have a basic image control that loads the extracted memo file. and it loads correctly.

IF InternetConnected = True THEN Connectionnum is int Connectionnum = SQLConnectWS("http://xxxxxxxx.com/WDSOAPDB_WEB/","AndroidApp","","") IF Connectionnum <> 0 THEN FOR EACH BinFile1 IF HExtractMemo(BinFile1,bin_data,"/storage/emulated/0/temp/Temp.jpg") = True THEN IF fFileExist("/storage/emulated/0/temp/Temp.jpg") THEN IMG_Test = dLoadImage("/storage/emulated/0/temp/Temp.jpg") sBinTest1 is string = "INSERT INTO BinFile(bin_data) VALUES {WDBinaryMemo('/storage/emulated/0/temp/Temp.jpg')}" Info("If you made it here, Checks came back true") IF SQLExec(sBinTest1,"Upload2") = True THEN SQLClose("Upload2") SQLDisconnect() fDelete("/storage/emulated/0/temp/Temp.jpg") Info("Data Uploaded") ELSE SQLInfo("Upload2") Info("SQL ERR: "+SQL.MesError +CR +"SQL ERR Num: " +SQL.Error) SQLClose("Upload2") SQLDisconnect() RETURN END ELSE Info("Failed extracting Memo(fFileExist)") END ELSE Info("Failed extracting Memo") END END //foreach ELSE Info("Cannot Connect to SQl Webservice") END END IF InternetConnected = False THEN Info("No Internet Connection Available, Please Try Again Later") END
i am confused now, as the only place my code fails (during breakpoint/stepping in IDE) is at the SqlEXEC when using the {WDBinaryMemo} in the query. if i input any other information at all it pushes correctly. as in anything other than the {WDBinaryMemo}

during my testing in the ide i put a breakpoint on my line 1 and single step the whole way down. there has never been a false return until the SqlExec for the binary memo.

so.
1-Yes i am performing a fFileExist check in testing and on device.
2-i can see the extracted image in its directory on both my pc and android.
3-i have commented out the query for hfsql and performed the test on pc and android.
4-the same wdjava framework error persists
5-testing on pc only returns false on the sqlexec and gives me no error message and an err code of 00000
6-yes the table in mssql had the same name as the table on the device, no longer is the case.

a couple of images, the first is an info box that pops before the sqlexec, if other checks come back true.
the second is of the error itself.
[attachment 2633 Screenshot_2018-03-20-18-04-521.jpg]
[attachment 2634 Untitled.jpg]
Publicado em março, 21 2018 - 2:56 PM
Elisha,

maybe you can save the image to the server by doing a regular insert, if as it were text.
Like any other text-value, without the WDBinaryMemo option.

Most probably you have base64-encode it first, so:
- read the image in a buffer variable buf1 = fLoadBuffer("/storage/emulated/0/temp/Temp.jpg")
- use the buf1 = Crypt(buf1,"",cryptNone,encodeBASE64) to make is base64
- and save that to the sqlserver something like "INSERT INTO BinFile(bin_data) VALUES (" + buf1 ")"
Publicado em março, 21 2018 - 4:10 PM
Hi

It seems that there is a syntax error in the SQL statement...
According to this page: https://www.w3schools.com/sql/sql_insert.asp

Both the (column) part AND the (values) part should be inside ()...
You do have the (bin_data) inside parenthesis, but not he values part {WDBi... should be ({WDBi...)

Best regards
Publicado em março, 22 2018 - 1:58 AM
Oops, thank you Fabrice, i overlooked that.

Arie, i tried that, but it was not working, as there were syntax errors from the buffer.

However, after plugging away at it tonight, i had a glimmer of success.

I was able to get it to successfully work in my IDE test with this code.
the commented portions are for the "real" android device.
IF InternetConnected = True THEN Connectionnum is int Connectionnum = SQLConnectWS("http://xxxxxxxx.com/WDSOAPDB_WEB/","AndroidApp2","username","password","","",hODBC) IF Connectionnum <> 0 THEN FOR EACH BinFile1 IF HExtractMemo(BinFile1,bin_data,"c:\ emp\ emp.jpg") = True THEN //IF HExtractMemo(BinFile1,bin_data,"/storage/emulated/0/temp/Temp.jpg") = True THEN IF fFileExist("c:\ emp\ emp.jpg") THEN //IF fFileExist("/storage/emulated/0/temp/Temp.jpg") THEN sBinTest2 is string = "INSERT INTO BinFile(bin_data) VALUES ({WDMemoBinaire('c:\ emp\ emp.jpg')})" //sBinTest2 is string = "INSERT INTO BinFile(bin_data) VALUES ({WDMemoBinaire('/storage/emulated/0/temp/Temp.jpg')})" IF SQLExec(sBinTest2,"Upload2") = True THEN SQLClose("Upload2") SQLDisconnect() fDelete("c:\ emp\ emp.jpg") //fDelete("/storage/emulated/0/temp/Temp.jpg") Info("Data Uploaded") ELSE SQLInfo("Upload2") Info("SQL ERR: "+SQL.MesError +CR +"SQL ERR Num: " +SQL.Error) SQLClose("Upload2") SQLDisconnect() RETURN END ELSE Info("Failed extracting Memo(fFileExist)") END ELSE Info("Failed extracting Memo") END END //foreach ELSE Info("Cannot Connect to SQl Webservice") END END IF InternetConnected = False THEN Info("No Internet Connection Available, Please Try Again Later") END The {WDMemoBinaire} is what i made a change in, changing it from {WDBinaryMemo} as exploring the WSDL that the pcsoft webservice uses, i could not find any references to WDBinaryMemo.
also, for clarity, the xxxxxxx.com and "username","password" are not the real thing, just placeholders as the boss is adamant not to give out any of that information. but everything else is 100% used.

So using this in the WinDev Mobile 21 IDE Generic Android Simulator, this worked like a charm, i was able to look at the exact query being passed to SQL and it looked good.

plus the varbinary(max) field in MSSQL returned the exact image i saved.

But once i uncommented out the lines and commented my testing lines, i generated an app for device testing.

However, i still received the WDJava Internal Error, on a null object reference.

even with the checks in place, in my code that verify the image is extracted to the correct location and i can see it in my file explorer, am i incorrect in thinking the file is somehow "locked" and unable to be opened?

i do not have any security software or anything other than a "empty" phone. the testing device is NOT Rooted as that is not an option, and does not have actual phone service as its just a dev device. it does connect to the wifi otherwise.

Also, when the app crashes from the internal error, i browse in the android file explorer to where the image is stored, and if i try to open it on the phone, it gives me an error of the media scanner is running, please wait until it completes" type message (That may not be exactly what the message says)
Publicado em março, 22 2018 - 2:47 PM
hmmm....

try to comment out the sqlexec part and check if trying to open the image on the phone still gives you the error...
It is possible that your smartphone includes a virus scanner from stock OS and that it is blocking it while trying to analyze it...

it that is the case, you may have to add a multitask to wait it out...

Best regards
Publicado em março, 22 2018 - 10:20 PM
i followed your advice, i commented out the sqlexec line. along with the following code with the exception of the sqldisconnect and an info box.

I was able to click on the button, got the returned info box, and exited the app. i browsed to the temp folder and the image was there, and i was able to open it successfully.

(i also tried adding a .nomedia file to the folder to prevent the gallery from scanning, it may be doing its job.)

The image opened successfully on the device, when browsing to it via the file explorer in android.

i also tried, just to see if there was a delay in extracting the image, to place a multitask(500) before the sqlexec.

But i am still recieving the WDJava Internal Error, on a null object reference, only with the sqlexec, commenting it out presents no error
IF fFileExist("/storage/emulated/0/temp/temp.jpg") THEN //Multitask(500) //sBinTest2 is string = "INSERT INTO BinFile(bin_data) VALUES ({WDMemoBinaire('c:\ emp\ emp.jpg')})" //sBinTest2 is string = "INSERT INTO BinFile(bin_data) VALUES ({WDMemoBinaire('/storage/emulated/0/temp/temp.jpg')})" //IF SQLExec(sBinTest2,"Upload2") = True THEN //SQLClose("Upload2") SQLDisconnect() //fDelete("c:\ emp\ emp.jpg") //fDelete("/storage/emulated/0/temp/temp.jpg") Info("Data Uploaded") //ELSE //SQLInfo("Upload2") //Info("SQL ERR: "+SQL.MesError +CR +"SQL ERR Num: " +SQL.Error) //SQLClose("Upload2") //SQLDisconnect() //RETURN //END ELSE Info("Failed extracting Memo(fFileExist)") i did not add the rest of the code, as there are no changes within it.

this is when i had the sql exec commented out, i got the info box successfully and as said, browsed to the file location on the android device using it's file explorer and the image was there, and i was able to click on it and open.

i am fully perplexed. the code works in wd21 perfectly now, but physical android devices throw the wdjava error.
Publicado em março, 30 2018 - 10:43 PM
Anymore thoughts on this?

Better question from me, what methods outside this are you all using for similar purposes?
Publicado em março, 31 2018 - 10:53 PM
Hi Elisha

I'm using my OWN rest webservice (a simple webdev awp page), and because I'm coding both sides, I can do whatever I want...

In this particular case, I encode the binary in base64 before sending it, and decode it before writing it in the blob...

So n the client side, I prepare a text packet, send it using httprequest, and on the server side, i read the packet to write in the base...

There is a full example of this in WXReplication, on my web site, as this is the communication system I'm using there.

Best regards
Publicado em abril, 01 2018 - 12:12 AM
Thank you, i will be sure to look at your link.

may i inquire, are you using MSSQL?
Publicado em abril, 01 2018 - 10:09 PM
Hi Elisha,

I'm not sure how to answer that one...

I, personally, do not use MSSQL...
Some of my customers are using MSSQL...
My WXReplication system does not care and has been designed to work with ANY DB supporting native access (writing records with hadd/hmodify/hdelete). So HFSQL, MSSQL, Oracle, MySQL, PostgresSQL...

Best regards