Android Question Variable table name through jRDC

Status
Not open for further replies.

yiankos1

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

Android code:
B4X:
    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
B4X:
sql.selectAll=SELECT * FROM daily.? WHERE daily.?.agms=265475

I get error setting "?" for variable table name
B4X:
<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

Administrator
Staff member
Licensed User
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.
 
Upvote 0

yiankos1

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

Erel

Administrator
Staff member
Licensed User
The query is created in RDCHandler.ExecuteQuery2:
B4X:
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.

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

yiankos1

Active Member
Licensed User
The query is created in RDCHandler.ExecuteQuery2:
B4X:
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.

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

yiankos1

Active Member
Licensed User
The type should be String.

You need to pass the query string and the parameters to con.ExecQuery2.
Last question:
B4X:
Dim params(cmd.Parameters.Length - 1)
'copy the items to params

Is it something missing? what type?
 
Upvote 0

OliverA

Expert
Licensed User
what type?
refers to
B4X:
Dim q As Query = Main.rdcConnector1.GetCommand(cmd.Name)
which should be
B4X:
Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)
since DBCommand Type is defined as
B4X:
Type DBCommand (Name As String, Parameters() As Object)
 
Upvote 0

yiankos1

Active Member
Licensed User
refers to

which should be
B4X:
Dim q As String = Main.rdcConnector1.GetCommand(cmd.Name)
since DBCommand Type is defined as
B4X:
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:
B4X:
Dim params(cmd.Parameters.Length - 1)
'copy the items to params
 
Upvote 0

yiankos1

Active Member
Licensed User
It should be Object.

I tried your code. Table variable name working fine. When i pass a second parameter i get this error:
B4X:
<pre>    java.io.NotSerializableException: java.lang.Object</pre></p>

My code at server is this:
B4X:
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)
 
Upvote 0

azzam223

Active Member
Licensed User
did you try treat the last parameter as the table name

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

yiankos1

Active Member
Licensed User
did you try treat the last parameter as the table name

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

yiankos1

Active Member
Licensed User
With Erel's post:
The query is created in RDCHandler.ExecuteQuery2:
B4X:
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.

B4X:
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:
B4X:
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:
B4X:
    cmd.Initialize
    cmd.Name = "dailyDuty"
    cmd.Parameters = Array As Object("ypiresiaid_25_08_2017","274263")
    reqManager.ExecuteQuery(cmd,0,"dailyDuty")

SQL command:
B4X:
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:
B4X:
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:
did you try treat the last parameter as the table name

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

azzam223

Active Member
Licensed User
on which line error happend

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

B4X:
cmd.Parameters = ArrayAs Object("274263","ypiresiaid_25_08_2017")
;)
 
Last edited:
Upvote 0

OliverA

Expert
Licensed User
My code at server is this:
B4X:
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!
 
Upvote 0

OliverA

Expert
Licensed User
did you try treat the last parameter as the table name

B4X:
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
B4X:
Dim params(cmd.Parameters.Length - 2)
should still just be
B4X:
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.
 
Upvote 0
Status
Not open for further replies.
Top