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 lang="b4x" title="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]

[CODE lang="b4x" title="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
[/CODE]

[CODE lang="b4x" title="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

ejs.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

ejss.DefaultSessionIdManager:main: Session workerName=node0
2023-01-29 09:48:52.065:INFO

ejsh.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

ejs.RequestLogWriter:main: Opened D:\SORG\jRDC2\jRDC\Objects\logs\b4j-2023_01_29.request.log
2023-01-29 09:48:52.104:INFO

ejs.AbstractConnector:main: Started ServerConnector@247310d0{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2023-01-29 09:48:52.109:INFO

ejs.Server:main: Started Server@33f676f6{STARTING}[11.0.9,sto=0] @1045ms
Emulated network latency: 100ms
jRDC is running (version = 2.23)
[/CODE]

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

[CODE lang="b4x" title="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

ejs.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

ejss.DefaultSessionIdManager:main: Session workerName=node0
2023-01-29 09:48:52.065:INFO

ejsh.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

ejs.RequestLogWriter:main: Opened D:\SORG\jRDC2\jRDC\Objects\logs\b4j-2023_01_29.request.log
2023-01-29 09:48:52.104:INFO

ejs.AbstractConnector:main: Started ServerConnector@247310d0{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2023-01-29 09:48:52.109:INFO

ejs.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 ]
[/CODE]

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
[CODE lang="b4x" title="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[/CODE]

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
[CODE lang="b4x" title="Web Browser after send 192.168.10.101:17178/test command"]RemoteServer is running (01/29/2023 11:12:29)
Error fetching connection.[/CODE]

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
[CODE lang="b4x" title="Web Browser after send 192.168.10.101:17178/test command"]RemoteServer is running (01/29/2023 11:12:29)
Error fetching connection.[/CODE]

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?

[CODE lang="b4x" title="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[/CODE]

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