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)

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

emexes · Jul 22, 2019

German Buchmuller said:
"id" is the table name

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.

and you have:

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

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!

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

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?

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

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?

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:

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 · Jul 22, 2019

German Buchmuller said:
a variable table name should not be entered by "?"

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

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.

Android Question Read from Variable Table Name in SQL

German Buchmuller

Member

emexes

Expert

German Buchmuller

Member

emexes

Expert

German Buchmuller

Member

OliverA

Expert

German Buchmuller

Member

Similar Threads