Solved

how to insert data in sybase thorugh ado.net using parameters

Posted on 2008-10-16
5
1,342 Views
Last Modified: 2013-11-07
I have written following code in .Net 2.0 using odbc connection to sybase database

String sql = "INSERT INTO BLOB_DATA (BLOB_DATA_BLOB) Values (@blob_data_blob)";
dbCommand.CommandText = sql;
db.AddInParameter(dbCommand, "blob_data_blob", DbType.Binary, blobData[0]);
db.ExecuteNonQuery(dbCommand, transaction);

After executing i am getting error "ERROR [42S22] [Sybase][ODBC Driver][Adaptive Server Anywhere]Column '@blob_data_blob' not found". Just want to know whether i can do this in sybase as it is working fine if i  choose sql server as my database.

Anurag
0
Comment
Question by:Anurag Agarwal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 22731809
First off, when you write "@blob_data_blob", Sybase is expecting a T-SQL variable by that name has been declared.  That can only happen in the context of a SQL block (Begin ........ End) or a stored procedure.

Second, Sybase does not support a BLOB variable type.

Third, you are using dynamic SQL instead of a prepared statement.  Aside from dropping a huge literal (only useful for a CLOB) into the statement itself, you cannot pass a BLOB with Dynamic SQL; at least no in a single statement.  You would have to use multiple statements and the writetext command.

ODBC can do all the messy stuff required to handle a BLOB however, I believe you need to use a real, prepared statement and fix up the parameter substitution.  I don't believe the @variable and AddInParameter combination is correct.

Regards,
Bill
0
 
LVL 11

Author Comment

by:Anurag Agarwal
ID: 22756824
I am doing all this through ODBC only.

So you are saying that i can do this by adding parameter to my command. Actually i am able to do it by fetching it through plain sql. But fetching blob in plain sql can be a performance issue so i decided to move it to parameter instead of fetching it into plain sql.

but somehow it is not working. I reffered the following url for this
http://www.codeproject.com/KB/database/odbcnet_syb_stp.aspx

Anurag
0
 
LVL 11

Author Comment

by:Anurag Agarwal
ID: 22756838
PLEASE READ

So you are saying that i can do this by adding parameter to my command.  

AS

So you are saying that i can not do this by adding parameter to my command.

:-)
Anurag
0
 
LVL 19

Expert Comment

by:grant300
ID: 22766973
The example code you are following from the link is all for STORED PROCEDURES, not dynamic SQL.

And, no, you can't just jam a parameter on a Dynamic SQL command and have this work.

There is a technique for doing substitution in a prepared SQL statement.  I do not know the syntax however, you "prepare" the statement with place keepers for each of the elements you wish to substitute at runtime.  Kind of like what you tried to do with the @variable in the text but that was the wrong syntax and was done on a dynamic SQL statement instead of a prepared statement.

Check the documentation around prepared SQL statements/commands and see what you find.

Regards,
Bill
0
 
LVL 11

Author Comment

by:Anurag Agarwal
ID: 22829249
Hi Bill,

Thanks for your reply, i was able to insert the values in Sybase by using inline dynamic sql.

String sql = "INSERT INTO <<Tabke Name>> (<<column name>>) Values (?)";
dbCommand.CommandText = sql;
db.AddInParameter(dbCommand, "<<column name>>", DbType.Binary, <<value>>);
db.ExecuteNonQuery(dbCommand, transaction);

It works fine

but not able to search for solution for selecting the data back from sybase using inline dynamic sql.

Anyways thanks for your response.

Anurag
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question