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)
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"))
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"))
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)
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)