Parameter in SELECT statement's fields list - error: Data type unknown

Question

I have a problem with Firebird (embedded) database. I would like to set a parameter values in the select statement. For example:

SELECT name, surname, :string AS myText
FROM myTable

where :string is a parameter. The above code works in SQLite and the result is (when the paramenter is "abcdef"):

+------+---------+---------+
|name  |surname  |myText   |
+------+---------+---------+
|John  |Black    |abcdef   |
+------+---------+---------+
|Thomas|Young    |abcdef   |
+------+---------+---------+
|...   |...      |abcdef   |
+------+---------+---------+
|nameX |surnameY |abcdef   |
+------+---------+---------+

When I try to execute this query then I get the following message: "An error was found in the application program input parameters for the SQL statement.

Dynamic SQL Error.
SQL error code = -804.
Data type unknown.

Answer 1

The problem is that Firebird needs to know the datatype of the parameter. IIRC in SQLite, everything is a string, but that is not the case in Firebird.

You will need to explicitly cast the parameter to inform Firebird about the expected type, for example:

SELECT name, surname, cast(? as varchar(100)) AS myText
FROM myTable

Where the ? is a positional parameter. Firebird doesn't have named parameters (except in procedures), but if your access library simulates named parameters, then probably the following will work as well:

SELECT name, surname, cast(:string as varchar(100)) AS myText
FROM myTable

The ability to cast parameters in the select-clause does not work in older Firebird version (I believe it was introduced in Firebird 2.5, but I'm not 100% sure).