B4J Library Build parametized SQL queries for Insert,Update and Delete

This is a small class to easily build SQL queries

declare an instance of the class clBuildSQL and initialize it
declare instance and initialize it:
Private fBuildSQL As clBuildSQL
....

fBuildSQL.initialize

Insert record
Just pass as first parameter the table name and as second parameter a map with pairs keys/values FieldName/value
insert new record:
    Dim s As TSQLQuery=fBuildSQL.insertSQL("t_persons",CreateMap("name":"Alan","age":35))
    fDB.ExecNonQuery2(s.fSQL,s.fValues)
the sql will be "insert into t_persons(name,age) values(?,?)" with parameters array as object("Alan",35)


update record
Just pass as first parameter the table name, as second parameter a map with pairs keys/values FieldName/value and as third parameter a map with pairs keys/values FieldName|Operator/value for the where clause.
update record:
    Dim s As TSQLQuery=fBuildSQL.UpdateSQL("t_persons",CreateMap("age":30),CreateMap("name=":"Mary"))
    fDB.ExecNonQuery2(s.fSQL,s.fValues)
the sql will be "update t_persons set name=?,,age=? where name=?" with parameters array as object(30,"Mary")

Delete record
Just pass as first parameter the table name, and as second parameter a map with pairs keys/values FieldName|Operator/value for the where clause
In the where clause you can combine more criteria
Delete record:
    Dim s As TSQLQuery=fBuildSQL.deleteSQL("t_persons",CreateMap("name=":"Alan","or age>":32))
    fDB.ExecNonQuery2(s.fSQL,s.fValues)
the sql will be "delete from t_persons where name=? or age>?" with parameters array as object("Alan",32)


Don't use space in your table name or column name!


A B4X sample (B4A and B4J) shows the result
 

Attachments

  • B4XBuildSQL.zip
    8.5 KB · Views: 78
Last edited:

Mashiane

Expert
Licensed User
The issue of the space in table name and field names can be sorted by enclosing them inside [] or ``.

You are perhaps making an assumption that deletes, updates work only with = sign in your class. Perhaps add an additional variable that could be a list that receives the operators like >, >= so that a person can be able to 'delete from abc where xxx > 1' and not only 'delete from abc where xxx = 1'
 

spsp

Member
Licensed User
For the spaces, it's only a personal rule: no space in table name and column name, i use underscore (_)
for the update and delete, you are now able to use other operators, see post #1

The issue of the space in table name and field names can be sorted by enclosing them inside [] or ``.

You are perhaps making an assumption that deletes, updates work only with = sign in your class. Perhaps add an additional variable that could be a list that receives the operators like >, >= so that a person can be able to 'delete from abc where xxx > 1' and not only 'delete from abc where xxx = 1'
 
Top