Steve & all,
I ended up decoding the string.
private static string GetTitleWithoutSpecialCharacters(string title)
{
Byte[] encodedBytes = ascii.GetBytes(title);
string decodedString = ascii.GetString(encodedBytes);
return decodedString;
}
Unfortunately one of those moments where I wrote some code and it seems to work. But I don't understand the whole picture. I'll be following up on the references provided.
Next opportunity is to replace the SqlBulkCopy with a DB2 equivalent. I think it's similar to an array insert. But I'll be conquering the beast on Monday.
Thanks all.
Vicki
------------------------------
message: 6
date: Fri, 3 Apr 2015 13:55:55 -0400
from: Steve Richter <stephenrichter@xxxxxxxxx>
subject: Re: C# insert data with special characters to DB2? (Steve
Richter)
Vicki,
I would try to log the code as it runs, use try ... catch to catch the
errors as they occur. I do not understand CCSID very well so I would just
use trial and error, try to see what the data looks like in the updates
that are failing.
Here is some code to try. The TextToHex method will dump the string fields.
Maybe that helps identify the error.
using System;
using System.Collections.Generic;
using System.Data.Odbc;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
UpdateProducts();
}
private static void UpdateProducts()
{
var userDsn = "abc";
string myDocsPath =
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
string logFilePath = System.IO.Path.Combine(myDocsPath,
"MigrateLog.txt");
System.IO.File.CreateText(logFilePath);
var products = new { id = "abc", title = "efg", sku = "123" };
string connString = "DSN=" + userDsn + ";";
var conn = new OdbcConnection(connString);
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = "update INproductstb set id=@id, " +
"title=@title, " +
" where LampsSku = @Sku";
cmd.Parameters.AddWithValue("@id", products.id);
string title37 = TextToHex(products.title);
Console.WriteLine(title37);
cmd.Parameters.AddWithValue("@title", products.title);
cmd.Parameters.AddWithValue("@Sku", products.sku);
cmd.ExecuteNonQuery();
}
catch (Exception excp)
{
var textLines = new List<string>();
textLines.Add("update error. " + "Sku:" + products.sku);
textLines.Add("title:" + products.title);
textLines.Add("title in hex:" + TextToHex(products.title));
textLines.Add(excp.ToString());
System.IO.File.AppendAllLines(logFilePath, textLines);
}
conn.Close();
}
public static string TextToHex(string Text)
{
StringBuilder sb = new StringBuilder();
foreach (var ch1 in Text)
{
var by1 = System.Convert.ToByte(ch1);
var hexChar = by1.ToString("X");
sb.Append(hexChar + " ");
}
return sb.ToString();
}
}
}
On Fri, Apr 3, 2015 at 11:40 AM, Vicki Wilson <VWilson@xxxxxxxxxxxxx> wrote:
I should know better to post a question at the end of the day when I'm
frustrated. This is screen-scraped data from a vendor api call. I don't
actually need the special characters. I only have about 14 out of every
1000 records that have the one problematic column.
What I would like to do is keep the default ccsid on the db2 table and
column - this would avoid any programming issues.
I noticed when I did a console.writeline that the data had questions marks
where the special characters were. This seems like it would be a great
solution I just don't know how to make that happen in the code.
Per Steve's request here's a small piece of the code:
cmd.CommandText = "update INproductstb set id=@id, " +
"title=@title, " +
" where LampsSku = @Sku";
cmd.Parameters.AddWithValue("@id",
apiData.response.products[0].id);
string title37 = apiData.response.products[0].title.ToString();
Console.WriteLine(title37);
cmd.Parameters.AddWithValue("@title",
apiData.response.products[0].title);
The title field is the problem field - it seems like I should be able to
do something with the string - but I'm not sure what.
Apologies in advance - I've never posted code here - I thought there was a
FAQ or some guidelines but I wasn't able to find them.
Thanks.
Vicki
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
------------------------------
Subject: Digest Footer
midrange.com
