B4J Library [B4X] MiniORMUtils

MiniORMUtils
Version: 3.70

This library can be use for creating db schema and performing CRUD operations.
It is suitable for Web API Template or any database system.
Currently it supports SQLite (for B4A, B4i, B4J), MariaDB and MySQL (for B4J only).

Visor


Project Template:
[B4X] [Project Template] MiniORM

Examples:
Create ConnectionInfo object
B4X:
Dim info As ConnectionInfo
info.Initialize
info.DBType = "SQLite"
info.DBFile = "data.db"

Create ORMConnector object
B4X:
Dim conn As ORMConnector
conn.Initialize(info)

Check if database exist
B4X:
Dim DBFound As Boolean = conn.DBExist
If DBFound = False Then
    LogColor($"${conn.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

Create database
B4X:
Private Sub CreateDatabase
    Dim Success As Boolean = conn.DBCreate
    If Success = False Then
        Log("Database creation failed!")
        Return
    End If
    ' The rest of code for creating tables
End Sub

Initialize MiniORM object
B4X:
Dim DB As MiniORM
DB.Initialize(DBType, DBOpen)
DB.QueryAddToBatch = True

Create table
B4X:
DB.Table = "tbl_category"
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "category_name")))
DB.Create

Insert rows
B4X:
DB.Columns = Array("category_name")
DB.Insert2(Array("Hardwares"))
DB.Insert2(Array("Toys"))

Execute NonQuery Batch
B4X:
Wait For (DB.ExecuteBatch) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
End If
DB.Close

Select All Rows
B4X:
DB.Table = "tbl_category"
DB.Query
Dim Items As List = DB.Results

Update row
B4X:
DB.Table = "tbl_products"
DB.Columns = Array("category_id", "product_code", "product_name", "product_price")
DB.Id = 2
DB.Save2(Array(Category_Id, Product_Code, Product_Name, Product_Price))

More examples on GitHub README page
https://github.com/pyhoon/MiniORMUtils-B4X

 

Attachments

  • MiniORMUtils.b4xlib
    13.8 KB · Views: 12
Last edited:

aeric

Expert
Licensed User
Longtime User
Testing this version 3 on MiniORM B4X template but getting error when creating database using ExecuteBatch.
Not sure why it used to work but not now.
I will use a different approach to solve this issue instead of troubleshooting the root cause.
 

aeric

Expert
Licensed User
Longtime User
Testing this version 3 on MiniORM B4X template but getting error when creating database using ExecuteBatch.
Not sure why it used to work but not now.
I will use a different approach to solve this issue instead of troubleshooting the root cause.

Found the issue.
I can't use DBParameters straight away. This can cause an issue of pass by reference.

B4X:
Public Sub AddNonQueryToBatch
    Dim paramsize As Int = ParametersCount
    Dim Args(paramsize) As Object
    Dim i As Int
    For Each Param In DBParameters
        Args(i) = Param
        i = i + 1
    Next
    DBSQL.AddNonQueryToBatch(DBStatement, Args)
    'DBSQL.AddNonQueryToBatch(DBStatement, DBParameters) ' Wrong
End Sub
 

aeric

Expert
Licensed User
Longtime User
Have you ever working with SQLite BLOB in B4i, B4A or B4J?

B4i-Blob-JSON.png

Next version will support BLOB.
 

aeric

Expert
Licensed User
Longtime User
Version: 3.30
Size: 14KB

Another major update!

What's New

1. BLOB column is now supported
2. ColumnsType property is added as Map of column name (or alias) as key and column type as value.
3. Specifying the ColumnsType is highly recommended especially when working with BLOB. It will fail in B4A if attempt to convert BLOB to String.
4. Parameters variable is now array of Object. It is no longer a List in B4J nor array of String in B4A and B4i.
5. Most subs are affected by #4 and updated
6. Query sub updated
7. DBUtils and JSON libraries are no longer required for B4A and B4i. These libraries have been removed in manifest.txt
8. First property is not returning a default Map
9. Execute2 sub added
 

aeric

Expert
Licensed User
Longtime User
Version: 3.40

What's New
  1. MariaDB is supported
  2. BLOB column is default to mediumblob for MariaDB and MySQL
  3. Update ExecQuery sub
  4. Update code example in B4XMainPage
  5. Added example for testing MariaDB
  6. Added InitSchema2
  7. Added LogQuery3 sub to print out DBStatements and parameters in DBBatch
  8. ShowExtraLogs in ExecuteBatch using LogQuery3
  9. Update README.md
  10. Change comment links to #Macro tags
 

aeric

Expert
Licensed User
Longtime User
Version: 3.50

What's New

  1. Added getFirst2 sub As Map
  2. Updated Query sub
 

aeric

Expert
Licensed User
Longtime User
Version: 3.60

What's New
  1. Changed Error property from String type to Exception type
    • You can now check Error with
      B4X:
      DB.Error.IsInitialized
    • and then get the description using
      B4X:
      DB.Error.Message
  2. ExecQuery sub has added with Try-Catch
  3. ExecNonQuery sub has added with Try-Catch
  4. Added #Macro tags
    • #Macro: Title, Release, ide://run?File=%WINDIR%\SysWOW64\explorer.exe&Args=%PROJECT%\..\..\release
    • #Macro: Title, Sync, ide://run?File=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
    • #Macro: Title, Publish, ide://run?File=%JAVABIN%\jar.exe&WorkingDirectory=..\..\..\release&Args=-cMf&Args=%ADDITIONAL%\..\B4X\%PROJECT_NAME%.b4xlib&Args=*
 

LucaMs

Expert
Licensed User
Longtime User
More examples on GitHub README page
Hi @aeric,

I was wondering: "More examples," but where's an example here?
Then I downloaded the library and the project template and realized the template itself "is" the example.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

Two more little things.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Finally, a superfluous suggestion because it's obvious you're an expert:
B4X:
'con.DBType = "MySQL"
Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants (Erel, are you reading this? Why don't you implement Enums? :mad: 😁)
 

aeric

Expert
Licensed User
Longtime User
Hi @aeric,

I was wondering: "More examples," but where's an example here?
Then I downloaded the library and the project template and realized the template itself "is" the example.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

Two more little things.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Finally, a superfluous suggestion because it's obvious you're an expert:
B4X:
'con.DBType = "MySQL"
Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants (Erel, are you reading this? Why don't you implement Enums? :mad: 😁)
I am happy to see questions! :)
Let me answer them one by one.

I was wondering: "More examples," but where's an example here?
There are actually indeed more examples on GitHub Readme.
The extra examples are:
  1. Soft delete row
  2. Permanent delete row
  3. Batch delete rows
  4. Return number of rows in query results
  5. Return single row
  6. Return multiple rows
  7. Join tables
Then I downloaded the library and the project template and realized the template itself "is" the example.
Yes! You are right.
This template [B4X] [Project Template] MiniORM is a complete working project.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
Yes, because I think it can be a good starter project to extend further for many more purposes. e.g as a POC.

it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
My concern is developers new to MiniORM will face difficulty to understand the concept.
The project template can let them see an immediate result without starting to read the documentation. It is almost zero configuration.
In fact, my real world projects are much more complicated.

Another advantage of project template is you can use any name for the project and usually created in a frequently use development path (more organized).

There are more reasons and benefits of project template instead of a "zipped" project file but I stopped here.

By the way, the main purpose of this project template is to showcase one of the uses of this library; for creating B4X cross platforms client apps (B4A, B4i, B4J).
Whilst MiniORMUtils can be use for UI apps for B4X, it is also part of the dependencies of Pakai framework which is a non-ui web server solution.

MiniORMUtils has a great "history".
Actually there is a more powerful library (now call CapORM) which is not generally available.
It supports wider database type i.e MS SQL Server, PostgreSQL and Firebird instead of only SQLite and MySQL/MariaDB
MiniORM is just a sub library.

The project template is guaranteed in high quality, well tested and clean of redundant files.
The file size is very small.
If you want, you can modify the template or remove the parts you think you don't want.
Then create your own version. The freedom is on your hand.

This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.
I don't agree it is a mistake.
Project Template can also be use as DRY principle (Don't Repeat Yourself).
It is a good way to start a project from a clean but included with features that are ready to be used.
My Pakai Server template is the best example I can think of.
Everything is ready to run. I can extend the generated boilerplate to many types of server apps.
You help other developers saving their time looking for other piece of puzzle.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?
Mentioned above, Pakai Server is the best mate for MiniORMUtils. You can just run it with SQLite backend. Zero configuration.
Another example is User Login Server but it is based on older version of libraries.

Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants
The constants are already existed in MiniORM class.
B4X:
Public Const MYSQL As String = "MYSQL"
Public Const SQLITE As String = "SQLITE"
Public Const MARIADB As String = "MARIADB"

For other questions, I welcome you to start a new thread.
 

aeric

Expert
Licensed User
Longtime User
Version: 3.70

What's New
  1. Handle error when ExecQuery failed in Query sub
  2. Pass Exception to Error property in subs:
    • DBCreate
    • InitPool
    • InitSchema
    • DBExist2
    • DBOpen2
    • GetDate
    • GetDate2
    • GetDateTime
    • GetDateTime2
  3. Add #Macro tag in B4XMainPage:
    • #Macro: Title, Copy, ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=copy&Args=%PROJECT%\..\*ORM*.bas&Args=%PROJECT%\..\..\release\
 
Last edited:
Top