SQLite SELECT Syntax

Databases
1

I am fairly new to Xojo and have a couple of problems with retrieving data from an SQLite database. The database is a simple one table affair with fields for first, last and full names and a primary key index; I’m using it purely as a trial for functionality of SQL commands.

The first issue I have involves retrieving data from a record using SELECT and WHERE and the first (or last or full) name. The syntax used is -

Dim rs as RecordSet
rs = db.SQLSelect("SELECT * FROM Names WHERE First = ‘Fred’ ")

If the record has been inserted manually using a database editor such as “DB Browser for SQLite”, retrieval is successful; if the record has been created by means of code using the INSERT INTO command, retrieval fails - no error message, it just doesn’t work. I notice also that if one looks at the records with the browser, those fields which have been inserted by code means are indented by a one character space compared with those created manually . I’m not sure whether this is significant.

The other issue concerns retrieving data where the WHERE criterion is supplied by means of a Text Field using the syntax -

Dim rs as RecordSet
rs = db.SQLSelect(“SELECT * FROM TableName WHERE fieldname =” + TFName.Text). This works fine with numeric fields but with text fields it stubbornly refuses to return the requisite data.
I’m sure the answer is perfectly simple, but so far has has completely eluded me. I really would appreciate some advice.

2 
rs = db.SQLSelect("SELECT * FROM TableName WHERE fieldname ='" + TFName.Text + "'")
3

Yes, the space is significant. You need to examine your code for inserting this data - you obviously have an unintentional space in there.

Nedi gave you the correct form for your second issue, BUT you SHOULD NOT use this as it will expose you to SQL injection. You should use Prepared Statements whenever you are dealing with values that a user has entered. Search on SQL injection if you’re not familiar with the subject.

4

I’m work only with database ( ERP software ) and this is a little sql guide :

Numeric number integer

sql = "SELECT field1, field2, field3, numericfield from table1 where numericfield = " + CType(txtfield.text, Integer) + ";"
Numeric number decimal, double o currency values

sql = "SELECT field1, field2, field3, numericfield from table1 where numericfield = " + CType(txtfield.text, double) + ";" or

sql = "SELECT field1, field2, field3, numericfield from table1 where numericfield = " + CType(txtfield.text, currency) + ";"
Date Value

sql = "SELECT field1, field2, field3, datefield from table1 where datefield = '" + txtdatefield.text + "';"
// date convert
If U prefer, can create a function to parse date in locale notation

Public Function isValidDate(strDate as String) as Boolean
// verify if a string are valid date 
  
  if len(strDate) < 10 then
    return false
  else
    Dim mDate as Date
    if ParseDate(strDate, mDate) then
      return true
    else
      return false
    end if
  end if
  
End Function

This other function are for transform text in date value

Public Function StringToDate(extends s as String) as Date
  Dim mdate as Date
  if ParseDate(s, mDate) then
    return mdate
  end if
  
End Function

use :

dim datenow as new date() datenow = text1.text.stringtodate

Other query facilities on Mysql, Postgree or Sqlite

record between two dates, or two number or two text

Text :

Select field1, field2, field3string from datatable where field3strin between '" + text1.text + "' AND '" + text2.text + "';"
Number

Select field1, field2number, field3 from datatable where field2number between " + CType(txtfield1.text, double) + " AND " +  CType(txtfield2.text, double) + ";"

Data

Select field1, filed2date, field3 from datatable where field2date between '" + txtfield1.text.stringtodate + "' AND '" + txtfield2.text.stringtodate + "';"