I am trying to get data from an IBM Database using the IfxDataReader like this:
Using myCon As New IfxConnection("CONSTRING")
Dim myQuery = "SELECT decimalValue FROM exampletable"
Using myCmd As New IfxCommand(myQuery, myCon)
myCon.Open()
Using myReader As IfxDataReader = myCmd.ExecuteReader
While myReader.Read
' error occurs below
Dim myVariable As Double = myReader("decimalValue")
End While
End Using
End Using
End Using
I receive a System.FormatException at myReader("decimalValue"). Hovering my mouse over this piece of code during dubugging, will also show me this exception, even before executing the line of code. The exception has the following stack trace.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
at IBM.Data.Informix.IfxDataReader.GetSytemTypeFromCacheType(Type systemType, Type cacheEntryType, Object cacheEntry)
at IBM.Data.Informix.IfxDataReader.GetValue(Int32 i)
at IBM.Data.Informix.IfxDataReader.get_Item(String value)
I am using Imports IBM.Data.Informix in my file and have a reference to a DLL with the same name in my project. The DLL's file version is 4.0.1000.2 and its product version is 4.00.01000.2.
I have found this similar-looking problem, but checking the mentioned box here, did not do the trick. Dim b As Integer = Integer.Parse("1") resulting in "System.FormatException: 'Input string was not in a correct format.'"
Apparently this issue only occurs on my own pc. I have executed the exact same code on the Computers of two of my colleagues, as well as one of our servers, and none of them get the issue.
At first I though it something to do, with me not having an Informix Client SDK installed, as I have found a similar issue described on ibm.com which suggest updating the SDK: https://www.ibm.com/support/pages/apar/IC60254 But after installing an SDK it stilled didn't work and I also found out, that it works on the PC of a colleague who has no SDK installed. Therefore I am stumped, not having any clue as to why this issue occurs, on why only for me.
Does anyone have a clue, what might be causing this error?
CodePudding user response：
The issues here might be two:
- Is the value always valued? May be Nothing or DbNull?
- Have your PC/machine the same locale/language of your colleagues?
Hoping that it is one of those two issues this code will help resolving your problem. You have to set the correct “default decimal separator” of your stored data also the correct language/iso-code to convert/format it (in my SO is Italian, means the stored data have "," as decimal separator and language/iso-code is “it-IT”)
Let us know if this resolve :)
Private Function ConvertToDecimal(ByVal value As Object) As Decimal
If value Is DBNull.Value Then Return 0
If value Is Nothing Then Return 0
Dim dValue As String = CStr(value)
'Specify here the default decimal separator and locale that data was stored before
Dim localeDefaultSeparator As String = ","
Dim localeDefaultCultureId As String = "it-IT"
Dim separators As String = System.Text.RegularExpressions.Regex.Replace(dValue, "[0-9]", "")
Dim decimalseparator As String = ""
If Not String.IsNullOrEmpty(separators) Then decimalseparator = Char.ToString(separators.Last())
Dim style As System.Globalization.NumberStyles = System.Globalization.NumberStyles.AllowDecimalPoint Or System.Globalization.NumberStyles.AllowThousands
Dim provider As System.Globalization.CultureInfo = If(Not String.IsNullOrEmpty(decimalseparator) AndAlso decimalseparator = localeDefaultSeparator, New System.Globalization.CultureInfo(localeDefaultCultureId), System.Globalization.CultureInfo.InvariantCulture)
Dim retValue As Decimal
If Decimal.TryParse(dValue, style, provider, retValue) Then Return retValue
Return 0
End Function
Usage:
Dim myVariable As Double = ConvertToDecimal(myReader("decimalValue"))
CodePudding user response：
Expanding on what G3nt_M3caj said.
The format of some data types (like decimal or date) depends on your localized settings (language/territory). In English(US) the decimal separator is "." (thousands is a ",") but in other languages (and territories) they may be different. In Italian or Spanish is the other way around, a "," for decimal and a "." for thousands.
.NET will use the default settings from your Windows, but you can overrite that with System.Globalization.CultureInfo.
BUT, with Informix clients (like .NET and ODBC) there are other settings that controls how the client returns the data to the application. For example, CLIENT_LOCALE is used to specify which language, territory and codeset you want the driver (.NET in this case) to return to data to the application.
If you don't specify CLIENT_LOCALE it will take the default "EN_US.1252" (1252 on Windows, this varies from platform to platform) This means that the driver will "expect" decimals in EN_US format ("." and ",") If your Windows box is not using the same settings, you will get an error while trying to get the decimal from the database (and the same applies to datetime)
Look at the example below:
// ---- dec2.cs ----
// compile with: csc.exe /R:%INFORMIXDIR%\bin\netf40\IBM.Data.Informix.dll /nologo dec2.cs
//
using System;
using System.IO;
using System.Data;
using System.Text;
using System.Threading;
using IBM.Data.Informix;
class sample {
static void Main(string[] args) {
IfxConnection conn;
try {
//conn = new IfxConnection("Server=ids1410;Database=sysmaster;CLIENT_LOCALE=en_US.1252");
conn = new IfxConnection("Server=ids1410;Database=sysmaster;CLIENT_LOCALE=it_IT.1252");
Console.WriteLine(conn.ConnectionString);
conn.Open();
IfxCommand cmmd = conn.CreateCommand();
cmmd.CommandText = "SELECT 1.234 from table (set{1}) ";
Console.WriteLine(cmmd.CommandText);
IfxDataReader drdr;
drdr = cmmd.ExecuteReader();
drdr.Read();
Console.WriteLine("System.Globalization.CultureInfo(\"en-US\")");
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try {
Console.WriteLine("\tGetDecimal (en-US):\t" drdr.GetDecimal(0));
}
catch (Exception e) {
Console.WriteLine("\t" e.Message);
Console.WriteLine("\t" e.StackTrace);
}
Console.WriteLine("System.Globalization.CultureInfo(\"it-IT\")");
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("it-IT");
try {
Console.WriteLine("\tGetDecimal (it-IT):\t" drdr.GetDecimal(0));
}
catch (Exception e) {
Console.WriteLine("\t" e.Message);
Console.WriteLine("\t" e.StackTrace);
}
conn.Close();
}
catch (Exception e) {
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
}
}
// ---- dec2.cs ----
If you run it with the default en_US.1252, you will be able to do a GetDecimal() only if the CultureInfo is set to "en-US" because decimal format has to match (what the client returns uses and what .net expects).
d:\Infx\work\cs>dec2
Server=ids1410;Database=sysmaster;CLIENT_LOCALE=en_US.1252
SELECT 1.234 from systables
System.Globalization.CultureInfo("en-US")
GetDecimal (en-US): 1.234
System.Globalization.CultureInfo("it-IT")
Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
at IBM.Data.Informix.IfxDataReader.GetDecimal(Int32 i)
at sample.Main(String[] args)
d:\Infx\work\cs>
If you change the setting to "it-IT" (Italian/Italy) it will fail with the exception you mentioned in your original post. because they won't match. .NET expects a "1,234" but it gets a "1.234"
If you change the CLIENT_LOCALE value in the connetion string, it will be the other way around:
d:\Infx\work\cs>dec2
Server=ids1410;Database=sysmaster;CLIENT_LOCALE=it_IT.1252
SELECT 1.234 from systables
System.Globalization.CultureInfo("en-US")
Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
at IBM.Data.Informix.IfxDataReader.GetDecimal(Int32 i)
at sample.Main(String[] args)
System.Globalization.CultureInfo("it-IT")
GetDecimal (it-IT): 1,234
d:\Infx\work\cs>
This link talks about this (in relation to .NET) but it can get a little bit more complicate as there are other Informix settings that can alter that like DBMONEY. DBMONEY can be used to specify the decimal format without having to specify CLIENT_LOCALE (so no other things are affected like codeset order).