B4J Code Snippet [Project Template] jRDC2 Server (SQLite, MySQL, MS SQL, Firebird, Postgresql, DBF)

jRDC2 Server Template​

Version: 1.12 (based on jRDC2 version 2.23)

Create a new project and run it!

To be accompanied with jRDC2 Client Template

Introduction:
To be edited

1697731328626.png


Version 1.00 : 9KB
Version 1.11 : 127KB
Version 1.12 : 127KB
Version 2.00 (beta) : 127KB

Note: If you think the latest version is big, you can use version 1.00 otherwise you can remove the images inside www/img or use smaller size images.
 

Attachments

  • JRDC Server (1.00).b4xtemplate
    9 KB · Views: 350
  • JRDC Server (1.11).b4xtemplate
    126.7 KB · Views: 263
  • JRDC Server (1.12).b4xtemplate
    126.7 KB · Views: 299
  • JRDC Server (2.00 beta).b4xtemplate
    126.5 KB · Views: 298
Last edited:

aeric

Expert
Licensed User
Longtime User
A member contacts me privately asking about putting SQLite database in other location instead of Objects folder.
Here are the changes required:

config.properties:
## SQLite configuration:
DriverClass=com.sqlite.JdbcUrl
JdbcUrl=jdbc:sqlite:C:\\Users\\aeric\\AppData\\Roaming\\JRDC\\test.db

Version 1.00:
RDCConnector.bas:
'Class module
Sub Class_Globals
    Private pool As ConnectionPool
    Private DebugQueries As Boolean
    Private commands As Map
    Private DBDir As String
    Private DBName As String = "test.db"
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    pool.Initialize(Main.config.Get("DriverClass"), _
    Main.config.Get("JdbcUrl"), _
    Main.config.Get("User"), _
    Main.config.Get("Password"))
    #if DEBUG
    DebugQueries = True
    #else
    DebugQueries = False
    #end if
    LoadSQLCommands(Main.config)
    CheckDatabase
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(Main.config)
    If Main.config.Get("DriverClass").As(String).Contains("sqlite") Then
        Dim sql As SQL
        'Dim jdbc() As String = Regex.Split(":", Main.config.Get("JdbcUrl"))
        'If jdbc.Length > 2 Then sql.InitializeSQLite(File.DirApp, jdbc(2), False)
        sql.InitializeSQLite(DBDir, DBName, False)
        Return sql
    Else
        Return pool.GetConnection
    End If
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

Private Sub CheckDatabase
    Try
        Dim con As SQL
        Dim DBType As String
        Dim DBFound As Boolean
        Log($"Checking database..."$)
      
        If Main.config.Get("DriverClass").As(String).Contains("sqlite") Then
            DBType = "sqlite"
            'Dim jdbc() As String = Regex.Split(":", Main.config.Get("JdbcUrl"))
            'If jdbc.Length > 2 Then
            '    DBName = jdbc(2)
            '    If File.Exists(File.DirApp, DBName) Then
            '        DBFound = True
            '    End If
            'End If
            Dim JdbcUrl As String = Main.config.Get("JdbcUrl")
            DBDir = JdbcUrl.SubString(12).Replace("\" & DBName, "")
            If File.Exists(DBDir, DBName) Then
                DBFound = True
            End If
        End If
      
        If Main.config.Get("DriverClass").As(String).Contains("mysql") Then
            DBType = "mysql"
            Dim JdbcUrl As String = Main.config.Get("JdbcUrl").As(String).Replace(DBName, "information_schema")
            pool.Initialize(Main.config.Get("DriverClass"), JdbcUrl, Main.config.Get("User"), Main.config.Get("Password"))
            con = GetConnection
            If con.IsInitialized Then
                Dim strSQL As String = GetCommand("CHECK_DATABASE")
                Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(DBName))
                Do While res.NextRow
                    DBFound = True
                Loop
                res.Close
            End If
        End If
      
        If DBFound Then
            Log("Database found!")
        Else   ' Create database if not exist
            Log("Database not found!")
            Log("Creating database...")
            Select DBType
                Case "sqlite"
                    'con.InitializeSQLite(File.DirApp, DBName, True)
                    con.InitializeSQLite(DBDir, DBName, True)
                    con.ExecNonQuery("PRAGMA journal_mode = wal")
                Case "mysql"
                    ConAddSQLQuery2(con, "CREATE_DATABASE", "{DBNAME}", DBName)
                    ConAddSQLQuery2(con, "USE_DATABASE", "{DBNAME}", DBName)
            End Select
      
            ConAddSQLQuery(con, "CREATE_TABLE_TBL_CATEGORY")
            ConAddSQLQuery(con, "INSERT_DUMMY_TBL_CATEGORY")
            ConAddSQLQuery(con, "CREATE_TABLE_TBL_PRODUCTS")
            ConAddSQLQuery(con, "INSERT_DUMMY_TBL_PRODUCTS")
            Dim CreateDB As Object = con.ExecNonQueryBatch("SQL")
            Wait For (CreateDB) SQL_NonQueryComplete (Success As Boolean)
            If Success Then
                Log("Database is created successfully!")
            Else
                Log("Database creation failed!")
            End If
        End If
        CloseDB(con)
    Catch
        LogError(LastException)
        CloseDB(con)
        Log("Error creating database!")
        Log("Application is terminated.")
        ExitApplication
    End Try
End Sub

Public Sub CloseDB (con As SQL)
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Private Sub ConAddSQLQuery (Comm As SQL, Key As String)
    Dim strSQL As String = GetCommand(Key)
    If strSQL <> "" Then Comm.AddNonQueryToBatch(strSQL, Null)
End Sub

Private Sub ConAddSQLQuery2 (Comm As SQL, Key As String, Val1 As String, Val2 As String)
    Dim strSQL As String = GetCommand(Key).As(String).Replace(Val1, Val2)
    If strSQL <> "" Then Comm.AddNonQueryToBatch(strSQL, Null)
End Sub

Note: Make sure the path is correct.

Please start a new thread if you have any question related to this template.
 
Last edited:

aeric

Expert
Licensed User
Longtime User
Version 1.10 is now released!

What's New:
  1. New Homepage - Index page is now a default test page, with information such as DBType and status of database connection test. Nice design with fadein-fadeout logo.
  2. Auto open default browser and navigate to homepage using jShell. This happens in Debug or Release (with argument "dev").
  3. Support more database connections! (SQLite, MySQL, MS SQL, Firebird, Postgresql, DBF)
  4. Default database with dummy data (not new actually). Currently tested for SQLite. May also works in MySQL if config.properties is set with root username.
Create a new project and hit the Run button!

Please report bugs if any. Thanks!
 

aeric

Expert
Licensed User
Longtime User
Version 1.12 updated!

What's New:
  1. Use build configuration to select which database to connect and AdditionalJar to use. You still need to activate the correct settings in config.properties.
  2. Changes in RDCConnector Initialize sub
  3. Reformat config.properties
Create a new project (default using SQLite) and hit the Run button!

Please report bugs if any. Thanks!
 

aeric

Expert
Licensed User
Longtime User
Version 2.00 (beta) uploaded!

What's New:
  1. New queries settings in config.properties file using DBType as prefix.
  2. Remove version 1 code
Create a new project (default using SQLite) and hit the Run button!

Note: Make sure match the right version of jdbc driver. For MySQL, make sure user name and password are correct for creating new database.

Please report bugs if any. Thanks!
 
Top