Solved

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

Posted on 2008-10-16
5
1,274 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:anuragal
  • 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:anuragal
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:anuragal
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:anuragal
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now