B4J Library [module] (Universal) DBUtils

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:

@ThRuST

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.
 

Attachments

  • DBUtils.zip
    9.1 KB · Views: 761

Johan Hormaza

Well-Known Member
Licensed User
Longtime 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)
 

OliverA

Expert
Licensed User
Longtime 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
B4X:
    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)
B4X:
SQL1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1:3306/somedbschema", "someuser", "somepassword")
If you get a valid connection, SQL1 should be initialized and work just fine with the following code
B4X:
DBUtils.DropTable(SQL1, "Students")
 

keirS

Well-Known Member
Licensed User
Longtime User
Just for info. MySQL can index TEXT columns. You have to specify the length of the index though.
 

Mashiane

Expert
Licensed User
Longtime 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..

B4X:
jdbc:mysql://127.0.0.1:3307/dbName?characterEncoding=utf8&autoReconnect=true&useSSL=false

Then this connection string can be passed to your initialize method..
 
Last edited:
Top