Java 和 PostgreSQL 存储过程 - 返回注册为 out 参数，导致 in 参数出现问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2277727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java and PostgreSQL stored procedure - return registered as out parameter, causing issues with in parameters
提问by Matt
I'm trying to call a PostgreSQL stored procedure from a Java app; the procedure has a DATE type parameter so I'm using a java.sql.Date type with CallableStatement.setDate(). However, executing the statement always results in an exception and the SQL logs show this:
我正在尝试从 Java 应用程序调用 PostgreSQL 存储过程；该过程有一个 DATE 类型参数，所以我使用 java.sql.Date 类型和 CallableStatement.setDate()。但是，执行该语句总是会导致异常，并且 SQL 日志显示如下：
LOG: execute <unnamed>: select * from athlete.create_athlete(,,,,,,) as result
DETAIL: parameters: = '', = '[email protected]', = 'Joe', = 'Blow', = 'foobar', = 'M', = '1979-03-22 -04:00:00'
ERROR: column "dob" is of type date but expression is of type text at character 122
HINT: You will need to rewrite or cast the expression.
QUERY: INSERT INTO athlete.athlete (email, first_name, last_name, password, gender, dob) VALUES ( , , , , , )
CONTEXT: PL/pgSQL function "create_athlete" line 2 at SQL statement
STATEMENT: select * from athlete.create_athlete(,,,,,,) as result
The stored procedure actually has 6 parameters (and should receive values $2 through $7 above) - the 7th comes from registering the return value as an out parameter. This has me confused - is it correct that it appears as 7 parameters when I register an out parameter for the return value?
存储过程实际上有 6 个参数（并且应该接收上面的 $2 到 $7 的值）——第 7 个来自将返回值注册为输出参数。这让我感到困惑 - 当我为返回值注册一个 out 参数时，它显示为 7 个参数是否正确？
From all the docs I've read I'm under the impression that the return value has to be registered as the first parameter:
从我读过的所有文档中，我的印象是必须将返回值注册为第一个参数：
registerQuery = "{? = call athlete.create_athlete(?,?,?,?,?,?)}";
...
CallableStatement cs = conn.prepareCall(registerQuery);
cs.registerOutParameter(1, Types.BOOLEAN);
cs.setString(2, email);
...
The error above suggests to me that there's a mismatch between stored procedure parameters and the parameters supplied to the insert statement. I've been following documentation for all of this but am clearly doing something wrong. How do I supply the proper parameters to the stored procedure and retrieve the return value after the call?
上面的错误告诉我，存储过程参数和提供给插入语句的参数不匹配。我一直在关注所有这些的文档，但显然我做错了什么。如何为存储过程提供正确的参数并在调用后检索返回值？
采纳答案by Matt
It turns out the issue was that the order of parameters passed to the stored procedure did not match the order those parameters were passed to the insert statement. I don't understand why PostgreSQL would use named parameters if order is significant.
事实证明，问题在于传递给存储过程的参数顺序与这些参数传递给插入语句的顺序不匹配。如果顺序很重要，我不明白为什么 PostgreSQL 会使用命名参数。
For example, the signature of the stored procedure was as follows:
例如，存储过程的签名如下：
CREATE FUNCTION insert_Person (IN in_name TEXT, IN in_gender CHAR(1), IN in_bdate DATE) RETURNS BOOLEAN...
CREATE FUNCTION insert_Person (IN in_name TEXT, IN in_gender CHAR(1), IN in_bdate DATE) RETURNS BOOLEAN...
The INSERT statement contained within that stored procedure was as follows:
该存储过程中包含的 INSERT 语句如下：
INSERT INTO Person (name, bdate, gender) VALUES (in_name, in_bdate, in_gender);
INSERT INTO Person (name, bdate, gender) VALUES (in_name, in_bdate, in_gender);
Changing the order of the parameters such that they matched in either the stored procedure signature or insert statement (I went with the former) resolved the issue.
更改参数的顺序，使它们在存储过程签名或插入语句中匹配（我采用前者）解决了该问题。
回答by Gladwin Burboz
Depends on signature of your stored procedure type (function/procedure).
取决于您的存储过程类型（函数/过程）的签名。
For function like one below, out parameter will be first one and will have param1 and param2 as second and third parameters.
对于像下面这样的函数，out 参数将是第一个，并将 param1 和 param2 作为第二个和第三个参数。
DB Procedure (for function):
DB 程序（用于函数）：
CREATE FUNCTION my_func (
param1 INT,
param2 INT)
RETURNS INT
AS
:
:
Java code (for function):
Java代码（用于函数）：
registerQuery = "{? = call my_func(?,?)}";
...
CallableStatement cs = conn.prepareCall(registerQuery);
cs.registerOutParameter(1, Types.INTEGER);
cs.setInteger(2, 10);
cs.setInteger(3, 10);
...
.
.
However for procedure like one below, out parameter will be third one and will have param1 and param2 as first and second parameters.
但是对于像下面这样的过程，out 参数将是第三个参数，并且将 param1 和 param2 作为第一个和第二个参数。
DB Procedure (for procedure):
DB 程序（用于程序）：
CREATE PROCEDURE my_proc (
param1 INT,
param2 INT,
OUT param3 INT)
BEGIN
:
:
END;
Java code (for procedure):
Java代码（用于过程）：
registerQuery = "{call my_func(?,?,?)}";
...
CallableStatement cs = conn.prepareCall(registerQuery);
cs.registerOutParameter(3, Types.INTEGER);
cs.setInteger(1, 10);
cs.setInteger(2, 10);
...
.
.
Note that you can have multiple out parameters while only one return value.
请注意，您可以有多个输出参数，而只有一个返回值。
回答by ManiP
Postgresql server supports named parameters but the jdbc driver does not support it(well not yet to my knowledge), so until such time only positional parameters is supported.
Postgresql 服务器支持命名参数，但 jdbc 驱动程序不支持它（据我所知，目前还不支持），所以在此之前只支持位置参数。
回答by Surjeet Katwal
Due to a restriction in the OCI layer, the JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT to BOOLEAN.
由于 OCI 层的限制，JDBC 驱动程序不支持将 BOOLEAN 参数传递给 PL/SQL 存储过程。如果 PL/SQL 过程包含 BOOLEAN 值，您可以通过用第二个 PL/SQL 过程包装 PL/SQL 过程来解决这个限制，第二个 PL/SQL 过程接受参数作为 INT 并将其传递给第一个存储过程。当调用第二个过程时，服务器执行从 INT 到 BOOLEAN 的转换。
