Page 1 of 1

MySQL INSERT via NeoDBPro doesnt work (Solved)

PostPosted: Fri May 08, 2015 8:20 am
by ebear
Hello All,

I'm already mocking about this problem several days and I cannot find the solution or what I'm doing wrong...

I need to add a record in a MySQL database which also is used for our ExtraNet (Joomla). In this record I also need to add a file name in a special way:
{"label":"Excel File","link":"http:\/\/exn.fseg.eu\/files\/500\/ncm\/ncm_2015-05-08.xlsx"}

A part of the code I use now is the following (The variables will be filled of cause via the DB application normally).

Code: Select all
SetVar "[FullName]" "Eric Beerhorst"
SetVar "[DeptNo]" "500"
SetVar "[DateNow]" "2015-05-08"
SetVar "[QH_Qty]" "5"
SetVar "[SC_Qty]" "2015-05-08"

SetVar "[FileName]" "{[#34]label[#34]:[#34]Excel File[#34],[#34]link[#34]:[#34]http:\/\/exn.fseg.eu\/files\/[DeptNo]\/ncm\/ncm_[DateNow].xlsx[#34]} "
dbpExecSQL "LdB" "INSERT INTO ncr_slips ( author, department, qty_onhold, qty_scrap, excel ) VALUES ( '[FullName]', '[DeptNo]', [QH_Qty], [SC_Qty], '[FileName]' )" ""
AlertBox "" "[_ExportReady] - [FileName]"


If I execute the code above the INSERT fails, if I execute the code without the "excel" and '[FileName]' the code works. The alertbox in the end shows the filename as expected:
{"label":"Excel File","link":"http:\/\/exn.fseg.eu\/files\/500\/ncm\/ncm_2015-05-08.xlsx"}

If I try an SQL INSERT query direct in the MySQL database without Neobook works normally (eg. PHPMyAdmin)

Any an Idea what I'm doing wrong.

Best regards,

Eric

Re: MySQL INSERT via NeoDBPro doesnt work

PostPosted: Fri May 08, 2015 9:12 am
by Gaev
Eric:

When you don't prefix values with a !, NeoBook will attempt to do math on text that looks like a formula ... like your date values ... perhaps even the ones with \

Try ...
Code: Select all
SetVar "[FullName]" "Eric Beerhorst"
SetVar "[DeptNo]" "500"
SetVar "[DateNow]" "!2015-05-08"
SetVar "[QH_Qty]" "5"
SetVar "[SC_Qty]" "!2015-05-08"

SetVar "[FileName]" "!{[#34]label[#34]:[#34]Excel File[#34],[#34]link[#34]:[#34]http:\/\/exn.fseg.eu\/files\/[DeptNo]\/ncm\/ncm_[DateNow].xlsx[#34]} "
dbpExecSQL "LdB" "INSERT INTO ncr_slips ( author, department, qty_onhold, qty_scrap, excel ) VALUES ( '[FullName]', '[DeptNo]', [QH_Qty], [SC_Qty], '[FileName]' )" ""
AlertBox "" "[_ExportReady] - [FileName]"


When in doubt, insert the ! :wink:

Re: MySQL INSERT via NeoDBPro doesnt work

PostPosted: Fri May 08, 2015 9:28 am
by ebear
Hello Gaev,

Thanks a lot for your response.

The ! isn't the problem, the field "excel" is a text field and accepts whatever. The problem is that the complete INSERT query doesn't work if I try to insert also the file name
As I already told if I remove the "excel" and '[FileName]' the DBPro INSERT works, and the complete INSERT query works also direct via PHPMyAdmin.
Even then I tried to add the ! at SETVAR but this doesn't make any changes as I expected.

Regards,

Eric

Re: MySQL INSERT via NeoDBPro doesnt work

PostPosted: Fri May 08, 2015 10:22 am
by Gaev
Eric:

The ! isn't the problem, the field "excel" is a text field and accepts whatever. The problem is that the complete INSERT query doesn't work if I try to insert also the file name

As I already told if I remove the "excel" and '[FileName]' the DBPro INSERT works, and the complete INSERT query works also direct via PHPMyAdmin.


From doing some Google searches, it looks like ...

1) you do not need to escape forward slashes

2) you do need to escape double quotes

Try ...
Code: Select all
{\"label\":\"Excel File\",\"link\":\"http://exn.fseg.eu/files/500/ncm/ncm_2015-05-08.xlsx\"}

... if not, you just have to start with a minimal text (even if it does not make sense) ... like ...
Code: Select all
{ blah blah}

... and gradually introduce addtional (real) values.

Re: MySQL INSERT via NeoDBPro doesnt work

PostPosted: Fri May 08, 2015 11:28 am
by ebear
Gaev,

After several tests I found the problem, but not yet the solution...

This works:
{"label":"Excel File","link":"http//exn.fseg.eu/files/500/ncm/ncr_2015-05-08.xlsx"}

This not:
{"label":"Excel File","link":"http://exn.fseg.eu/files/500/ncm/ncr_2015-05-08.xlsx"}

The ":" in http: is the problem.

Strange but true...

Also I found that I don't need the \/ ... / is enough, now only the problem with http:

My code is now:

Code: Select all
SetVar "[FileName]" "!{[#34]label[#34]:[#34]Excel File[#34],[#34]link[#34]:[#34]http://exn.fseg.eu/files/[DeptNo]/ncm/ncr_[LdB.ncr_sum.DateNow].xlsx[#34]}"


Any idea???

Regards,

Eric

Re: MySQL INSERT via NeoDBPro doesnt work

PostPosted: Fri May 08, 2015 11:36 am
by Neosoft Support
I don't think NeoBook or DBPro care about the : character, so it may be mySQL. You might try escaping the second : and see if that makes any difference.

Re: MySQL INSERT via NeoDBPro doesnt work

PostPosted: Fri May 08, 2015 11:44 am
by ebear
Hello Dave,

I also think that is should not be a problem, but it is?????

Okay I now found that I even dont need the "http://" "exn.fseg.eu" is enough. I'm now testing if it makes any difference, but it seems to work.

The SetVar is now:

Code: Select all
SetVar "[FileName]" "!{[#34]label[#34]:[#34]Excel File[#34],[#34]link[#34]:[#34]exn.fseg.eu/files/[DeptNo]/ncm/ncr_[LdB.ncr_sum.DateNow].xlsx[#34]}"


And with this the variable [FileName] is {"label":"Excel File","link":"exn.fseg.eu/files/500/ncm/ncr_2015-05-08.xlsx"} and the INSERT works????

Strange but true....

Regards,

Eric

Re: MySQL INSERT via NeoDBPro doesnt work (Solved)

PostPosted: Fri May 08, 2015 12:02 pm
by ebear
It works,

What I'm trying to archive:

1. Neobook generates an Excel file from data in a MySQL database (VBscript)
2. The excel file is uploaded via NeoBookFM/FTP to my FTP server (on the ExtraNet site)
3. The file location and filename is written into the MySQL database
4. Joomla Fabrik is connected to this MySQL database and shows a list, in this list is also an link to download the excel file

Thanks a lot both

Regards,

Eric