我一直有一个问题,写在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
我的连接字符串如下:
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 levelThe 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 :)