B4J Library Alpha/Beta testing of jRDC2 server with stored procedures support

Looking for testers!!!!

This modification adds support for calling stored procedures that can return zero or more result sets and zero or more output parameters to my previously modified jRDC2 server (https://www.b4x.com/android/forum/threads/modded-jrdc2-w-sqlite-support-and-more.85578/). It uses JDBC’s CallableStatement (https://docs.oracle.com/javase/8/docs/api/java/sql/CallableStatement.html) and has the ability to use named parameters. For all JDBC drivers, this allows you to name parameters that are passed to the CallableStatement. As for using named parameters as part of your SQL statement, that is JDBC driver specific (and version 5.1.49 for MySQL does not seem to support them).

In order to support CallableStatment, an additional method (ExecuteCall2) was added to the jRDC2 code and two methods (ExecuteCall and HandleCallJobAsync) have been added to the DBRequestManager.

Example Usage:

In config.properties:

sql.UserLogin=CALL spUserLogin_GET(?, ?, ?)

Note: No named parameters as part of the SQL statement. Connector/J 5.1.49 does not seem to support it. I may be wrong and will be gladly corrected on this.

In MySQL (this is just for testing. You’ll have to create your own routine):

CREATE DEFINER=`root`@`%` PROCEDURE `spUserLogin_GET`(       IN varUserName VARCHAR(255),
       IN varPassword VARCHAR (255),
       OUT varResponse LONGTEXT)
sp:
BEGIN
     DECLARE tempUserId VARCHAR(20) DEFAULT "";
   
     #check if all required fields are not empty
     IF varUserName = '' OR varPassword = '' THEN
        SET varResponse = '{"status": 105, "message": "Please fill up all required fields."}';
        LEAVE sp;
     END IF;
   
     SELECT id
     INTO tempUserId
     FROM user_accounts
     WHERE username = varUserName AND password = varPassword;
   
     IF tempUserId = '' THEN
        SET varResponse = '{"status": 104, "message": "Invalid user credentials."}';
        LEAVE sp;
     END IF;
   
     SET varResponse = '{"status": 100, "message": "Login Successfully!"}';
   
     SELECT @varResponse as response;
END

On the client, setting up the DBCommand for the above would be something like this:

    Dim cmd As DBCommand = CreateCommand("UserLogin", _
        Array("John", CreateMap("value":"Doe"), CreateMap("name":"varResponse", "type":"OUT", "sqlType": "VARCHAR")))

Note the parameter array. This array will now contain either values only, maps only, or a combination thereof. If the array item is a value only (such as “John” above) it will set the parameter via the setObject method (just like the SQL library does). If a map is used, then the following happens:

The type of parameter (IN, INOUT, OUT) is determined by the key “type”. If that key is missing, the parameter defaults to IN.

The SQL type that should be used for a given parameter can get set by using the “sqlType” key. This should be set to a string of the desired type available for a given “sqlTypeClass”. As is, the only “sqlTypeClass” supported is “JDBC” (and if “sqlTypeClass” is not set, will default to “JDBC”). The “JDBC” type class is mapped to “java.sql.Types” and he values for this class can be found here: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver15. Other type classes can be added, but require a modification of the jRDC2 servers code (It happens in the few lines of code follwing AppStart).

To pass on a SQL NULL value for an IN/INOUT parameter, the “sqlType” must be set and the “value” key must not be present. So

CreateMap(“sqlType”:”VARCHAR”)

Will call CallableStatement’s setNull method, passing it the VARCHAR SQL type.

For OUT parameters, the minimum requirements are the “type” key (can be OUT or INOUT) and the “sqlType” key. If the “name” key is missing, then the index position (plus one) within the parameters array will be used to set the parameter. If the “name” key is set, then the value of the “name” key will be used to set the parameter. In the example above

CreateMap("name":"varResponse", "type":"OUT", "sqlType": "VARCHAR")

will translate to the following method call

registerOutParameter(“varResponse”, 12)

Whereas if the map would be

CreateMap("type":"OUT", "sqlType": "VARCHAR")

would produce the following call

registerOutParameter(3, 12)

To execute the command, use the new ExecuteCall method

Wait For (req.ExecuteCall(cmd, 0, Null)) JobDone(j As HttpJob)

If this call is successful, use the new HandleCallJobAsync method with its associated _CallResult callback event to process the results

    If j.Success Then
        Log("Call executed successfully")
        req.HandleCallJobAsync(j, "req")
        Wait For (req) req_CallResult(resultSets As List, parameters As Map)
        'Let's print out the returned result sets. resultSets is a list containing 0 or more DBResult objects.
        For Each res As DBResult In resultSets
            req.PrintTable(res)
        Next
        'Let's print the returned OUT parameters
        Log($"Parameters map content: ${parameters}"$)
    Else
        Log($"ERROR: ${j.ErrorMessage}"$)
    End If

Note: This jRDC2 modification is for testing purposes only (and I hope some of you will test this code). Run the modified jRDC2 server in DEBUG mode. There should be plenty of Log() statements to inform one of what is going on. Any issues encountered, please relay them to me in this thread.

Note2: Thanks to @Chris Guanzon for kick starting this journey

Note3: This write-up probably leaves a lot of details out. The source should help with some questions, otherwise, feel free to ask.

Updates:
2020/10/09: Update (jRDCv2_mod.2020.10.09.02a_published.zip) contains the code change to check if more ResultSets are available (see post#5)

 

[Chris Guanzon]

Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("UserLogin", Array("demo", "demo", CreateMap("name":"varResponse", "type":"OUT", "sqlType": "VARCHAR")))
Wait For (req.ExecuteCall(cmd, 0, Null)) JobDone (j As HttpJob)
If j.Success Then
    Log("Call executed successfully")
    req.HandleCallJobAsync(j, "req")
    Wait For (req) req_CallResult(resultSets As List, parameters As Map)
    'Let's print out the returned result sets. resultSets is a list containing 0 or more DBResult objects.
    For Each res As DBResult In resultSets
           req.PrintTable(res)
    Next
    'Let's print the returned OUT parameters
    Log($"Parameters map content: ${parameters}"$)
Else
     Log($"ERROR: ${j.ErrorMessage}"$)
End If

Above is the code I used. If I put "demo" in

Dim cmd As DBCommand = CreateCommand("UserLogin", Array("demo", "demo", CreateMap("name":"varResponse", "type":"OUT", "sqlType": "VARCHAR")))

I get this error,

ResponseError. Reason: java.sql.SQLException: Operation not allowed after ResultSet closed, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 java.sql.SQLException: Operation not allowed after ResultSet closed</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.sql.SQLException: Operation not allowed after ResultSet closed</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.SQLException: Operation not allowed after ResultSet closed</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.sql.SQLException: Operation not allowed after ResultSet closed</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

but if I do it like this

Dim cmd As DBCommand = CreateCommand("UserLogin", Array("", "", CreateMap("name":"varResponse", "type":"OUT", "sqlType": "VARCHAR")))

I can get the OUT response.

Parameters map content: {varResponse=Error}

Is my code above correct?

 

[OliverA]

When running the jRDC2 server in debug mode, where does this error occur? Most likely my fault.

 

[Chris Guanzon]

When running the jRDC2 server in debug mode, where does this error occur? Most likely my fault.

this is the log from server

ExecuteCall2: Connection supports named parameters = false
Creating CallableStatement
Setting standard parameter value of demo for index 1
Setting standard parameter value of demo for index 2
sqlTypeOrdinal = 12
Registering out parameter named varResponse, with SQL type: 12
Executing CallableStatement
CallableStatement returned one or more result sets: true
(SQLException) java.sql.SQLException: Operation not allowed after ResultSet closed
Command: call: UserLogin, took: 2ms, client=192.168.1.9

 

[OliverA]

Starting with Line#258 in the RDCHandler module (as it pertains to the jRDCv2_mod.2020.10.09.01a_published edition of the jRDC2 server), replace

                Do While hadResults
                    rs = stmtObject.RunMethod("getResultSet", Null)
                    resultSets.Add(ProcessRS(rs, limit))
                    rs.Close
                Loop

with

                Do While hadResults
                    rs = stmtObject.RunMethod("getResultSet", Null)
                    resultSets.Add(ProcessRS(rs, limit))
                    rs.Close
                    hadResults = stmtObject.RunMethod("getMoreResults", Null)
                Loop

I forgot to check if more result sets are available, creating a situation of requesting a ResultSet where there is no ResultSet. I'm attaching the update (jRDCv2_mod.2020.10.09.02a_published.zip)in the first post, but by just changing these lines, you don't have to worry about your config.properties file.

 

[Chris Guanzon]

Great work! It is now working. Thanks a lot!

 

[Chris Guanzon]

Waiting for debugger to connect...
ResponseError. Reason: (RuntimeException) java.lang.RuntimeException: Field: LONGTEXT not found in: java.sql.Types, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 (RuntimeException) java.lang.RuntimeException: Field: LONGTEXT not found in: java.sql.Types</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    (RuntimeException) java.lang.RuntimeException: Field: LONGTEXT not found in: java.sql.Types</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 (RuntimeException) java.lang.RuntimeException: Field: LONGTEXT not found in: java.sql.Types</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    (RuntimeException) java.lang.RuntimeException: Field: LONGTEXT not found in: java.sql.Types</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

I have a new error, This error occurs when using LONGTEXT in sqltype.

This is the log in the server

ExecuteCall2: Connection supports named parameters = false
Creating CallableStatement
Setting standard parameter value of 0A-00-27-00-00-16-0A-00-27-00-00-10-B4-2E-99-53-FB-31 for index 1
(RuntimeException) java.lang.RuntimeException: Field: LONGTEXT not found in: java.sql.Types
Command: call: CheckSoftwareRegistration, took: 2ms, client=192.168.1.9

 

[OliverA]

The “JDBC” type class is mapped to “java.sql.Types” and he values for this class can be found here: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver15. Other type classes can be added, but require a modification of the jRDC2 servers code (It happens in the few lines of code follwing AppStart).

LONGTEXT is not a type defined in "java.sql.Types". I also cannot find any MySQL specific type class that would provide that for JDBC. If you look at the table provided here (https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html), you should be using VARCHAR as the JDBC type.

 

[Chris Guanzon]

LONGTEXT is not a type defined in "java.sql.Types". I also cannot find any MySQL specific type class that would provide that for JDBC. If you look at the table provided here (https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html), you should be using VARCHAR as the JDBC type.

the reason why i used longtext because i am encrypting some value like password. but it's ok. i will use varchar for the meantime ☺

 

[OliverA]

the reason why i used longtext because i am encrypting some value like password. but it's ok. i will use varchar for the meantime

Please note: You can use LONGTEXT. There just is not a JDBC type for LONGTEXT. That does not keep you from using LONGTEXT, it just means that the type that you use for the parameter is VARCHAR, since that is the closest thing that JDBC has to LONGTEXT (as per the MySQL documentation linked above). In other words:

When setting the JDBC type of a named variable for a particular SQL data type of your database and the JDBC standard type class of java.sql.Types does not have a matching type, then
1) See if you database provides an alternative Java class that has the JDBC type for your particular SQL data type, or
2) See if the documentation of your database contains instructions on which JDBC type to use (as in your LONGTEXT case), or
3) Use a JDBC data type that closest matches the SQL data type
None of these preclude you from using a SQL data type that is not perfectly matched by the JDBC type class java.sql.Types

Hope this makes some sense...