Mar 9, 2025 Replies: 45 MiniORMUtils Version: 6.04 GitHub: https://github.com/pyhoon/MiniORMUtils-B4X This library can be use for creating database tables and performing CRUD operations. It is suitable for B4X apps, non-UI web app or REST API servers. It is useful to return the query results as JSON since the rows are list of maps. Currently it supports SQLite (for B4A, B4i, B4J), MariaDB and MySQL (for B4J only). Project Template: [B4X] [Project Template] MiniORM Examples: Initialize MiniORM B4X: Private DB As MiniORM DB.Initialize Set file name for SQLite B4X: DB.Settings.DBFile = "blog.db" Set ORMSettings for MySQL B4X: DB.Initialize Dim MS As MiniORMSettings MS.Initialize MS.DBType = DB.MYSQL MS.JdbcUrl = "jdbc:mysql://{DbHost}:{DbPort}/{DbName}?characterEncoding=utf8&useSSL=False" MS.Driver = "com.mysql.cj.jdbc.Driver" MS.DBName = "blog" MS.DbHost = "localhost" MS.User = "root" MS.Password = "password" DB.Settings = MS Check if database exist (SQLite) B4X: If Not(DB.Exist) Then LogColor($"${DB.DBType} database not found!"$, COLOR_RED) CreateDatabase End If Create Database B4X: Dim Success As Boolean = DB.CreateSQLite Connect to Database B4X: DB.Open Create Table B4X: DB.Table = "categories" DB.Columns = Array("category_code", "category_name") DB.Create Create Table (with column definitions) B4X: DB.Table = "products" DB.Columns.Add(CreateMap("N": "category_id", "T": DB.INTEGER)) DB.Columns.Add(CreateMap("N": "product_code", "S": 12)) DB.Columns.Add(CreateMap("N": "product_name")) DB.Columns.Add(CreateMap("N": "product_price", "T": DB.DECIMAL, "S": "10,2", "D": 0.0)) DB.Columns.Add(CreateMap("N": "product_image", "T": DB.BLOB)) DB.Foreign = "category_id" DB.References("categories", "id") DB.Create Insert Rows B4X: DB.Columns = Array("category_id", "product_code", "product_name", "product_price") DB.Inserts = Array(2, "T001", "Teddy Bear", 99.9) DB.Inserts = Array(1, "H001", "Hammer", 15.75) DB.Inserts = Array(2, "T002", "Optimus Prime", 1000) Execute NonQuery Batch B4X: Wait For (DB.ExecuteBatchAsync) Complete (Success As Boolean) If Success Then Log("Database is created successfully!") Else Log("Database creation failed!") End If Select All Rows B4X: DB.Table = "categories" DB.Query If DB.Error.IsInitialized Then Log(DB.Error.Message) Else Dim Items As List = DB.Results End If Return single row B4X: DB.Find(3) If DB.Found Then Log(DB.First) End If Update Row B4X: DB.Table = "products" DB.Columns = Array("category_id", "product_code", "product_name", "product_price", "modified_date") DB.Parameters = Array(Category, Code, Name, Price, Modified_Date) DB.Condition = "id = ?" DB.Parameter = 2 Spoiler: Version 1 [B4X] MiniORMUtils - SQL Query Builder Updates Version 2.00 is available https://www.b4x.com/android/forum/threads/b4x-miniormutils.166030/ MiniORMUtils Version: 1.17 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... www.b4x.com Attachments MiniORMUtils.b4xlib MiniORMUtils.b4xlib 13.5 KB · Views: 148 Last edited: Aug 28, 2026 Click to expand...
MiniORMUtils Version: 6.04 GitHub: https://github.com/pyhoon/MiniORMUtils-B4X This library can be use for creating database tables and performing CRUD operations. It is suitable for B4X apps, non-UI web app or REST API servers. It is useful to return the query results as JSON since the rows are list of maps. Currently it supports SQLite (for B4A, B4i, B4J), MariaDB and MySQL (for B4J only). Project Template: [B4X] [Project Template] MiniORM Examples: Initialize MiniORM B4X: Private DB As MiniORM DB.Initialize Set file name for SQLite B4X: DB.Settings.DBFile = "blog.db" Set ORMSettings for MySQL B4X: DB.Initialize Dim MS As MiniORMSettings MS.Initialize MS.DBType = DB.MYSQL MS.JdbcUrl = "jdbc:mysql://{DbHost}:{DbPort}/{DbName}?characterEncoding=utf8&useSSL=False" MS.Driver = "com.mysql.cj.jdbc.Driver" MS.DBName = "blog" MS.DbHost = "localhost" MS.User = "root" MS.Password = "password" DB.Settings = MS Check if database exist (SQLite) B4X: If Not(DB.Exist) Then LogColor($"${DB.DBType} database not found!"$, COLOR_RED) CreateDatabase End If Create Database B4X: Dim Success As Boolean = DB.CreateSQLite Connect to Database B4X: DB.Open Create Table B4X: DB.Table = "categories" DB.Columns = Array("category_code", "category_name") DB.Create Create Table (with column definitions) B4X: DB.Table = "products" DB.Columns.Add(CreateMap("N": "category_id", "T": DB.INTEGER)) DB.Columns.Add(CreateMap("N": "product_code", "S": 12)) DB.Columns.Add(CreateMap("N": "product_name")) DB.Columns.Add(CreateMap("N": "product_price", "T": DB.DECIMAL, "S": "10,2", "D": 0.0)) DB.Columns.Add(CreateMap("N": "product_image", "T": DB.BLOB)) DB.Foreign = "category_id" DB.References("categories", "id") DB.Create Insert Rows B4X: DB.Columns = Array("category_id", "product_code", "product_name", "product_price") DB.Inserts = Array(2, "T001", "Teddy Bear", 99.9) DB.Inserts = Array(1, "H001", "Hammer", 15.75) DB.Inserts = Array(2, "T002", "Optimus Prime", 1000) Execute NonQuery Batch B4X: Wait For (DB.ExecuteBatchAsync) Complete (Success As Boolean) If Success Then Log("Database is created successfully!") Else Log("Database creation failed!") End If Select All Rows B4X: DB.Table = "categories" DB.Query If DB.Error.IsInitialized Then Log(DB.Error.Message) Else Dim Items As List = DB.Results End If Return single row B4X: DB.Find(3) If DB.Found Then Log(DB.First) End If Update Row B4X: DB.Table = "products" DB.Columns = Array("category_id", "product_code", "product_name", "product_price", "modified_date") DB.Parameters = Array(Category, Code, Name, Price, Modified_Date) DB.Condition = "id = ?" DB.Parameter = 2 Spoiler: Version 1 [B4X] MiniORMUtils - SQL Query Builder Updates Version 2.00 is available https://www.b4x.com/android/forum/threads/b4x-miniormutils.166030/ MiniORMUtils Version: 1.17 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... www.b4x.com
aeric Expert Licensed User Longtime User Mar 9, 2025 #2 Migration from version 1.x DatabaseConnector is now ORMConnector ORMConnector requires a ConnectionInfo object to pass as a parameter for initialization. B4X: Dim Info As ConnectionInfo Info.Initialize Info.DBType = "SQLite" Info.DBFile = "test.db" Then we can initialize ORMConnector as follow: B4X: Dim Con As ORMConnector Con.Initialize(Info) To initialize MiniORM object, we need to specify the database type as first parameter and an SQL object as second parameter. We can use DBType and DBOpen from ORMConnector object. B4X: Dim DB As MiniORM DB.Initialize(DBType, DBOpen) Some methods name have been renamed. CreateORMColumn and CreateORMColumn2 renamed to CreateColumn and CreateColumn2. CreateORMFilter renamed to CreateFilter. CreateORMJoin renamed to CreateJoin. setRawSQL changed to setStatement ToString changed to getStatement Need to specify journal mode for SQLite when use in server. Default is "DELETE". B4X: Info.JournalMode = "WAL" When using MySQL, connect to main schema using InitSchema before calling DBExist2 or DBCreate. AddQuery replaced by AddNonQueryToBatch BlnAddAfterCreate and BlnAddAfterInsert replaced by setQueryAddToBatch BlnExecuteAfterCreate and BlnExecuteAfterInsert replaced by setQueryExecute Last edited: Apr 23, 2025
Migration from version 1.x DatabaseConnector is now ORMConnector ORMConnector requires a ConnectionInfo object to pass as a parameter for initialization. B4X: Dim Info As ConnectionInfo Info.Initialize Info.DBType = "SQLite" Info.DBFile = "test.db" Then we can initialize ORMConnector as follow: B4X: Dim Con As ORMConnector Con.Initialize(Info) To initialize MiniORM object, we need to specify the database type as first parameter and an SQL object as second parameter. We can use DBType and DBOpen from ORMConnector object. B4X: Dim DB As MiniORM DB.Initialize(DBType, DBOpen) Some methods name have been renamed. CreateORMColumn and CreateORMColumn2 renamed to CreateColumn and CreateColumn2. CreateORMFilter renamed to CreateFilter. CreateORMJoin renamed to CreateJoin. setRawSQL changed to setStatement ToString changed to getStatement Need to specify journal mode for SQLite when use in server. Default is "DELETE". B4X: Info.JournalMode = "WAL" When using MySQL, connect to main schema using InitSchema before calling DBExist2 or DBCreate. AddQuery replaced by AddNonQueryToBatch BlnAddAfterCreate and BlnAddAfterInsert replaced by setQueryAddToBatch BlnExecuteAfterCreate and BlnExecuteAfterInsert replaced by setQueryExecute
aeric Expert Licensed User Longtime User Mar 10, 2025 #3 Version: 2.10 Size: 14KB What's New Fixed bug in setParameters sub for B4J Replaced Where3 sub with WhereParam and WhereParams Show logs when ShowExtraLogs = True with LogQuery, LogQuery2 and LogQueryWithArg
Version: 2.10 Size: 14KB What's New Fixed bug in setParameters sub for B4J Replaced Where3 sub with WhereParam and WhereParams Show logs when ShowExtraLogs = True with LogQuery, LogQuery2 and LogQueryWithArg
aeric Expert Licensed User Longtime User Mar 10, 2025 #4 Version: 2.11 Size: 14KB What's New Update WhereParam sub to accept mCondition instead of mColumn as first parameter to avoid confusion with WhereParams
Version: 2.11 Size: 14KB What's New Update WhereParam sub to accept mCondition instead of mColumn as first parameter to avoid confusion with WhereParams
aeric Expert Licensed User Longtime User Mar 10, 2025 #5 Version: 2.20 Size: 14KB What's New Fixed bugs for B4A and B4i
aeric Expert Licensed User Longtime User Mar 10, 2025 #6 Version: 2.21 Size: 14KB What's New Updated AddParameters sub Updated ORMConnector Initialize sub for B4A and B4i Removed ByteConverter from manifest for B4A and B4i Removed Merge sub Fixed Find sub Fixed Find2 sub Fixed setId sub Updated Query sub Added setShowDBUtilsJson sub for B4A and B4i
Version: 2.21 Size: 14KB What's New Updated AddParameters sub Updated ORMConnector Initialize sub for B4A and B4i Removed ByteConverter from manifest for B4A and B4i Removed Merge sub Fixed Find sub Fixed Find2 sub Fixed setId sub Updated Query sub Added setShowDBUtilsJson sub for B4A and B4i
aeric Expert Licensed User Longtime User Mar 13, 2025 #7 Version: 2.30 Size: 14KB What's New Added View property Added ResetParameters sub Added IfNull sub Added Create2 sub Added setSelect2 sub Added SelectFromView sub Added SelectFromTable sub Added ViewExists sub Added ViewExists2 sub Updated setSelect sub Thanks to Support Ticketing System. aeric said: Many new functions in DatabaseBuilder class such as IfNull, Create2, setSelect2, SelectFromView, ViewExists and ViewExists2. Click to expand...
Version: 2.30 Size: 14KB What's New Added View property Added ResetParameters sub Added IfNull sub Added Create2 sub Added setSelect2 sub Added SelectFromView sub Added SelectFromTable sub Added ViewExists sub Added ViewExists2 sub Updated setSelect sub Thanks to Support Ticketing System. aeric said: Many new functions in DatabaseBuilder class such as IfNull, Create2, setSelect2, SelectFromView, ViewExists and ViewExists2. Click to expand...
aeric Expert Licensed User Longtime User Mar 18, 2025 #8 Version: 2.40 Size: 14KB What's New Added Save3 sub Updated Create sub Updated ORMConnector class
Mashiane Expert Licensed User Longtime User Mar 19, 2025 #9 Hi, a kind request In the readme, please include examples of doing executing conditions for updates & deletes 1. Update Where e.g. UPDATE Users Where ... 2. Delete Where e.g. DELETE Users Where... Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution? Thanks in advance. PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need?
Hi, a kind request In the readme, please include examples of doing executing conditions for updates & deletes 1. Update Where e.g. UPDATE Users Where ... 2. Delete Where e.g. DELETE Users Where... Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution? Thanks in advance. PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need?
aeric Expert Licensed User Longtime User Mar 19, 2025 #10 Mashiane said: Hi, a kind request In the readme, please include examples of doing executing conditions for updates & deletes 1. Update Where e.g. UPDATE Users Where ... 2. Delete Where e.g. DELETE Users Where... Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution? Thanks in advance. PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need? Click to expand... Thanks for your suggestion. I will try to add more tutorials when I am free. For any new questions, please post new threads.
Mashiane said: Hi, a kind request In the readme, please include examples of doing executing conditions for updates & deletes 1. Update Where e.g. UPDATE Users Where ... 2. Delete Where e.g. DELETE Users Where... Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution? Thanks in advance. PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need? Click to expand... Thanks for your suggestion. I will try to add more tutorials when I am free. For any new questions, please post new threads.
aeric Expert Licensed User Longtime User Mar 20, 2025 #11 Version: 2.50 Size: 14KB What's New Changed MiniORM class Initialize sub parameters switch order, SQL object is now second parameter which can be Null Renamed Engine or DBEngine property to DBType Added Error property Updated ORMConnector class with DBType and Error property This is a breaking update. B4X: Dim DB As MiniORM DB.Initialize(DBType, DBOpen)
Version: 2.50 Size: 14KB What's New Changed MiniORM class Initialize sub parameters switch order, SQL object is now second parameter which can be Null Renamed Engine or DBEngine property to DBType Added Error property Updated ORMConnector class with DBType and Error property This is a breaking update. B4X: Dim DB As MiniORM DB.Initialize(DBType, DBOpen)
aeric Expert Licensed User Longtime User Apr 23, 2025 #12 Version: 2.51 Size: 14KB What's New updated setView
amorosik Expert Licensed User Longtime User Apr 23, 2025 #13 The project seems very interesting But it is really difficult to do something without following some working examples, at least for me it is impossible I have tried several times to spend time to understand how it works, but so far I have not succeeded It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it
The project seems very interesting But it is really difficult to do something without following some working examples, at least for me it is impossible I have tried several times to spend time to understand how it works, but so far I have not succeeded It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it
aeric Expert Licensed User Longtime User Apr 23, 2025 #14 amorosik said: The project seems very interesting But it is really difficult to do something without following some working examples, at least for me it is impossible I have tried several times to spend time to understand how it works, but so far I have not succeeded It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it Click to expand... Check the B4X project template (B4A, B4i, B4J) [B4X] [Project Template] MiniORM There is also tutorial for using this library in server solution [Tutorial] Using MiniORMUtils in Web API Server v3 Edit: Some examples added to first post Last edited: Apr 23, 2025
amorosik said: The project seems very interesting But it is really difficult to do something without following some working examples, at least for me it is impossible I have tried several times to spend time to understand how it works, but so far I have not succeeded It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it Click to expand... Check the B4X project template (B4A, B4i, B4J) [B4X] [Project Template] MiniORM There is also tutorial for using this library in server solution [Tutorial] Using MiniORMUtils in Web API Server v3 Edit: Some examples added to first post
aeric Expert Licensed User Longtime User Apr 23, 2025 #15 Version: 2.60 Size: 14KB What's New Fix unsupported function Initialized in B4i Update DBOpen sub
aeric Expert Licensed User Longtime User Apr 24, 2025 #16 Version: 2.61 Size: 14KB What's New Fix Close RS in Query sub (B4i) Version: 2.62 Size: 14KB What's New Fix Close RS in Query sub (B4A) Last edited: Apr 24, 2025
Version: 2.61 Size: 14KB What's New Fix Close RS in Query sub (B4i) Version: 2.62 Size: 14KB What's New Fix Close RS in Query sub (B4A)
aeric Expert Licensed User Longtime User May 14, 2025 #17 Version: 2.63 Size: 14KB What's New Fix IfNull sub when setting default value and empty alias name
aeric Expert Licensed User Longtime User May 22, 2025 #18 I hope this idea will work and solve a decade problem for generating ordered keys JSON
aeric Expert Licensed User Longtime User May 23, 2025 #19 aeric said: I hope this idea will work and solve a decade problem for generating ordered keys JSON View attachment 164253 Click to expand... Unfortunately, I got a better idea from ChatGPT but I wrote most of the code.
aeric said: I hope this idea will work and solve a decade problem for generating ordered keys JSON View attachment 164253 Click to expand... Unfortunately, I got a better idea from ChatGPT but I wrote most of the code.
aeric Expert Licensed User Longtime User May 23, 2025 #20 Version: 3.00 Size: 14KB This is a major update. This may break compatibility with older app (especially not tested for B4A and B4i which will be fix in next version if there is any error). What's New Big update on Query sub ORMTable type Added Results2 property with "__order" key -> helps for generating ordered key JSON Added Columns property as List (vs map as in ORMResults type) Added Rows property (same as ORMResults type Rows) Removed Row property Added DBObject use in some cases instead of DBTable and DBView Added Reset2 sub Added Results2 sub returns the property from ORMTable Added SelectAllFromDBObject sub Added SelectFromTableOrView sub Added JournalMode sub Added ExecNonQuery sub (Private) Added ExecQuery sub (Private) returns a ResultSet Execute sub calls ExecNonQuery sub Updated Create sub Updated Insert sub Updated Save sub Updated Save3 sub Updated Delete sub Updated Destroy sub Updated SoftDelete sub Updated ViewExists sub Updated ViewExists2 sub Updated setId sub Removed conditional symbol WAL Removed SelectFromTable sub Removed SelectFromView sub Select2 sub removed - use Select instead Some internal variables renamed Last edited: May 24, 2025
Version: 3.00 Size: 14KB This is a major update. This may break compatibility with older app (especially not tested for B4A and B4i which will be fix in next version if there is any error). What's New Big update on Query sub ORMTable type Added Results2 property with "__order" key -> helps for generating ordered key JSON Added Columns property as List (vs map as in ORMResults type) Added Rows property (same as ORMResults type Rows) Removed Row property Added DBObject use in some cases instead of DBTable and DBView Added Reset2 sub Added Results2 sub returns the property from ORMTable Added SelectAllFromDBObject sub Added SelectFromTableOrView sub Added JournalMode sub Added ExecNonQuery sub (Private) Added ExecQuery sub (Private) returns a ResultSet Execute sub calls ExecNonQuery sub Updated Create sub Updated Insert sub Updated Save sub Updated Save3 sub Updated Delete sub Updated Destroy sub Updated SoftDelete sub Updated ViewExists sub Updated ViewExists2 sub Updated setId sub Removed conditional symbol WAL Removed SelectFromTable sub Removed SelectFromView sub Select2 sub removed - use Select instead Some internal variables renamed
