Solved

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

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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