OpenEuphoria

1. EuSQL 'EQUALTO' syntax error

• Posted by ghaberek (admin) Mar 10, 2012
• 1175 views

Matt,

I'm not sure what gives here. Can we not compare fields to an empty string? Is this a bug or did I miss something?

Quick example program:

include "eusql.e" 
 
constant temp_db = "temp.edb" 
object void, query, result 
 
void = create_db( temp_db ) 
void = run_sql( "CREATE TABLE people id AS AUTONUMBER, name AS TEXT, address AS TEXT, city AS TEXT, state AS TEXT, country AS TEXT" ) 
query = parse_sql( "INSERT INTO people name, address, city, state, country VALUES( [@name], [@address], [@city], [@state], [@country] )" ) 
 
set_parameter( "@name", "Greg Haberek" ) 
set_parameter( "@address", "123 Super Secret" ) 
set_parameter( "@city", "Anywhere" ) 
set_parameter( "@state", "XY" ) 
set_parameter( "@country", "USA" ) 
void = run_query( query ) 
 
set_parameter( "@name", "Matthew Lewis" ) 
set_parameter( "@address", "" ) 
set_parameter( "@city", "" ) 
set_parameter( "@state", "" ) 
set_parameter( "@country", "" ) 
void = run_query( query ) 
 
result = run_sql( sprintf("SELECT id FROM people WHERE name = '%s' AND address = '%s'", {"Greg Haberek", "123 Super Secret"}) ) 
if atom( result ) then 
    puts( 2, get_sql_err(result) ) 
    abort( 1 ) 
end if 
 
result = run_sql( sprintf("SELECT id FROM people WHERE name = '%s' AND address = '%s'", {"Matthew Lewis", ""}) ) 
if atom( result ) then 
    puts( 2, get_sql_err(result) ) 
    abort( 1 ) 
end if 
 
close_db( temp_db ) 

Output:

EuSQL Error: ERROR IN 'WHERE' CLAUSE 
Error in clause: 'EQUALTO' syntax error 
NAME = "Matthew Lewis" AND ADDRESS = "" 

-Greg

3. Re: EuSQL 'EQUALTO' syntax error

• Posted by ghaberek (admin) Mar 10, 2012
• 1132 views

When I try that, it does not return the record with the empty address. (I've come to the conclusion that '%' returns only non-empty string values, which seems correct.)

Updated example program:

include "std/filesys.e" 
include "std/pretty.e" 
include "eusql.e" 
 
constant temp_db = "temp.edb" 
object void, query, result 
 
if file_exists( temp_db ) then 
    void = delete_file( temp_db ) 
end if 
 
void = create_db( temp_db ) 
void = run_sql( "CREATE TABLE people id AS AUTONUMBER, name AS TEXT, address AS TEXT, city AS TEXT, state AS TEXT, country AS TEXT" ) 
query = parse_sql( "INSERT INTO people name, address, city, state, country VALUES( [@name], [@address], [@city], [@state], [@country] )" ) 
 
set_parameter( "@name", "Greg Haberek" ) 
set_parameter( "@address", "123 Super Secret" ) 
set_parameter( "@city", "Anywhere" ) 
set_parameter( "@state", "XY" ) 
set_parameter( "@country", "USA" ) 
void = run_query( query ) 
 
set_parameter( "@name", "Matthew Lewis" ) 
set_parameter( "@address", "" ) 
set_parameter( "@city", "" ) 
set_parameter( "@state", "" ) 
set_parameter( "@country", "" ) 
void = run_query( query ) 
 
result = run_sql( sprintf("SELECT * FROM people WHERE name = '%s' AND address = '%s'", {"Greg Haberek", "123 Super Secret"}) ) 
if atom( result ) then 
    puts( 2, get_sql_err(result) ) 
    abort( 1 ) 
end if 
 
pretty_print( 1, result, {2} ) 
 
result = run_sql( sprintf("SELECT * FROM people WHERE name = '%s' AND address = '%s'", {"Matthew Lewis", "%"}) ) 
if atom( result ) then 
    puts( 2, get_sql_err(result) ) 
    abort( 1 ) 
end if 
 
pretty_print( 1, result, {2} ) 
 
close_db( temp_db ) 

Output:

{ 
  { 
    "ID", 
    "NAME", 
    "ADDRESS", 
    "CITY", 
    "STATE", 
    "COUNTRY" 
  }, 
  { 
    { 
      1, 
      "Greg Haberek", 
      "123 Super Secret", 
      "Anywhere", 
      "XY", 
      "USA" 
    } 
  }, 
  "9-----" 
}{ 
  { 
    "ID", 
    "NAME", 
    "ADDRESS", 
    "CITY", 
    "STATE", 
    "COUNTRY" 
  }, 
  "",             <-- empty result! 
  "9-----" 
} 

And I can open the database in wxEDB and see the data:

The weirdest part is, this query works in wxEDB:

SELECT * FROM people WHERE name = 'Matthew Lewis' AND address = '' 

-Greg

5. Re: EuSQL 'EQUALTO' syntax error

• Posted by mattlewis (admin) Mar 10, 2012
• 1136 views

I love it when problems solve themselves!

Matt