We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

LOCATE on firebird generates unknown function

asked 2021-02-27 17:54:05 +0200

Przemo gravatar image

updated 2021-02-27 23:12:04 +0200

Edit: WARNING: The title was edited, it was about HSQLDB previously

System info: linux fedora 33 LO 7.0.4.2

Table t_test with 2 columns, one is ID [INTEGER], the other is number TEXT [VARCHAR] The name field contains values like:

1/2012
2/2012
1/2013
I'm trying to use query:
SELECT "number", LOCATE( '/', "number" ) FROM "t_test"
but I get error:
The data content could not be loaded. /builddir/build/BUILD/libreoffice-7.0.4.2/connectivity/source/commontools/dbtools.cxx:751
Error code 

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -804
*Function unknown
*LOCATE
caused by
'isc_dsql_prepare'
 /builddir/build/BUILD/libreoffice-7.0.4.2/connectivity/source/drivers/firebird/Util.cxx:68

I tried to use " ' and [] as delimiters, but none works. What I'm doing wrong?

I used this [1] as reference describing formatting of LOCATE

[1] http://www.hsqldb.org/doc/guide/built...

edit retag flag offensive close merge delete

Comments

2

firebird_sdbc error:

You don't seem to use a hsqldb but a firebird database and you may want to check http://firebirdsql.org/ for functions supported by firebird.

Opaque gravatar imageOpaque ( 2021-02-27 18:01:43 +0200 )edit

@Opaque Thank you! - that's another lesson for me. I use the same ods file on 2 systems: windows 10 and linux fedora and I wrongly assumed that if I see HSQLDB on windows it's the same on linux...

Przemo gravatar imagePrzemo ( 2021-02-27 18:25:29 +0200 )edit
1

Edit: WARNING: The title is misleading! I don't use HSQLSB, but I can't edit the topic title.

You can edit the title:

image description

Opaque gravatar imageOpaque ( 2021-02-27 18:37:24 +0200 )edit

I think it depends on the karma. I've fixed the title.

Przemo gravatar imagePrzemo ( 2021-02-27 23:12:52 +0200 )edit

I think it depends on the karma

No - it doesn't

Opaque gravatar imageOpaque ( 2021-02-28 11:30:14 +0200 )edit

OK, anyway it works now.

Przemo gravatar imagePrzemo ( 2021-02-28 17:28:34 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-02-27 18:21:43 +0200

Ratslinger gravatar image

Hello,

Just as the version of a database is important to refer to documentation, the actual database type is even more important. In Base this can be seen on the bottom line of the main Base screen. Most databases follow standards but also have additional processes not in the standard. This is true for most databases you will come across.

In Firebird there is no Locate but rather Position() (see -> this post).

So the statement previously given for HSQLDB just needs a simple modification to work with Firebird:

SELECT "DATEID", "text_date", LEFT( "text_date", POSITION( '/', "text_date" ) - 1 ) "data_left", RIGHT( "text_date", 4 ) "data_right" FROM "DATETEST"
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-02-27 17:54:05 +0200

Seen: 52 times

Last updated: Feb 27