B4J Question jRdc2 + Firebird - connection problem

amorosik · Jan 29, 2023

I'm trying the jRdc2 system for reading/writing data on Firebird db from a remote device
Database server is Firebird 3.0.1 64bit on Windows 10 64 bit
But with the basic example code I can't get the correct response on the web browser
Below is the code on the Main and the configuration

Code on Main:

'Non-UI application (console / server application)
#Region  Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region

'change based on the jdbc jar file
'#AdditionalJar: mysql-connector-java-5.1.27-bin
#AdditionalJar: jaybird-5.0.0.java11.jar
'#AdditionalJar: postgresql-9.4.1207

Sub Process_Globals
    Public srvr As Server
    Public rdcConnector1 As RDCConnector
    Public const VERSION As Float = 2.23
    Type DBCommand (Name As String, Parameters() As Object)
    Type DBResult (Tag As Object, Columns As Map, Rows As List)
End Sub

Sub AppStart (Args() As String)
    srvr.Initialize("")
    rdcConnector1.Initialize
    srvr.Port = rdcConnector1.serverPort
    srvr.AddHandler("/test", "TestHandler", False)
    srvr.AddHandler("/rdc", "RDCHandler", False)
    srvr.Start
    Log($"jRDC is running (version = $1.2{VERSION})"$)
    StartMessageLoop
End Sub

Code for Configuration file:

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

#DATABASE CONFIGURATION
DriverClass=org.firebirdsql.jdbc.FBDriver
JdbcUrl=jdbc:firebirdsql://192.168.10.101/D:/DIR_DB/FIREBIRD_DATA/DB_NAME.fdb
#JdbcUrl=jdbc:firebirdsql://192.168.10.101/3050/D:/DIR_DB/FIREBIRD_DATA/DB_NAME.fdb
#JdbcUrl=jdbc:firebirdsql://192.168.10.101:3050/D:/DIR_DB/FIREBIRD_DATA/DB_NAME.fdb
User=SYSDBA 
Password=masterkey

#Java server port
ServerPort=17178

#SQL COMMANDS
sql.select=select * from ANAGRAFICHE

Log windows:

Waiting for debugger to connect...
Program started.
2023-01-29 09:48:51.706:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2023-01-29 09:48:51.896:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2023-01-29 09:48:51.987:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 11.0.1+13
2023-01-29 09:48:52.056:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
2023-01-29 09:48:52.065:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@30e868be{/,file:///D:/SORG/jRDC2/jRDC/Objects/www,AVAILABLE}
2023-01-29 09:48:52.087:INFO :oejs.RequestLogWriter:main: Opened D:\SORG\jRDC2\jRDC\Objects\logs\b4j-2023_01_29.request.log
2023-01-29 09:48:52.104:INFO :oejs.AbstractConnector:main: Started ServerConnector@247310d0{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2023-01-29 09:48:52.109:INFO :oejs.Server:main: Started Server@33f676f6{STARTING}[11.0.9,sto=0] @1045ms
Emulated network latency: 100ms
jRDC is running (version = 2.23)

Then when from web browser I start the test command http://192.168.10.190:17178/test appears on the log window

Log windows Aftrer web browser command:

Waiting for debugger to connect...
Program started.
2023-01-29 09:48:51.706:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2023-01-29 09:48:51.896:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2023-01-29 09:48:51.987:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 11.0.1+13
2023-01-29 09:48:52.056:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
2023-01-29 09:48:52.065:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@30e868be{/,file:///D:/SORG/jRDC2/jRDC/Objects/www,AVAILABLE}
2023-01-29 09:48:52.087:INFO :oejs.RequestLogWriter:main: Opened D:\SORG\jRDC2\jRDC\Objects\logs\b4j-2023_01_29.request.log
2023-01-29 09:48:52.104:INFO :oejs.AbstractConnector:main: Started ServerConnector@247310d0{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2023-01-29 09:48:52.109:INFO :oejs.Server:main: Started Server@33f676f6{STARTING}[11.0.9,sto=0] @1045ms
Emulated network latency: 100ms
jRDC is running (version = 2.23)
2023-01-29 09:50:26.935: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 -> 1hge184at1y4arzj1uiyuys|3911c2a7, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.firebirdsql.jdbc.FBDriver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge184at1y4arzj1uiyuys|3911c2a7, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:firebirdsql://192.168.1.101/3050/D:/FIREBIRD_DATA/ERP2_MOL_30.fdb, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 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 ]

Anyone can help me to find the problem?

teddybear · Jan 29, 2023

It is irrelevant to jdrc2 to connect firebird db, you just showed DATABASE CONFIGURATION, how to connect firebird? you may do the connecting test in b4j instead of jserver

amorosik · Jan 29, 2023

teddybear said:
It is irrelevant to jdrc2 to connect firebird db, you just showed DATABASE CONFIGURATION, how to connect firebird? you may do the connecting test in b4j instead of jserver

How to do 'the connecting test' in b4j ?

teddybear · Jan 29, 2023

amorosik said:
How to do 'the connecting test' in b4j ?

Here is an example

amorosik · Jan 29, 2023

Yes, the example, with my db, works correctly

B4X:

'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region
#CommandLineArgs:
    #MergeLibraries: True
    #additionaljar: jaybird-5.0.0.java11.jar
    
Sub Process_Globals
    Private SQL As SQL
End Sub

Sub AppStart (Args() As String)
    Private FBUsername As String = "SYSDBA"
    Private FBPassword As String = "masterkey"
    SQL.InitializeAsync("FireBirdDB","org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql://192.168.10.101:3050/d:/dir_db/db_name.fdb", FBUsername, FBPassword)
    StartMessageLoop
End Sub

Sub FireBirdDB_Ready (Success As Boolean)
    If Success Then
        Dim RS As ResultSet = SQL.ExecQuery("SELECT DITTA1 FROM ANAGRAFICHE")
        Do While RS.NextRow
            Log(RS.GetString2(0))
        Loop
        RS.Close
    End If
    StopMessageLoop 'only required in a console app
    SQL.Close
End Sub

teddybear · Jan 29, 2023

Do you modify RDCConnector module?

amorosik · Jan 29, 2023

No, the example is the original file from Erel post
Except for db related information

teddybear · Jan 29, 2023

amorosik said:
No, the example is the original file from Erel post
Except for db related information

I don't know if the JSQL pool.initialize supports firebird, I think you may modify RDCConnector and add a SQL.InitializeAsync("FireBirdDB",..) branch to process firebird connection like that example, and then it can returns the firebird sql handle in GetConnection method .

aeric · Jan 29, 2023

amorosik said:
works correctly

Then what's the problem?

amorosik · Jan 29, 2023

aeric said:
Then what's the problem?

The problem is understanding why using the original example taken from the post jRDC2 - B4J implementation of RDC and:
- replacing the library for connecting to the db (#AdditionalJar: jaybird-5.0.0.java11.jar)
- replacing the parameters for connecting to the db with mine

Database Configuration on config.properties file:

DriverClass=org.firebirdsql.jdbc.FBDriver
JdbcUrl=jdbc:firebirdsql://192.168.10.101:3050/D:/DIR_DB/DB_NAME.fdb
User=SYSDBA   
Password=masterkey

when I try to send the test command via web browser, the B4J program responds to the browser but fails to connect to the db server

Web Browser after send 192.168.10.101:17178/test command:

RemoteServer is running (01/29/2023 11:12:29)
Error fetching connection.

teddybear · Jan 29, 2023

amorosik said:
when I try to send the test command via web browser, the B4J program responds to the browser but fails to connect to the db server

Web Browser after send 192.168.10.101:17178/test command:

RemoteServer is running (01/29/2023 11:12:29) Error fetching connection.

It has responded the Error fetching connection, I think JSQL doesn't support the firebird, you should modify Initialize and GetConnection methods of the RDCConnector in order to the example.

amorosik · Jan 29, 2023

I don't understand HOW I should change it
This is the content of the RDCConnector module
How would you modify it?

RDCconnector module:

'Class module
Sub Class_Globals
    Private pool As ConnectionPool
    Private DebugQueries As Boolean
    Private commands As Map
    Public serverPort As Int
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    Dim config As Map = LoadConfigMap
    pool.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"), config.Get("User"), config.Get("Password"))
#if DEBUG
    DebugQueries = True
#else
    DebugQueries = False
#end if
    serverPort = config.Get("ServerPort")
    LoadSQLCommands(config)
End Sub

Private Sub LoadConfigMap As Map
    Return File.ReadMap(File.DirAssets, "config.properties")
End Sub

Public Sub GetCommand(Key As String) As String
    If commands.ContainsKey("sql." & Key) = False Then
        Log("*** Command not found: " & Key)
    End If
    Return commands.Get("sql." & Key)
End Sub

Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    
    Return pool.GetConnection
End Sub


Private Sub LoadSQLCommands(config As Map)
    Dim newCommands As Map
    newCommands.Initialize
    For Each k As String In config.Keys
        If k.StartsWith("sql.") Then
            newCommands.Put(k, config.Get(k))
        End If
    Next
    commands = newCommands
End Sub

aeric · Jan 29, 2023

amorosik said:
D:/DIR_DB/DB_NAME.fdb

I suspect you wrongly specify the path.
First time I tested with Firebird with no issue.

B4X:

JdbcUrl=jdbc:firebirdsql://192.168.50.42:3050/C:/Program Files/Firebird/Firebird_3_0/examples/empbuild/EMPLOYEE.fdb

aeric · Jan 29, 2023

amorosik said:
User=SYSDBA

Or maybe because you have a trailing space after the User name "SYSDBA " !

amorosik · Jan 29, 2023

aeric said:
Or maybe because you have a trailing space after the User name "SYSDBA " !

Yes, there might be even some small difference
But I copied and pasted the same data to make the test program in post #5 and using the SQL.InitializeAsync everything works fine
While in Erel's example it doesn't work but use another method to connect to the db

amorosik · Jan 29, 2023

aeric said:
First time I tested with Firebird with no issue.

Do you mean that on the demo code of the post describing the jRdc2 system you used Firebird and it worked correctly?

aeric · Jan 29, 2023

amorosik said:
Do you mean that on the demo code of the post describing the jRdc2 system you used Firebird and it worked correctly?

Yes, I just run jrdc as it is. Just make sure no spaces after SYSDBA

amorosik · Jan 29, 2023

UAOOOOOooo
It was the space on SYSDBA
I thought copying and pasting to another program was proof that the data was correct
But it wasn't like that, evidently the editor cut the final space
Many many thanks

aeric · Jan 29, 2023

I also facing similar issue last week and took sometime to find out that I had a trailing space after the backslash in my query which caused my query failed. So just beware of copy and paste.

Harris · Jan 30, 2023

aeric said:
I also facing similar issue last week and took sometime to find out that I had a trailing space after the backslash in my query which caused my query failed. So just beware of copy and paste.

This has also bitten me in the rear end, in the past. It is very hard to see visually. ..... So just beware of copy and paste.

.....

B4J Question jRdc2 + Firebird - connection problem

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Well-Known Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads