FirebirdSQL/firebird-odbc-driver

SUBSTRING in ODBC escape translations

edwig opened this issue · 15 comments

edwig commented

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 commented

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

edwig commented

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 commented

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 commented

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 commented

@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 commented

@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 commented

@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 commented

@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 commented

@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 commented

Hi @edwig

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

edwig commented
irodushka commented

Hi @edwig

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

irodushka commented

Hi @edwig

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

edwig commented
irodushka commented

Ok! Feel free to contact me for new issues)