B4J Code Snippet [B4X] MiniORMUtils - SQL Query Builder

• (new) Query sub - if BlnUseTimestampsAsTicks = True returns Date Time columns as Ticks otherwise return as String
• (new) Insert sub - if BlnUseTimestampsAsTicks = True and created_date value is not specified, value defaulted to current date time
• (new) Save sub - if BlnUseTimestampsAsTicks = True and created_date value is not specified, value defaulted to current date time for insert
• (new) Save sub - if BlnUpdateModifiedDate = True and modified_date value is not specified, value defaulted to current date time for update
• (new) Columns for User Id audit (created, modified, deleted) can be added independent on BlnUseTimestamps
• (update) Reset sub does not reset value for Boolean properties to False

• (new) DatabaseConnector included
• (new) Support B4J and (B4A and B4i only SQLite)

• (fix) jdbcURL error for B4A and B4i

• (fix) SQLite database not created (B4J)

• (fix) MaxPoolSize for non_UI app in DataConnector class

• (fix) missing comma in Save sub when appending modified_date to query in MiniORM class

• (new) getFirstId method
• (fix) get the correct id of updated row in Save sub

• (new) support for B4i
• (new) getFound property
• (new) setShowExtraLogs property
• (new) SortByLastId sub
• (fix) Query sub - Use DBUtils.ExecuteJSON instead of DBUtils.ExecuteMap and DBUtils.ExecuteMemoryTable which causing wrong order of map key-value items in B4i
• (fix) getScalar sub
• (fix) DatabaseConnector class: clean up conditional symbols for B4J, server, B4A and B4i
• (fix) bug: parameter Nullable changed to AllowNull due to compilation error in B4i
• (fix) bug: First sub returns a default map if ORMTable is not initialized
• (fix) bug: AddQuery sub creating a wrong copy of second last item due to passing List by reference
• (fix) bug: Save sub return map of Insert or Update
• (fix) Snippets: WebApi snippets updated, clean up some conditional symbols
• (fix) Snippets: change default #Version = dev
• (fix) Snippets: CreateDBConnection added
• (remove) AddQuery2 sub - unused
• (remove) CopyObject sub - independent from B4XSerializator (RandomAccessFile library)

• (fix) B4i app is now tested and working
• (fix) new app icon
• (fix) Pass String array as Parameters to support B4i/B4A
• (fix) Bug cannot add item when Category table is empty
• (fix) Check entered Product Price is a number
• (fix) some UI changes

• (new) SelectOnly sub
• (new) LogQuery sub
• (fix) bug: setRawSQL sub DBParameters is not initialized

DB.Table = "tbl_invoices"
DB.Columns = Columns
DB.Parameters = Values
DB.Save

Dim ProcessedMap As Map = DB.SelectOnly(Array As String("invoice_identifier", "invoice_type", "supplier_tin", "buyer_tin", "invoice_submitted_type", "created_date"))
HRM.ResponseCode = 201
HRM.ResponseObject = ProcessedMap
HRM.ResponseMessage = "Invoice has been processed"

DB.Table = "tbl_statuses"
DB.Where = Array("status_name = ?", "id <> ?")
DB.Parameters = Array As String(data.Get("status_name"), id)
DB.Query
DB.LogQuery

• (update) DatabaseConnector
  • (remove) DBFile
  • (add) DBHost
  • (add) DBPort
  • (clean) Initialize sub
    • JdbcUrl using placeholder
  • (update) DBCreate sub
    • return Boolean (Resumable sub) instead of SQL object
    • JdbcUrl using placeholder
  • (clean) DBExist sub
    • JdbcUrl using placeholder
  • (remove) DBExist2 sub
  • (clean) DBOpen sub
  • (clean) GetDate sub
  • (clean) GetDateTime sub
  • (clean) getLastInsertIDQuery sub

• (update) Snippets

1. Remove default assignment for INTEGER, DECIMAL and VARCHAR
2. Add TIMESTAMP for column type
3. Add DBUniqueKey
4. Add DBConstraint
5. Add DisableAutoIncrementId
6. Add ExecuteAfterCreate
7. Add ExecuteAfterInsert
8. Add Collation in ORMColumn
9. Add Find2 sub
10. Update Create sub
11. Add Unique sub
12. Update Execute sub
13. Update ExecuteBatch sub
14. Update Query sub
15. Update Insert sub
16. Update Save sub, skipping appending "= ?" if "=" already added
17. Remove localtime in sqlite
18. DBSaveStatement for showing original statement during LogQuery
19. Add ParametersCount
20. Add TableExists2 sub
21. Update CreateORMColumn
22. Update CreateORMColumn2
23. Remove redundant variables in DatabaseConnector
24. Update DBCreate only support SQLite
25. Add DBCreateSQLite to create db in WAL journal mode
26. Add DBCreateMySQL for MySQL
27. Update DBExist only support SQLite
28. Add DBExists2 for MySQL
29. Update DBOpen
30. Add DBOpen2
31. Remove H2 support
32. Update GetDate
33. Add GetDate2
34. Update GetDateTime
35. Add GetDateTime2

1. Update DatabaseConnector Initialize sub not to replace DbName in Conn.JdbcUrl
2. Update DatabaseConnector Initialize sub to replace DbFile in Conn.JdbcUrl
3. Update replace DbName in DBOpen and DBOpen2 for MySQL
4. Update not to close SQL object in GetDate, GetDate2, GetDateTime, GetDateTime2 subs
5. Add AutoIncrement in Create sub
6. Remove Int size for MySQL column in Create sub
7. Update setting Primary key when DisableAutoIncrementId = False in Create sub
8. Remove Logs for parameters in Create sub
9. Add new "column++" for increment 1 update query in Save sub

Xfood said:

hi aeric
, do you think it could be possible to integrate the possibility of managing a remote mysql db with php, for support such as altervista, aruba? maybe adding to this a small MiniORMUtils.php exchange file to upload to the altervista or aruba server?

Click to expand...

Xfood said:

no, sorry, maybe I can't explain, but if you have a mysql db on altervista or on aruba, to read and write data from b4x more or less similar techniques are used

B4A Altervista mySql PHP

Ciao A tutti, ho attivato un account su altervista, creata un db di esempio, ma adesso mi piacerebbe potermi connette da un'app B4A, qualcuno mi puo aiutare a configurare un file php che mi permetta tramite parametri di inserire,ricercare,modificare i record della tabella. Grazie mille a tutti

www.b4x.com

It would be nice to be able to adapt yours to these specs by now

Click to expand...