@ZEAL Blog·厉
WWW Zeal Blog
We stand alone,
TOGETHER.
+ 0 - 0 | §SQL Server不靠谱的isnumeric函数

MS SQL Server 的 isnumeric 函数返回为1的字符串有时候并不能被直接 CAST 成数值型字段。比如包含逗号(12,345)、制表符的字符串用isnumeric来判断的话返回都是1。所以在进行某些数据转化的时候想靠 isnumeric 来保证转化脚本不报数据类型转换失败的错误是不保险的。

解决办法只有用额外的字符串校验来做判断:

IF OBJECT_ID(N'dbo.isReallyNumeric', N'FN') IS NOT NULL
DROP FUNCTION dbo.isReallyNumeric;
GO

CREATE FUNCTION dbo.isReallyNumeric
(
    @num VARCHAR(128)
)
RETURNS BIT
BEGIN
    set @num = LTRIM(RTRIM(@num))
    IF LEFT(@num, 1) = '-'
        SET @num = SUBSTRING(@num, 2, LEN(@num))

    DECLARE @pos INTEGER

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

    RETURN CASE
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0
        AND @num NOT IN ('.', '-', '+', '^')
        AND LEN(@num)>0
        AND @num NOT LIKE '%-%'
        AND
        (
            ((@pos = LEN(@num)+1)
            OR @pos = CHARINDEX('.', @num))
        )
    THEN
        1
    ELSE
    0
    END
END
GO
IF OBJECT_ID(N'dbo.isReallyInteger', N'FN') IS NOT NULL
DROP FUNCTION dbo.isReallyInteger;
GO

CREATE FUNCTION dbo.isReallyInteger
(
    @num VARCHAR(128)
)
RETURNS BIT
BEGIN
    set @num = LTRIM(RTRIM(@num))
    IF LEFT(@num, 1) = '-'
        SET @num = SUBSTRING(@num, 2, LEN(@num))

    RETURN CASE
    WHEN PATINDEX('%[^0-9-]%', @num) = 0
        AND CHARINDEX('-', @num) <= 1
        AND @num NOT IN ('.', '-', '+', '^')
        AND LEN(@num)>0
        AND @num NOT LIKE '%-%'
    THEN
        1
    ELSE
        0
    END
END
GO

通过创建以上的自定义函数来检查字符串是否确实可以被CAST到数值型字段来保存,替换掉调用 isnumeric 的地方即可(因为带前后导空格的字符串可以被自动CAST,所以先做TRIM处理)。

+ 0 - 0 | §unbuffered queries are active问题

使用 PDOStatement->execute() 执行查询时出现错误:

Message: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

经过测试发现是因为在前一次 execute 里面执行了多条SQL语句(即通过分号分隔连续执行了多次的select/insert/update)。

http://blog.stormwild.net/2009/04/21/cannot-execute-queries-while-other-unbuffered-queries-are-active/

看上去应该是 Pdo_Mysql 库本身存在的问题?没有找到比较好的解决方案。唯一比较笨的方法是:在执行了 multi-query 语句之后立即 closeConnection ,断开本次数据库连接。再下次查询之前先重新建立连接。这样 unbuffered queries 就被重置了。

标签 ( PHP ) :
Since 2005.04.27  梦想 就像鸡蛋 要么孵化 要么臭掉RSS Feed (Entries) | About me | Back To Home | @ZEAL | zbird.com | 沪ICP备05024379号