Android Question Read from Variable Table Name in SQL

G

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)
B4X: 
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:
B4X: 
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:
E

emexes

Well-Known Member
Licensed User
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?
 
G

German Buchmuller

Member
Licensed User
Thanks for the reply!
emexes said:
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"?
Click to expand...
2. The name of the tabble is held in the variable called "id".
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?
Click to expand...
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:
B4X: 
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 **
 
E

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:
G

German Buchmuller

Member
Licensed User
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:
B4X: 
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
B4X: 
sql.selectCuponCodeTenti=SELECT * FROM ? WHERE used = 0 LIMIT 1
I write :
B4X: 
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

OliverA

Expert
Licensed User
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).
 
G

German Buchmuller

Member
Licensed User
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:
B4X: 
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:
B4X: 
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.
 
You must log in or register to reply here.
Top