dbpPro query with variables

#1 · July 10, 2019, 4:03 pm

Hello all,

Even when I did in the past lots of applications with DataBase connections via the dbPro plugin, this is the first time I try to archive the following:

I have an MySQL server database table with the following example content in the lquery field:

INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')

The variables [TCP_Received2] are before taken from an TCP/IP array.

I want to do the following VisualNeo code to run the insert query which is received from the table "tcpip_messages"

dbpExecSQL "Lunar" "SELECT lquery FROM tcpip_messages WHERE command= '[TCP_Recieved1]'" "TCP_Mgs"
dbpExecSQL "Lunar" "[Lunar.TCP_Mgs.lquery]" ""

But is seems that VisualNeo not interprets the variables in the query eg. [TCP_Recieved2] and doesn't give the values to it?

When I use the following everything works as it should:

dbpExecSQL "Lunar" "INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')" ""

What I'm I doing wrong to get the variables in the query active as VisualNeo variables during the insert?

Best regards,

Eric Beerhorst

#2 · July 10, 2019, 6:05 pm

@ebear

Try this first to see if everything is being resolved as expected ...

AlertBox "TCP_Recieved1" "[TCP_Recieved1]"
SetVar "[mySelect] "SELECT lquery FROM tcpip_messages WHERE command= '[TCP_Recieved1]'"
AlertBox "mySelect" "[mySelect]"
dbpExecSQL "Lunar" "[mySelect]" "TCP_Mgs"

... if not, post the results of the two AlertBox commands ... otherwise add the command in Line 2 ... tell us if it behaves differently from expectations.

#3 · July 11, 2019, 3:08 pm

Quote from ebear on July 11, 2019, 3:08 pm
Hello Gaev,

The first part of my script works fine, it is the second part where the insert query comes from which doesn't work.

First of all an more detailed description of what I want to archive.

I have an TCPIP client who sends to my application commands, the commands are the request to save data in more MySQL server tables. This is done because the client isnt able to work with MySQL server.

The commands are as follow as an example:
CYCLETIME;<username>;<workinstructionname>;<time in seconds>
CYCLETIME;Eric Beerhorst;Build an PC;500

The first part of the array received is used to search the table "tcpip_messages" where the INSERT queries are saved.
I want to make an dynamic application where the future queries will be saved in an database table instead of fixed in the VisualNeo code. This makes it possible to add or modify queries when needed, without changing the application.

The first part where the [TCP_Received1] is used to search for the right database record in the table"tcpip_messages" works fine. I see also that the correct query is saved in the variable [Lunar.TCP_Mgs.lquery] in the VisualNeo debugger.

After this is done I want to execute dbpExecSQL "Lunar" "[Lunar.TCP_Mgs.lquery]" "" but this doesn't work.
The [Lunar.TCP_Mgs.lquery] contains for example :
INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')
But it seems that the variables "[TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]" are not changed to the correct data.

When I put direct in the VisualNeo code the following everything works:
dbpExecSQL "Lunar" "INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')" ""

It seems to me that VisualNeo doesn't see the variables retrieved for the database table as variables and doesn't covert them in the correct content.

I hope everything above makes sense, otherwise please let me know and I will try to give more information.

Thanks a lot for you help.

Eric Beerhorst

Hello Gaev,

The first part of my script works fine, it is the second part where the insert query comes from which doesn't work.

First of all an more detailed description of what I want to archive.

I have an TCPIP client who sends to my application commands, the commands are the request to save data in more MySQL server tables. This is done because the client isnt able to work with MySQL server.
The commands are as follow as an example:
CYCLETIME;<username>;<workinstructionname>;<time in seconds>
CYCLETIME;Eric Beerhorst;Build an PC;500
The first part of the array received is used to search the table "tcpip_messages" where the INSERT queries are saved.
I want to make an dynamic application where the future queries will be saved in an database table instead of fixed in the VisualNeo code. This makes it possible to add or modify queries when needed, without changing the application.
The first part where the [TCP_Received1] is used to search for the right database record in the table"tcpip_messages" works fine. I see also that the correct query is saved in the variable [Lunar.TCP_Mgs.lquery] in the VisualNeo debugger.
After this is done I want to execute dbpExecSQL "Lunar" "[Lunar.TCP_Mgs.lquery]" "" but this doesn't work.
The [Lunar.TCP_Mgs.lquery] contains for example :
INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')
But it seems that the variables "[TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]" are not changed to the correct data.
When I put direct in the VisualNeo code the following everything works:
dbpExecSQL "Lunar" "INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')" ""

It seems to me that VisualNeo doesn't see the variables retrieved for the database table as variables and doesn't covert them in the correct content.

I hope everything above makes sense, otherwise please let me know and I will try to give more information.

Thanks a lot for you help.

Eric Beerhorst

#4 · July 11, 2019, 4:51 pm

@ebear

Thank you for the detailed explanation.

After this is done I want to execute dbpExecSQL "Lunar" "[Lunar.TCP_Mgs.lquery]" "" but this doesn't work.

The [Lunar.TCP_Mgs.lquery] contains for example :

INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')

But it seems that the variables "[TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]" are not changed to the correct data.

VisualNEOWin does NOT resolve variable references (with their current values) multiple times ... so you could try this alternate method to dynamically create your SQL Query ...

SetVar "[myQuery]" "INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')"
... verify that variables references were resolved
AlertBox "myQuery" "[myQuery]"
... now do the SQL thingy
dbpExecSQL "Lunar" "[myQuery]" ""

If that works, replace line 1 with ...

SetVar "[myQuery]" "[Lunar.TCP_Mgs.lquery]"

... and see if it still works

#5 · July 11, 2019, 7:47 pm

Hello Gaev,

The first case works, this is also what I already found.

After replacing the first line with

SetVar "[myQuery]" "[Lunar.TCP_Mgs.lquery]"

The alertbox message (the variable [myQuery]) contains:

myQuery=INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], [#34][TCP_Recieved4][#34])

And of course the INSERT fails.

Any other thoughts??

Thanks a lot for your support,

Eric

#6 · July 11, 2019, 9:43 pm

@ebear

Any other thoughts??

Try this ...

1) Instead of saving the query as ...

INSERT INTO test (test_int, test_float, test_text) VALUES ([TCP_Recieved2], [TCP_Recieved3], '[TCP_Recieved4]')

.. save it as ...

INSERT INTO test (test_int, test_float, test_text) VALUES ({TCP_Recieved2}, {TCP_Recieved3}, '{TCP_Recieved4}')

Note: You can use any wrapper text (other than {}) as long as does not interfere with other text in the query

2) Then do this ...

SetVar "[myQuery]" "[Lunar.TCP_Mgs.lquery]"
StrReplace "[myQuery]" "{TCP_Recieved2}" "[TCP_Recieved2]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved3}" "[TCP_Recieved3]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved4}" "[TCP_Recieved4]" "[myQuery]" ""
... verify that variables references were resolved
AlertBox "myQuery" "[myQuery]"
... now do the SQL thingy
dbpExecSQL "Lunar" "[myQuery]" ""

... let us know the results posted by AlertBox.

#7 · July 11, 2019, 10:27 pm

Hello Geav,

You're great, this works!!!!

Only thing I have to figure out is that the amount of [TCP_Recievedx] can be different in each query command, so this makes it less dynamic because I have to know how many times to do the StrReplace command.

I tried:

StrReplace "[myQuery]" "{" "[#91]" "[myQuery]" ""
StrReplace "[myQuery]" "}" "[#93]" "[myQuery]" ""

Instead of your code, but this seems also not to work.

Thanks a lot. and best regards,

Eric Beerhorst

#8 · July 11, 2019, 10:38 pm

Solved!!!

I have an variable [TCP_ReceivedNo] which contains the amount of parameters [TCP_Recievedx] i receive from the client. [TCP_Recieved1] is always the command used to find the correct query, so the loop starts at 2.

Replacing the non-dynamic "StrReplace" with the following "loop" solves the issue.

Loop "2" "[TCP_RecievedNo]" "[TCP_No]"
StrReplace "[myQuery]" "{TCP_Recieved[TCP_No]}" "[TCP_Recieved[TCP_No]]" "[myQuery]" ""
EndLoop

Again, thanks a lot.

Best regards,

Eric Beerhorst

#9 · July 12, 2019, 12:17 am

@ebear

Only thing I have to figure out is that the amount of [TCP_Recievedx] can be different in each query command, so this makes it less dynamic because I have to know how many times to do the StrReplace command.

Think of the maximum value for x that any query will have ... let us say 6 ... then do ...

SetVar "[myQuery]" "[Lunar.TCP_Mgs.lquery]"
StrReplace "[myQuery]" "{TCP_Recieved2}" "[TCP_Recieved2]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved3}" "[TCP_Recieved3]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved4}" "[TCP_Recieved4]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved5}" "[TCP_Recieved5]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved6}" "[TCP_Recieved6]" "[myQuery]" ""
StrReplace "[myQuery]" "{TCP_Recieved7}" "[TCP_Recieved7]" "[myQuery]" ""

... queries with less than 6 parameters will not be adversely affected because when StrReplace does not find a matching string, it does not do anything (i.e. if you can't find it, you can't replace it) ... the additional commands will only cause about a 5 millisecond delay :-)

#10 · July 12, 2019, 7:40 pm

Hello Gaev and All,

Below the final solution for my issue shared for everyone who can use it. This is only the part of the application which gave me troubles. For the TCPIP communication I use npTalk from https://www.davidesperalta.com/ . Thanks David for the great Plugin.

Best regards and thanks for the support Gaev.

Eric Beerhorst

:.TCP_OnServerRead

.TCPIP incoming communication array. Plugin used is npTalk from https://www.davidesperalta.com/
  npLogsAdd "[LogID]" "{n}[#9]TCPIP[#9]Message received:[#9][Socket.ReceivedText]" "[Result]"
  SetVar "[TCP_ClientLog]" "[TCP_ClientLog][Socket.RemoteAddress]:[Socket.ReceivedText][#13]"
  StrParse "[Socket.ReceivedText]" "[tcpip_delimiter]" "[Variable_]" "[Variable_No]"

.To support the differences in MS SQL and MySQL
  If "[db_type]" "=" "1"
     dbpExecSQL "Lunar" "SELECT msquery FROM lquery WHERE command= '[Variable_1]' AND deleted = 0" "TCP_Mgs"
     SetVar "[lquery]" "[Lunar.TCP_Mgs.msquery]"
  EndIf
  
  If "[db_type]" "=" "2"
     dbpExecSQL "Lunar" "SELECT myquery FROM lquery WHERE command= '[Variable_1]' AND deleted = 0" "TCP_Mgs"
     SetVar "[lquery]" "[Lunar.TCP_Mgs.myquery]"
  EndIf

.Loop to replace the {} in the query with [] to make the variables work
  Loop "2" "[Variable_No]" "[Var_No]"
       StrReplace "[lquery]" "{Variable_[Var_No]}" "[Variable_[Var_No]]" "[lquery]" ""
  EndLoop
  
.Some information variables which can be used in the queries
  StrReplace "[lquery]" "{Identification.LocalComputerName}" "[Identification.LocalComputerName]" "[lquery]" ""
  StrReplace "[lquery]" "{Identification.IPAddress}" "[Identification.IPAddress]" "[lquery]" ""
  StrReplace "[lquery]" "{Socket.RemoteAddress}" "[Socket.RemoteAddress]" "[lquery]" ""

.Here runs the final INSERT query
  dbpExecSQL "Lunar" "[lquery]" ""
Return

dbpPro query with variables - Forum

dbpPro query with variables