Android Question Read from Variable Table Name in SQL

German Buchmuller · Jul 22, 2019

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, 0, Null)) 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

emexes · Jul 22, 2019

German Buchmuller said: ↑

"id" is the table name
Click to expand...

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/

A command is an object of type DBCommand:
Type DBCommand (Name As String, Parameters() As Object)
Name - The case sensitive command name as configured in the server configuration (without sql.).
Parameters - An array of objects that will be sent to the server and will replace the question marks in the command.
Click to expand...

and you have:

jRDC2 Config File:
sql.selectCuponCodeTenti=SELECT * FROM ?
Click to expand...

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?

German Buchmuller · Jul 22, 2019

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 **

emexes · Jul 22, 2019

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?

German Buchmuller · Jul 22, 2019

emexes said: ↑

The bad command that the error quotes is:

'tenti001' WHERE used = 0 LIMIT 1

which looks nothing like the example you gave. What are the actual B4A and jRDC2 Config commands that generates the "tenti001" command?
Click to expand...

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

jRDC2 Config File:
Click to expand...

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 "?".

OliverA · Jul 22, 2019

German Buchmuller said: ↑

a variable table name should not be entered by "?"
Click to expand...

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

German Buchmuller · Jul 22, 2019

T

OliverA said: ↑

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).
Click to expand...

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.

Log in or Sign up

Android Question Read from Variable Table Name in SQL

Similar threads

German Buchmuller Member Licensed User

emexes Well-Known Member Licensed User

German Buchmuller Member Licensed User

emexes Well-Known Member Licensed User

German Buchmuller Member Licensed User

OliverA Expert Licensed User

German Buchmuller Member Licensed User