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