QA@IT
«質問へ戻る

質問を投稿

SQLParameter のvalueに改行が含まれる場合の挙動

お世話になります。
vb.net と PostgreSQLにてシステムを構築しています。
Insert のクエリを実行した際、NpgsqlParameterのValueに改行が含まれるものを渡した時に
以下のようなエラーとなります。
改行が含まれる場合は、エスケープを行う必要があるのでしょうか?

エラーメッセージ

ERROR: 42804: 列"order_date"は型dateですが、式は型numericでした

catch したExceptionでのErrorSQL内容

insert into bfl500tb_alt values(
((E'1817')::numeric),
((E'1')::numeric),
((E'20121119-0001')::varchar),
((E'本日発送')::varchar),
((NULL)::date),
((E'[客先要望:]
配達日指定:2012-11-21(水)
配達時間指定:19時~21時')::varchar),
((E'16700')::numeric),
((E'0')::numeric),
((E'2012-11-08')::date),
((E'01:54:12')::varchar),
((E'1')::varchar),
((E'1')::varchar),
((E'xxx-xxxx')::varchar),
((E'大阪府')::varchar),
((E'大阪市中央区')::varchar),
((E'○○○1-1-1')::varchar),
((E'□□ □□')::varchar),
((E'06-xxxx-xxxx')::varchar),
((NULL)::varchar));

以下、コード

    Public Function createInsertSQL(ByRef param As ArrayList) As String
        Dim sql As New StringBuilder
        With sql
            .AppendLine("insert into bfl500tb_alt values(")
            .AppendLine("@order_id,")
            .AppendLine("@order_detail_no,")
            .AppendLine("@order_comments,")
            .AppendLine("@total,")
            .AppendLine("@tax,")
            .AppendLine("@shipping,")
            .AppendLine("@order_date,")
            .AppendLine("@order_postno,")
            .AppendLine("@order_pref,")
            .AppendLine("@order_city,")
            .AppendLine("@order_town,")
            .AppendLine("@order_name,")
            .AppendLine("@order_tel,")
            .AppendLine("@luggage_slipno);")
        End With

        param.Add(addSQLParam("order_id", order_id, TYPE_NUM))
        param.Add(addSQLParam("order_detail_no", order_detail_no, TYPE_NUM))
        param.Add(addSQLParam("order_no", order_no, TYPE_STR))
        param.Add(addSQLParam("order_sts", order_sts, TYPE_STR))
        param.Add(addSQLParam("delivery_date", delivery_date, TYPE_DAT))
        param.Add(addSQLParam("delivery_timezone", delivery_timezone, TYPE_STR))
        param.Add(addSQLParam("order_comments", order_comments, TYPE_STR))
        param.Add(addSQLParam("total", total, TYPE_NUM))
        param.Add(addSQLParam("tax", tax, TYPE_NUM))
        param.Add(addSQLParam("shipping", shipping, TYPE_NUM))
        param.Add(addSQLParam("order_date", order_date, TYPE_DAT))
        param.Add(addSQLParam("order_postno", order_postno, TYPE_STR))
        param.Add(addSQLParam("order_pref", order_pref, TYPE_STR))
        param.Add(addSQLParam("order_city", order_city, TYPE_STR))
        param.Add(addSQLParam("order_town", order_town, TYPE_STR))
        param.Add(addSQLParam("order_name", order_name, TYPE_STR))
        param.Add(addSQLParam("order_tel", order_tel, TYPE_STR))
        param.Add(addSQLParam("luggage_slipno", luggage_slipno, TYPE_STR))

        Return sql.ToString
    End Function

    Public Function execSQL(ByVal strSQL As String, ByVal params As ArrayList) As Boolean
        Try
            Using con As New NpgsqlConnection(conStr)
                con.Open()
                Using tran As NpgsqlTransaction = con.BeginTransaction
                    Try
                        Using com As New NpgsqlCommand(strSQL, con)
                            For Each param As clsDBParam In params
                                com.Parameters.Add(setParameter(param))
                            Next
                            com.Transaction = tran
                            com.ExecuteNonQuery()  '←ここでエラー
                        End Using
                        tran.Commit()
                    Catch ex As Exception
                        tran.Rollback()
                        Throw New Exception("SQL実行時エラー", ex)
                        Return False
                    End Try
                End Using
            End Using
        Catch ex As Exception
            Throw New Exception("SQL接続時エラー", ex)
            Return False
        End Try
        Return True
    End Function

    Private Function setParameter(ByVal param As clsDBParam) As NpgsqlParameter
        Dim prm As NpgsqlParameter
        Select Case param.type
            Case TYPE_DAT
                prm = New NpgsqlParameter(param.key.ToString, NpgsqlTypes.NpgsqlDbType.Date)
                If getString(param.val) = String.Empty Then
                    prm.Value = DBNull.Value
                Else
                    prm.Value = CDate(param.val)
                End If
            Case TYPE_NUM
                prm = New NpgsqlParameter(param.key.ToString, NpgsqlTypes.NpgsqlDbType.Numeric)
                If getString(param.val) = String.Empty Then
                    prm.Value = DBNull.Value
                Else
                    prm.Value = getDouble(param.val)
                End If
            Case Else
                prm = New NpgsqlParameter(param.key.ToString, NpgsqlTypes.NpgsqlDbType.Varchar)
                If getString(param.val) = String.Empty Then
                    prm.Value = DBNull.Value
                Else
                    prm.Value = param.val
                End If
        End Select
        Return prm
    End Function
お世話になります。
vb.net と PostgreSQLにてシステムを構築しています。
Insert のクエリを実行した際、NpgsqlParameterのValueに改行が含まれるものを渡した時に
以下のようなエラーとなります。
改行が含まれる場合は、エスケープを行う必要があるのでしょうか?

エラーメッセージ
>ERROR: 42804: 列"order_date"は型dateですが、式は型numericでした

catch したExceptionでのErrorSQL内容
>insert into bfl500tb_alt values(
>((E'1817')::numeric),
>((E'1')::numeric),
>((E'20121119-0001')::varchar),
>((E'本日発送')::varchar),
>((NULL)::date),
>((E'[客先要望:]
>配達日指定:2012-11-21(水)
>配達時間指定:19時~21時')::varchar),
>((E'16700')::numeric),
>((E'0')::numeric),
>((E'2012-11-08')::date),
>((E'01:54:12')::varchar),
>((E'1')::varchar),
>((E'1')::varchar),
>((E'xxx-xxxx')::varchar),
>((E'大阪府')::varchar),
>((E'大阪市中央区')::varchar),
>((E'○○○1-1-1')::varchar),
>((E'□□ □□')::varchar),
>((E'06-xxxx-xxxx')::varchar),
>((NULL)::varchar));

以下、コード

```
    Public Function createInsertSQL(ByRef param As ArrayList) As String
        Dim sql As New StringBuilder
        With sql
            .AppendLine("insert into bfl500tb_alt values(")
            .AppendLine("@order_id,")
            .AppendLine("@order_detail_no,")
            .AppendLine("@order_comments,")
            .AppendLine("@total,")
            .AppendLine("@tax,")
            .AppendLine("@shipping,")
            .AppendLine("@order_date,")
            .AppendLine("@order_postno,")
            .AppendLine("@order_pref,")
            .AppendLine("@order_city,")
            .AppendLine("@order_town,")
            .AppendLine("@order_name,")
            .AppendLine("@order_tel,")
            .AppendLine("@luggage_slipno);")
        End With

        param.Add(addSQLParam("order_id", order_id, TYPE_NUM))
        param.Add(addSQLParam("order_detail_no", order_detail_no, TYPE_NUM))
        param.Add(addSQLParam("order_no", order_no, TYPE_STR))
        param.Add(addSQLParam("order_sts", order_sts, TYPE_STR))
        param.Add(addSQLParam("delivery_date", delivery_date, TYPE_DAT))
        param.Add(addSQLParam("delivery_timezone", delivery_timezone, TYPE_STR))
        param.Add(addSQLParam("order_comments", order_comments, TYPE_STR))
        param.Add(addSQLParam("total", total, TYPE_NUM))
        param.Add(addSQLParam("tax", tax, TYPE_NUM))
        param.Add(addSQLParam("shipping", shipping, TYPE_NUM))
        param.Add(addSQLParam("order_date", order_date, TYPE_DAT))
        param.Add(addSQLParam("order_postno", order_postno, TYPE_STR))
        param.Add(addSQLParam("order_pref", order_pref, TYPE_STR))
        param.Add(addSQLParam("order_city", order_city, TYPE_STR))
        param.Add(addSQLParam("order_town", order_town, TYPE_STR))
        param.Add(addSQLParam("order_name", order_name, TYPE_STR))
        param.Add(addSQLParam("order_tel", order_tel, TYPE_STR))
        param.Add(addSQLParam("luggage_slipno", luggage_slipno, TYPE_STR))

        Return sql.ToString
    End Function

    Public Function execSQL(ByVal strSQL As String, ByVal params As ArrayList) As Boolean
        Try
            Using con As New NpgsqlConnection(conStr)
                con.Open()
                Using tran As NpgsqlTransaction = con.BeginTransaction
                    Try
                        Using com As New NpgsqlCommand(strSQL, con)
                            For Each param As clsDBParam In params
                                com.Parameters.Add(setParameter(param))
                            Next
                            com.Transaction = tran
                            com.ExecuteNonQuery()  '←ここでエラー
                        End Using
                        tran.Commit()
                    Catch ex As Exception
                        tran.Rollback()
                        Throw New Exception("SQL実行時エラー", ex)
                        Return False
                    End Try
                End Using
            End Using
        Catch ex As Exception
            Throw New Exception("SQL接続時エラー", ex)
            Return False
        End Try
        Return True
    End Function

    Private Function setParameter(ByVal param As clsDBParam) As NpgsqlParameter
        Dim prm As NpgsqlParameter
        Select Case param.type
            Case TYPE_DAT
                prm = New NpgsqlParameter(param.key.ToString, NpgsqlTypes.NpgsqlDbType.Date)
                If getString(param.val) = String.Empty Then
                    prm.Value = DBNull.Value
                Else
                    prm.Value = CDate(param.val)
                End If
            Case TYPE_NUM
                prm = New NpgsqlParameter(param.key.ToString, NpgsqlTypes.NpgsqlDbType.Numeric)
                If getString(param.val) = String.Empty Then
                    prm.Value = DBNull.Value
                Else
                    prm.Value = getDouble(param.val)
                End If
            Case Else
                prm = New NpgsqlParameter(param.key.ToString, NpgsqlTypes.NpgsqlDbType.Varchar)
                If getString(param.val) = String.Empty Then
                    prm.Value = DBNull.Value
                Else
                    prm.Value = param.val
                End If
        End Select
        Return prm
    End Function
```