B4J Question Question about parameterizing queries

j_o_h_n · Apr 2, 2021

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)

Chris2 · Apr 2, 2021

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.

OliverA · Apr 2, 2021

j_o_h_n said:
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"))

j_o_h_n · Apr 2, 2021

Chris2 said:
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.

j_o_h_n · Apr 2, 2021

OliverA said:
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)

rosippc64a · Apr 2, 2021

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)

j_o_h_n · Apr 2, 2021

rosippc64a said:

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!

B4J Question Question about parameterizing queries

j_o_h_n

Active Member

Chris2

Active Member

OliverA

Expert

j_o_h_n

Active Member

j_o_h_n

Active Member

rosippc64a

Active Member

j_o_h_n

Active Member

Similar Threads

B4J Question Question about parameterizing queries

j_o_h_n

Active Member

Chris2

Active Member

OliverA

Expert

j_o_h_n

Active Member

j_o_h_n

Active Member

rosippc64a

Active Member

j_o_h_n

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency