1. null values in edbi giving machine-level exception
- Posted by Anthill Nov 23, 2010
- 1275 views
I am having some problems in edbi with NULL values against sqlite3 db.
The error message I get is:
/usr/share/euphoria/edbi/edbi/edbi.e:514 in function next() A machine-level exception occurred during execution of this statement
This occurs while running:
data = edbi:query_rows(sql)
I know that the query works since it works using firefoxes sqlite manager. Any help appreciated.
2. Re: null values in edbi giving machine-level exception
- Posted by jeremy (admin) Nov 23, 2010
- 1261 views
Can you give me a bit more of a code example? For instance, what is NULL? A parameter in your SQL statement or a field value that is NULL?
Thanks,
Jeremy
3. Re: null values in edbi giving machine-level exception
- Posted by Anthill Nov 23, 2010
- 1239 views
Jeremy,
Here is some example code. You will need to change your include, driver path and db handle. You may also need to comment out the drop table query the first time.
include /usr/share/euphoria/edbi/edbi/edbi.e
edbi:set_driver_path("/usr/share/euphoria/edbi/drivers")
edbi:db_handle dbh = edbi:open("sqlite3:///data/sqlite/TM")
edbi:execute("DROP TABLE TEST_NULL;")
edbi:execute("CREATE TABLE TEST_NULL (ID integer(10) NOT NULL, A TEXT(40) DEFAULT NULL);")
edbi:execute("INSERT INTO TEST_NULL (ID,A) VALUES (1,'test');")
edbi:execute("INSERT INTO TEST_NULL (ID,A) VALUES (2, NULL);")
sequence sql, data
sql = "SELECT A FROM TEST_NULL WHERE ID=1;"
data = edbi:query_rows(sql) --ok
object o = data[1]
puts(1,o[1])
sql = "SELECT A FROM TEST_NULL WHERE ID=2;"
data = edbi:query_rows(sql) --not ok
o = data[1]
puts(1,o[1])
edbi:close()
4. Re: null values in edbi giving machine-level exception
- Posted by jeremy (admin) Nov 23, 2010
- 1274 views
Thanks for reporting the problem. It is now fixed, you'll need to update your edbi source and recompile the SQLite3 driver. I am not sure if you are aware of the move to using Mercurial, please update your source from: http://bitbucket.org/jcowgar/edbi not the old SVN repo. I will be removing it shortly, it's no longer in use and outdated.
I also updated the binary sqlite driver for EDBI users on Windows, it can be downloaded from: http://bitbucket.org/jcowgar/edbi/downloads?highlight=15467
Also... I am not sure the fix is the best. The area EDBI was going next was better NULL handling. Right now it will return an empty sequence. I'm open to suggestions on how to better handle NULL values.
Jeremy
5. Re: null values in edbi giving machine-level exception
- Posted by Anthill Nov 23, 2010
- 1280 views
Thanks Jeremy! I verified that it worked. By the way, what is really the difference between edbi:query and edbi:query_rows?
6. Re: null values in edbi giving machine-level exception
- Posted by jeremy (admin) Nov 23, 2010
- 1275 views
Thanks Jeremy! I verified that it worked. By the way, what is really the difference between edbi:query and edbi:query_rows?
query will return a query handle, you then step through each result with edbi:next(queryHandle). query_rows() does all that work for you and returns a sequence of rows. One may wonder why use query() then... the reason would be if you are doing a large query and only need to have one record in memory at a time. Say you are querying 60,000 products for an inventory report or something. Using query_rows would load each and every record into memory at the same time. Say in addition, that product query included a product image.
Jeremy
