Android Question RDC CreateTable error

[Declan]

I receive the following error when attempting to create a new table with RDC.
Error:

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)

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:

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

 

[Erel]

I don't think that the table name can be parameterized.

 

[Declan]

Thanks.
I could create a temp table, then rename the table.
Would this be possible?
RENAME TABLE tb1 TO tb2

 

[DonManfred]

Would this be possible?

Why you did not try it? It takes seconds to try. Even posting this question.

 

[Declan]

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.

 

[Erel]

This seems strange as I can use both aboce SQL commands within MySQL to rename the table.

Are you using parameters in the MySQL console?

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.

 

[Declan]

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.

This I will attempt.

 

[Declan]

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.

@Erel
Could you give me some advise on this modification?
I cannot find: RDCHandler.ExecuteBatch2 in DBRequestManager.

 

[DonManfred]

I cannot find: RDCHandler.ExecuteBatch2 in DBRequestManager.

Erel is refering to jRDC2 source.
There is a RDCHandler class and inside this class there is the ExecuteBatch2.

 

[Declan]

Thanks Erel & DM,
In jRDC2 source RDCHandler / ExecuteBatch2 , I need to do the following:

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?

 

[Erel]

Almost. It should be:

  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

The syntax should be:

con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name).Replace("?", cmd.Parameters.Get(0)))

When all done, what do I do with the "new" edited jRDC2 to function in my app?

You need to run the updated jRDC2 server.

 

[Declan]

Cool, I now have:

'**********************************************
'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."

You need to run the updated jRDC2 server.

How?

 

[Erel]

I didn't remember that cmd.Parameters is an array. Change it to cmd.Parameters(0).

You need to use jRDC2 instead of the old RDC server.

https://www.b4x.com/android/forum/threads/61801/#content

 

[Declan]

@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.

 

[OliverA]

Having had no experience with writing jServers, could you point me to a possible example?

jRDC2 (https://www.b4x.com/android/forum/t...-rdc-remote-database-connector.61801/#content) is an example of jServer. Here (https://www.b4x.com/android/forum/threads/server-building-web-servers-with-b4j.37172/#content) is jServer's intro.

I am attempting to set the new table name with a parameter (? == Phone IMEI)

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]

@OliverA
Thanks, will start with this - first will be querying folders list on drive, then creating folder

 

[OliverA]

@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?