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

Question

我试图在数据库中存储用户的IP地址，基于此堆栈过流问题，我了解到IP地址应该以二进制格式存储。

以准备语句的形式运行函数-我可以将@ip绑定为$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));