Source language: Translate to:

MySQL INSERT via NeoDBPro doesnt work (Solved)

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

MySQL INSERT via NeoDBPro doesnt work (Solved)

Postby ebear » Fri May 08, 2015 8:20 am

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
Last edited by ebear on Fri May 08, 2015 12:03 pm, edited 1 time in total.
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: MySQL INSERT via NeoDBPro doesnt work

Postby Gaev » Fri May 08, 2015 9:12 am

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:
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: MySQL INSERT via NeoDBPro doesnt work

Postby ebear » Fri May 08, 2015 9:28 am

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: MySQL INSERT via NeoDBPro doesnt work

Postby Gaev » Fri May 08, 2015 10:22 am

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.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: MySQL INSERT via NeoDBPro doesnt work

Postby ebear » Fri May 08, 2015 11:28 am

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: MySQL INSERT via NeoDBPro doesnt work

Postby Neosoft Support » Fri May 08, 2015 11:36 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: MySQL INSERT via NeoDBPro doesnt work

Postby ebear » Fri May 08, 2015 11:44 am

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: MySQL INSERT via NeoDBPro doesnt work (Solved)

Postby ebear » Fri May 08, 2015 12:02 pm

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests