CODEWITHSUNDEEP
.netado.netcharacter-encodingibm-midrange
T. Fabre
T. Fabre

Reputation: 1527

Inserting special characters in database with IBM.Data.DB2.ISeries provider

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 :

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 ?

Upvotes: 2

Views: 2825

Answers (1)

T. Fabre
T. Fabre

Reputation: 1527

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 :)

Upvotes: 1

Related Questions