SUBSTRING in ODBC escape translations

[edwig]

The ODBC documentation states that since version 1.0 the construction of the string function "SUBSTRING(string,start[,length]) should be translatable to native SQL trhough the "{ fn SUBSTRING(string,start[,length]) }" construct.
If you do that in the current Firebird ODBC driver you get a "SUBSTRING(string,start,length)" back, instead of a "SUBSTRING(string FROM start FOR length)" translation. And this invariable leads to a syntax error -104.
In my opinion the "{ fn .... }" translator in the ODBC driver should take care of this conversion.

[mrotteveel]

Thanks for reporting. The firebird-odbc-driver project currently has no active maintainer.

[edwig]

That's a shame. There are at least 2 pull requests for this repository and 2 forks with plenty of updates and fixes. No candidates for the position?

[irodushka]

Hi @edwig
I've fixed this issue in the new ODBC driver edition - https://github.com/FirebirdSQL/firebird-odbc-driver/wiki
The recent version is 3.0.0.3.
Will appreciate if you take part in testing.
Regards

[edwig]

Hi @irodushka
Immediately downloaded the new driver and tested it. Works fine on all combinations!

SELECT {fn SUBSTRING('one two three',5) FROM rdb$database; => Yields 'two three'
SELECT {fn SUBSTRING('one two three,4,3) FROM rdb$database; => Yields 'two'
SELECT {fn SUBSTRING('one two three,-7,3) FROM rdb$database; => Yields '' (empty string)
SELECT {fn SUBSTRING('one two three,5,-3) FROM rdb$database; => Yields error message
Also works fine with SQLNativeSql => Yields "SELECT SUBSTRING('one two three' FROM 5 FOR 3) FROM rdb$database"
Also tested corner cases (past end selection etc)
Then I ran it through the unit tests in the SQLComponents project and that went well.

From my perspective: AOK !!
Will make this my new default driver for daily use.
Thanks!

[irodushka]

@edwig
Glad to hear it)
But I must warn you that this driver is a beta version that uses the new FB client API (OO API). More than 50% of the code has been totally rewritten)) Thus, it may contain bugs - and very probable, it does. Be careful with it)

BTW it will produce an error on SELECT {fn SUBSTRING('one {two} three',5)} FROM rdb$database;
or SELECT {fn SUBSTRING('one two three:))',5)} FROM rdb$database;
Problem with brackets.
I will fix this in the next build.

[irodushka]

@edwig

Then I ran it through the unit tests in the SQLComponents project and that went well.

Could you tell me about the SQLComponents unit tests you mentioned above, please? Perhaps I could use it to fully test ODBC driver. We have a big shortage of testing tools.

[edwig]

@irodushka,

Good to know that.. I will add the test with the brackets to my unit test suite.
For testing: the OpenODBCQuerytool was build upon my SQLComponents library.
To use the unit test: do the following

1. Checkout the project SQLComponents from github (sic)
2. Build in debug mode in Visual Studio 2022
3. Create an ODBC datasourcename (DSN) for the included Firebird testing.fdb database (sysdba password in the unit test project) Name of the DSN should be "testing" for the unit tests. (charset = WIN1252, dialect=3, quoted identifiers, set NULL field schema)
4. Select the Unit test "Test Explorer"
5. Press "Run all tests"

It is a work in progress (of course) and will be expanded in the future.
The library has been reported in many production commercial products as of to day, so there is a reasonably fair chance that most of the functionality is used on a daily basis (at least here in the Netherlands).

Have fun!

[irodushka]

@edwig

Well, I've done it, it sees 87 tests but does not run any of them)
87 tests: passed:0 failed:0 skipped:87

I think I'll try again i 2024) Happy New Year, Edwig!

P.S. It seems you are using user name "sysdba" in lowercase & non-masterkey password)

[edwig]

@irodushka
That's too bad. The total of (now) 88 test METHODS feature a total test set of 8839 tests (See under the output pane while selecting 'Tests' on the combobox 'Show output from:'
Added tests for all the escape sequences in the ODBC standard. Here are the results:

Working OK:

• Date literal
• Time literal
• Timestamp literal
• Function: BIT_LENGTH
• Function: CHAR_LENGTH, CHARACTER_LENGTH
• Function: LCASE
• Function: OCTET_LENGTH
• Function: POSITION
• Function: REPLACE
• Function: RIGHT
• Function: SUBSTRING (! :-) )
• Function: UCASE

Sadly NOT working:

• LIKE {ESCAPE 'c'}
• Function: ASCII
• Function: CHAR
• Function: CONCAT
• Function: INSERT
• Function: LOCATE
• Function: LTRIM, RTRIM, TRIM

The unit test in "BasicDatabase.cpp" beyond lin 950 shows how the not working functions could be translated into working Firebird equivalents

Furthermore: NOT tested (no equivalent in Firebird)

• Function: DIFFERENCE
• Function: SOUNDEX
• Function: REPEAT

[irodushka]

Hi @edwig

Can you please check previously not working funcs in the 3.0.0.5 build?
Regards

[edwig]

Hi Irodushka, Did a quick test this evening. The following seem to work correctly for the basic smoke test: (UnitTest.BasicTesting.ODBCEscapeSequences) { fn ASCII('A')} -> Now working { fn CHAR(65)} -> Now working { fn CONCAT(a,b)} -> Now wokking { fn INSERT(a,start,len,b) -> Now working { fn LOCATE(a,b)} -> Now working { fn LTRIM(a) -> Now working { fn RTRIM(a) -> Now working The only one NOT working is: { fn SPACE(x)} -> NOT WORKING: [HY000][-104][ODBC Firebird Driver][Firebird]Dynamic SQL Error

…

-SQL error code = -104

-Token unknown - line 1, column 14 Will also test corner cases and one-offs in the days to come. This seems to be a BIG improvement over the previous versions!!! Thank you! Van: irodushka ***@***.***> Verzonden: woensdag 24 januari 2024 10:40 Aan: FirebirdSQL/firebird-odbc-driver ***@***.***> CC: Edwig Huisman ***@***.***>; Mention ***@***.***> Onderwerp: Re: [FirebirdSQL/firebird-odbc-driver] SUBSTRING in ODBC escape translations (Issue #207) Hi @edwig <https://github.com/edwig> Can you please check previously not working funcs in the 3.0.0.5 build? Regards — Reply to this email directly, view it on GitHub <#207 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABFDS323BY5ZZVT4G4FQSLDYQDJIDAVCNFSM6AAAAAA2C2NQZKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMBXG42TSNRRGU> . You are receiving this because you were mentioned. <https://github.com/notifications/beacon/ABFDS32NPP7B2S3FPZ26GMTYQDJIDA5CNFSM6AAAAAA2C2NQZKWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTRWYM76.gif> Message ID: ***@***.*** ***@***.***> >

[irodushka]

Hi @edwig

{ fn SPACE(x)} -> NOT WORKING:
Should work in 3.0.0.6)

[irodushka]

Hi @edwig

Perhaps we will close this issue - if you confirm that everything was completed ok.

[edwig]

Hi Irodushka, Fine with me. No more issues found. You may close this issue. Van: irodushka ***@***.***> Verzonden: donderdag 8 februari 2024 14:39 Aan: FirebirdSQL/firebird-odbc-driver ***@***.***> CC: Edwig Huisman ***@***.***>; Mention ***@***.***> Onderwerp: Re: [FirebirdSQL/firebird-odbc-driver] SUBSTRING in ODBC escape translations (Issue #207) Hi @edwig <https://github.com/edwig> Perhaps we will close this issue - if you confirm that everything was completed ok. — Reply to this email directly, view it on GitHub <#207 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABFDS355KHWOXLJO7OXU47LYSTIPPAVCNFSM6AAAAAA2C2NQZKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZUGE2DGOJUGE> . You are receiving this because you were mentioned. <https://github.com/notifications/beacon/ABFDS335U7YYBLH3ARDUTK3YSTIPPA5CNFSM6AAAAAA2C2NQZKWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTTJCY4K.gif> Message ID: ***@***.*** ***@***.***> >

[irodushka]

Ok! Feel free to contact me for new issues)