Android Question jRDC 2.1 Error : dbrequestmanager_ser_bytestoobject

[Anser]

Hi,

I am getting the following error

An error has occured in
sub:dbrequestmanager_ser_bytestoobject (java line: 290)
java.lang.NullPointerException

The Full error log is pasted below


Error reading response: (EOFException) java.io.EOFException
dbrequestmanager_ser_bytestoobject (java line: 290)
java.lang.NullPointerException
at com.myappname.dbrequestmanager._ser_bytestoobject(dbrequestmanager.java:290)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:169)
at anywheresoftware.b4a.BA$2.run(BA.java:328)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
java.lang.NullPointerException

The following line on the Sub ser_BytesToObject inside DbRequestManager.bas is causing the error.

res.Tag = m.Get("tag")

I am calling a Stored Procedure as given below with the parameters
call test_login('UserName','Password','e3ac950caee4d7e1',1)

Based on the parameters, the Stored procedure will validate the login and depending on the validation inside the stored procedure it may return 2 types of result.
ie

1. If not a valid user then, it will return a StatusID (Int) and a Msg (VarChar2). For eg, 1, "Invalid Login"
   
2. If the login was successful it will return StatusID, Msg and few other details/columns pertaining to the successfully logged-in user.

I am getting the run time error ONLY when the Login is successful, otherwise it is working fine as expected.

I tried to run the Stored Procedure from Heidi SQL to test the result when the login is successful. It is working fine and is returning all the columns as expected.

This the result that I get from the Stored Procedure on a Successful Login, unfortunately jRDC 2.1 errors out here without any proper clue of the error (at least for a person like me)

This code was perfectly working fine using jRDC ver 1. Two days back I tried changing to jRDC ver 2.1 and then the problem started.

I really don't understand why this is failing at dbrequestmanager_ser_bytestoobject and that too at the result tag.

I have included the latest DbRequestManager in the project
RandomAccessFile ver 2.20 is also included in my Project

Any help will be appreciated. I am stuck with this issue.


Regards

Anser

 

[Anser]

The following is My Stored Procedure code that returns data after a successful login

SELECT 0 AS 'StatusID', 'Login Successful' AS 'Msg' ,
                   @CompanyID AS 'Comp_ID', @BranchID AS 'Branch_ID',
                     @UserTypeID AS 'UserType_ID', @UserID AS 'User_ID',
                     @FinYear AS 'FinYear', @UserFullName AS 'User_FullName' ,
                     @BranchShortName AS 'Branch_ShortName' ,
                     @GcmID AS 'GCM_ID';

If I change the above code as given below ie Hardcode the return values, then jRDC works fine.

SELECT 0 AS 'StatusID', 'Login Successful' AS 'Msg' ,
                   1 AS 'Comp_ID', 1 AS 'Branch_ID',
                     1 AS 'UserType_ID', 24 AS 'User_ID',
                     2015 AS 'FinYear', 'Anser' AS 'User_FullName' ,
                     'MyBranch_01' AS 'Branch_ShortName' ,
                     'TestID' AS 'GCM_ID';

The @variables contain certain data stored on to it after executing certain SQL queries

For Eg

SELECT Fin_Year INTO @FinYear FROM cmn_finyear WHERE Date(Now()) BETWEEN Start_date AND End_Date;

Friends,
Any help ?

 

[Erel]

How do you call the stored procedure? jRDC doesn't support stored procedures.

You can use a regular parameterized query.

 

[Anser]

How do you call the stored procedure? jRDC doesn't support stored procedures.

You can use a regular parameterized query.

Ohh God. This was working fine in jRDC ver 1 and the Old RDC

I believe that you have missed my previous post. We both posted almost same time.

In b4A Code

    Dim cmd As DBCommand
cmd.Initialize
cmd.Name="login_sp"
cmd.Parameters=Array As Object(cUserName,cPassword,Starter.cDeviceId,nAppVersion)

'To verify the parameters 
Msgbox(cUserName &CRLF&   cPassword &CRLF& Starter.cDeviceId &CRLF& nAppVersion, "Test")
  
reqManager.ExecuteQuery(cmd, 0, "login")

In my Config.Properties File

sql.login_sp=call test_Login(?,?,?,?)

call test_login('UserName','Password','e3ac950caee4d7e1',1)

Regards
Anser

 

[Erel]

You can run the server in debug mode and see which values are returned.

You can also modify the server code and use sql.CreateCallStatement and sql.ExecCall (based on the command name).

 

[Anser]

You can run the server in debug mode and see which values are returned.

You can also modify the server code and use sql.CreateCallStatement and sql.ExecCall (based on the command name).

As time is really crucial for me at this point of time and as I am not an expert in B4A and B4J, I think that the best option for me is to revert back to jRDC ver 1; along with the old DbRequestManager.Bas

To my bad luck, I did not take a backup of my B4A app code with jRDC ver 1, I blindly modified the code to use jRDC ver 2.1

I never expected to end up like this

 

[Anser]

I modified the Stored procedure code as follows

Method 1

  SET @sql= 'SELECT 0 AS "StatusID", "Login Successful" AS "Msg" ,
                   @CompanyID AS "Comp_ID", @BranchID AS "Branch_ID",
                     @UserTypeID AS "UserType_ID", @UserID AS "User_ID",
                     @FinYear AS "FinYear", @UserFullName AS "User_FullName" ,
                     @BranchShortName AS "Branch_ShortName" ,
                     @GcmID AS "GCM_ID"';
                  
    PREPARE stmt FROM @sql ;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt;

Method 2

SELECT 0 , 'Login Successful'  ,
                   @CompanyID , @BranchID ,
                     @UserTypeID , @UserID ,
                     @FinYear , @UserFullName ,
                     @BranchShortName  ,
                     @GcmID ;

Unfortunately both fails.

I am currently doing another project too using this jRDC2. At present, I have reached only just 20% of the new project and in this new project, till now I never had to use Stored Procedures, but definitely as the project progress, I need to depend on Stored Procedures for complex queries and operations in this project.

How come this is working on jRDC ver 1 and not in jRDC ver 2.1 ?

I found your description about jRDC2 very useful.
"The main advantage of jRDC2 and the version 2 clients is that the data is serialized asynchronously. This means that the UI will not slow down when the request is built or when the response is parsed."

Which technology do you recommend to use ? jRDC or the php in this scenario ?

Decision regarding this is very crucial for me. Once we choose a technology then all the future upgrades in the technology becomes very crucial and it should be backward compatible. Both RDC & jRDC1 supported Stored Procedures and now at this point of time I read from you that "jRDC doesn't support stored procedures."

Your advice will be appreciated.

Thanks & Regards
Anser

 

[Erel]

jRDC 1 and jRDC 2 are based on the same code. If it worked in jRDC 1 then it should work in jRDC 2. You need to debug it and see where it fails. I'm sure that it is fixable.

 

[Anser]

Here is a sample project that demonstrates the problem what I am saying in this thread

A Simple Table containing 2 columns
ID INT
NAME Varchar(50)

Sample Records in the Table
ID NAME
12 FORD
22 AUDI
42 BMW

The Sample Stored Procedure Code that demonstrates the problem in jRDC2
This stored procedure named SP_MOB_Test receives a parameter named cParameter of Text type. The parameters value can be either A,B or C

BEGIN

   IF cParameter = "A" THEN  /*  This will work  */
     SELECT ID AS 'IdNo', Brand_Name AS 'BrandName' FROM vehicle_brands WHERE ID = 12 ;
   ELSEIF cParameter = "B" THEN  /*  This will FAIL  */
     SELECT ID, Brand_Name INTO @nIdNo, @cBrandName FROM vehicle_brands WHERE ID = 12 ;
     SELECT @nIdNo AS 'IdNo', @cBrandName AS 'BrandName' ;
   ELSEIF cParameter = "C" THEN  /*  This will work  */
     SELECT 12 AS 'IdNo', 'Test Vehicle' AS 'BrandName' ;
   END IF;

END

If you run the above code from any MySQL database tool it will work fine
For eg.

CALL SP_MOB_Test('A')
CALL SP_MOB_Test('B')
CALL SP_MOB_Test('C')

Config.Properties file entries

#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://192.168.0.170/MyDbName?characterEncoding=utf8
User=MyUserName
Password=MyPassword
#Java server port
ServerPort=17179

#example of MS SQL Server configuration:
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>

#SQL COMMANDS

sql.test=CALL SP_MOB_Test(?)

A Simple B4A Code.
The Activity has 3 buttons on it namely BtnTestA, BtnTestB and BtnTestC
Clicking on each button will call the stored procedure with parameter either A,B or C

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Private BtnTestA, BtnTestB, BtnTestC As Button
    Private reqManager As DBRequestManager
    Type DBResult (Tag As Object, Columns As Map, Rows As List)
    Type DBCommand (Name As String, Parameters() As Object)
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("Test")

    If FirstTime Then
        reqManager.Initialize(Me, Starter.RdcServerIP)
    Else
        If reqManager.IsInitialized = False Then
            reqManager.Initialize(Me, Starter.RdcServerIP)
        End If
    End If

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub BtnTestA_Click
    Test("A")
End Sub

Sub BtnTestB_Click
    Test("B")
End Sub

Sub BtnTestC_Click
    Test("C")
End Sub

Sub Test(cParameter As String)

    Dim cTag As String
    cTag = "Test"&cParameter  'For Eg TestA, TestB TestC

    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name="test"
    cmd.Parameters=Array As Object(cParameter)
    ProgressDialogShow("Retrieving data")
    reqManager.ExecuteQuery(cmd, 0, cTag)

End Sub

Sub JobDone(Job As HttpJob)
    ProgressDialogHide
  If Job.Success = False Then
     Log("Error: " & Job.ErrorMessage)

  Else
    If Job.JobName = "DBRequest" Then
       reqManager.HandleJobAsync(Job, "ReqManager")
    End If
  End If
  Job.Release
End Sub

Sub ReqManager_Result(result As DBResult)
    reqManager.PrintTable(result)
    If result.Tag = "TestA" Then 'This will work fine
        ToastMessageShow("Test A Passed",False)

    Else If result.Tag = "TestB" Then 'This will error out
        ToastMessageShow("Test B Passed",False)

    Else If result.Tag = "TestC"    Then 'This will work fine
        ToastMessageShow("Test C Passed",False)

    End If
End Sub

Regards
Anser

 

[Anser]

I found out the issue.

From jRDC2.1 onwards the stored procedures with procedure variables ie those variables that starts with @ will fail. So instead of procedure variables you will have to use session variables. There are lot of dangers associated with using session variables instead of procedure variables, because procedure variable is reinitialized to NULL each time the procedure is called, while the session-specific variable is not

So the following stored procedure with procedure variables will NOT work

BEGIN

   IF cParameter = "A" THEN  /*  This will work  */
     SELECT ID AS 'IdNo', Brand_Name AS 'BrandName' FROM vehicle_brands WHERE ID = 12 ;
   ELSEIF cParameter = "B" THEN  /*  This will FAIL  */
     SELECT ID, Brand_Name INTO @nIdNo, @cBrandName FROM vehicle_brands WHERE ID = 12 ;
     SELECT @nIdNo AS 'IdNo', @cBrandName AS 'BrandName' ;
   ELSEIF cParameter = "C" THEN  /*  This will work  */
     SELECT 12 AS 'IdNo', 'Test Vehicle' AS 'BrandName' ;
   END IF;

END

The following stored procedure code modified to use session-specific variable WILL WORK

BEGIN

    DECLARE nIdNo INT DEFAULT 0;
    DECLARE cBrandName VARCHAR(50) DEFAULT '' ;
 
    IF cParameter = "A" THEN
        SELECT ID AS 'IdNo', Brand_Name AS 'BrandName' FROM vehicle_brands WHERE ID = 12 ;
    ELSEIF cParameter = "B" THEN
        SELECT ID, Brand_Name INTO nIdNo, cBrandName FROM vehicle_brands WHERE ID = 12 ;
        SELECT nIdNo AS 'IdNo', cBrandName AS 'BrandName' ;
    ELSEIF cParameter = "C" THEN
        SELECT 12 AS 'IdNo', 'Test Vehicle' as 'BrandName' ;
    END IF;

END

I still wonder why this worked up to jRDC1 and not from jRDC2 onward.

I am really sad that it is breaking backward compatibility.

Now If I want to use jRDC2 then I will have to modify all the stored procedures that use procedure variables to session variables. Its a real nightmare to me. My B4A app is just a small part of huge Desktop/Web application that share a common MySQL database and the stored procedures and triggers. Over and above, it is not a good idea to replace procedure variables with session variables in all the stored procedures.

One more question, can I consider each request using jRDC as a new session ?.
Why I am asking this is that, if each query in jRDC is a new session then I don't have to bother too much about whether its a session variable or procedure variable.

I know that the source code of jRDC2 is available and that I can modify it if I am an expert in B4J. Unfortunately I am not an expert. Any advice to rectify this issue in jRDC2.1 code will be appreciated. I already reached a long way using RDC,jRDC1 and finally to jRDC2. Its really hard to change the database connectivity part all of a sudden. Hope you understand the situation.

If you ignore the above said issue with jRDC2.1, it is a wonderful tool, performance wise as well as security wise when it comes to accessing remote database.


Regards
Anser

 

[Erel]

Are you using MySQL? If you like you can send me a dump of your database and I'll test it here.

 

[Anser]

Are you using MySQL? If you like you can send me a dump of your database and I'll test it here.

Yes,
I use MySQL as well as MariaDB. The problem is there on both

To test this error, you just need a simple table and a Stored Procedure in MySQL.


Here is the dump file content

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for anser_test
CREATE DATABASE IF NOT EXISTS `anser_test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `anser_test`;


-- Dumping structure for procedure anser_test.SP_MOB_Test
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `SP_MOB_Test`(IN `cParameter` TEXT)
BEGIN
    IF cParameter = "A" THEN
        SELECT ID AS 'IdNo', Brand_Name AS 'BrandName' FROM vehicle_brands WHERE ID = 12 ;
    ELSEIF cParameter = "B" THEN
        SELECT ID, Brand_Name INTO @nIdNo, @cBrandName FROM vehicle_brands WHERE ID = 12 ;
        SELECT @nIdNo AS 'IdNo', @cBrandName AS 'BrandName' ;
    ELSEIF cParameter = "C" THEN
        SELECT 12 AS 'IdNo', 'Test Vehicle' as 'BrandName' ;
    END IF;

END//
DELIMITER ;


-- Dumping structure for table anser_test.vehicle_brands
CREATE TABLE IF NOT EXISTS `vehicle_brands` (
  `ID` int(11) DEFAULT NULL,
  `Brand_Name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table anser_test.vehicle_brands: ~4 rows (approximately)
DELETE FROM `vehicle_brands`;
/*!40000 ALTER TABLE `vehicle_brands` DISABLE KEYS */;
INSERT INTO `vehicle_brands` (`ID`, `Brand_Name`) VALUES
    (1, 'Ford'),
    (2, 'AUDI'),
    (3, 'BMW'),
    (4, 'Benz');
/*!40000 ALTER TABLE `vehicle_brands` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Anyway the dump file is also attached (zip file) in this post.

To call the Stored Procedure, the following entry on your jRDC2's Config.Properties file

sql.test=CALL SP_MOB_Test(?

The sample B4A Code to reproduce this issue is already given in Post #9 in this thread.
Calling the Stored Procedure with Parameter 'B' will reproduce the error

Regards
Anser

 

[Erel]

I'm not getting any error. These are the logs:

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Class not found: b4j.example.main$_dbresult, trying: b4a.example.main$_dbresult <-- this is expected
Tag: B, Columns: 2, Rows: 1
IdNo   BrandName   
1   Ford

Note that I changed the stored procedure to ID = 1 instead of 12.

 

[Anser]

I'm not getting any error.

I am confused now.

I changed all the Stored Procedures that are called from the B4A app that returns a procedure variable (ie those that start with @ ) to session variable and now it is working fine. The same procedures without the above said modifications were working fine till jRDC1

I have 2 VPS servers, one with MySQL and one with MariaDB.

Really sorry if I have wasted your time.

I shall re-check once again.

Thanks & Regards

Anser