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

 

[DonManfred]

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

my fault. Remove the ) and the end of the query and replace it by ;

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]

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

[B4X] Regular expressions (RegEx) tutorial

Regular expressions are very powerful and make complicate parsing challenges much easier. This short tutorial will describe the usage of regular expressions in Basic4android. If you are not familiar with regular expressions you can find many good tutorials online. I recommend you to start with...

www.b4x.com

 

[Peter Lewis]

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"))

Yes, that was one of the first things I tried, its in my first post

 

[DonManfred]

Yes, that was one of the first things I tried

if you use the VALUES Variant you have to define all Fields from the table. If you change the table the query will error.
Better is to define all fields which you want to set. See my code example i posted above.

 

[LucaMs]

Yes, that was one of the first things I tried, its in my first post

No, you tried:

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

It's very different.

 

[Peter Lewis]

my fault. Remove the ) and the end of the query and replace it by ;

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"))

Yes, that worked . thank you

 

[Peter Lewis]

No, you tried:

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

It's very different.

I also tried taking out the PayloadStr and replaced it with the actual values as in your post which also failed

 

[Peter Lewis]

my fault. Remove the ) and the end of the query and replace it by ;

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"))

as soon as I put the variable back in the Array area I get the initial error again

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

So it must be something to do the whole string seen as the first value. i am going to have to break the string into seperate fields

 

[Xandoca]

Have you tried something like?

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

I'm using MySql (5.1.48) and only works when I explicitly list the columns after table name.

If you still got the error I would recommend to you the "divide and conquer" strategy. Configure all fields to allow Null and try the insert sql with one field, then two fields, and so on. You will then see exactly where the problem is.

 

[OliverA]

Error i am Getting

Any Ideas would be appreciated.

The initial issue, the issue that was listed in the first post is that only one parameter was passed to the database, not 13 (?). That issue is solved by splitting PayLoadStr and passing the resulting array to the ExecNonQuery2. Any additional error messages after that point (due to auto-increment field, ordering of fields vs ?) is technically a new thread. Nowhere have I seen yet where the poster addressed the initial issue of passing only one parameter instead of correctly splitting out the incoming text string.