B4J Question Question about parameterizing queries

[j_o_h_n]

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

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

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]

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]

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:

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]

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]

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:

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]

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)

            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]

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)

            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!