B4J Question Question about parameterizing queries

j_o_h_n

Active Member
Licensed User
I can't seem to figure out how to deal with dates and "in" clauses for parameterized queries.
I'd be grateful if someone could tell me how to do it.
Thanks

Date
I have a date in string format, Postgresql wants that wrapped in apostrophes in order to accept it into a timestamp field

B4X:
Dim completeddate As String = "'" & "2020-03-29 13:12:56" & "'"

'This doesn't work
Main.SQL.ExecNonQuery2("INSERT INTO table1 (completeddate, field2) Values(?,?) ", Array As Object(completeddate, field2))

'This does work but I'd prefer it to have it all parameterized
Main.SQL.ExecNonQuery2( "INSERT INTO table1 (completeddate, field2) Values(" & completeddate & ",?) " ", Array As Object(field2))


In clause


B4X:
dim sID as string = "0, 1, 2"

'This doesn't work     
Dim sQuery As String = "select * from table1 where id in (?)"
Dim rs As ResultSet = Main.SQL.ExecQuery2(sQuery, Array As Object(sID))

'This does work
Dim sQuery As String = "select * from table1 where id in ( " & sID & ")"
Dim rs As ResultSet = Main.SQL.ExecQuery(sQuery)
 

OliverA

Expert
Licensed User
Longtime User
In clause
You'll need a ? for each member in the IN clause. You could dynamically build the query, inclusive necessary question marks and then pass that and an array of parameters (the elements in the IN clause, each individually) to the ExecQuery2 method.
Manually, the correct way for a parameterized query for your example would be:
B4X:
Dim sQuery As String = "select * from table1 where id in (?, ?, ?)"
Dim rs As ResultSet = Main.SQL.ExecQuery2(sQuery, Array As Object("0", "1", "2"))
 
Upvote 0

j_o_h_n

Active Member
Licensed User
Are you sure you need the apostrophes in the parametrized version?
See code smell number 4 - https://www.b4x.com/android/forum/t...ommon-mistakes-and-other-tips.116651/#content.
Thanks Chris. Unfortunately that does not work either.
With or without the apostrophes I get the same error message.
org.postgresql.util.PSQLException: ERROR: column "completeddate" is of type timestamp without time zone but expression is of type character varying

I guess it is no big deal for the user to write a small function to test the date input is clean
Thanks for your help


Edit I saw this post on stackoverflow and I tried to adjust the postgresql driver properties accordingly but it didn't work.
 
Last edited:
Upvote 0

j_o_h_n

Active Member
Licensed User
You'll need a ? for each member in the IN clause. You could dynamically build the query, inclusive necessary question marks and then pass that and an array of parameters (the elements in the IN clause, each individually) to the ExecQuery2 method.
Manually, the correct way for a parameterized query for your example would be:
B4X:
Dim sQuery As String = "select * from table1 where id in (?, ?, ?)"
Dim rs As ResultSet = Main.SQL.ExecQuery2(sQuery, Array As Object("0", "1", "2"))
Thanks! That explains it and that worked (I needed to have numbers in the arrray though rather than strings)
 
Upvote 0

rosippc64a

Active Member
Licensed User
Longtime User
org.postgresql.util.PSQLException: ERROR: column "completeddate" is of type timestamp without time zone but expression is of type character varying
in this case I use this: ::date
My date fields are the keltdat, teljdat and fizdat. The values:
DateTime.Date(keltdat),DateTime.Date(teljdat), DateTime.Date(fizdat)
B4X:
            ao = Array As Object(szrs.GetString("rksh"),b.bsz,b.bsznr,"",DateTime.Date(keltdat),DateTime.Date(teljdat), _
            DateTime.Date(fizdat),.....,elolegin)
                    Codebase.sql.ExecNonQuery2($"INSERT INTO szfej
                    (
                      rksh,bsz,bsznr,hivbsz,keltdat,teljdat,
                      fizdat,.....,elolegin
                    )  
                    VALUES
                    (?,?,?,?,?::date,?::date,
                    ?::date,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                    ?,?,?,?,?,?,?,?,?,?,?::numeric,?::numeric,?::numeric,?::numeric,
                    ?)
                    "$,ao)
 
Upvote 0

j_o_h_n

Active Member
Licensed User
in this case I use this: ::date
My date fields are the keltdat, teljdat and fizdat. The values:
DateTime.Date(keltdat),DateTime.Date(teljdat), DateTime.Date(fizdat)
B4X:
            ao = Array As Object(szrs.GetString("rksh"),b.bsz,b.bsznr,"",DateTime.Date(keltdat),DateTime.Date(teljdat), _
            DateTime.Date(fizdat),.....,elolegin)
                    Codebase.sql.ExecNonQuery2($"INSERT INTO szfej
                    (
                      rksh,bsz,bsznr,hivbsz,keltdat,teljdat,
                      fizdat,.....,elolegin
                    ) 
                    VALUES
                    (?,?,?,?,?::date,?::date,
                    ?::date,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                    ?,?,?,?,?,?,?,?,?,?,?::numeric,?::numeric,?::numeric,?::numeric,
                    ?)
                    "$,ao)
You sir, are a genius!
 
Upvote 0
Top