B4J Question Problem writing to MySQL file

Status
Not open for further replies.
P

Peter Lewis

Active Member
Licensed User
Longtime User
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"
Click to expand...
"NULL","41hts","Peter3133","Peter","Lewis","0716743133","peter@","1587603550972","Durban","KwaZulu-Natal","South Africa","4051","1"
Click to expand...
]
NULL,41hts,Peter3133,Peter,Lewis,0716743133,peter@,1587603550972,Durban,KwaZulu-Natal,South Africa,4051,1
Click to expand...

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

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

B4X: 
 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
Click to expand...

Any Ideas would be appreciated.

Thank you
 
Sort by date Sort by votes

Toky Olivier

Active Member
Licensed User
Longtime User
You should use an array of of object not a string:
B4X: 
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))
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
That came up with the same error
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
José J. Aguilar said:
If first field is autoincrement, try not to set it with null. Don’t sent the first parameter
Click to expand...

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

 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
Erel said:
Try to explicitly list the columns. Maybe the order is different than what you expect.
Click to expand...

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

B4X: 
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

B4X: 
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

B4X: 
'    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
 
Upvote 0

josejad

Expert
Licensed User
Longtime User
What is there on line: 98 (MQTTutils) ? The Sql2.ExecNonQuery2 sentence?
 
Upvote 0

Lahksman

Active Member
Licensed User
Longtime User
José J. Aguilar said:
If first field is autoincrement, try not to set it with null. Don’t sent the first parameter
Click to expand...
Erel said:
Try to explicitly list the columns. Maybe the order is different than what you expect.
Click to expand...

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.
B4X: 
sql2.ExecNonQuery2("INSERT INTO User_db (password, loginname, firstname, ...) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(PayLoadStr))
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
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
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
Erel said:
Try to explicitly list the columns. Maybe the order is different than what you expect.
Click to expand...
I have tried your option and I get

This is after i changed it to
B4X: 
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
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
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

B4X: 
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
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
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
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
I have tried

B4X: 
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).
Click to expand...
 
Upvote 0

LucaMs

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

B4X: 
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"))
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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:
B4X: 
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"))
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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.
 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
I tried your code exactly copied and past and did not work.

 
Upvote 0
P

Peter Lewis

Active Member
Licensed User
Longtime User
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

B4X: 
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
 
Upvote 0
Status
Not open for further replies.

Similar Threads

  • Article
Share My Creation [Web][SithasoDaisy] Secure CRUD MySQL REST API Web App using Api Key on top of HTTPS
Replies
0
Views
522
Mashiane
  • Article
B4J Code Snippet [Project Template] jRDC2 Server (SQLite, MySQL, MS SQL, Firebird, Postgresql, DBF)
Replies
6
Views
4K
PABLO2013
  • Article
Share My Creation [B4J] [MySQL] [API] Server (Key + Token) and [B4X] User Login Apps
2 3
Replies
46
Views
23K
aeric
  • Article
B4J Library [B4X] [B4XLib] SD_SQL (direct access to MySQL, MariaDB, MS SQL, FireBird)
2 3 4
Replies
64
Views
23K
Star-Dust
  • Article
Share My Creation Erels [B4X] CloudKVS - synchronized key / value store - MySQL
Replies
12
Views
9K
janderkan
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…