php - 将 SQL 函数作为 PDO 准备语句运行并返回值

Question

我正在尝试将用户的 IP 地址存储在数据库中并基于此 Stackoverflow Question我了解到 IP 地址应该以 BINARY 格式存储。

将函数作为准备好的语句运行 - 我可以将 @ip 绑定(bind)为 $text 参数

$stmt = $this->dbconnect->prepare ('CREATE FUNCTION dbo.fnBinaryIPv4(@:ip AS VARCHAR(15)) RETURNS BINARY(4)
        AS
        BEGIN
        DECLARE @bin AS BINARY(4)
        SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
        + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
        + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
        + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
        RETURN @bin
 END;');


    $stmt->execute(array(':ip'=>$text));

如何使用返回值(@bin)？

Answer 1

使用您的代码，您并不是在执行存储的函数，而是在创建它。如果您想执行之前创建的存储函数，只需选择它的返回值

例如:

$stmt = $this->dbconnect->prepare ('SELECT dbo.fnBinaryIPv4(:ip) as bin');
$stmt->execute(array(':ip'=>$text));