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

[JDR]

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:

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

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

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

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!

 

[DonManfred]

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

 

[aeric]

I tried in debug mode, commands are correct now, but the program is now recompiled:

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.


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?

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.

 

[JDR]

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!