B4J Library [module] (Universal) DBUtils

Discussion in 'B4J Libraries & Classes' started by OliverA, Jun 12, 2017.

Similar threads

B4A Tutorial [B4X] Smart String Literal
B4J Tutorial SQL Tutorial
B4J Library [B4X] DBUtils 2
B4J Tutorial TableView Tutorial
B4J Tutorial DBUtils Example
  1. OliverA

    OliverA Well-Known Member Licensed User

    I've originally posted this under the B4J Tutorial DBUtils Example. I've been asked by @Erel to post my slight contribution here. Please note that the modification only pertains to version 1.01 of DBUtils as posted in the B4J Tutorial forum. I'm re-posting my content below (originally found here). I've had a slight update since then, which is noted after the re-posting.

    Original post:


    Just for fun (it's Friday) I modified DBUtils and the example that @Erel provides here -> (https://www.b4x.com/android/forum/threads/dbutils-example.34611/)

    In both the demo and DBUtils.bas, the modifications were minor in order to accommodate MySQL.

    Summary of DBUtils changes:
    1) Added the public variables DB_VARCHAR and DB_BIGINT. MySQL cannot index TEXT fields, thus I had change the Student's Id to DB_VARCHAR. Also, MySQL's INTEGER field could not hold DateTime's long values and thus I added DB_BIGINT.

    2) Added a couple of private variables and a new public function (SetEscapeChars) that lets you set the DB's escape character. I also modified EscapeField to use these new variables and made the method Public (since it seemed useful for modifying some code in the demo).

    3) InsertMaps had an SQL statement that hard coded the escape characters and I changed it to use the EscapeField method

    That was it for DBUtils! Please note that you would need to add more DB_* variables if you need support for the plethora of column types that MySQL has (compared to sqlite).

    Summary of the demo changes:
    (Before Process_Globals)
    1) Add #AdditionalJar: mysql-connector-java-5.1.40-bin. Please note, this can change (with never version of the jar).
    (In AppStart)
    2) Replace the sqlite specific SQL1.InitializeSQLite method with the SQL1.Initialize2 method
    3) Used the new DBUtils.SetEscapeChars to set MySQL's escape characters
    4) Changed the Student's database to use the new DB_* variables
    5) Did the same for Grades
    6) Change SQL statement to use public version of EscapeField - this is a generic change and not MySQL specific
    (In cmbStudentID_ValueChanged)
    7) Change SQL statement to use public version of EscapeField - this is a generic change and not MySQL specific
    (In FindFailedTests)
    8) Changed SQL statement to use MySQL concatenation operator - THIS CAN BE VERY DB SPECIFIC!!!!!

    As to having ExecuteHTML call the server - Why? Do it like the demo and create you SQL connection, create your query string and you string list of arguments and then call ExecuteHTML. From looking at it's code, it is very DB agnostic. Adding code to it to connect to a SQL server would make it far less generic.

    I'm attaching the modified DBUtils and demo. Before running against MySQL server, change the SQL.Initialize2 to your setup

    Note: Search the forum and there are lots of examples of additional functions that could be put into the DBUtils.bas module.

    End of original post

    2017/06/12: Updated DeleteRecord to use EscapeField instead of hard coded field escaping.

    Even though initially this was a modification to accommodate MySQL, this should now be universally applicable to various SQL DB's.

    SQLite has a very limited set of column types. This will most likely be some cause of errors (as seen above) when converting to another DB. The interesting part is that SQLite will gladly map another DB's type to its own build in types (see https://sqlite.org/datatype3.html). One may consider using a more specific data type even with SQLite in order to help with any future DB migrations. The additional data types will be one of the reasons to modify DBUtils code (to add additional DB_* declarations). Any data types that use sizing (such as DB_VARCHAR()) may need multiple DB_* declarations (one for each size) and may seem a little awkward.

    The other main issue you will run into when switching DB's is SQL syntax (see the concatenate above). This is not an issue of DBUtils itself (it uses pretty plain SQL), but the programmer must be aware of this issue when passing SQL statements to DBUtils.

    Please note, in the end this is a minor update to code base originally provided by @Erel .

    As it stands (if you are using DBUtils 1.01 unmodified), this DBUtils should be a drop in replacement.

    Attached Files:

  2. Johan Hormaza

    Johan Hormaza Active Member Licensed User

    I get this error
    DropTable3: DROP TABLE IF EXISTS `Students`
    dbutils._droptable (java line: 139)
    java.lang.RuntimeException: Object should first be initialized.
    at anywheresoftware.b4j.objects.SQL.checkNull(SQL.java:131)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:152)
    at b4j.example.dbutils._droptable(dbutils.java:139)
    at b4j.example.main._appstart(main.java:80)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:748)
  3. OliverA

    OliverA Well-Known Member Licensed User

    Looks like your SQL object is not initialized. You need to make a database connection first. The SQL object behind that database connection then needs to be passed on to the DBUtils.DropTable method.

    In the sample, in the Main program module, in Process_Globals, the following SQL object is declared
    Private SQL1 As SQL
    Then in AppStart, you create a database connection with the following line (substitute everything as needed to establish a valid connection to your SQL server)
    If you get a valid connection, SQL1 should be initialized and work just fine with the following code
    DBUtils.DropTable(SQL1, "Students")
    Johan Hormaza likes this.
  4. keirS

    keirS Well-Known Member Licensed User

    Just for info. MySQL can index TEXT columns. You have to specify the length of the index though.
  5. Mashiane

    Mashiane Expert Licensed User

    The new driver to use is : com.mysql.cj.jdbc.Driver with connector, mysql-connector-java-8.0.12.jar

    When working from localhost without SSL, you need to turn it off otherwise a warning will be shown. Example..

    Then this connection string can be passed to your initialize method..
    Last edited: Sep 23, 2018
    Johan Hormaza likes this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice