B4J Question Problem writing to MySQL file

[Peter Lewis]

Hi all

I have a String that has all my fields in, I have tried then with and without " , I want to insert all the fields into a MySQL Database and using the exact method on the source machine it worked fine.
Now all I have done is sent the Fields to a Remote machine via MQTT and they arrive looking good.

"","41hts","Peter3133","Peter","Lewis","0716743133","peter@","1587603550972","Durban","KwaZulu-Natal","South Africa","4051","1"

"NULL","41hts","Peter3133","Peter","Lewis","0716743133","peter@","1587603550972","Durban","KwaZulu-Natal","South Africa","4051","1"

]

NULL,41hts,Peter3133,Peter,Lewis,0716743133,peter@,1587603550972,Durban,KwaZulu-Natal,South Africa,4051,1

Then I want the server to insert them into a SQL Database. The Database Auto Increments.

    sql2.ExecNonQuery2("INSERT INTO User_db VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(PayLoadStr))

 sql2.ExecNonQuery2("INSERT INTO User_db VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As String(PayLoadStr))

[/CODE]

As i mentioned it worked perfectly if I accessed the database from the original program , so all I did was transport the data. I wrote this server program to Retrieve the listed from a Topic and when the data arrives put it into the Database.

I am sure if i extracted each field out separately and also named the fields it would work and then put it into a List (Even better) but I am trying to keep the operations short so that the server has time for more incoming messages

2 Hours of trying all different permutations and I am ready to throw something. LOL

Error i am Getting

java.sql.SQLException: No value specified for parameter 2

Any Ideas would be appreciated.

Thank you

 

[Toky Olivier]

You should use an array of of object not a string:

sql2.ExecNonQuery2("INSERT INTO User_db VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object (NULL,"41hts","Peter3133","Peter","Lewis","0716743133","peter@",1587603550972,"Durban","KwaZulu-Natal","South Africa",4051,1))

 

[Peter Lewis]

You should use an array of of object not a string:

sql2.ExecNonQuery2("INSERT INTO User_db VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object (NULL,"41hts","Peter3133","Peter","Lewis","0716743133","peter@",1587603550972,"Durban","KwaZulu-Natal","South Africa",4051,1))

That came up with the same error

 

[Erel]

Try to explicitly list the columns. Maybe the order is different than what you expect.

 

[josejad]

If first field is autoincrement, try not to set it with null. Don’t sent the first parameter

 

[Peter Lewis]

If first field is autoincrement, try not to set it with null. Don’t sent the first parameter

I Tried the easier option first and still came up with the same error

Message Arrived
Message Topic Peter3133/Register
119
Peter3133/Register = "41hts","Peter3133","Peter","Lewis","0716743133","peter@",1587603550972,"Durban","KwaZulu-Natal","South Africa",4051,1
This should be right Peter3133/Register
Received reg message
"41hts","Peter3133","Peter","Lewis","0716743133","peter@",1587603550972,"Durban","KwaZulu-Natal","South Africa",4051,1
init database
Error occurred on line: 98 (MQTTutils)
java.sql.SQLException: No value specified for parameter 2
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2256)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2236)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2166)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1207)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:192)
at b4j.example.mqttutils._client1_messagearrived(mqttutils.java:241)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.BA$3.run(BA.java:247)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)

 

[Peter Lewis]

Try to explicitly list the columns. Maybe the order is different than what you expect.

I have double and triple checked the sending side and also the MySQL Structure

Looking at it now I see I changed the Date format but tried the DATE Format and it still gave the same error

This is the Original Code that the Sending Program user to have. I used to make this program go directly to the MySQL database to Register but it is much Faster to do it via MQTT

Sql2.ExecNonQuery2("INSERT INTO User_db VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?,?)", Array As Object(Null,dtaPassword.Text,Loginnam,dtaName.Text,dtaLastName.Text,dtaCellular.text,dtaEmail.Text,"1965/03/17",dtaCity.Text,SelectedItem,"South Africa",dtaPostCode.Text,1))

This is the code I am using to send the string via MQTT

sendreg= "Null,"&dtaPassword.Text&","&dtaUsername.text&","&dtaName.Text&","&dtaLastName.Text&","&dtaCellular.text&","&dtaEmail.Text&","&DateTime.Now&","&dtaCity.Text&","&SelectedItem&",South Africa"&","&dtaPostCode.Text&",1"
   
    Log(sendreg)
    MQTTutils.PostThis3(Main.GloLoginName.Trim&"/Register",(sendreg),2,False)

Other options that I tried to send the Strings but it was not reliable

'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaPassword.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaUsername.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaName.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaLastName.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaCellular.text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaEmail.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",DateTime.Now)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaCity.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",SelectedItem)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register","South Africa")
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register",dtaPostCode.Text)
'    MQTTutils.PostThis(Main.GloLoginName.Trim&"/Register","1")

Possibly you can see something here ?

3 Hours sleep and I might be able to see it with New Eyes..LOL

It is easier to test now as I can just send a String from a MQTT Client and publish it to that Peter3133/Register Topic. So i have the server on my B4J and can monitor the Logs

Thank you

 

[josejad]

What is there on line: 98 (MQTTutils) ? The Sql2.ExecNonQuery2 sentence?

 

[Peter Lewis]

What is there on line: 98 (MQTTutils) ? The Sql2.ExecNonQuery2 sentence?

    sql2.ExecNonQuery2("INSERT INTO User_db VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(PayLoadStr))

 

[Lahksman]

If first field is autoincrement, try not to set it with null. Don’t sent the first parameter

Try to explicitly list the columns. Maybe the order is different than what you expect.

As José and Erel already stated, leave out the userID column and state the other columns explicitly in your query. It should be something like this.

sql2.ExecNonQuery2("INSERT INTO User_db (password, loginname, firstname, ...) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(PayLoadStr))

 

[Peter Lewis]

As José and Erel already stated, leave out the userID column and state the other columns explicitly in your query. It should be something like this.

sql2.ExecNonQuery2("INSERT INTO User_db (password, loginname, firstname, ...) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(PayLoadStr))

I was going to try that , but what i do not understand is that is works perfectly in the sending program as it is. I tied taking out the Auto increment and changed to code but that did not help

I am going to have to implicitly now try. Did not want to do that but that is the next option.

Option no3, is to take the string and break it into each field, put it into a List and save it

 

[Peter Lewis]

Try to explicitly list the columns. Maybe the order is different than what you expect.

I have tried your option and I get

Message Topic Peter3133/Register
122
Peter3133/Register = "41hts","Peter3133","Peter","Lewis","0716743133","peter@","1965/03/17","Durban","KwaZulu-Natal","South Africa","4051","1"
This should be right Peter3133/Register
Received reg message
"41hts","Peter3133","Peter","Lewis","0716743133","peter@","1965/03/17","Durban","KwaZulu-Natal","South Africa","4051","1"
init database
Error occurred on line: 98 (MQTTutils)
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3367)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3352)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4068)
at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3571)
at com.mysql.jdbc.JDBC42PreparedStatement.setObject(JDBC42PreparedStatement.java:68)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:189)
at b4j.example.mqttutils._client1_messagearrived(mqttutils.java:241)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.BA$3.run(BA.java:247)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)

This is after i changed it to

sql2.ExecNonQuery2("INSERT INTO User_db VALUES(password,LoginName,FirstName,LastName,Cellular,Email,DateOfBirth,City,RegionState,Country,PostalCode,Active)", Array As Object(PayLoadStr))

and Took out the Autonumber Field

and I tried with the Autonumber field

 

[Peter Lewis]

I have now tried on the server to put in the string and ignore the incoming string , all that does it now trigger the routine so it uses this

PayLoadStr="41hts,Peter3133,Peter,Lewis,0716743133,peter@,1965/03/17,Durban,KwaZulu-Natal,South Africa,4051,1"

I tied with both the userID auto increment field and without obviously changing the query to match still the same error

 

[Peter Lewis]

What it look like is it is expecting 12 or 13 variables in the Array as section even though I put in a string , it is seeing that string as only the first field. So possibly to make it work I would have to break up the incoming string and put is into 12/13 variables in the Array

 

[Peter Lewis]

I have tried

sql2.ExecNonQuery2("INSERT INTO User_db VALUES(password,LoginName,FirstName,LastName,Cellular,Email,DateOfBirth,City,RegionState,Country,PostalCode,Active)", Array As Object("41hts","Peter3133","Peter","Lewis","0716743133","peter@","1965/03/17","Durban","KwaZulu-Natal","South Africa","4051","1"))

Same Error

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

 

[LucaMs]

Using a parameterized query you have to use "?" as placeholders, to be replaced by the array:

sql2.ExecNonQuery2("INSERT INTO User_db VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object("41hts","Peter3133","Peter","Lewis","0716743133","peter@","1965/03/17","Durban","KwaZulu-Natal","South Africa","4051","1"))

 

[DonManfred]

Using this code you do not need to set all values and/or know the order.
You just define all Values which you want to set.

Something like:

sql2.ExecNonQuery2("INSERT INTO User_db SET password=?,LoginName=?,FirstName=?,LastName=?,Cellular=?,Email=?,DateOfBirth=?,City=?,RegionState=?,Country=?,PostalCode=?,Active=?)", Array As Object("41hts","Peter3133","Peter","Lewis","0716743133","peter@","1965/03/17","Durban","KwaZulu-Natal","South Africa","4051","1"))

 

[OliverA]

You realize your PayLoadStr is not an array. It’s just one string. Therefore the error message. There is no second value, just a first value (and no values after that). You need to split up your string into an array of values first before using that newly created array for your ExecQuery2 statement.

 

[Peter Lewis]

Using this code you do not need to set all values and/or know the order.
You just define all Values which you want to set.

Something like:

sql2.ExecNonQuery2("INSERT INTO User_db SET password=?,LoginName=?,FirstName=?,LastName=?,Cellular=?,Email=?,DateOfBirth=?,City=?,RegionState=?,Country=?,PostalCode=?,Active=?)", Array As Object("41hts","Peter3133","Peter","Lewis","0716743133","peter@","1965/03/17","Durban","KwaZulu-Natal","South Africa","4051","1"))

I tried your code exactly copied and past and did not work.

init database
Error occurred on line: 101 (MQTTutils)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:192)
at b4j.example.mqttutils._client1_messagearrived(mqttutils.java:244)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.BA$3.run(BA.java:247)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)

 

[Peter Lewis]

Does anyone have a routine to break up the comma delimited string to different fields ? Possible that is going to be the best way to make this work

I did break up one with 2 variables yesterday and it was a mission as it will get very long

sql1.ExecNonQuery2("INSERT INTO  SubCatagory Values(?,?)", Array As String((PayLoadStr.SubString2(1,((PayLoadStr.IndexOf(","))))), (PayLoadStr.SubString2((PayLoadStr.IndexOf(",")+1),(PayLoadStr.Length)))))

there are a few extra brackets as I was trying with .trim . But this code does work