Solved

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

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get distinct values from excel or comm seperated file 4 30
VB.NET 2008 Winforms Signing 13 31
SSIS On fail action 5 38
VS 2017 18 42
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…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

860 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