flamerobin | database administration tool for Firebird RDBMS | Database library

The error "multiple rows in singleton select" means that a query that should provide a single scalar value produced multiple rows. And that is not unexpected for a query with GROUP BY FK_T1, as it will produce a row per FK_T1 value.

To fix this, you need to use a correlated sub-query by doing the following:

1. Alias the table in the subquery to disambiguate it from the table itself
2. Add a where clause, making sure to use the aliased table (e.g. src, and src.FK_T1), and explicitly reference the table itself for the other side of the comparison (e.g. T2.FK_T1)
3. (optional) remove the GROUP BY clause because it is not necessary given the WHERE clause. However, leaving the GROUP BY in place may uncover certain types of errors.

The resulting subquery then becomes:

The problem is that, when connecting to Firebird 3, the Firebird ADO.net provider (FirebirdSql.Data.FirebirdClient) only supports the new SRP authentication plugin, not the Legacy_Auth authentication plugin. If your user is created using the Legacy_UserManager, then you cannot authenticate using the Firebird ADO.net provider. You need to create the user using Srp.

Depending on your exact Firebird config, you need to add Srp to the UserManager config list, and then create the user with:

Since you do not want your user to have grants on the table, then it is the VIEW which should have, (or a selectable Stored Procedure, when user permissions are regulated with SPs). The user "invokes" the VIEW (or an SP) and then the VIEW invokes the table.

The error "Syntax error in SQL statement" is a HSQLDB error. So, first, make sure your LibreOffice Base project is actually created as a Firebird Embedded project.

I was able to reproduce the error in LibreOffice Base 6.4.4.2 with a Firebird Embedded project. It looks like LibreOffice first tries to parse the query using HSQLDB (probably to be able to transform HSQLDB syntax to Firebird syntax), and only then forwards it to Firebird.

The cause of the error is the $ in RDB$GET_CONTEXT which is not a valid character in an unquoted object name in the HSQLDB SQL syntax, while it is valid in the Firebird SQL syntax. Normally, double quoting the specific object name would solve this, but RDB$GET_CONTEXT is not actually a function, but a syntax construct, so it can't be quoted in Firebird.

To be able to execute this query, you need to enable the 'Run SQL command directly' option in the SQL View, either under Edit > 'Run SQL command directly', or using the 'Run SQL command directly' button in the toolbar (database icon with >_).

I couldn't immediately find a way of getting the result set from the FDScript. However, it is straightforward to get the result set using an FDQuery configured to access the Firebird database, preferably via an FDConnection.

1. Place the TFDConnection and TFDQuery on the form of a new VCL project.

2. Add a TDataSource and TDBGrid to the form, with the TDataSource's DataSet set to FDQuery1 and DBGrid1's DataSource set to DataSource1. Set the width of DBGrid1 to 500 or more.

3. Set FDQuery1's Sql.Text to

Creating stored functions works the same as creating stored procedures. For FlameRobin (like ISQL) that means switch statement terminators of FlameRobin using set term and provide the DDL of the function:

I just found that before appeals.SQLTransaction1.Active:=false, i have to store DBLookupComboBox1.KeyValue into a local variable as database connection afterwards is lost and thus there is not any value to DBLookupComboBox1.KeyValue but NULL.

Therefore i have to use that local variable into my SQL query to update the appropriate record.

I should be more careful next time!

The reason the DBgrid shows nothing is that SqlQuery1.ExecSQL does not return a result set, so SqlQuery1 cannot supply any records for the DBGrid to display. So call SqlQuery1.Open after your Commit.

However, before you can call SqlQuery1.Open, you need to give it a SELECT statement to Execute, because a SQL UPDATE statement does not return a result set, as you have already found. So, do something like:

The error tells you that your Firebird version does not understand the on-disk structure (ODS) version of the database file. The database file has ODS 11.2, while your Firebird understands ODS 11.1 (and lower).

ODS 11.2 means it is a Firebird 2.5 database, while support for ODS 11.1 indicates you are currently using a Firebird 2.1 server. To be able to read the database, you will need to install Firebird 2.5.

See also All Firebird and InterBase On-Disk-Structure (ODS) versions.

Note that this is unrelated to the version of FlameRobin.