B4J Question [B4X] jRDC2 - Issues with sql command not found

JDR

Member
Hello

I'm experimenting with the jRDC2 tool, but at this point, I'm receiving a weird issue.
I created a sql query, just like in this tutorial, but for some queries I receive the message that the command could not be found out of the config.properties file.

jRDC2 server:

config.properties:
#SQL COMMANDS
# DB_USERS commands
sql.get_userid_ibuttonid=select userID from where DB_USERS.TBL_IBUTTON.IButtonID = ?
sql.get_all_users=select * from DB_USERS.TBL_USERS
sql.get_IbuttonID_user=select IButtonID from DB_USERS.TBL_IBUTTON where userID = ?
sql.get_user_UserID=select * from DB_USERS.TBL_USERS where userID = ?

Server error message for the get_userid_ibuttonid command:
jRDC2 server error message:
db java[11803]: 10/08/2022 10:59:16 *** Command not found: get_userid_ibuttonid
db java[11803]: [ WARN] (qtp1496724653-20) Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB
db java[11803]: 10/08/2022 10:59:16 (SQLSyntaxErrorException) java.sql.SQLSyntaxErrorException: (conn=383) You have an error in your SQL syntax; chec
db java[11803]: 10/08/2022 10:59:16 Command: , took: 5ms, client=127.0.0.1


jRDC2 client
On the jRDC2 client, the command get_all_users works perfectly fine, all the users out of the database are passed through jRDC2 and stored in the program.
But the command get_userid_ibuttonid always returns that the command could not be found on the server and a http 500 message to the client.
I call the sql command with the following sub (all other related subs and code module DBRequestManager are copied out of the jRDC2 tutorial):
jRDC2 command GetUserIDIButtonID method:
' This method will send a jRDC commando with a IbuttonID as parameter in order to receive the corresponding UserID
Sub GetUserIDIButtonID (iButtonID As String)
    Private req As DBRequestManager = CreateRequest
    Private cmd As DBCommand = CreateCommand("get_userid_ibuttonid", Array(iButtonID))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        ' Check if resulting data is not empty
        If res.Rows.Size > 0 Then
            Private row() As Object = res.Rows.Get(0)
            Private userID As String = row(res.Columns.Get("userID"))
            Main.userIButton.Initialize(iButtonID,userID)
        Else
            Logger.Logging("UserID for the given IbuttonID could not be found.")
        End If
    Else
        Logger.Logging($"ERROR: ${j.ErrorMessage}"$)
    End If
    j.Release
End Sub

Client error message for the get_userid_ibuttonid command:
jRDC2 clienterror message:
ResponseError. Reason: java.sql.SQLSyntaxErrorException: (conn=390) 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 'null' at line 1, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 java.sql.SQLSyntaxErrorException: (conn=390) 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;null&apos; at line 1</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.sql.SQLSyntaxErrorException: (conn=390) 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;null&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 java.sql.SQLSyntaxErrorException: (conn=390) 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;null&apos; at line 1</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.sql.SQLSyntaxErrorException: (conn=390) 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;null&apos; at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>


Database server
It's not that the sql query is invalid, as I can execute the query succesfully on the database directly:
2022-08-10 11_24_37-DBeaver 22.1.3 - _MariaDB-001_ Script-7.png


What can cause this issue?

Thank you in advance!
 
Solution
I tried in debug mode, commands are correct now, but the program is now recompiled:
jRDC server log:
Waiting for debugger to connect...
Program started.
2022-08-16 11:47:01.159:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2022-08-16 11:47:01.252:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
sql.get_userid_ibuttonid
sql.get_gameday
sql.get_user_UserID
sql.get_IbuttonID_user
sql.get_all_users
2022-08-16 11:47:01.330:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 11.0.16+8-post-Debian-1deb10u1
2022-08-16 11:47:01.407:INFO :oejss.DefaultSessionIdManager:main: Session...

JDR

Member
In this SQL statement there is no table name.
Unfortunately It's not the SQL command itselfs, I changed the SQL query in the config.properties file like this:
config.properties:
#SQL COMMANDS
sql.get_userid_ibuttonid=select userID from DB_USERS.TBL_IBUTTON where IButtonID = ?
sql.get_all_users=select * from DB_USERS.TBL_USERS
sql.get_IbuttonID_user=select IButtonID from DB_USERS.TBL_IBUTTON where userID = ?
sql.get_user_UserID=select * from DB_USERS.TBL_USERS where userID = ?

After that I restarted jRDC2, I still got the exact same error:
jRDC error message:
11/08/2022 09:30:08 *** Command not found: get_userid_ibuttonid
Aug 11 09:30:08 mariadb-001 java[14283]: [ WARN] (qtp1496724653-15) Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version
Aug 11 09:30:08 mariadb-001 java[14283]: 11/08/2022 09:30:08 (SQLSyntaxErrorException) java.sql.SQLSyntaxErrorException: (conn=501) You have an error in your SQL syntax; check the manual th
Aug 11 09:30:08 mariadb-001 java[14283]: 11/08/2022 09:30:08 Command: , took: 4ms, client=127.0.0.1


What else could be wrong?

Thank you in advance!
 
Upvote 0

PaulMeuris

Active Member
Licensed User
You have an error in your SQL syntax;
There still is an error in your SQL syntax. The error message should indicate 'near ...' but i can't see this because the message is truncated. Something like near 'null' for instance.
Can you send the database table structures from DB_USERS.TBL_IBUTTON and DB_USERS.TBL_USERS ?
Can you show the complete messages ?
 
Upvote 0

John Naylor

Active Member
Licensed User
Longtime User
Your JRDC2 connection (should) already know what database you are connecting to so there is no need to reference it in your SQL. Using dots in SQL is never good.

So - you first connect to DB_USERS with what you put in the #DATABASE CONFIGURATION section which is earlier in the file.

Something like this

B4X:
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
DriverClass=com.mysql.jdbc.Driver
JdbcUrl=jdbc:mysql://localhost/DB_USERS?characterEncoding=utf8
User=YourDatabaseUser
Password=UserPassword
ServerPort=17179

#That is your connection to the DB_USERS database sorted out. 
#Note that ServerPort= is the port that JRDC2 is to be run on, not the port your database is using.
#Finally define your SQL statements (without reference to the database name)

sql.get_userid_ibuttonid=select userID from TBL_IBUTTON where IButtonID = ?
sql.get_all_users=select * from TBL_USERS
sql.get_IbuttonID_user=select IButtonID from TBL_IBUTTON where userID = ?
sql.get_user_UserID=select * from TBL_USERS where userID = ?
 
Last edited:
Upvote 0

JDR

Member
There still is an error in your SQL syntax. The error message should indicate 'near ...' but i can't see this because the message is truncated. Something like near 'null' for instance.
Can you show the complete messages ?
Sorry, didn't thought about the truncated output, this is the fill error message. It looks like the query is executed without the parameter I'm adding inside in the jRDC client:
jRDC server error message:
*** Command not found: get_userid_ibuttonid
mariadb java[14283]: [ WARN] (qtp1496724653-16) Error: 1064-42000: 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 'null' at line 1
mariadb java[14283]: (SQLSyntaxErrorException) java.sql.SQLSyntaxErrorException: (conn=753) 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 'null' at line 1
mariadb java[14283]: Command: , took: 5ms, client=127.0.0.1

The jRDC client:
B4X:
' Callinge sub (variables are not null, reviewed in debug mode)
jrdc.GetUserIDIButtonID(receivedIButtonID.ToUpperCase)   

' The sub itselfs:
' This method will send a jRDC commando with a IbuttonID as parameter in order to receive the corresponding UserID
Sub GetUserIDIButtonID (iButtonID As String)
    Private req As DBRequestManager = CreateRequest
    Private cmd As DBCommand = CreateCommand("get_userid_ibuttonid", Array(iButtonID))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        ' Check if resulting data is not empty
        If res.Rows.Size > 0 Then
            Private row() As Object = res.Rows.Get(0)
            Private userID As String = row(res.Columns.Get("userID"))
            Main.userIButton.Initialize(iButtonID,userID)
        Else
            Logger.Logging("UserID for the given IbuttonID could not be found.")
        End If
    Else
        Logger.Logging($"ERROR: ${j.ErrorMessage}"$)
    End If
    j.Release
End Sub

Can you send the database table structures from DB_USERS.TBL_IBUTTON and DB_USERS.TBL_USERS ?
DB structure:
CREATE DATABASE /*!32312 IF NOT EXISTS*/`DB_USERS` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `DB_USERS`;

DROP TABLE IF EXISTS `TBL_USERS`;

CREATE TABLE `TBL_USERS` (
  `userID` int not null AUTO_INCREMENT,
  `firstName` nvarchar(100) default NULL,
  `lastName` nvarchar(100) default NULL,
  `gender` nvarchar(10) default NULL,
  `street` nvarchar(255) default NULL,
  `houseNumber` nvarchar(8) default NULL,
  `postalCode` nvarchar(10) default NULL,
  `city` nvarchar(150) default NULL,
  `birthDate` date default NULL,
  `birthPlace` nvarchar(150) default NULL,
  `country` nvarchar(100) default NULL,
  `password` nvarchar(150) default NULL,
  `gsm` nvarchar(15) default NULL,
  `email` nvarchar(100) default NULL,
  `privateEmail` nvarchar(100) default NULL,
  `language` nvarchar(100) default NULL,
  `hireDate` date default NULL,
  PRIMARY KEY (`userID`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

DROP TABLE IF EXISTS `TBL_IBUTTON`
CREATE TABLE `TBL_IBUTTON`(
  `IButtonID` nvarchar(16) not null,
  `userID` int not null,
  PRIMARY KEY (`IButtonID`),
  CONSTRAINT `fk_IButton_userID` FOREIGN KEY (userID) REFERENCES TBL_USERS (userID)
);

Your JRDC2 connection (should) already know what database you are connecting to so there is no need to reference it in your SQL. Using dots in SQL is never good.

So - you first connect to DB_USERS with what you put in the #DATABASE CONFIGURATION section which is earlier in the file then

That's correct, the config.properties file is build up with all the needed variables for the database connection (DriverClass, JdbcUrl, User, Password, ServerPort,...).
What's not good at using dots in SQL queries? I would like to now why I should avoid it ;)

Thank you for your assistance!
 
Upvote 0

John Naylor

Active Member
Licensed User
Longtime User
That's correct, the config.properties file is build up with all the needed variables for the database connection (DriverClass, JdbcUrl, User, Password, ServerPort,...).
What's not good at using dots in SQL queries? I would like to now why I should avoid it ;)

Thank you for your assistance!
I should have put that better - I had issues doing it that way whilst trying to connect to multiple databases on different servers so I avoid it.
 
Upvote 1

John Naylor

Active Member
Licensed User
Longtime User
B4X:
    Private cmd As DBCommand = CreateCommand("get_userid_ibuttonid", Array(iButtonID))

iButtonID definately has a value at this point?
 
Upvote 0

John Naylor

Active Member
Licensed User
Longtime User
OK - I set up a database on a VPS, ran JRDC2 set up with your queries, used your client code (worked into a B4A app), assuming iButtonID has a value, and it all works fine.

I'm using B4J 9.8
Openjdk 11.0.16
mysql Ver 15.1 Distrib 10.2.44-MariaDB, for Linux (x86_64)
B4A 11.8

All the latest libraries
 
Last edited:
Upvote 0

JDR

Member
Thank you for testing the code, I'm also using the exact same version, except for mariadb where it's newer:

jRDC client

B4J 9.80 (x64)
Openjdk 11.0.16

jRDC Server

Openjdk 11.0.16
mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64)

But I don't think it's a MariaDB version problem, as other queries where executed correctly by the jRDC server.

When debugging the jRDC client, I can see that for some reason the httpJob's Success parameter has the value "False" which results in not executing the query.
1660480112894.png


I think It's an issue on the jRDC server side, I uploaded the code as an attachement of this post.
 

Attachments

  • jRDC.zip
    148.7 KB · Views: 221
Upvote 0

JDR

Member
config.properties:
Bash:
sql.get_useridibuttonid=select userID from DB_USERS.TBL_IBUTTON where IButtonID = ?;

Can you spot the difference?

get_userid_ibuttonid vs get_useridibuttonid <- missing one underscore
I'm sorry, the config.properties file in the jRDC server I provided early wasn't the latest version running, the screenshot below is the live version:
1660490907937.png


As I can verify in the logs, the command in the config.properties is correct:
1660491045330.png


I hoped it was just a damn typo in code or config file ?
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Try check the sql commands in config.properties by adding the highlighted code in RDCConnector:
B4X:
Public Sub Initialize
    pool.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"), config.Get("User"), _
        config.Get("Password"))
#if DEBUG
    DebugQueries = True
#else
    DebugQueries = False
#end if
    serverPort = config.Get("ServerPort")
    serverSSLPort = config.Get("ServerSSLPort")
    For Each cmd As String In config.Keys
        If cmd.StartsWith("sql.") Then Log( cmd )
    Next
    LoadSQLCommands(config)
End Sub
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
After that I restarted jRDC2, I still got the exact same error:
after you updated the config.properties file, did you restart the jRDC server?
Please note that if you use the standard jRDC2 server, you have to recompile the server everytime you make a modification to the config.properties file. The author of the post did not clearly indicate if that is what he is doing.
 
Upvote 1

JDR

Member
Try check the sql commands in config.properties by adding the highlighted code in RDCConnector:
B4X:
Public Sub Initialize
    pool.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"), config.Get("User"), _
        config.Get("Password"))
#if DEBUG
    DebugQueries = True
#else
    DebugQueries = False
#end if
    serverPort = config.Get("ServerPort")
    serverSSLPort = config.Get("ServerSSLPort")
    For Each cmd As String In config.Keys
        If cmd.StartsWith("sql.") Then Log( cmd )
    Next
    LoadSQLCommands(config)
End Sub
I tried in debug mode, commands are correct now, but the program is now recompiled:
jRDC server log:
Waiting for debugger to connect...
Program started.
2022-08-16 11:47:01.159:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2022-08-16 11:47:01.252:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
sql.get_userid_ibuttonid
sql.get_gameday
sql.get_user_UserID
sql.get_IbuttonID_user
sql.get_all_users
2022-08-16 11:47:01.330:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 11.0.16+8-post-Debian-1deb10u1
2022-08-16 11:47:01.407:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
~e:
2022-08-16 11:47:01.417:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@712625fd{/,file:///tmp/tempjars/www,AVAILABLE}
2022-08-16 11:47:01.435:INFO :oejs.RequestLogWriter:main: Opened /tmp/tempjars/logs/b4j-2022_08_16.request.log
2022-08-16 11:47:01.440:INFO :oejs.AbstractConnector:main: Started ServerConnector@7e07db1f{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2022-08-16 11:47:01.444:INFO :oejs.Server:main: Started Server@26b3fd41{STARTING}[11.0.9,sto=0] @2742ms
Emulated network latency: 100ms
16/08/2022 11:47:01 jRDC is running (version = 2.23)
2022-08-16 11:47:20.140:INFO :cmvci.AbstractPoolBackedDataSource:main: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge1i9aq1a2mbpe1k9zuwn|4fe3c938, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.mariadb.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge1i9aq1a2mbpe1k9zuwn|4fe3c938, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mariadb://127.0.0.1/DB_USERS?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxCo
nnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
16/08/2022 11:47:20 Command: query: get_userid_ibuttonid, took: 99ms, client=127.0.0.1

And that works.

Please note that if you use the standard jRDC2 server, you have to recompile the server everytime you make a modification to the config.properties file. The author of the post did not clearly indicate if that is what he is doing.
Yes, thank you, didn't knew that. Now the jRDC2 server can read the sql command and execute it correctly.
But it's not very usefull for me to recompile the jRDC2 server every time I just want to add or update a SQL query inside the config.properties file.
I thought editing a config file near the compiled jRDC server would interpret the changed config.properties file every time the jRDC server is restarted.

Any ideas how I can achieve this?
 
Upvote 0
Top