.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
Problem with binding Oracle-BLOB to SQL-Parameters
Messages   Related Types
This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
Post a new message to this list...

Mirco
Hi,

First of all, I am a newbie to .NET and Oracle.
I would appreciate any help or hints which lead me in the right
direction.

I am building a Database converter. Basically what it should do, is
take a table (schema and content) from MS-SQL server and transfer it
to an Oracle 10g Database.
Creating a table in Oracle is not a problem.
In MS-SQL I have a column of type "TEXT" which I map to a "BLOB" on
the Oracle side.
Now in .NET I use parameterized SQL queries to insert data in Oracle
like this:
statement = "INSERT INTO scott.test1 (" +
"DWDOCID, " +
"DWPAGECOUNT, " +
"DWSTOREDATE, " +
"DWSTORETIME, " +
"KEYWORDS, " +
"COMMENT" +
") VALUES (" +
":DWDOCID, " +
":DWPAGECOUNT, " +
":DWSTOREDATE, " +
":DWSTORETIME, " +
":KEYWORDS, " +
":COMMENT" +
")";

A reader reads through the source table (MS-SQL), gets a row and
populates the SQL-Parameters for each column as follows:
while(reader.Read())
{
OracleCommand cmd = new OracleCommand(statement, connection);
                cmd.Parameters.Add("DWDOCID",OracleDbType.Varchar2);                cmd.Parameters.Add("DWPAGECOUNT",OracleDbType.Int32);                cmd.Parameters.Add("DWSTOREDATE",OracleDbType.Varchar2);                cmd.Parameters.Add("DWSTORETIME",OracleDbType.Varchar2);                cmd.Parameters.Add("KEYWORDS",OracleDbType.Varchar2);
cmd.Parameters.Add("COMMENT",OracleDbType.Blob);
cmd.Parameters["DWDOCID"].Value = reader["DWDOCID"];
cmd.Parameters["DWPAGECOUNT"].Value = reader["DWPAGECOUNT"];
string date = reader["DWSTOREDATE"].ToString();
Console.WriteLine("date: " + date);
cmd.Parameters["DWSTOREDATE"].Value = "16.05.2004";
cmd.Parameters["DWSTORETIME"].Value = reader["DWSTORETIME"];
cmd.Parameters["KEYWORDS"].Value = reader["KEYWORDS"];
cmd.Parameters["COMMENT"].Value = reader["COMMENT"];
cmd.ExecuteNonQuery();
}
Now, all the simple datataypes like Varchar2 and Int32 work perfectly.
The thing which makes problem is the columns of type
"OracleDbType.Blob".
When the line "cmd.ExecuteNonQuery();" is processed I get the error

Unhandled Exception: System.ArgumentException: Ungültiges
Parameter-Binding
Parameter name: COMMENT

OK, there is a German word "Ungültiges" which is probably "Invalid" in
English.

I have used the same approach for a MySQL-Database and here I was able
to bind Binary Data to SQP-Parameters. What is the deal with Oracle
not being able to handle these parameters.

Is there another approach to get Binary data from MS-SQL to Oracle
using ADO.NET.

Please help me with this.

Greetings Mirc
Reply to this message...
 
    
Frans Bouma [C# MVP] (VIP)
Mirco wrote:

[Original message clipped]

    TEXT is character data. You should map it to a CLOB field, not a BLOB field.
Furthermore, you don't cast the types read from the datareader. This might
cause a problem. A BLOB field has to be filled with a byte array. You
probably store a string in there with your current code.

        Frans.

--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP
Reply to this message...
 
    
Mirco (VIP)
"Frans Bouma [C# MVP]" wrote:

[Original message clipped]
Reply to this message...
 
    
Mirco (VIP)
Hi Frans,

Thank you for your advice.
You are writing "A BLOB field has to be filled with a byte array. You
probably store a string in there with your current code."
Does a CLOB field also needs to be filled with a byte array and if "yes" how
do i cast the reader object into a byte array.

Thanks again for an answer

Mirco

"Frans Bouma [C# MVP]" wrote:

[Original message clipped]
Reply to this message...
 
    
Frans Bouma [C# MVP] (VIP)
Mirco wrote:

[Original message clipped]

    No, CLOB fields can be filled with a string. Your sqlserver datareader will
contain a System.String value for the Text field, so you can put that value
directly into the CLOB parameter :)

        Frans.

[Original message clipped]

--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP
Reply to this message...
 
 
System.ArgumentException
System.Console
System.Data.OracleClient.OracleCommand
System.String




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification