softace/sqliteodbc

SQL Execute returned an error: HY000: [SQLite]ESCAPE expression must be a single character (1)

chainria opened this issue · 6 comments

chainria commented

I am trying to get Asterisk 13.14 to work with sqliteodbc, but I am having troubles with it. I really don't know how to fix this error. Tried to replicate the problem using isql, Asterisk uses prepared statements but the outcome seems the same:

#1: The query as stated by Asterisk:
SQL> SELECT * FROM "ast_extensions_table" WHERE exten like "\_%" ESCAPE "\\" AND priority = 1 AND context = "incoming"
[ISQL]ERROR: Could not SQLExecute

#2: Simply trying to ashere to the error message:
SQL> SELECT * FROM "ast_extensions_table" WHERE exten like "\_%" ESCAPE "\" AND priority = 1 AND context = "incoming"
(data snipped)
SQLRowCount returns 0
1 rows fetched

How can I make the first query work? It is coming straight from asterisk, so I cannot change much there. If code in sqliteodbc itself needs to be changed just for this case / breaks other things am fine with that as well, since I can build a local package if needed. I just don't know whether it's a bug or just a very rare problem only I have.

(Edited to fix vanishing backslashes)

chainria commented

Thank you for your fast response and the look in the documentation. However I already tried that parameter and if I set it to false, it will simply omit the ESCAPE clause, which yields correct SQL, but no data unfortunately. The SQL looks like this (again, prepared statements, but should not make any difference in here)

SQL> SELECT * FROM "ast_extensions_table" WHERE exten like "\_%" AND priority = 1 AND context = "incoming"

Note, that the backslash is still present and therefore the data never gets found. I haven't looked in other ODBC implementations yet, but I am pretty sure that the double backslash needs to be reduced into a single one before handing it to the engine itself. Funnily enough I found code which should do just make the version without the ESCAPE clause work in https://github.com/softace/sqliteodbc/blob/master/sqlite3odbc.c#L1945 but it doesn't seem to work for me. I am using 0.9995-1 from Debian Sarge. But this was already implemented back then. Or is my installation really just too old?

EionRobb commented

Looks more like a problem in asterisk to me? The escape clause should still be being provided, but just as a single \.

I've run your same #2 query (although swapping " with ' to be odbc-compliant) and it works fine, bringing through rows that start with an _ character, but only when the ESCAPE clause is present, without the escape it's looking for strings at least two characters long that start with a backslash

Also, I'm running some legacy ODBC code that unfortunately has the \ character for a column value, to signal a continuation of data from a previous row. The only database engine that we have to escape that to \\ for is MySQL. The \ isn't treated as an escape character for postgres, ms sql, sqlite nor foxpro

chainria commented

Thank you once more very much for your detailed answer. I found the code responsible under https://github.com/asterisk/asterisk/blob/13.14.1/res/res_config_odbc.c#L368 but before I go patching Asterisk, I would prefer maintaining a local patched version of sqliteodbc. Not ideal, but still better than maintaining Asterisk myself. So how can I make this work? I don't know whether keeping the escpae flag on or off is better, and I am open to both options. But somehow I think "unescaping" the search might be easier. What I cannot wrap my head around is why it doesn't work with the namematch function, shouldn't it do exactly what I need, matching \_ like _ ? Or is there a flag I have missed? namematch is used several times in the code.

chainria commented

I dug a little bit deeper, and it really seems like Asterisk is doing it wrong: https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/like-predicate-escape-character?view=sql-server-ver15 - in this manual there is only a single backslash used as well. However I just couldn't figure out how exactly I should contact them about it. Plus, I would need to build Asterisk completely from source myself, which really is not feasible at all. So I would be thankful for any and every pointer that helps me how to fix this problem in the driver itself. Even a hint for a location (file/function) would be very helpful, since I couldn't yet find the right place to do this replace.

Edit: I found the relevant commit in Asterisk: asterisk/asterisk@15d266b which talks about this MySQL article: https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html Now I am at a complete loss what's the right syntax to use. Who is doing it wrong? Who is doing it right? MySQL? SQLite? I really don't know anymore.

chainria commented

In case anybody needs this in the future, I have decided to simply replace "ESCAPE '\\'" with "ESCAPE '\'" in SQLPrepare() (using a simple string replacement).
Performance seems fine, there are not many queries really. I am pretty sure it's not the best way to do it, but it does the job.
I understand this is a niche case and so I don't expect it to be changed/fixed soon. That's why I went with the local patch route.
Thank you for your help @EionRobb !