Android Question How to rename mysql database table using jRDC2 with B4A?

[Philipp W]

Hello everyone,

i am building an app for data acquisition in which for every series of measurement a new table in a sql database should be created.
Since you cant create a table with variable name easily, i tried to always give the same name "messwerte" and rename after the
measurement is done. However, RENAME TABLE `messwerte` TO ?; seems not to work.

Using jRDC2, my config.properties file looks like this:

sql.neue_tabelle=CREATE TABLE IF NOT EXISTS messwerte (\
    Unix Text,\
    Uhrzeit Text,\
    Counter TEXT,\
    Drehmoment TEXT,\
    Kadenz TEXT,\
    Fahrradgeschwindigkeit TEXT,\
    Platinenspannung TEXT,\
    Fahrradakku TEXT,\
    Strom TEXT,\
    Lng TEXT,\
    Lat TEXT,\
    GPS_Speed TEXT)

sql.neue_messwerte=INSERT INTO Messwerte VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

sql.rename=RENAME TABLE `messwerte` TO ?;
#sql.rename=ALTER TABLE `messwerte` RENAME TO ?;

In B4A im calling the commands with the following subs:

Sub Create_Table
    rdcLink = "http://" & EditText25.Text & ":" & EditText26.Text & "/rdc" '"http://141.19.77.240:17178/rdc" '
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("neue_tabelle", Null)
    'Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    Wait For (req.ExecuteCommand(cmd, Null)) JobDone(j As HttpJob)
    Log("Try to create SQL Table")
    ToastMessageShow("Try to create SQL Table", False)
    If j.Success Then
        Log("Table created")
        ToastMessageShow("Table created", False)
    Else
        Log("Table not created")
        ToastMessageShow("Table not created", False)
    End If
    j.Release
End Sub

Sub CloseSQLConnection
    rdcLink = "http://" & EditText25.Text & ":" & EditText26.Text & "/rdc" 'rdcLink = "http://141.19.77.240:17178/rdc"
    Dim req As DBRequestManager = CreateRequest
    Dim name As String = "newtablename"
    Dim cmd As DBCommand = CreateCommand("rename", Array(name))
    'Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    Wait For (req.ExecuteCommand(cmd, Null)) JobDone(j As HttpJob)
    Log("Tried to rename database. (next time new one is used)")
    
    If j.Success Then
        Log("erfolgreich")
    Else
        Log("fehlgeschlagen")
    End If
    j.Release
End Sub

Am i using RENAME in a wrong way? I also tried ALTER TABLE `messwerte` RENAME TO ?;
The creation of table "messwerte" works perfectly, however RENAME or ALTER both dont rename the tablename.


I hope someone could help me with this.


Philipp

 

[Erel]

Sounds like you are making things more complicated than they should.

Add an indexed id field to the main table and add all data to the same table.

 

[Philipp W]

Thanks for your fast reply!
So i should add an id column so that i can distinguish between the series of measurements?
Sounds plausible.

But for my interest: am i handling the rename command not properly or is it just not that easy/possible to rename a table?

Thanks a lot!
Philipp

 

[Erel]

Tables names cannot be parameterized. I guess that this is also true with the RENAME command.

 

[josejad]

Maybe I'm wrong, but if you can do the rename... you should change config.properties everytime and recompile the jRDC2 jar server with the new name of the table, right?
The sql.neue_messwerte query would not be valid after you rename the table.