Solved

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

Posted on 2008-10-16
5
1,288 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
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…
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 is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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

11 Experts available now in Live!

Get 1:1 Help Now