插入数据库特殊字符IBM.Data.DB2.ISeries提供商 [英] Inserting special characters in database with IBM.Data.DB2.ISeries provider

问题描述

我一直有一个问题，写在VB.Net一个应用程序，依靠在iSeries数据库。用户可以保存笔记，这（很）经常拷贝/从他们的邮箱中粘贴。

但是，许多邮件包含触发iDb2ConversionException当数据被保存到数据库中无效字符。

有关目前，我擦洗数据，与之相配的HTML实体替换无效字符，但我真的不喜欢这种方法：

• 我要保持一个转换列表，即使我设法覆盖大部分无效字符，新的总是冒出来。
• 如果我运行在客户端访问使用STRSQL相同的命令，其实我可以插入这些无效字符。
• 我们有许多固定长度的字段，并用HTML实体替换字符使得很多难以执行在UI层面最大字符串长度

使用ADO.NET同样的命令工作，如果我不使用参数（但后来我不得不擦洗用户输入了，所以一切的一切......它只是把问题移到其他地方）

 暗淡命令= connection.CreateCommand（）
command.CommandText =UPDATE表SET域='€€€€€]]]]]]]]]]]°°°°°°§§§§§'在哪里......
command.ExecuteNonQuery（）执行成功

昏暗的命令= connection.CreateCommand（）
command.CommandText =UPDATE表SET域= @value哪里......
command.DeriveParameters（）
command.Parameters（@值）。值='€€€€€]]]]]]]]]]]°°°°°°§§§§§'
command.ExecuteNonQuery（）抛出iDb2ConversionException
 

我的连接字符串如下：

<$p$p><$c$c>Datasource=server;DataBase=DBNAME;DefaultCollection=DBCOLLECTION;HexParserOption=Binary;LibraryList=LIBRARIES;Naming=SQL;DataCom$p$pssion=True;AllowUnsupportedChar=true;

有没有一个成功地写这些字的DB没有得到那个异常的任何选项？

解决方案

好了，终于得到了它。它所需要的是改变物理文件的CCSSID（它已编译CCSID 297一段时间）。

  CHGPF FILE（MYLIB / MYFILE）CCSID（XXXX）
 

在我的情况，我使用CCSID 65535。然而，这一次，将字符串存储就没有那么简单，我要提取我的字符串的字节数组：

 暗淡命令= connection.CreateCommand（）
command.CommandText =UPDATE表SET域= @value哪里......
command.DeriveParameters（）
command.Parameters（@值）。值= Encoding.Uni code.GetBytes（'€€€€€]]]]]]]]]]]°°°°°°§§§§ §'）
command.ExecuteNonQuery（）的作品...
 

但好事是，我现在可以存储几乎任何字符：）

I've been having an issue with an app written in VB.Net that relies on an iSeries database. Users can save notes, which (very) often are copy/pasted from their mailbox.

But many mails contain invalid characters that trigger iDb2ConversionException when the data is saved to the database.

For the time being, I scrub the data, replacing invalid characters with matching html entities, but I really dislike that approach :

• I have to maintain a conversion list, and even though I managed to cover most invalid characters, new ones always come up.
• If I run the same command using STRSQL in Client Access, I can actually insert those invalid characters.
• We have many fixed-length fields, and replacing chars with HTML entities makes it a lot harder to enforce maximum string lengths at the UI level

The same command works with ADO.NET if I don't use parameters (but then I have to scrub user input again, so all in all... it just moves the problem elsewhere)

Dim command = connection.CreateCommand()
command.CommandText = "UPDATE table SET field = '€€€€€]]]]]]]]]]]°°°°°°§§§§§' where ...."
command.ExecuteNonQuery() ' Executes successfully '

Dim command = connection.CreateCommand()
command.CommandText = "UPDATE table SET field = @value where ...."
command.DeriveParameters()
command.Parameters("@value").Value = "'€€€€€]]]]]]]]]]]°°°°°°§§§§§'"
command.ExecuteNonQuery() ' Throws iDb2ConversionException '

My connection string is as follows :

Datasource=server;DataBase=DBNAME;DefaultCollection=DBCOLLECTION;HexParserOption=Binary;LibraryList=LIBRARIES;Naming=SQL;DataCompression=True;AllowUnsupportedChar=true;

Is there any option available to successfully write those characters to the DB without getting that exception ?

解决方案

Well, finally got it. All it takes is to change the CCSSID of the physical file (it had been compiled a while with CCSID 297).

CHGPF FILE(MYLIB/MYFILE) CCSID(xxxx)

In my case, I'm using CCSID 65535. However, with this one, storing strings is not as straightforward, I have to extract the byte array of my string :

Dim command = connection.CreateCommand()
command.CommandText = "UPDATE table SET field = @value where ...."
command.DeriveParameters()
command.Parameters("@value").Value = Encoding.Unicode.GetBytes("'€€€€€]]]]]]]]]]]°°°°°°§§§§§'")
command.ExecuteNonQuery() ' Works... '

But the good thing is that I can store virtually any character now :)

这篇关于插入数据库特殊字符IBM.Data.DB2.ISeries提供商的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！