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:


What can cause this issue?

Thank you in advance!
 
Solution

DonManfred

Expert
Licensed User
Longtime User
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.
Set debug to true in the configfile. Adapt the path for the ini too to match a folder where it can access the file.
Do not use the files-folder as this is a path inside the jar (you can not modify the ini)....

 
Upvote 1

aeric

Expert
Licensed User
Longtime User
Then you need to put the config file outside the asset (Files) folder. Eg. inside the Objects folder and this file will not be compiled into the jar. However, to refresh the commands, you need to restart the server.
 
Upvote 1
Solution

JDR

Member
Set debug to true in the configfile. Adapt the path for the ini too to match a folder where it can access the file.
Do not use the files-folder as this is a path inside the jar (you can not modify the ini)....

View attachment 132605
Thank you for that advice, I'll try it!

Then you need to put the config file outside the asset (Files) folder. Eg. inside the Objects folder and this file will not be compiled into the jar. However, to refresh the commands, you need to restart the server.
Ow god, didn't noticed the difference between the File.DirAssets and File.DirApp to read the config.properties file,...

Now it works as it should! Thank you for your help and assitance!
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…