issue when fetching several records
areski opened this issue · 8 comments
I'm having an issue if I try to fetch a certain number of record, it works well with LIMIT 1 up to LIMIT 3 but if I set the query with LIMIT 4 or higher I get the following error
iex(8)> Sqlitex.with_db('./data/mydb.db', fn(db) ->
...(8)> Sqlitex.query(db, "SELECT * FROM cdr LIMIT 3")
...(8)> end)
{:ok,
[[field1: "...", ...],
[field1: "...", ...]}
iex(9)>
nil
iex(10)> Sqlitex.with_db('./data/mydb.db', fn(db) ->
...(10)> Sqlitex.query(db, "SELECT * FROM cdr LIMIT 4")
...(10)> end)
** (MatchError) no match of right hand side value: []
(sqlitex) lib/sqlitex/row.ex:31: Sqlitex.Row.translate_value/1
(elixir) lib/enum.ex:1184: Enum."-map/2-lists^map/1-0-"/2
(elixir) lib/enum.ex:1184: Enum."-map/2-lists^map/1-0-"/2
(sqlitex) lib/sqlitex/row.ex:12: Sqlitex.Row.build_row/4
(sqlitex) lib/sqlitex/row.ex:4: anonymous fn/5 in Sqlitex.Row.from/4
(elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
(sqlitex) lib/sqlitex/row.ex:3: Sqlitex.Row.from/4
(sqlitex) lib/sqlitex/statement.ex:129: Sqlitex.Statement.fetch_all/2
I notice you're using different databases for the succeding example vs the failing example. Do they contain the same data? Would you be able to provide a failing database file?
I've tried to reproduce locally without much luck:
iex(1)> Sqlitex.with_db(":memory:", fn (db) ->
...(1)> Sqlitex.exec(db, "CREATE TABLE x (x integer);")
...(1)> Sqlitex.exec(db, "INSERT INTO x (x) VALUES (1), (2), (3), (4)")
...(1)> Sqlitex.query(db, "SELECT * FROM x LIMIT 3")
...(1)> end)
[[x: 1], [x: 2], [x: 3]]
iex(2)> Sqlitex.with_db(":memory:", fn (db) ->
...(2)> Sqlitex.exec(db, "CREATE TABLE x (x integer);")
...(2)> Sqlitex.exec(db, "INSERT INTO x (x) VALUES (1), (2), (3), (4)")
...(2)> Sqlitex.query(db, "SELECT * FROM x LIMIT 4")
...(2)> end)
[[x: 1], [x: 2], [x: 3], [x: 4]]I see from the stacktrace that it's failing when tying to parse out a datetime, which suggests that row 4 of your database has a datetime format that sqlitex doesn't expect.
Would you be able to give us the output of sqlite ./data/freeswitchcdr.db "SELECT * FROM cdr LIMIT 4"? Might also help to understand how the data is being inserted into the database.
I was using the same database, I forgot to change the name, now I did.
Here an extract of the data:
sqlite> SELECT * FROM cdr;
Outbound Call|XXXXXXXXXXXX|XXXXXXXXXXXX|default|2016-09-09 13:19:49|2016-09-09 13:19:49|2016-09-09 13:20:06|17|17|NORMAL_CLEARING|59d88d1a-39ab-4bfe-9219-3bce6fb01590|||1|1|1597|||||||
Outbound Call|XXXXXXXXXXXX|XXXXXXXXXXXX|default|2016-09-09 16:40:59|2016-09-09 16:40:59|2016-09-09 16:41:16|17|17|NORMAL_CLEARING|4ca0cb3c-9064-4a2d-9d0d-af128dbe3abc|||1|1|1597|||XXXXXXXXXXXX@YYYYYYYYYYYYY|||16|
Outbound Call|XXXXXXXXXXXX|XXXXXXXXXXXX|default|2016-09-09 17:05:32|2016-09-09 17:05:32|2016-09-09 17:05:50|18|18|NORMAL_CLEARING|7ed526f0-7442-4aa4-b36e-6e9d852e7bbe|||1|1|1597|||XXXXXXXXXXXX@YYYYYYYYYYYYY||aleg|16|
|11111111111|XXXXXXXXXXXX|default|2016-09-12 09:32:56||2016-09-12 09:33:28|32|0|RECOVERY_ON_TIMER_EXPIRE|2c2d2cc1-b8eb-4da0-abcc-c78e14e699e1|||1|1|1597|||XXXXXXXXXXXX@YYYYYYYYYYYYY||aleg|102|
|11111111111|XXXXXXXXXXXX|default|2016-09-12 09:33:52||2016-09-12 09:34:24|32|0|RECOVERY_ON_TIMER_EXPIRE|563e3372-7c45-40fc-8053-f0d68b392b5a|||1|1|1597|||XXXXXXXXXXXX@YYYYYYYYYYYYY||aleg|102|
|11111111111|123|default|2016-09-12 09:35:10||2016-09-12 09:35:42|32|0|RECOVERY_ON_TIMER_EXPIRE|dfd3e018-d6c6-4e66-afe3-6ba0356c0859|||1|1|1597|||XXXXXXXXXXXX@YYYYYYYYYYYYY||aleg|102|
sqlite> .schema cdr
CREATE TABLE cdr (
caller_id_name VARCHAR,
caller_id_number VARCHAR,
destination_number VARCHAR,
context VARCHAR,
start_stamp DATETIME,
answer_stamp DATETIME,
end_stamp DATETIME,
duration INTEGER,
billsec INTEGER,
hangup_cause VARCHAR,
uuid VARCHAR,
bleg_uuid VARCHAR,
account_code VARCHAR
, user_id INTEGER, used_gateway_id INTEGER, callrequest_id INTEGER, nibble_total_billed VARCHAR, nibble_increment INTEGER, dialout_phone_number VARCHAR, amd_status VARCHAR, legtype VARCHAR, hangup_cause_q850 INTEGER, job_uuid VARCHAR);
How are you inserting data into your database? If there's no datetime, shouldn't the field be NULL, rather than an empty string?
Sqlitex appears to handle NULL fine:
iex(5)> Sqlitex.with_db(":memory", fn (db) ->
...(5)> Sqlitex.exec(db, "CREATE TABLE x (dt DATETIME);")
...(5)> Sqlitex.exec(db, "INSERT INTO x (dt) VALUES ('2016-09-12 09:32:56'), (NULL);")
...(5)> Sqlitex.query(db, "SELECT * FROM x LIMIT 2;")
...(5)> end)
The data are inserted by a Telco switch called FreeSWITCH. I added a sql dump in the project https://github.com/areski/excdr-pusher/blob/master/data/import_sqlite.sql#L33
It seems that SQLite accept empty string for datetime so I believe we need to make sqlitex handling those strange cases
I see. Pretty sure sqlite actually doesn't do much with those types. It'll quite happily accept a string in an INTEGER column for example:
sqlite> CREATE TABLE x (int INTEGER);
sqlite> INSERT INTO x (int) VALUES ("string");
sqlite> select * from x;
string