Android Question Variable table name through jRDC

yiankos1 · Aug 28, 2017

I have a mySQL server that creates daily a new table according to daily date. So i use this code:

Android code:

Code:

    cmd.Initialize
    cmd.Name = "selectAll"
'Parameters for table name
    cmd.Parameters = Array As Object("01_01_2017","01_01_2017")
    reqManager.ExecuteQuery(cmd,0,"selectAll")

config file

Code:

sql.selectAll=SELECT * FROM daily.? WHERE daily.?.agms=265475

I get error setting "?" for variable table name

Code:

<pre>    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 &apos;&apos;01_01_2017&apos; WHERE daily.&apos;01_01_2017&apos;.agms=265475&apos; at line 1</pre></p>

My question is how can i set a mySQL command for a variable table name.
Thank you for your time.

Erel · Aug 28, 2017

yiankos1 said:
I have a mySQL server that creates daily a new table according to daily date.

Sounds like a design mistake.
Add a date column with an index instead.

It will be more robust and easier to maintain or change.

The table name cannot parameterized. You can modify jRDC2 and send the table name as an additional parameter however it is not the correct approach.

yiankos1 · Aug 28, 2017

Erel said:
Sounds like a design mistake.
Add a date column with an index instead.

It will be more robust and easier to maintain or change.

The table name cannot parameterized. You can modify jRDC2 and send the table name as an additional parameter however it is not the correct approach.

I agree with you that is the worst approach. But two years, till now, 'company' creates a daily table. From today i created a table exactly as you said(date column). So i need to get connected to these tables through a variable. Please can someone help me in order to modify jRDC2 so i can send table name.

Erel · Aug 28, 2017

The query is created in RDCHandler.ExecuteQuery2:

Code:

Private Sub ExecuteQuery2 (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 cmd As DBCommand = m.Get("command")
   Dim limit As Int = m.Get("limit")
   Dim rs As ResultSet = con.ExecQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)

You can modify this code and treat the first parameter as the table name and create a new array with the other parameters.

Code:

Dim q As Query = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
if q.Contains("$TABLE$") Then
 q = q.Replace("$TABLE$", cmd.parameters(0))
 Dim params(cmd.Parameters.Length - 1)
 'copy the items to params
End If

yiankos1 · Aug 28, 2017

Erel said:

The query is created in RDCHandler.ExecuteQuery2:

Code:

Private Sub ExecuteQuery2 (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 cmd As DBCommand = m.Get("command")
   Dim limit As Int = m.Get("limit")
   Dim rs As ResultSet = con.ExecQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)

You can modify this code and treat the first parameter as the table name and create a new array with the other parameters.

Code:

Dim q As Query = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
if q.Contains("$TABLE$") Then
q = q.Replace("$TABLE$", cmd.parameters(0))
Dim params(cmd.Parameters.Length - 1)
'copy the items to params
End If

Thank you for your quick answer.

Code:

Dim q as Query

is an unknown member. I found the first part of code at jRDC2 server but i am little bit confused what am i supposed to do with second part of code and where should i put that code.

Erel · Aug 28, 2017

The type should be String.

You need to pass the query string and the parameters to con.ExecQuery2.

yiankos1 · Aug 28, 2017

Erel said:
The type should be String.

You need to pass the query string and the parameters to con.ExecQuery2.

Last question:

Code:

Dim params(cmd.Parameters.Length - 1)
'copy the items to params

Is it something missing? what type?

OliverA · Aug 28, 2017

yiankos1 said:
what type?

refers to

Erel said:
Code:

Dim q As Query = Main.rdcConnector1.GetCommand(cmd.Name)

which should be

Code:

Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)

since DBCommand Type is defined as

Code:

Type DBCommand (Name As String, Parameters() As Object)

yiankos1 · Aug 28, 2017

OliverA said:
refers to

which should be

Code:

Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)

since DBCommand Type is defined as

Code:

Type DBCommand (Name As String, Parameters() As Object)

I have already did this.

I have problem about this because it needs a type cause it throws error:

Code:

Dim params(cmd.Parameters.Length - 1)
'copy the items to params

Erel · Aug 28, 2017

It should be Object.

yiankos1 · Aug 29, 2017

Erel said:
It should be Object.

I tried your code. Table variable name working fine. When i pass a second parameter i get this error:

Code:

<pre>    java.io.NotSerializableException: java.lang.Object</pre></p>

My code at server is this:

Code:

Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)
    Dim params() As Object = cmd.Parameters
    If q.Contains("$TABLE$") Then
         q = q.Replace("$TABLE$", cmd.parameters(0))
        Dim params(cmd.Parameters.Length - 1) As Object
         'copy the items to params
    End If
       
    Dim rs As ResultSet = con.ExecQuery2(q,params)

azzam223 · Aug 29, 2017

did you try treat the last parameter as the table name

Code:

Dim q AsString = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
If q.Contains("$TABLE$") Then
q = q.Replace("$TABLE$", cmd.parameters(cmd.parameters.length-1))
Dim params(cmd.Parameters.Length - 2) As Object'copy the items to params
EndIf
Dim rs AsResultSet = con.ExecQuery2(q,params)

yiankos1 · Aug 29, 2017

azzam223 said:

did you try treat the last parameter as the table name

Code:

Dim q AsString = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
If q.Contains("$TABLE$") Then
q = q.Replace("$TABLE$", cmd.parameters(cmd.parameters.length-1))
Dim params(cmd.Parameters.Length - 2) As Object'copy the items to params
EndIf
Dim rs AsResultSet = con.ExecQuery2(q,params)

It throws this error:

Code:

<pre>    java.sql.SQLException: No value specified for parameter 1</pre></p>

If i put two times the second parameter, i get same error for NotSerializableException

azzam223 · Aug 29, 2017

what is the parameters you use in dbcommand

Erel · Aug 29, 2017

Please post the full error message with the stack trace and the line that caused the error.

azzam223 · Aug 29, 2017

yiankos1 said:
<pre> java.sql.SQLException: No value specified for parameter 1</pre></p>

this mean you have only 1 parameter

post full error message as Erel Said

yiankos1 · Aug 29, 2017

With Erel's post:

Erel said:

The query is created in RDCHandler.ExecuteQuery2:

Code:

Private Sub ExecuteQuery2 (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 cmd As DBCommand = m.Get("command")
   Dim limit As Int = m.Get("limit")
   Dim rs As ResultSet = con.ExecQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)

You can modify this code and treat the first parameter as the table name and create a new array with the other parameters.

Code:

Dim q As Query = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
if q.Contains("$TABLE$") Then
q = q.Replace("$TABLE$", cmd.parameters(0))
Dim params(cmd.Parameters.Length - 1)
'copy the items to params
End If

Server code:

Code:

Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)
    Dim params() As Object = cmd.Parameters
    If q.Contains("$TABLE$") Then
         q = q.Replace("$TABLE$", cmd.parameters(0))
        Dim params(cmd.Parameters.Length - 1) As Object
         'copy the items to params
    End If
    
    Dim rs As ResultSet = con.ExecQuery2(q,params)

Android code:

Code:

    cmd.Initialize
    cmd.Name = "dailyDuty"
    cmd.Parameters = Array As Object("ypiresiaid_25_08_2017","274263")
    reqManager.ExecuteQuery(cmd,0,"dailyDuty")

SQL command:

Code:

sql.dailyDuty= SELECT * FROM ypiresia.astynomikos, ypiresia.ypiresies_db, daily.$TABLE$ WHERE ypiresia.astynomikos.agms=? AND ypiresia.astynomikos.agms=daily.$TABLE$.agms AND ypiresia.ypiresies_db.id=daily.$TABLE$.id_ypiresias

Error code:

Code:

ResponseError. Reason: java.io.NotSerializableException: java.lang.Object, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 </title>
</head>
<body>
<h2>HTTP ERROR: 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.io.NotSerializableException: java.lang.Object</pre></p>
<hr /><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.3.z-SNAPSHOT</a><hr/>
</body>
</html>
dailyDuty
Error: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 </title>
</head>
<body>
<h2>HTTP ERROR: 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.io.NotSerializableException: java.lang.Object</pre></p>
<hr /><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.3.z-SNAPSHOT</a><hr/>
</body>

With azzam223 post:

azzam223 said:

did you try treat the last parameter as the table name

Code:

Dim q AsString = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
If q.Contains("$TABLE$") Then
q = q.Replace("$TABLE$", cmd.parameters(cmd.parameters.length-1))
Dim params(cmd.Parameters.Length - 2) As Object'copy the items to params
EndIf
Dim rs AsResultSet = con.ExecQuery2(q,params)

Same android code.

Error:

Code:

ResponseError. Reason: java.sql.SQLException: No value specified for parameter 1, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 </title>
</head>
<body>
<h2>HTTP ERROR: 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.sql.SQLException: No value specified for parameter 1</pre></p>
<hr /><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.3.z-SNAPSHOT</a><hr/>
</body>
</html>
dailyDuty
Error: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 </title>
</head>
<body>
<h2>HTTP ERROR: 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.sql.SQLException: No value specified for parameter 1</pre></p>
<hr /><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.3.z-SNAPSHOT</a><hr/>
</body>

If i put a third parameter, it will show me same error as above about "NotSerializableException"

azzam223 · Aug 29, 2017

on which line error happend

yiankos1 said:
ResponseError. Reason: java.sql.SQLException: No value specified for parameter 1, Response: <html>

you must put table name in last parameters not first one like this

Code:

cmd.Parameters = ArrayAs Object("274263","ypiresiaid_25_08_2017")

OliverA · Aug 29, 2017

yiankos1 said:

My code at server is this:

Code:

Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)
    Dim params() As Object = cmd.Parameters
    If q.Contains("$TABLE$") Then
         q = q.Replace("$TABLE$", cmd.parameters(0))
        '***************************************************
        'The command below will create a new array named params that
        'is sized one less then the Parameter array passed to the server
        '***************************************************
        Dim params(cmd.Parameters.Length - 1) As Object
         'copy the items to params
         '*******************************************************
         'As @Erel points out, here you should copy the cmd.Parameters array 
         'to the params array, skipping the first element of cmd.Parameters.
         'If you do not do this, the call below (ExecQuery2) will be called with an
         'empty array!
         '*******************************************************
    End If
      
    Dim rs As ResultSet = con.ExecQuery2(q,params)

@Erel's solution uses Table name as the first parameter passed to the server (when required). If your query includes the string $TABLES$, the first parameter (params(0)) is used as the table name. At that point, parmas is reDIMmed and becomes and empty array! So now when you call con.ExecQuery2, you are calling it with an empty array! Please note that @Erel notes that you should "copy the items to params" after the reDim and you have not done that and therefore you are passing an empty array to ExecQuery2!

OliverA · Aug 29, 2017

azzam223 said:

did you try treat the last parameter as the table name

Code:

Dim q AsString = Main.rdcConnector1.GetCommand(cmd.Name)
Dim params() As Object = cmd.Parameters
If q.Contains("$TABLE$") Then
q = q.Replace("$TABLE$", cmd.parameters(cmd.parameters.length-1))
Dim params(cmd.Parameters.Length - 2) As Object'copy the items to params
EndIf
Dim rs AsResultSet = con.ExecQuery2(q,params)

The issue is not which index location of the parameter (first or last) the table name is located, the issue is that the params array is not repopulated after is reDIMmed.

Also

Code:

Dim params(cmd.Parameters.Length - 2)

should still just be

Code:

Dim params(cmd.Parameters.Length - 1)

. The new params array is only one item shorter (the table name) than the original params array. The issue now is that this new params array is empty and has not been populated. The crucial missing steps are to copy the proper, non-table, elements from the passed on Parameters to the new, one less element, params array. Without that step, an empty array is used in the ExecQuery2 method and produces the failure/error message.

Android Question Variable table name through jRDC

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

Expert

Well-Known Member

B4X founder

Well-Known Member

Active Member

Well-Known Member

Active Member

B4X founder

Active Member

Well-Known Member

Active Member

Expert

Expert

Similar Threads