[Gambas-user] Problem with SQLite3

[Leonardo Miliani]

I don't know if it's me what I'm wrong, or Gambas or SQLite3 but I'm
having an annoying problem with a SQLite3 query.
I have an SQLite3 DB with a table named "pagamenti" (payments). Inside
it I have a list of payments with some fields. Some of them are
"saldato" (payed), "data" (date), "cliente" (customer).
The field "data" is a Date field.

For some reason I have the necessity to extract all the datas that are
still unpayed (I use the field "saldato" as a boolean check to tell me
if the payment has been done or not) and order them by fields "cliente"
and "data".
So I use this piece of code:

Private Function Extract_data(Customer as string, PayDate as date) as
boolean
Dim hResult as Result
Dim hConnection as new Connection

  hConnection.Name = Application.Path & "/archive"
  hConnection.Type = "sqlite3"
  hConnection.Open

  hResult = hConnection.Exec("SELECT * FROM pagamenti WHERE cliente = '"
& Customer & "' AND data = #" & PayDate & "# AND NOT saldato ORDER BY
cliente,data")

  IF hResult.Available = FALSE THEN
    RETURN FALSE
  END IF
......
......
  RETURN TRUE
END


When I call the function I always get FALSE as return value because the
SQLite query doesn't get any match. It seems that the use of the date
value is a bit difficult.
I know (or mayber I thinked to know...) that in SQL the dates must be
put inside 2 chars '#' but this notation seems not to work in
Connection.Exec...
In fact, if I use this SQL query:

"SELECT * FROM pagamenti WHERE cliente = '" & Customer & "' AND NOT
saldato ORDER BY cliente,data"

(just taking away the check on the date) I get results.

-- 
Ciao.
Leo.

Web: www.leonardomiliani.com
E-mail: leonardo at ...1237...
Scegli software opensource - Choose opensource software