Android Question Read from Variable Table Name in SQL

Discussion in 'Android Questions' started by German Buchmuller, Jul 22, 2019.

  1. German Buchmuller

    German Buchmuller Member Licensed User

    Hi, I got a problem. I want to get data from a variable table name. Example:
    B4A:
    ("id" is the table name)
    Code:
    Sub GetCuponCode(id As String)
        
    Dim req As DBRequestManager = CreateRequest
        
    Dim cmd As DBCommand = CreateCommand("selectCuponCodeTenti"Array(id))
        
    Wait For (req.ExecuteQuery(cmd, 0Null)) JobDone(j As HttpJob)
    End Sub
    jRDC2 Config File:
    Code:
    sql.selectCuponCodeTenti=SELECT * FROM ?
    I hope you get the idea of what I need, and can help me to find the right solution. Thanks
     
    Last edited: Jul 22, 2019
  2. emexes

    emexes Well-Known Member Licensed User

    Does that mean:
    1/ the name of the table is "id", or
    2/ the name of the table is held in the variable called "id"?

    I haven't actually used SQL in B4A, but according to:

    https://www.b4x.com/android/forum/t...connector-rdc-connect-to-any-remote-db.31540/
    and you have:
    so, eg with id = "example123" I would expect your code to generate the command:

    SELECT * FROM example123

    Is that what actually happens? More importantly, is that what you want?
     
  3. German Buchmuller

    German Buchmuller Member Licensed User

    Thanks for the reply!
    2. The name of the tabble is held in the variable called "id".
    Yes, that is exactly whay I want. But I get a syntax error when executing that command on the logs in B4A. The error is:
    Code:
    ResponseError. Reason: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''tenti001' WHERE used = 0 LIMIT 1' at line 1, Response: <html>
    <head>
    <meta http-equiv=
    "Content-Type" content="text/html;charset=utf-8"/>
    <title>Error 
    500 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &apos;&apos;tenti001&apos; WHERE used = 0 LIMIT 1&apos; at line 1</title>
    </head>
    <body><h2>HTTP ERROR 
    500</h2>
    <p>Problem accessing /rdc. Reason:
    <pre>    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error 
    in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &apos;&apos;tenti001&apos; WHERE used = 0 LIMIT 1&apos; at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
    </body>
    </html>
    ERROR: <html>
    <head>
    <meta http-equiv=
    "Content-Type" content="text/html;charset=utf-8"/>
    <title>Error 
    500 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &apos;&apos;tenti001&apos; WHERE used = 0 LIMIT 1&apos; at line 1</title>
    </head>
    <body><h2>HTTP ERROR 
    500</h2>
    <p>Problem accessing /rdc. Reason:
    <pre>    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error 
    in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &apos;&apos;tenti001&apos; WHERE used = 0 LIMIT 1&apos; at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
    </body>
    </html>
    ** 
    Activity (cuponelegido) Pause, UserClosed = false **
     
  4. emexes

    emexes Well-Known Member Licensed User

    The bad command that the error quotes is:

    'tenti001' WHERE used = 0 LIMIT 1

    which looks nothing like the example you gave.

    1/ What are the actual B4A and jRDC2 Config commands that generate the "tenti001" command?
    2/ What is the SQL command that you are expecting?
     
    Last edited: Jul 22, 2019
  5. German Buchmuller

    German Buchmuller Member Licensed User

    This is the actual config file command on jRDC2:
    Code:
    sql.selectCuponCodeTenti=SELECT * FROM ? WHERE used = 0 LIMIT 1
    I did not include "WHERE used = 0 LIMIT 1" before in here
    so that it was easier to understand what I was talking about.
    Anyways, if instead of
    Code:
    sql.selectCuponCodeTenti=SELECT * FROM ? WHERE used = 0 LIMIT 1
    I write :
    Code:
    sql.selectCuponCodeTenti=SELECT * FROM tenti001 WHERE used = 0 LIMIT 1
    I dont get any error.
    The error is in the way I am entering the parameter. Perhaps, a variable table name should not be entered by "?".
     
  6. OliverA

    OliverA Well-Known Member Licensed User

    Parameterized queries do not allow such. What you are trying to create is dynamic SQL, not parameterized SQL. If that is what you really want to do, then take a look at this: https://www.b4x.com/android/forum/threads/variable-table-name-through-jrdc.83262/#post-527239
    Before implementing this, why not read this first: http://www.sommarskog.se/dynamic_sql.html. I know it's a long read, but everyone that contemplates on implementing dynamic SQL should read that (IMHO).
     
    emexes likes this.
  7. German Buchmuller

    German Buchmuller Member Licensed User

    T
    Thanks. I found that I should be using something like:
    Code:
    declare @tablename varchar(50)
    set @tablename = 
    'test'
    EXEC('SELECT * FROM ' + @tablename)
    However, I dont quite know how to set @tablename to be my parameter from my android App.
    In the config file, I should somehow say that "@tablename = ?" right? so that tablename will be my parameter.
    I dont know how to do it. Never programmed in sql.
    I wrote in the config file:
    Code:
    sql.selectCuponCodeTenti=
    declare @tablename varchar(
    50)
    set @tablename = ?
    EXEC(
    'SELECT * FROM ' + @tablename)
    but clearly it does not work. Im getting parameter index out of bounds exception. Any idea? Thanks and sorry im just trying to understand sql.
     
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