I receive the following error when attempting to create a new table with RDC.
Error:
B4X:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''833521620800013' (id INT NOT NULL AUTO_INCREMENT,imei CHAR(45),contactid CHAR(4' at line 1
I am attempting to set the new table name with a parameter (? == Phone IMEI)
B4X:
Sub RDCcreateContactsTable
Dim imei As String = MyIMEI
Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "create_contactstable"
cmd.Parameters = Array As Object(imei)
reqManager.ExecuteCommand(cmd,"create_contactstable")
End Sub
Server Config:
B4X:
sql.create_contactstable= CREATE TABLE ? (\
id INT NOT NULL AUTO_INCREMENT,\
imei CHAR(45),\
contactid CHAR(45),\
name CHAR(60),\
note CHAR(150),\
starred CHAR(45),\
mobile1 CHAR(45),\
mobile2 CHAR(45),\
mobile3 CHAR(45),\
other1 CHAR(45),\
other2 CHAR(45),\
other3 CHAR(45),\
email1 CHAR(80),\
email2 CHAR(80),\
email3 CHAR(80),\
picture BLOB,\
PRIMARY KEY (id),\
UNIQUE INDEX id_UNIQUE (id ASC)\
I tried both:
sql.rename_contactstable = ALTER TABLE conttemp RENAME ?
and
sql.rename_contactstable = RENAME TABLE conttemp TO ?
It seems like above SQL commands do not accept parameterized values using RDC.
This seems strange as I can use both above SQL commands within MySQL to rename the table.
Another option is to modify RDCHandler.ExecuteBatch2 and if the cmd.Name is create_contactstable then build the command yourself. It is a quite simple change.
Are you using parameters in the MySQL console?
Yes
Another option is to modify RDCHandler.ExecuteBatch2 and if the cmd.Name is create_contactstable then build the command yourself. It is a quite simple change.
Another option is to modify RDCHandler.ExecuteBatch2 and if the cmd.Name is create_contactstable then build the command yourself. It is a quite simple change.
Thanks Erel & DM,
In jRDC2 source RDCHandler / ExecuteBatch2 , I need to do the following:
B4X:
Private Sub ExecuteBatch2(con As SQL, in As InputStream, resp As ServletResponse) As String
Dim ser As B4XSerializator
Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
Dim commands As List = m.Get("commands")
Dim res As DBResult
res.Initialize
res.columns = CreateMap("AffectedRows (N/A)": 0)
res.Rows.Initialize
res.Tag = Null
Try
con.BeginTransaction
For Each cmd As DBCommand In commands
con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)
'**********************************************
'Option 1
' Built command to create table with phone IMEI number as Table Name
If cmd.Name = "create_contactstable" Then
'
End If
'Option 2
' Create table with "tableTemp" as Table Name
' Built command to Rename Table "tableTemp" with phone IMEI number as Table Name
If cmd.Name = "rename_contactstable" Then
'
End If
'**********************************************
Next
res.Rows.Add(Array As Object(0))
con.TransactionSuccessful
Catch
con.Rollback
Log(LastException)
resp.SendError(500, LastException.Message)
End Try
Dim data() As Byte = ser.ConvertObjectToBytes(res)
resp.OutputStream.WriteBytes(data, 0, data.Length)
Return $"batch (size=${commands.Size})"$
End Sub
I have two possible options:
I can create the table with the tablename as phone IMEI in place.
OR
I can create a temptable and then Rename Table to phone IMEI number.
Are the proposed changes in the correct position in the above code?
What is the syntax of the required commands?
When all done, what do I do with the "new" edited jRDC2 to function in my app?
For Each cmd As DBCommand In commands
'**********************************************
'Option 1
' Built command to create table with phone IMEI number as Table Name
If cmd.Name = "create_contactstable" Then
'
Else If cmd.Name = "rename_contactstable" Then
'
Else
con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)
End If
'**********************************************
Next
'**********************************************
'Option 1
' Built command to create table with phone IMEI number as Table Name
If cmd.Name = "create_contactstable" Then
con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name).Replace("?", cmd.Parameters.Get(0))) '<<--- Only 'Length' is supported by arrays
Else If cmd.Name = "rename_contactstable" Then
con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name).Replace("?", cmd.Parameters.Get(0))) '<<--- Only 'Length' is supported by arrays
Else
con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)
End If
'**********************************************
Next
I get the error "Only 'Length' is supported by arrays."
@Erel
Many thanks for all your assistance.
I cannot get this to work and am going to look at an alternative - a jServer that will allow me to create unique folders on my server.
I can then use these unique folders to store user-specific data.
Having had no experience with writing jServers, could you point me to a possible example?
I have searched, but am confused.
As @Erel mentioned, the table name cannot be a parameter. What you are now attempting is the creation of "dynamic" SQL statements. While there (sorta) is nothing wrong with that, you may want to read this (http://www.sommarskog.se/dynamic_sql.html) to get a real understanding of what you are trying to accomplish and the pitfalls (dangerous pitfalls) you may encounter. Please note that I'm not saying not to do this, but to be aware of the dangers that come with dynamically creating SQL statements on your own.
@Declan
Why are you creating a table for each IMEI# AND have an IMEI column? Technically, you could store all IMEI information in one table. The IMEI column would then be used to distinguish which IMEI this data belongs to. Otherwise, you're pretty much just creating one table for one record?