B4J Question JRDC2 and server side processing

[Jack Cole]

I currently use PHP for all my server side processing and database interaction with my b4x apps. It would be nice to use JRDC2, but I'm not certain if it will do what I need. Can you do processing on the server side before sending results?

Example:
1). Client sends email address and password to the server.
2). Server checks to see if the password is valid.
3). Server response includes the full user record (without the password) or a response indicating that the login was unsuccessful.

Or, something like code below that resets a password. PHP works fine for me, but I like the idea of the added security of JRDC2.

<?php

$serverName = "";
$connectionOptions = array("Database" => "",
                           "UID" => "",
                           "PWD" => "");
$con = sqlsrv_connect($serverName, $connectionOptions);

// Was the form submitted?
if (isset($_POST["ResetPasswordForm"]))
{
    // Gather the post data
    $email = $_POST["email"];
    $password = $_POST["password"];
    $confirmpassword = $_POST["confirmpassword"];
    $hash = $_POST["q"];
    if (strlen($password)<6) {
        die("<h1>Password must be at least 6 characters long</h1>");
        
    }
    // Use the same salt from the forgot_password.php file
    $salt = "498#2DDDB631%38EBD!801600D*7E34444";
    // Generate the reset key
    $resetkey = hash('sha512', $salt.$email);

    // Does the new reset key match the old one?
    if ($resetkey == $hash)
    {
        if ($password == $confirmpassword)
        {
            // Update the user's password
                $tsql=" update usertable set passwordhash=HASHBYTES('SHA2_512', ?+CAST(salt AS NVARCHAR(36))) where email=?";
                $params = array(&$password ,&$email );
                /* Prepare and execute the statement. */
                $insertReview = sqlsrv_prepare($con, $tsql, $params);
                    if( $insertReview === false )
                        {
                            echo "<h1>Sorry.  We were unable to reset your password.  Please contact support@mindware.mobi for help.</h1>";
                        } else {
                            $sth=sqlsrv_execute($insertReview);
                               if ($sth == FALSE)
                               {
                                    echo "<h1>Sorry.  We were unable to reset your password.  Please contact support@mindware.mobi for help.</h1>";
                               }
                            echo "<h1>Your password has been successfully reset.</h1>";
                        }
        }
        else
            echo "<h1>Your password's do not match.</h1>";
    }
    else
        echo "<h1>Your password reset key is invalid.</h1>";
}

?>

 

[Anser]

Can you do processing on the server side before sending results?

Example:
1). Client sends email address and password to the server.
2). Server checks to see if the password is valid.
3). Server response includes the full user record (without the password) or a response indicating that the login was unsuccessful.

Or, something like code below that resets a password

Yes. JRDC works fine for the above-said purpose

Inspecting your code to reset the password, it would be better to use a StoredProcedure to do whatever you want to do with the database table and then return the corresponding Flag value OR message from the StoredProcedure so that you can display meaningful information to the user at the Front end application.

 

[Erel]

jRDC2 is a specific implementation based on jServer. jRDC2 handler allows you to run SQL queries and commands.
It is very simple to add more handlers with any other logic that you need.

 

[Alexander Stolte]

I built a jRDC2 in my jRDC2, thats mean, I can still do normal RDC statement with "qury" or "batch", but I need more logic for example to send another notification then I can simply send custom commands to the RDC instead of the "query" odr "batch" command to run my own logic.

I can write you more about this tonight when I'm at home.

 

[Alexander Stolte]

It's a bit complicated at the beginning and there is certainly room for improvement, but that's just what I'm doing right now.
In the Attachment is the modified jRDC2 Example and the modified DBRequestManager.

The Trick is to set your own "method", jRDC2 is simple, you have "batch2" and "query2", if you modified the "DBRequestManager" a little bit you can set your own methods, but you have the options to set the old command with query2 or batch2.

RDCHandler

Spoiler: Handle

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim start As Long = DateTime.Now
    Dim q As String
    Dim in As InputStream = req.InputStream
    Dim method As String = req.GetParameter("method")
    Dim con As SQL
    Try
        
        con = Main.rdcConnector1.GetConnection
        If method = "query2" Then
            q = ExecuteQuery2(con, in, resp)
#if VERSION1
        Else if method = "query" Then
            in = cs.WrapInputStream(in, "gzip")
            q = ExecuteQuery(con, in, resp)
        Else if method = "batch" Then
            in = cs.WrapInputStream(in, "gzip")
            q = ExecuteBatch(con, in, resp)
#end if
        Else if method = "batch2" Then
            q = ExecuteBatch2(con, in, resp)
            
        Else if method = "SendMessage" Then'this is your custom item
            
            Dim ser As B4XSerializator
            Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
            Dim cmd As DBCommand = m.Get("command")
            set_SendMessage(cmd.Parameters(0),cmd.Parameters(1),cmd.Parameters(2),con,resp)
            StartMessageLoop
            
        Else
            Log("Unknown method: " & method)
            resp.SendError(500, "unknown method")
        End If
    Catch
        Log(LastException)
        resp.SendError(500, LastException.Message)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
    Log($"Command: ${q}, took: ${DateTime.Now - start}ms, client=${req.RemoteAddress}"$)
End Sub

Spoiler: set_SendMessage

Private Sub set_SendMessage(parameter1 As Object,parameter2 As Object,parameter3 As Object,con As SQL,resp As ServletResponse)
    
    'put your logik here.... for example:
    
    'wait for SendMessage2Device(parameter1,parameter2)
    
    'if you want to insert something
    ExecuteBatch3("INSERT INTO myTable (par1,par2,par3) VALUES (?,?,?);",Array As Object(parameter1,parameter2,parameter3),con,resp)
    
    'if you want to select something
    Dim rs As ResultSet = con.ExecQuery2("SELECT * FROM Table",Array As Object(parameter1,parameter2,parameter3))
    Dim ser As B4XSerializator
    Dim data() As Byte = ser.ConvertObjectToBytes(processgetrequest(rs,rs.ColumnCount))
    resp.OutputStream.WriteBytes(data, 0, data.Length)
    
    StopMessageLoop'its important to stop it
    
End Sub

Spoiler: processgetrequest

Private Sub processgetrequest(rs As ResultSet,cols As Int) As DBResult
    
    Dim jrs As JavaObject = rs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
    

    Dim res As DBResult
    res.Initialize
    res.columns.Initialize
    res.Tag = Null 'without this the Tag properly will not be serializable.
    Try
        For i = 0 To cols - 1
            res.columns.Put(rs.GetColumnName(i), i)
        Next
        res.Rows.Initialize
    
        Do While rs.NextRow
            Dim row(cols) As Object
            For i = 0 To cols - 1
                Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
                'check whether it is a blob field
                If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
                    row(i) = rs.GetBlob2(i)
                Else if ct = 2 Or ct = 3 Then
                    row(i) = rs.GetDouble2(i)
                Else If DateTimeMethods.ContainsKey(ct) Then
                    Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                    If SQLTime.IsInitialized Then
                        row(i) = SQLTime.RunMethod("getTime", Null)
                    Else
                        row(i) = Null
                    End If
                Else
                    row(i) = jrs.RunMethod("getObject", Array(i + 1))
                End If

            Next
            res.Rows.Add(row)
    
        Loop
        rs.Close
    
    
        Return res
    
    Catch
        
        Log("processgetrequest: " & LastException)
    
        Return res
    End Try
    
End Sub

Spoiler: ExecuteBatch3

Private Sub ExecuteBatch3(query As String, arguments() As Object,con As SQL,resp As ServletResponse) As Boolean
        #If Performance
    Dim start As Long = DateTime.Now
    #end if
    
    Dim ser As B4XSerializator
    
    Dim res As DBResult
    res.Initialize
    res.columns = CreateMap("AffectedRows (N/A)": 0)
    res.Rows.Initialize
    res.Tag = Null
    Try
        con.BeginTransaction
    
        con.ExecNonQuery2(query,arguments)
                
    
        res.Rows.Add(Array As Object(0))
        con.TransactionSuccessful
    Catch
        con.Rollback
                #If Development
        Log("ExecuteBatch2: " & LastException)
        #end if
        resp.SendError(500, LastException.Message)
        
        Return False
    End Try
    Dim data() As Byte = ser.ConvertObjectToBytes(res)

    resp.OutputStream.WriteBytes(data, 0, data.Length)
    
    #If Performance
    Log($"Command: ${"v1: ExecuteBatch2"}, took: ${DateTime.Now - start}ms"$)
    #end if
    
    Return True
    
End Sub