SELECT语句的字段列表中的参数-错误:数据类型未知 [英] Parameter in SELECT statement's fields list - error: Data type unknown

问题描述

我对Firebird(嵌入式)数据库有问题.我想在select语句中设置参数值.例如:

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

其中，:string是参数. 上面的代码在SQLite中有效，结果是(当参数为"abcdef"时):

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   |
+------+---------+---------+

当我尝试执行此查询时，我收到以下消息:在SQL语句的应用程序输入参数中发现错误.

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.

推荐答案

问题是Firebird需要知道参数的数据类型.在SQLite中的IIRC中，所有内容都是字符串，但Firebird并非如此.

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.

您将需要显式转换参数，以将所需的类型通知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

?是位置参数. Firebird没有命名参数(过程中除外)，但是如果您的访问库模拟命名参数，那么以下方法也可能会起作用:

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

在选择子句中强制转换参数的功能在较旧的Firebird版本中不起作用(我相信它是在Firebird 2.5中引入的，但我不确定100％).

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).

这篇关于SELECT语句的字段列表中的参数-错误:数据类型未知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！