B4J Question xyzBD, a Class that repackages SQL objects

eric19740521 · Sep 7, 2022

xyzBD, a Class that repackages SQL objects

xzyDB Class Code:

xyzDB Class:

'VERSION:20220905
'備註
'LIB:jdbc,jSQL
'mssql lib jdbcSQL需開啟

'MySQL
'#AdditionalJar: mysql-connector-java-5.1.47-bin.jar
'#AdditionalJar: mysql-connector-java-5.1.34-bin.jar
'MSSQL
'#AdditionalJar: jtds-1.3.1.jar     
'SQLite
'#AdditionalJar: sqlite-jdbc-3.7.2 
'firebird 2.5
'#AdditionalJar: jaybird-full-2.2.15.jar

#Region Project Attributes
    '跳過未使用的提示!!!
    #IgnoreWarnings: 12
#End Region
Sub Class_Globals
    'Private fx As JFX
  
    'Public SQLDB As Object
    Public MySQL As SQL
    Public SQLite As SQL
    Public MSSQL As JdbcSQL        '特別用在mssql
    Public Firebird As SQL
  
    Private SQLDriver As String
    Private SQLJDBC As String
    'Public SQLServerIP As String
  
    Public uc_DB_Type As String = "mySQL"
    Public uc_DB_SERVER As String = "127.0.0.1"
    Public uc_DB_Path As String = ""
    Public uc_DB_USER As String    = ""
    Public uc_DB_PASSWORD As String = ""
  
    Public LastError As String = ""
  
    Public Is_CollateNocase As Boolean = False
  
    '資料表欄位屬性
    Public DB_INTEGER,DB_NUMERIC   As String
    Public DB_CHAR As String
    Public DB_BLOB,DB_REAL,DB_TEXT As String
  
  
    '
    Private TableFields As List
    Private TableWheres As List
    Private SQLCommands As List
  
    Public TableName As String
    Public CommandType As String
    Public PrimaryKey As String
  
    '
    Public DBResultSet As ResultSet
  
  
    '
    Type FieldAttributes (fieldName As String,fieldType As String,fieldWidth As Int,fieldDec As Int)
    Type FieldAttributesN (fieldName As String,fieldValue As String,fieldType As String)

    Type SQLCommand (Command As String, Parameters() As Object)
  
    Type MyTrigger (TriggerName As String, TriggerContent As String)
    'procedure
    Type MyProcedure (ProcedureName As String, ProcedureContent As String)
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    '
    SQLDriver = "com.mysql.jdbc.Driver"
    SQLJDBC = "jdbc:mysql://"
  
    '
    DB_INTEGER = "INTEGER"
    DB_NUMERIC = "NUMERIC"

    DB_CHAR = "CHAR"
  
    DB_REAL = "REAL"
    DB_BLOB = "BLOB"
    DB_TEXT = "TEXT"
  
    '資料表欄位 列表
    TableFields.Initialize
    TableWheres.Initialize
    SQLCommands.Initialize
  
    TableName = ""
    CommandType = ""
    PrimaryKey = ""
End Sub

Sub Close
  
    Select uc_DB_Type.ToUpperCase
        Case "MYSQL"
            MySQL.Close
      
        Case "MSSQL"
            MSSQL.Close

        Case "SQLITE"
            SQLite.Close
  
        Case "FIREBIRD"
            Firebird.Close
  
          
        Case Else
            Log("close.err")
    End Select
    'SQLDB.close
  
End Sub


Sub Connect(tc_DATABASE As String) As Boolean
    Dim jdbcUrl As String
  
    '連線到資料庫
    Try
        Log("uc_DB_Type= "&uc_DB_Type)
        Select uc_DB_Type.ToUpperCase
            Case "MYSQL"
          
                SQLDriver = "com.mysql.jdbc.Driver"
                SQLJDBC = "jdbc:mysql://"
          
          
                jdbcUrl =  SQLJDBC & $"${uc_DB_SERVER}:3306/${tc_DATABASE}?characterEncoding=utf8"$

                MySQL.Initialize2( SQLDriver, jdbcUrl , uc_DB_USER , uc_DB_PASSWORD)

  
                If MySQL.IsInitialized = False Then
                    LastError = "connect fail"
                    Return False
                End If
  
  
  
            Case "MSSQL"
              

                SQLDriver = "net.sourceforge.jtds.jdbc.Driver"
                jdbcUrl = $"jdbc:jtds:sqlserver://${uc_DB_SERVER}"$

                'MSQL 7.0
                'jdbcUrl = jdbcUrl & $";databaseName=${tc_DATABASE};user=${uc_DB_USER};password=${uc_DB_PASSWORD};appname=SKMJL;wsid=TEST;loginTimeout=10;socketTimeout=10"$
                'MSSQL SQLEXPRESS 2019
                jdbcUrl = jdbcUrl & $"/${tc_DATABASE};instance=SQLEXPRESS;user=${uc_DB_USER};password=${uc_DB_PASSWORD};integratedSecurity=true;"$
              
              
                '
                'jdbcUrl="jdbc:jtds:sqlserver://127.0.0.1/tudou;integratedSecurity=true;user=sa;password=Ho123456789;instance=SQLEXPRESS;"
              
                Log("jdbcUrl= "&jdbcUrl )
                'sql.Initialize("net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://localhost:1433/test;instance=SQLEXPRESS;?user=test&password=test")

              
                MSSQL.Initialize( SQLDriver, jdbcUrl  )
              
                If MSSQL.IsInitialized = False Then
                    LastError = "connect fail"
                    Return False
                End If
              
 
  
            Case "SQLite".ToUpperCase
              
                Dim dbName As String = tc_DATABASE & ".db"
                '檢查資料庫檔案是否存在
                If File.Exists(uc_DB_Path, dbName)==False Then
                    Log("DB不存在??程式將自動建立 ")
                End If
              
                Log("uc_DB_Path= "&uc_DB_Path)
              
                SQLite.InitializeSQLite(uc_DB_Path, dbName, True)
                SQLite.ExecQuerySingleResult("PRAGMA journal_mode = wal")
  
                If SQLite.IsInitialized = False Then
                    LastError = "connect fail"
                    Return False
                End If
              
            Case "FIREBIRD"
                '"jdbc:firebirdsql://127.0.0.1:3050/d:/NEWPOS.GDB"
                SQLDriver = "org.firebirdsql.jdbc.FBDriver"
                SQLJDBC = "jdbc:firebirdsql://"
          
          
                jdbcUrl =  SQLJDBC & $"${uc_DB_SERVER}:3050/${tc_DATABASE}"$

                Firebird.Initialize2( SQLDriver, jdbcUrl , uc_DB_USER , uc_DB_PASSWORD)

  
                If Firebird.IsInitialized = False Then
                    LastError = "connect fail"
                    Return False
                End If
                              

            Case Else
                Log("uc_DB_Type.err")
                Return False
        End Select
  
    Catch
        'Log(LastException)
        'Log("sql錯誤: "&LastException)
        LastError = LastException
        Return False
    End Try
  
  
 
  
    Return True
End Sub



Private Sub EscapeField(f As String) As String
    Return "[" & f & "]"
End Sub


public Sub ClearCommand()
  
    '資料表欄位 列表
    TableFields.Initialize
    TableWheres.Initialize
    SQLCommands.Initialize
  
    TableName = ""
    CommandType = ""
    PrimaryKey = ""
  
End Sub

'AddField 自己用,所以沒有嚴謹的錯誤判斷.
'db.AddField("mem_no",db.DB_CHAR,"10")
public Sub AddField(p1 As String,p2 As String,p3 As String)
  
 
    Select CommandType
        Case "CreateTable"
            Dim lc_fieldName ,lc_fieldType ,lc_fieldWidth As String
      
            lc_fieldName = p1
            lc_fieldType = p2
            lc_fieldWidth =p3
      
      
            Dim f As FieldAttributes
            f.Initialize
  
            Dim lc_fieldDec As Int = 0        '預設為0
            Dim lc_str As String = lc_fieldWidth

            If lc_str.IndexOf(",")>0 Then
                lc_fieldWidth = lc_str.SubString2(0,lc_str.IndexOf(","))
                lc_fieldDec = lc_str.SubString(lc_str.IndexOf(",")+1)
 
            End If

  
            '依照個別欄位屬性去做處裡
            Select lc_fieldType.ToUpperCase
                Case DB_CHAR
                    f.fieldName = lc_fieldName
                    f.fieldType = lc_fieldType
                    f.fieldWidth = lc_fieldWidth
                    f.fieldDec = 0
          
                Case DB_INTEGER,DB_NUMERIC
                    If lc_fieldType.ToUpperCase = DB_INTEGER Then
                        f.fieldName = lc_fieldName
                        f.fieldType = lc_fieldType
                        f.fieldWidth = lc_fieldWidth
                        f.fieldDec = 0
                    End If
                    If lc_fieldType.ToUpperCase = DB_NUMERIC Then
                        f.fieldName = lc_fieldName
                        f.fieldType = lc_fieldType
                        f.fieldWidth = lc_fieldWidth
                        f.fieldDec = lc_fieldDec
                    End If

                Case DB_TEXT
                    f.fieldName = lc_fieldName
                    f.fieldType = lc_fieldType
                    f.fieldWidth = lc_fieldWidth
                    f.fieldDec = 0

                Case DB_BLOB
                    f.fieldName = lc_fieldName
                    f.fieldType = lc_fieldType
                    f.fieldWidth = lc_fieldWidth
                    f.fieldDec = 0
          
                Case DB_REAL
                    f.fieldName = lc_fieldName
                    f.fieldType = lc_fieldType
                    f.fieldWidth = lc_fieldWidth
                    f.fieldDec = 0
            End Select
            '
            TableFields.Add(f)
          
          
          
          
        Case  "QUERY","INSERT","UPDATE","DELETE"
            Dim lc_fieldName ,lc_fieldType ,lc_fieldValue As String
      
            lc_fieldName = p1
            lc_fieldValue = p2
            lc_fieldType =p3
      
      
            Dim fan As FieldAttributesN
            fan.Initialize
      
            fan.fieldName = lc_fieldName
            fan.fieldValue = lc_fieldValue
            fan.fieldType = lc_fieldType
      
            '
            TableFields.Add(fan)


        Case Else
            Log("CommandType Err")
    End Select
          





End Sub

public Sub AddWhere(p1 As String,p2 As String,p3 As String)
    Dim lc_fieldName ,lc_fieldValue As String
      
    lc_fieldName = p1
    lc_fieldValue = p2
  
      
      
    Dim fan As FieldAttributesN
    fan.Initialize
  
    fan.fieldName = lc_fieldName
    fan.fieldValue = lc_fieldValue
  
  
    TableWheres.Add(fan)
End Sub


'SQLite特別欄位屬性
'REAL : 浮點數值。以 8 Bytes來存放IEEE浮點數。
'TEXT : 文字字串值。以資料庫的文字編碼方式：UTF-8, UTF-16BE, UTF-16LE來儲存資料。
'BLOB : 二進位大型物件(Binary Large OBject)。
 
Public Sub SQLBuilder() As String
    Log("DBConnect SQLBuilder==>")
  
    Dim lc_sqlCommand As String
  
  
    Select CommandType
        Case "CreateTable"
            lc_sqlCommand = SQLBuilder_CreateTable
          
        Case "DropTable"
            lc_sqlCommand = SQLBuilder_DropTable
          
        Case "INSERT"
            lc_sqlCommand = SQLBuilder_Insert

        Case "UPDATE"
            lc_sqlCommand = SQLBuilder_Update

        Case "DELETE"
            lc_sqlCommand = SQLBuilder_Delete
          
        Case "QUERY"
            lc_sqlCommand = SQLBuilder_Query
          
        Case Else
            Log("CommandType Err")
    End Select
  
    '資料表欄位 列表
    TableFields.Initialize
    TableWheres.Initialize
    'SQLCommands.Initialize
  
    TableName = ""
    CommandType = ""
    PrimaryKey = ""
  
  
    Log("SQLCommand: "&lc_sqlCommand)
  
    Return lc_sqlCommand
End Sub


private Sub SQLBuilder_DropTable() As String
    Dim lc_sqlCommand As String
  
    lc_sqlCommand = "DROP TABLE IF EXISTS " & EscapeField(TableName)

    Return lc_sqlCommand
End Sub


private Sub SQLBuilder_CreateTable() As String
    Dim sb As StringBuilder
    '
    sb.Initialize
    sb.Append("(")
    For i=0 To TableFields.Size-1
        Dim fieldName As String
        Dim fieldType As String
        Dim fieldWidth As Int = 0
        Dim fieldDec As Int = 0
        Dim fieldCollateNocase As String = ""
      
        If Is_CollateNocase = True Then    'SQLite不分大小寫用
            fieldCollateNocase = "COLLATE NOCASE"
        End If
        Dim f As FieldAttributes
      
        f.Initialize
        f=TableFields.Get(i)
      
        fieldName = f.fieldName
        fieldType = f.fieldType
        fieldWidth= f.fieldWidth
        fieldDec = f.fieldDec
      
'        Log("fieldName: "&fieldName)
'        Log("fieldType: "&fieldType)
'        Log("fieldWidth:"&fieldWidth)
'        Log("fieldDec: "&fieldDec)
      
        If i > 0 Then sb.Append(", ")
      
        'SQLite欄位屬性
        Select fieldType.ToLowerCase
            Case "char"
                sb.Append( $" ${fieldName}  ${fieldType}(${fieldWidth}) DEFAULT '' ${fieldCollateNocase} "$ )
              
            Case "numeric","decimal"
                sb.Append(  $" ${fieldName}  ${fieldType}(${fieldWidth},${fieldDec}) DEFAULT '0' "$ )

            Case "int","smallint","tinyint"
                fieldDec=0
                sb.Append(  $" ${fieldName}  ${fieldType}(${fieldWidth}) DEFAULT '0' "$ )

            Case "datetime"
                sb.Append(  $" ${fieldName}  ${fieldType}    "$ )
              
                '比較特別的欄位 real/text    /blob,我通常不會使用
            Case "real","text","blob"
                sb.Append(  $" ${fieldName}  ${fieldType}    "$ )
        
              
              
            Case Else
                Log("未知的欄位名稱: "&fieldType)
        End Select
      
        'PK
        If fieldName = PrimaryKey Then sb.Append(" PRIMARY KEY")
    Next
    sb.Append(")")
  
 
  
    Dim lc_sqlCommand As String
    lc_sqlCommand = "CREATE TABLE IF NOT EXISTS " & EscapeField(TableName) & " " & sb.ToString
      
    Return lc_sqlCommand
End Sub


private Sub SQLBuilder_Insert() As String
    Dim sb, columns, values As StringBuilder
  
    sb.Initialize
    columns.Initialize
    values.Initialize
  
    sb.Append("INSERT INTO [" & TableName & "] (")
    For i=0 To TableFields.Size-1
        Dim fieldName As String
        'Dim fieldType As String
        Dim fieldValue As String
      
        Dim fan As FieldAttributesN
      
        fan.Initialize
        fan=TableFields.Get(i)
      
        fieldName = fan.fieldName
        fieldValue = fan.fieldValue
        'fieldType = fan.fieldType
      
        If i > 0 Then
            columns.Append(", ")
            values.Append(", ")
        End If
        columns.Append(EscapeField(fieldName))
              
        values.Append($"'${fieldValue}'"$)
      
      
    Next
    sb.Append(columns.ToString)
    sb.Append(") VALUES (")
    sb.Append(values.ToString)
    sb.Append(")")
  
  
  
    Dim lc_sqlCommand As String
  
    lc_sqlCommand = sb.ToString

    Return lc_sqlCommand
End Sub


private Sub SQLBuilder_Update() As String
    Dim sb, columns, wheres As StringBuilder
  
    sb.Initialize
    columns.Initialize
    wheres.Initialize

  
    For i=0 To TableFields.Size-1
        'Log("1")
      
        Dim fieldName As String
        Dim fieldValue As String
      
        Dim fan As FieldAttributesN
      
        fan.Initialize
        fan=TableFields.Get(i)
      
        fieldName = fan.fieldName
        fieldValue = fan.fieldValue
      
        If i > 0 Then
            columns.Append(", ")
        End If
        ' update member set mem_name='123'  where mem_no='a001'
        columns.Append(EscapeField(fieldName))
        columns.Append($"='${fieldValue}' "$ )
      
    Next
  
  
    For i=0 To TableWheres.Size-1
        'Log("2")
      
        Dim fieldName As String
        Dim fieldValue As String
      
        Dim fan As FieldAttributesN
      
        fan.Initialize
        fan=TableWheres.Get(i)
      
        fieldName = fan.fieldName
        fieldValue = fan.fieldValue
      
        If i > 0 Then
            wheres.Append(" AND ")
        End If
        ' update member set mem_name='123'  where mem_no='a001'
        wheres.Append(EscapeField(fieldName))
        wheres.Append($"='${fieldValue}' "$ )
      
    Next
  
    'Log("3")
    '
    Dim lc_sqlCommand As String
  
    lc_sqlCommand = $"UPDATE ${EscapeField(TableName)} SET ${columns.ToString} WHERE ${wheres.ToString} "$

    Return lc_sqlCommand
End Sub


private Sub SQLBuilder_Delete() As String
    Dim sb, columns, wheres As StringBuilder
  
    sb.Initialize
    columns.Initialize
    wheres.Initialize

    'WHERE 
    For i=0 To TableWheres.Size-1
        'Log("2")
      
        Dim fieldName As String
        Dim fieldValue As String
      
        Dim fan As FieldAttributesN
      
        fan.Initialize
        fan=TableWheres.Get(i)
      
        fieldName = fan.fieldName
        fieldValue = fan.fieldValue
      
        If i > 0 Then
            wheres.Append(" AND ")
        End If
 
        wheres.Append(EscapeField(fieldName))
        wheres.Append($"='${fieldValue}' "$ )
      
    Next
  
    'Log("3")
    '
    Dim lc_sqlCommand As String
  
    lc_sqlCommand = $"DELETE FROM ${EscapeField(TableName)} WHERE ${wheres.ToString} "$

    Return lc_sqlCommand
End Sub


private Sub SQLBuilder_Query() As String
    Dim sb, columns, wheres As StringBuilder
  
    sb.Initialize
    columns.Initialize
    wheres.Initialize

    'Fields
    For i=0 To TableFields.Size-1
        'Log("1")
      
        Dim fieldName As String
      
      
        Dim fan As FieldAttributesN
      
        fan.Initialize
        fan=TableFields.Get(i)
      
        fieldName = fan.fieldName
      
      
        If i > 0 Then
            columns.Append(", ")
        End If
 
        columns.Append(EscapeField(fieldName))
      
    Next
  
    Dim lc_field As String = ""
  
    If TableFields.Size = 0 Then
        lc_field = "*"
    Else 
        lc_field = columns.ToString
    End If

    'WHERE 
    For i=0 To TableWheres.Size-1
        'Log("2")
      
        Dim fieldName As String
        Dim fieldValue As String
      
        Dim fan As FieldAttributesN
      
        fan.Initialize
        fan=TableWheres.Get(i)
      
        fieldName = fan.fieldName
        fieldValue = fan.fieldValue
      
        If i > 0 Then
            wheres.Append(" AND ")
        End If
 
        wheres.Append(EscapeField(fieldName))
        wheres.Append($"='${fieldValue}' "$ )
      
    Next
  
    Dim lc_where As String = ""
  
    If TableWheres.Size = 0 Then
        lc_where = " "
    Else 
        lc_where = $" WHERE ${wheres.ToString} "$
    End If
  
    '
    Dim lc_sqlCommand As String
  
    lc_sqlCommand = $"SELECT ${lc_field} FROM ${EscapeField(TableName)}  ${lc_where} "$

    Return lc_sqlCommand
End Sub

public Sub AddCommand(cmd As String)
    Dim s As SQLCommand
  
    s.Initialize
    s.Command = cmd
  
    SQLCommands.Add(s)
End Sub

public Sub AddCommand2(cmd As String, params() As Object)
    Dim s As SQLCommand
  
    s.Initialize
    s.Command = cmd
    s.Parameters = params
  
    SQLCommands.Add(s)
End Sub


public Sub ExecuteCommands() As  Boolean
    Log("DBConnect ExecuteCommands==>")
    Dim SQL As SQL
 

    'SQL連線給定
    Select uc_DB_Type.ToUpperCase
        Case "MYSQL"
            SQL=MySQL
        Case "MSSQL"
            SQL=MSSQL
        Case "SQLITE"
            SQL=SQLite
        Case "FIREBIRD"
            SQL=Firebird
        Case Else
            Log("ExecuteCommands.err")
    End Select

    SQL.BeginTransaction                '交易開始
    '
    Try
        For Each cmd As SQLCommand In SQLCommands
            Dim pa() As Object

            If pa = Null Then
                pa= Array()
            Else
                pa=cmd.Parameters
            End If
            Log("Command: "&cmd.Command)
          
            SQL.ExecNonQuery2(cmd.Command ,pa)
          
        Next
        SQL.TransactionSuccessful    '交易成功
      
    Catch
        SQL.Rollback                '交易失敗
        'Log(LastException)
        LastError = LastException
      
        Return False
    End Try

    Return True
End Sub

public Sub ExecuteQuery() As  Boolean
    Log("DBConnect ExecuteQuery==>")
    Dim SQL As SQL
 

    'SQL連線給定
    Select uc_DB_Type.ToUpperCase
        Case "MYSQL"
            SQL=MySQL
        Case "MSSQL"
            SQL=MSSQL
        Case "SQLITE"
            SQL=SQLite
        Case "FIREBIRD"
            SQL=Firebird
        Case Else
            Log("ExecuteQuery.err")
    End Select

    If SQLCommands.Size = 0 Then
        LastError = "SQL Command is Null"
      
        Return False
    End If
    '
    Try
        Dim i As Int =0
        For Each cmd As SQLCommand In SQLCommands
            Dim pa() As Object

            If pa = Null Then
                pa= Array()
            Else
                pa=cmd.Parameters
            End If
          
            DBResultSet = SQL.ExecQuery2(cmd.Command ,pa)
          
            i=i+1
            If i=1 Then            '只執行第一個
                Exit
            End If
  
        Next
      
'     
'        For i=0 To SQLCommands.Size-1             
'            Dim s As SQLCommand
'            Dim cmd As String
'            Dim pa() As Object
'            s.Initialize
'            s=SQLCommands.Get(i)
'         
'            cmd=s.Command
'            pa=s.Parameters
'         
'            If pa = Null Then
'                pa= Array()
'            End If
'
'            DBResultSet = SQL.ExecQuery2(cmd,pa)
'
'            If i=0 Then            '只執行第一個
'                Exit
'            End If
'        Next

    Catch
        'Log(LastException)
        LastError = LastException
      
        Return False
    End Try

    Return True
End Sub

  
public Sub SQLExec(lc_sqlCommand As String) As  Boolean
    Log("DBConnect SQLExec==>")
    Dim SQL As SQL
 

    'SQL連線給定
    Select uc_DB_Type.ToUpperCase
        Case "MYSQL"
            SQL=MySQL
        Case "MSSQL"
            SQL=MSSQL
        Case "SQLITE"
            SQL=SQLite
        Case "FIREBIRD"
            SQL=Firebird
        Case Else
            Log("SQLExec.err")
    End Select
    ' 
    If lc_sqlCommand.Length = 0 Then
        LastError = "sqlCommand Null"
        Return False
    End If
    '
    Try
        If CommandType = "QUERY" Then
            DBResultSet = SQL.ExecQuery(lc_sqlCommand)
        Else
            SQL.ExecNonQuery(lc_sqlCommand) 
        End If
      
    Catch
        'Log(LastException)
        LastError = LastException
        Return False
    End Try
    Return True
End Sub


Public Sub ExecuteTableView(TableView1 As TableView) As ResumableSub
    Log("DBConnect ExecuteTableView==>")
  
    Dim Result As Int = -1
  
 
  
  
    TableView1.Items.Clear
  
    Dim Cursor As ResultSet = DBResultSet
 
    'Cursor.ColumnCount
    Do While Cursor.NextRow
         Dim values(2) As String
      
        values(0) = Cursor.GetString("mem_no")
        values(1) = Cursor.GetString("mem_name")
      
        TableView1.Items.Add(values)
      
        Result = 1
    Loop
  
' 
'    If res <> Null And res.IsInitialized Then
'        Dim cols As List
'        cols.Initialize
'        For i = 0 To res.Columns.Size -1
'            cols.Add(res.Columns.GetKeyAt(i))
'        Next
'        TableView1.SetColumns(cols)
'        For Each row() As Object In res.Rows
'            Dim values(res.Columns.Size) As String
'            Dim value As String
'            For col = 0 To res.Columns.Size - 1
'                value = row(col)
'                values(col) = value
'            Next
'            TableView1.Items.Add(values)
'        Next
'        Result = res.Rows.Size
'    End If
    Return Result
End Sub

The actual method used is as follows

B4X Client:

    Dim db As xyzDB
  
    db.Initialize
    db.uc_DB_Type = "SQLite"
    db.uc_DB_Path = "D:\"    'File.DirApp
    'connSQLite.uc_DB_Path = "D:\"
    '連到資料庫Connect DB
    If db.Connect("test") = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
      
        db.close
        Return
    Else
        Log("Connect  OK!!連線成功")
    End If

    '移除資料表DROP TABLE
    'SQLCommand: DROP TABLE IF EXISTS [member]
    db.ClearCommand    'ClearCommand
    db.AddCommand("DROP TABLE IF EXISTS [member] ")
    If db.ExecuteCommands = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
      
        db.close
        Return
    Else
        Log("DropTable OK!!成功")
    End If
 

    '建立資料表CREATE TABLE
    'SQLCommand: CREATE TABLE IF NOT EXISTS [member] ( mem_no  CHAR(10) DEFAULT ''   PRIMARY KEY,  mem_name  CHAR(20) DEFAULT ''  ,  mem_memo  CHAR(50) DEFAULT ''  ,  mem_num  NUMERIC(20,5) DEFAULT '0' )
    db.ClearCommand    'ClearCommand
    db.AddCommand("CREATE TABLE IF NOT EXISTS [member] ( mem_no  CHAR(10) DEFAULT ''   PRIMARY KEY,  mem_name  CHAR(20) DEFAULT ''  ,  mem_memo  CHAR(50) DEFAULT ''  ,  mem_num  NUMERIC(20,5) DEFAULT '0' ) ")
    If db.ExecuteCommands = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
      
        db.close
        Return
    Else
        Log("CreateTable OK!!成功")
    End If
  
    '執行指令ExecuteCommands
    db.ClearCommand    'ClearCommand
    'SQLCommand: INSERT INTO [member] ([mem_no], [mem_name], [mem_memo], [mem_num]    ) VALUES ('A003', '小王', 'xxx', '3')
    db.AddCommand2("INSERT INTO member (mem_no,mem_name,mem_memo,mem_num) VALUES(?,?,?,?) ",Array("A001", "小明","xxx", "1") )
    db.AddCommand2("INSERT INTO member (mem_no,mem_name,mem_memo,mem_num) VALUES(?,?,?,?) ",Array("A002", "阿嬌","xxx", "2") )
    'db.AddCommand2("INSERT INTO member (mem_no,mem_name,mem_memo,mem_num) VALUES(?,?,?,?) ",Array("A003", "小王","xxx", "3") )
    '
    db.CommandType="INSERT"
    db.TableName="member"
    db.AddField("mem_no","A003",db.DB_CHAR)
    db.AddField("mem_name","小王",db.DB_CHAR)
    db.AddField("mem_memo","xxx",db.DB_Char)
    db.AddField("mem_num","3",db.DB_NUMERIC)
    db.AddCommand(db.SQLBuilder())
    '
    db.CommandType="UPDATE"
    db.TableName="member"
    'db.AddField("mem_no","A002",db.DB_CHAR)
    db.AddField("mem_name","阿華",db.DB_CHAR)
    db.AddField("mem_memo","小美人",db.DB_Char)
    'db.AddField("mem_num","1",db.DB_NUMERIC)
    db.AddWhere("mem_no","A001",db.DB_CHAR)
    db.AddCommand(db.SQLBuilder())
    '
    db.AddCommand("DELETE FROM member WHERE mem_no='A001'")
    '
    If db.ExecuteCommands = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
      
        db.close
        Return
    Else
        Log("ExecuteCommands OK!!成功")
    End If
  
    '查詢紀錄ExecuteQuery
    db.ClearCommand    'ClearCommand
    db.AddCommand("Select mem_no,mem_name from member")
    'db.AddCommand2("Select mem_no,mem_name from member where mem_no=?",Array("A001"))
    If db.ExecuteQuery = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
      
        db.close
        Return
    Else
        Log("QUERY OK!!成功")

        db.ExecuteTableView(TableView1)
    End If

 
    db.close

Attach a working project.

It's still in development... there are bound to be bugs?? Any suggestion is good.

aeric · Sep 7, 2022

I think square brackets "[ ]" is only supported in MSSQL. There may be error in SQLite or MySQL. Need to check some special characters like single quote, double quotes and back quote (`) as well.

eric19740521 · Sep 7, 2022

This is what you said
SQLCommand: INSERT INTO [member] ([mem_no], [mem_name], [mem_memo], [mem_num]) VALUES ('A003', 'Xiao Wang', 'xxx', '3')

brackets "[ ]" is OK...

I am currently using SQLite to test,
MySQL will be added in a few days

aeric · Sep 7, 2022

Ya, I think MySQL does not support "[ ]".

Mashiane · Sep 7, 2022

Perhaps instead of repetitive code where you add the data types for INSERT & UPDATE etc.

as in..

B4X:

db.CommandType="INSERT"
    db.TableName="member"
    db.AddField("mem_no","A003",db.DB_CHAR)
    db.AddField("mem_name","小王",db.DB_CHAR)
    db.AddField("mem_memo","xxx",db.DB_Char)
    db.AddField("mem_num","3",db.DB_NUMERIC)
    db.AddCommand(db.SQLBuilder())
    '
    db.CommandType="UPDATE"
    db.TableName="member"
    'db.AddField("mem_no","A002",db.DB_CHAR)
    db.AddField("mem_name","阿華",db.DB_CHAR)
    db.AddField("mem_memo","小美人",db.DB_Char)
    'db.AddField("mem_num","1",db.DB_NUMERIC)
    db.AddWhere("mem_no","A001",db.DB_CHAR)
    db.AddCommand(db.SQLBuilder())

Have the data types for the table stored separately. You can use a map for that, lets call it a schema.

As an example...

B4X:

Dim schema As Map = CreateMap()
schema.put("mem_no", db.DB_CHAR)
schema.put("mem_name", db.DB_CHAR)
schema.put("mem_memo", db.DB_CHAR)
schema.put("mem_num", db.DB_NUMERIC)
db.AddSchema("member", schema)

Then when doing inserts / updates, the existing schema for that table will be read and your code might be..

B4X:

db.CommandType="INSERT"
    db.TableName="member"
    db.AddField("mem_no","A003")
    db.AddField("mem_name","小王")
    db.AddField("mem_memo","xxx")
    db.AddField("mem_num","3")
    db.AddCommand(db.SQLBuilder())

    db.CommandType="UPDATE"
    db.TableName="member"
    'db.AddField("mem_no","A002")
    db.AddField("mem_name","阿華")
    db.AddField("mem_memo","小美人")
    'db.AddField("mem_num","1")
    db.AddWhere("mem_no","A001")
    db.AddCommand(db.SQLBuilder())

It will just mean you can tweak your code to get the data-type of each field before building the SQL stuff.

#JustThoughts

eric19740521 · Sep 7, 2022

aeric said:
Ya, I think MySQL does not support "[ ]".

Yes,
MySQL SQL Command : SELECT * FROM `member`

eric19740521 · Sep 8, 2022

Mashiane said:
It will just mean you can tweak your code to get the data-type of each field before building the SQL stuff.

#JustThoughts

Table Schema has been split...

B4X:

    '建立資料表CreateTable
    db.ClearCommand    '清除指令用的相關變數
    db.CommandType="CreateTable"
    db.TableName="member"
    db.PrimaryKey="mem_no"
   
    db.AddSchema("mem_no",db.DB_CHAR,"10")
    db.AddSchema("mem_name",db.DB_CHAR,"20")
    db.AddSchema("mem_memo",db.DB_CHAR,"50")
    db.AddSchema("mem_num",db.DB_NUMERIC,"20,5")
    'lc_sqlCommand = db.SQLBuilder()
    'Log("SQLCommand: " & lc_sqlCommand)
   
    If db.SQLExec(db.SQLBuilder()) = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
       
        db.close
        Return
    Else
        Log("CreateTable OK!!成功")
    End If
   
    '新增一筆紀錄 INSERT a Record
    'Dim lc_sqlCommand As String = ""
   
    db.ClearCommand    '清除指令用的相關變數
    db.CommandType="INSERT"
    db.TableName="member"
    db.AddField("mem_no","A001")
    db.AddField("mem_name","小明")
    db.AddField("mem_memo","xxx")
    db.AddField("mem_num","1")
    'lc_sqlCommand = db.SQLBuilder()
    'Log("SQLCommand: " & lc_sqlCommand)
   
    If db.SQLExec(db.SQLBuilder()) = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
       
        db.close
        Return
    Else
        Log("INSERT OK!!成功")
    End If

eric19740521 · Sep 9, 2022

Happy Mid-Autumn Festival 2022. Everyone

MySQL, MsSQL support

New Version,In Attach files

xyzDB ,The method of use is as follows:

    Dim db As xyzDB
    

    
    db.Initialize
    db.uc_DB_Type = "MySQL"
    db.uc_DB_SERVER = "127.0.0.1"
    db.uc_DB_USER = "root"
    db.uc_DB_PASSWORD = "1234"
    '連到資料庫Connect DB
    If db.Connect("test") = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
        
        db.close
        Return
    Else
        Log("Connect  OK!!連線成功")
    End If

    '移除資料表DROP TABLE
    db.ClearCommand    '清除指令用的相關變數
    db.CommandType="DropTable"
    db.TableName="member"
    If db.SQLExec(db.SQLBuilder()) = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
        
        db.close
        Return
    Else
        Log("DropTable OK!!成功")
    End If


'    '移除資料表DROP TABLE
'    'SQLCommand: DROP TABLE IF EXISTS [member]
'    db.ClearCommand    '清除指令用的相關變數
'    db.AddCommand("DROP TABLE IF EXISTS `member` ")
'   
'    If db.ExecuteCommands = False Then
'        xui.MsgboxAsync(db.LastError,"err")
'        Log("err: " & db.LastError)
'       
'        db.close
'        Return
'    Else
'        Log("DropTable OK!!成功")
'    End If
 

    '建立資料表CREATE TABLE
    'SQLCommand: CREATE TABLE IF NOT EXISTS [member] ( mem_no  CHAR(10) DEFAULT ''   PRIMARY KEY,  mem_name  CHAR(20) DEFAULT ''  ,  mem_memo  CHAR(50) DEFAULT ''  ,  mem_num  NUMERIC(20,5) DEFAULT '0' )
    db.ClearCommand    '清除指令用的相關變數
    db.CommandType="CreateTable"
    db.TableName="member"
    db.PrimaryKey="mem_no"
    
    db.AddSchema("mem_no",db.DB_CHAR,"10")
    db.AddSchema("mem_name",db.DB_CHAR,"20")
    db.AddSchema("mem_memo",db.DB_CHAR,"50")
    db.AddSchema("mem_num",db.DB_NUMERIC,"20,5")
    db.AddSchema("mem_image",db.DB_BLOB,"")            'BLOB
    
    db.AddSchema("mem_m1",db.DB_INTEGER,"10")
    db.AddSchema("mem_m2",db.DB_REAL,"10")
    db.AddSchema("mem_m3",db.DB_TEXT,"")
    db.AddSchema("mem_m4",db.DB_Datetime,"")
    
    'db.AddCommand(db.SQLBuilder())
    'If db.ExecuteCommands = False Then 'Or this method
    If db.SQLExec(db.SQLBuilder()) = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
        
        db.close
        Return
    Else
        Log("CreateTable OK!!成功")
    End If
    
    '執行指令ExecuteCommands
    db.ClearCommand    '清除指令用的相關變數
    'db.AddCommand2("INSERT INTO member (mem_no,mem_name,mem_memo,mem_num,mem_m4) VALUES(?,?,?,?,?) ",Array("A001", "小明","小小明", "1.5",d1) )
    db.AddCommand2("INSERT INTO member (mem_no,mem_name,mem_memo,mem_num,mem_m1,mem_m2,mem_m3) VALUES(?,?,?,?,?,?,?) ",Array("A002", "阿嬌","xxx", "2.3","1","2","test") )

    db.AddCommand2("INSERT INTO member (mem_no,mem_name,mem_memo,mem_num,mem_image) VALUES(?,?,?,?,?) ",Array("A003", "小王","xxx", "3.3",Utils.FileToByte(File.DirApp , "keys.png") ) )
    '
    db.CommandType="INSERT"
    db.TableName="member"
    db.AddField("mem_no","A001")
    db.AddField("mem_name","涼粉")
    db.AddField("mem_memo","好吃")
    db.AddField("mem_num","1.1")
    db.AddField("mem_m4", Utils.getToday )
    db.AddCommand(db.SQLBuilder())

    '
    db.CommandType="INSERT"
    db.TableName="member"
    db.AddField("mem_no","A004")
    db.AddField("mem_name","酸辣粉")
    db.AddField("mem_memo","xxx")
    db.AddField("mem_num","4")
    db.AddCommand(db.SQLBuilder())
    '
    db.CommandType="UPDATE"
    db.TableName="member"
    'db.AddField("mem_no","A002",db.DB_CHAR)
    db.AddField("mem_name","中秋")
    db.AddField("mem_memo","烤肉")
    'db.AddField("mem_num","1",db.DB_NUMERIC)
    db.AddWhere("mem_no","A001")
    db.AddCommand(db.SQLBuilder())
    '
    db.AddCommand("DELETE FROM member WHERE mem_no='A002'")
    '
    If db.ExecuteCommands = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
        
        db.close
        Return
    Else
        Log("ExecuteCommands OK!!成功")
    End If
    
    '查詢紀錄ExecuteQuery
    db.ClearCommand    '清除指令用的相關變數
    db.AddCommand("Select mem_no,mem_name,mem_image from member")
    'db.AddCommand2("Select mem_no,mem_name from member where mem_no=?",Array("A001"))
    If db.ExecuteQuery = False Then
        xui.MsgboxAsync(db.LastError,"err")
        Log("err: " & db.LastError)
        
        db.close
        Return
    Else
        Log("QUERY OK!!成功")

        ExecuteTableView(db)
        
    End If

 
    db.close

aeric · Sep 9, 2022

Your members are all food name. ?

eric19740521 · Sep 9, 2022

Concerns. Worries...
When writing programs, I have to write something boring,
such as today's Mid-Autumn BBQ

(煩躁..唉..中秋...我沒烤肉??隨便打得)

I don't know how to spend the Mid-Autumn Festival in Malaysia?? You are still researching your new products so late

B4J Question xyzBD, a Class that repackages SQL objects

eric19740521

Member

Attachments

aeric

Expert

eric19740521

Member

aeric

Expert

Mashiane

Expert

eric19740521

Member

eric19740521

Member

Attachments

eric19740521

Member

Attachments

aeric

Expert

eric19740521

Member

Similar Threads