Android Question Variable table name through jRDC

Discussion in 'Android Questions' started by yiankos1, Aug 28, 2017.

Thread Status:
Not open for further replies.
  1. yiankos1

    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:
    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.
     
  2. Erel

    Erel Administrator Staff 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.
     
    yiankos1 likes this.
  3. yiankos1

    yiankos1 Active Member Licensed User

    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.
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    The query is created in RDCHandler.ExecuteQuery2:
    Code:
    Private Sub ExecuteQuery2 (con As SQLin As InputStream,  resp As ServletResponseAs 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 likes this.
  5. yiankos1

    yiankos1 Active Member Licensed User

    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.
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    The type should be String.

    You need to pass the query string and the parameters to con.ExecQuery2.
     
    yiankos1 likes this.
  7. yiankos1

    yiankos1 Active Member Licensed User

    Last question:
    Code:
    Dim params(cmd.Parameters.Length - 1)
    'copy the items to params
    Is it something missing? what type?
     
  8. OliverA

    OliverA Expert Licensed User

    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)
     
  9. yiankos1

    yiankos1 Active Member Licensed User

    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
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    It should be Object.
     
    yiankos1 likes this.
  11. yiankos1

    yiankos1 Active Member Licensed User

    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 - 1As Object
             
    'copy the items to params
        End If
           
        
    Dim rs As ResultSet = con.ExecQuery2(q,params)
     
  12. azzam223

    azzam223 Active Member Licensed User

    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 - 2As Object'copy the items to params
    EndIf
    Dim rs AsResultSet = con.ExecQuery2(q,params)
     
    yiankos1 likes this.
  13. yiankos1

    yiankos1 Active Member Licensed User

    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
     
  14. azzam223

    azzam223 Active Member Licensed User

    what is the parameters you use in dbcommand
     
  15. Erel

    Erel Administrator Staff Member Licensed User

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

    azzam223 Active Member Licensed User

    this mean you have only 1 parameter

    post full error message as Erel Said
     
  17. yiankos1

    yiankos1 Active Member Licensed User

    With Erel's post:

    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 - 1As 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:
    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"
     
    Last edited: Aug 29, 2017
  18. azzam223

    azzam223 Active Member Licensed User

    on which line error happend

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

    Code:
    cmd.Parameters = ArrayAs Object("274263","ypiresiaid_25_08_2017")
    ;)
     
    Last edited: Aug 29, 2017
  19. OliverA

    OliverA Expert Licensed User

    @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!
     
  20. OliverA

    OliverA Expert Licensed User

    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.
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice