B4J Question jDRC2 server setup

[Domingo Garcia]

Thanks for the Tutorial, it's help a lot. I'm a little confuse on executing the server side. I'm trying to run everything on my PC with a phone attached through USB to run the client. I'm also running XAMPP so I have an apache web server running with the root directory for it as C:\xampp\htdocs . The jRDC2 is in another directory C:\B4J\jRDC2. When I start the IDE (B4J) with the jRDC2 code do I need to compile and move it to the apache directory or can I just do F5 and run it on the IDE. I'm trying to connect to an MS-SQL Server (2012) running on a separate server. When I start it on the IDE I get a log msg:
jRDC is running (version = 2.1)
When I run the client it gives this error:
ResponseError. Reason: java.net.ConnectException: Failed to connect to localhost/127.0.0.1:17178, Response:

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

Type DBResult (Tag As Object, Columns As Map, Rows As List)
Type DBCommand (Name As String, Parameters() As Object)
Public const rdcLink As String = "http://localhost:17178/rdc"

End Sub

Sub GetChkOutId
Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("sel_chk_id", Array(gtrndate, groute))
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)
ckid = 1
'work with result
req.PrintTable(res)
Else
Log("ERROR: " & j.ErrorMessage)
End If
j.Release
End Sub

CONFIG:
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://192.168.1.200/pan
User=sa
Password=Pupi911!
SERVERNAME=WIN2012-PAN\SQLEXPRESS ' not used?
PORTNUMBER=1433 ' not used?
DATABASENAME=pan ' not used?
SERVERTYPE=1 ' not used?
XAEMULATION=true
#Java server port
ServerPort=17178

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

#example of MYSQL Server configuration:
#DriverClass=com.mysql.jdbc.Driver
#JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8

#example of postegres configuration:
#JdbcUrl=jdbc:postgresql://localhost/test
#DriverClass=org.postgresql.Driver

#SQL COMMANDS
#sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
# id INTEGER PRIMARY KEY AUTO_INCREMENT,\
# name CHAR(30) NOT NULL,\
# image BLOB)
#sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)
#sql.select_animal=SELECT name, image, id FROM animals
sql.sel_chk_id=SELECT CheckOutDate, CheckOutId, Route \
FROM CheckOut WHERE CheckOutDate = ? AND Route = ?
sql.sel_chk_max= SELECT MAX(CheckOutId) AS CheckOutId \
FROM CheckOut WHERE CheckOutDate = ?
sql.ins_chk_id=INSERT INTO CheckOut (CheckOutDate, \
CheckOutId, Route, DriverId, UserId) VALUES (?, ?, ?, ?, ?)
sql.sel_chkdet=SELECT * FROM CheckOutDetail WHERE \
CheckOutDate = (?) AND CheckOutId = (?) AND ProductId = (?)
sql.upd_chkdet_sobra=UPDATE CheckOutDetail SET DriverID = (?), \
Sobra = (?) WHERE CheckOutDate = (?) AND CheckOutId = (?) \
AND ProductId = (?)
sql.upd_chkdet_stale=UPDATE CheckOutDetail SET DriverID = (?), \
Stale = (?) WHERE CheckOutDate = (?) AND CheckOutId = (?) \
AND ProductId = (?)
sql.ins_chkdet_sobra=INSERT INTO CheckOutDetail (CheckOutDate, \
CheckOutId, ProductId, DriverId, Sobra, Stale) VALUES (?, ?, ?, ?, ?, ?)
sql.ins_chkdet_stale=INSERT INTO CheckOutDetail (CheckOutDate, \
CheckOutId, ProductId, DriverId, Sobra, Stale) VALUES (?, ?, ?, ?, ?, ?)

 

[Erel]

There is no relation between the B4J server (jRDC2) and the apache server.
You just need to run it.

localhost points to the current machine. It is incorrect. You need to use the PC address instead.

Make sure to start with the test link from the PC browser.

 

[Domingo Garcia]

Ok, I have a response from the jRDC2 saying:
RemoteServer is running (02/01/2018 15:56:01)
Error fetching connection.
and that is fine since I don't have an sqlite connection to test.
but when i try an transaction from the client to the MS-SQL Server I get an error:
Caused by: java.net.ConnectException: Connection refused: connect
I'm assuming that that is because the full credential required by the DB are not being sent.
to connect with the MSSQl Server I normally use the following parms (ie PHP connection):

define("HOST", "WIN2012-PAN\SQLEXPRESS");       // The host you want to connect to.
define("USER", "sa");                                             // The database username.
define("PASSWORD", "xxxxxxxx");                         // The database password.
define("DATABASE", "dbname");                             // The database name.
$connectioninfo = array("Database"=> DATABASE, "UID"=> USER, "PWD"=> PASSWORD, 'ReturnDatesAsStrings'=>true);
$servername = HOST;
$conn = sqlsrv_connect( $servername, $connectioninfo);

 

[Erel]

If the "test" query fails then you should first fix it. No point in trying to connect from a client. It will not work.

The test query has nothing to do with SQLite. Check the server logs (in B4J IDE) and see why jRDC2 failed to connect to the database.

 

[Domingo Garcia]

Ok, using the test i gave the error fetching connection and the same error as above. I found a jTDS document that was passing the instance name in the url:

Here are the JDBC parameters passed to jTDS.
url=jdbc:jtds:sqlserver://localhost:1433/MY_DATABASE;instance=MY_INSTANCE
driver=net.sourceforge.jtds.jdbc.Driver
user=MY_USERNAME
password=MY_PASSWORD
Please replace the MY_ variables with your own values.

so I modified my config.properties with:
JdbcUrl=jdbc:jtds:sqlserver://SERVERNAME/dbname;instance=INSTNAME
since I'm not on a domain I added the SERVERNAME to my hosts file.
now it works:

 

[Domingo Garcia]

Now I've got an error:
(ClassNotFoundException) java.lang.ClassNotFoundException: b4j.example.starter$_dbcommand

 

[DonManfred]

From the tutorial....

Client configuration

1. Add to main module:
Code:

Sub Process_Globals
   Type DBResult (Tag As Object, Columns As Map, Rows As List)
   Type DBCommand (Name As String, Parameters() As Object)
   Private const rdcLink As String = "http://192.168.0.6:17178/rdc"
End Sub
Sub Process_Globals
   Type DBResult (Tag As Object, Columns As Map, Rows As List)
   Type DBCommand (Name As String, Parameters() As Object)
   Private const rdcLink As String = "http://192.168.0.6:17178/rdc"
End Sub

Change the link with the ip address or host name of the server hosting jRDC2. It must end with /rdc.

2. Add DBRequestManager class to your project.
It depends on RandomAccessFile and OkHttpUtils2 libraries (or the matching libraries in B4J or B4i).

3. Add these two subs:
Code:

Sub CreateRequest As DBRequestManager
   Dim req As DBRequestManager
   req.Initialize(Me, rdcLink)
   Return req
End Sub

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
   Dim cmd As DBCommand
   cmd.Initialize
   cmd.Name = Name
   If Parameters <> Null Then cmd.Parameters = Parameters
   Return cmd
End Sub

A new DBRequestManager is created for each request.

 

[Domingo Garcia]

Thanks DonManfred, I moved the DBresult and DBcommand from Starter to Main and it resolved the error. Now I have a condition where the response is not correct or not happening. I added the code and modified it for my case:

Sub GetChkOutId
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("sel_chk_id", Array(gtrndate, groute))
    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)
        ckid = 1
        'work with result
        req.PrintTable(res)
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
End Sub

I step through the code and it reaches he "Wait for" line but then it ends and never reaches the j.Success if statement.
I saw the command reach the jDRC2 module and respond (It should be a no record found response) but I don't know whats happening.

 

[DonManfred]

New issue. New thread.