.net, parameters and ms enterprise library
Padu
I've been stuck all day long with something I thought it would be easy
to do.
I'm using MS Enterprise Library and C# (.net 3.5) to retrieve some
data from informix (via OleDB). If I create a statement like
Database db = Program.GetDatabase(myDbName);
try
{
int id = 386
string sqlCommand = "select * from users where id = "
+ id;
DbCommand dbCommand =
db.GetSqlStringCommand(sqlCommand);
DataSet ds = db.ExecuteDataSet(dbCommand);
Console.WriteLine(String.Format("Username = {0}",
ds.Tables[0].Rows[0]["name"]));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
It works fine, thus proving that I have connection to the informix.
Now, I don't want to use the concatenation for obvious reasons, so I
tried to use parameters... I tried with @ and :, I'll post here my
version with :, but it doesn't work:
Database db = Program.GetDatabase(_cmecfDatabaseName);
try
{
string sqlCommand = "select * from users where id
= :id";
DbCommand dbCommand =
db.GetSqlStringCommand(sqlCommand);
db.AddInParameter(dbCommand, ":id", DbType.Int32,
386);
DataSet ds = db.ExecuteDataSet(dbCommand);
Console.WriteLine(String.Format("Username = {0}",
ds.Tables[0].Rows[0]["name"]));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
It catches me the following exception:
E42000: (-201) A syntax error has occurred.
Anybody had the same problem?
Cheers
Ian Michael Gumby
Doing a quick look on Google:
Found a source that used the @ symbol for replacement:
database.AddInParameter(clearCommand, "@BaseName", DbType.String, baseName);
And in another site:
Looking at your code, you wrote:public void AddInParameter ( DbCommand command, string name, DbType dbType, Object value )
> DbCommand dbCommand =
> db.GetSqlStringCommand(sqlCommand);
> > DbCommand dbCommand =
> db.GetSqlStringCommand(sqlCommand);
> db.AddInParameter(dbCommand, ":id", DbType.Int32,
> 386);
Ok, so lets change a couple of things.
your local variable to my_id...
So your script would look like
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
db.AddInParameter(dbCommand, "@id", DbType.Int32, my_id);
I'm not sure if you have to change your query statement to use a ':' or an '@' but my guess is that you use a ':' id in the string and the parser looks for the substitution '@id' and replaces it with the contents in my_id.
But hey! What do I know? I've never played with dot net just a host of other languages.... But I did stay at a Holiday Inn one time long ago... ;-)
HTH
-G
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
The i’m Talkathon starts 6/24/08. For now, give amongst yourselves. Learn More
RedGrittyBrick
>
> I'm using MS Enterprise Library and C# (.net 3.5) to retrieve some
> data from informix (via OleDB).
>
>
> I tried to use parameters... I tried with @ and :, I'll post here my
> version with :, but it doesn't work:
>
> DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
> db.AddInParameter(dbCommand, ":id", DbType.Int32, 386);
>
> It catches me the following exception:
> E42000: (-201) A syntax error has occurred.
>
>
> Anybody had the same problem?
No, but I think I can tell the difference between an Informix-SQL
question and a C# API question.
If the output of Gumby's impressive crystal ball didn't work, I'd ask in
news:microsoft.public.dotnet.languages.csharp
--
RGB
Ian Michael Gumby
[SNIP]
> If the output of Gumby's impressive crystal ball didn't work, I'd ask in
> news:microsoft.public.dotnet.languages.csharp
>
> --
> RGB
Write generic ADO.NET code - data provider independent
When using the Microsoft Data Access Application Block if you want to execute a SQL statement that requires parameters you need to add specific-type references to the data provider that you are using. If you want to develop .NET code compatible with different database vendors or if you need to change from database vendor in the future you would have to change this specific-type references in your application code.1Using thi example,using Microsoft.Practices.EnterpriseLibrary.Data; 2
You can see that they use @ and not :. Ok interesting enough.
Also note that the substitution parameter outside of the string isn't in all caps. That would
imply that the variable susbstitution will take the 2nd parameter and upshift it in
both the string and the input variable name and then match on that.
Also note that you don't necessarily need to have a variable as the last object. You could put a string
literal in its place.
But hey! What do I know?
I just did this in less time than it takes for my coffee to cool.
Introducing Live Search cashback . It's search that pays you back! Try it Now
Ian Michael Gumby
http://www.davidhayden.com/blog/dave/archive/2007/01/27/TransactionScopeDataAccessApplicationBlock.aspx
And from this code snippet, you only need to use the @ symbol to indicate variable replacement in the sql string. And its optional in the object's name as seen here"
string insertCustomerSql = "INSERT INTO Customers
(Name, EmailAddress) VALUES (@Name,@EmailAddress)
SELECT @CustomerId = SCOPE_IDENTITY()"; string insertOrderSql = "INSERT INTO Orders (CustomerId,
Number, OrderDate) VALUES (@CustomerId, @Number,
@OrderDate)"; using (TransactionScope scope =
new TransactionScope(TransactionScopeOption.RequiresNew)) { DbCommand insertCustomerCommand =
database.GetSqlStringCommand(insertCustomerSql); database.AddInParameter(insertCustomerCommand,
"Name", DbType.String, "Bill Doe"); database.AddInParameter(insertCustomerCommand,
"EmailAddress", DbType.String, "bi...@doe.com"); database.AddOutParameter(insertCustomerCommand,
"CustomerId", DbType.Int32, 4); database.ExecuteNonQuery(insertCustomerCommand); int customerId = (int)database.GetParameterValue
(insertCustomerCommand, "CustomerId"); DbCommand insertOrderCommand =
database.GetSqlStringCommand(insertOrderSql); database.AddInParameter(insertOrderCommand,
"CustomerId", DbType.Int32, customerId); database.AddInParameter(insertOrderCommand,
"Number", DbType.String, "12345"); database.AddInParameter(insertOrderCommand, "OrderDate",
DbType.DateTime, System.DateTime.Now); database.ExecuteNonQuery(insertOrderCommand);
What do I know?
Naw, I don't know a thing. ;-)
HTH
-G
> From: pmer...@gmail.com
> Subject: .net, parameters and ms enterprise library
> Date: Wed, 18 Jun 2008 17:39:14 -0700
> To: inform...@iiug.org
>
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
RedGrittyBrick
>
> > If the output of Gumby's impressive crystal ball didn't work, I'd ask in
> > news:microsoft.public.dotnet.languages.csharp
> >
> string you might find some examples....
>
My intent was to encourage the OP to find an appropriate forum for any
further C# API questions.
You probably mistook my "impressive" for sarcasm instead of the genuine
praise I intended. You crusty old git :-)
--
RGB
Ian Michael Gumby
Or was that Shirley? ;-)
I don't mind the C# and .net questions here.
I would rather encourage it since its something I haven't really played with, and if those who are forced to use such a substandard language and interface are enlightened to use a *real* database, I feel that we should help them as much as possible. ;-)
I mean, it beats watching the grass grow as we wait for a TPC-E benchmark from IBM.
BTW, do you get the feeling that Ambush says one thing to the loyal crowds of IDS and then another to the DB2 side of the house?
But hey! What do I know? All my sources are old crusty men who've decided to stay because life in Mass. just would be just boring with nothing else to do. ;-)
Right Stu? :-)
> Date: Thu, 19 Jun 2008 15:03:31 +0100
> From: RedGrit...@SpamWeary.foo
> Subject: Re: .net, parameters and ms enterprise library
> To: inform...@iiug.org
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Padu
wrote:
>
> - Show quoted text -
I'm just asking here because this works with other DB's (MS SQL for
example)... But maybe the problem is not informix, but the OleDb
driver? I don't know...
Padu
> Ah but sarcasm is my middle name?
> ...and if those who are forced to use such a substandard language and interface are enlightened to use a *real* database ...
now I see sarcasm :-)
Padu
> Ok, so lets change a couple of things.
>
> your local variable to my_id...
>
> So your script would look like
> DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);db.AddInParameter(dbCommand, "@id", DbType.Int32, my_id);
>
Nope, that didn't work. And I remember I tried that yesterday a couple
of times... I will try the suggestions below.
If I only had a way to see what statement is being sent to informix,
or what informix is receiving, that would be easier.
VG
You can use onmode -I 201 and run the app again, this will cause a af
file and a mem dump to be created. You can look at it to see what has
been sent to the server
Alternatively, you can try turning SQLIDEBUG on.
Padu
> 2using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
> 3using System.Data.SqlClient;
> 4
> 5SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase("DemoDbSqlClient");
> 6
> 7SqlCommand command = (SqlCommand)db.GetSqlStringCommand(
> 8 "SELECT * FROM CUSTOMER WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME");
> 9
> 10db.AddInParameter(command, "@FirstName", SqlDbType.VarChar, "Luis");
> 11command.Parameters[0].Size = 50;
> 12db.AddInParameter(command, "@LastName", SqlDbType.VarChar, "Ramirez");
> 13command.Parameters[1].Size = 50;
> 14
> 15this.GridView1.DataSource = db.ExecuteReader(command);
> 16this.GridView1.DataBind();
> Using thi example,You can see that they use @ and not :. Ok interesting enough.Also note that the substitution parameter outside of the string isn't in all caps. That wouldimply that the variable susbstitution will take the 2nd parameter and upshift it inboth the string and the input variable name and then match on that. Also note that you don't necessarily need to have a variable as the last object. You could put a string literal in its place. But hey! What do I know?I just did this in less time than it takes for my coffee to cool.
That works.... for MS SQL. For informix, the first problem is that
obviously you can't cast to SqlCommand. Using the generic database
(Database class), informix keep responding with syntax error:
Padu
ditch the enterprise library and use the IBM drivers directly... this
one now works:
try
{
string IfxConnectionString =
"Host=blablabla; " +
"Service=blablabla; " +
"Server=blablabla; " +
"Database=blablabla; " +
"User Id=blablabla; " +
"Password=blablabla; ";
IfxConnection myIfxConn = new
IfxConnection(IfxConnectionString);
myIfxConn.Open();
IfxCommand myIfxCmd = new IfxCommand();
myIfxCmd.Connection = myIfxConn;
myIfxCmd.CommandText = "SELECT * FROM USERS WHERE ID
= ?;";
myIfxCmd.CommandType = CommandType.Text;
myIfxCmd.CommandTimeout = 1200;
myIfxCmd.Parameters.Add("ID", IfxType.Integer);
myIfxCmd.Parameters["ID"].Value = 386;
IfxDataReader reader = myIfxCmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(String.Format("id={0},
name={1}",reader["prid"],reader["name"]));
}
myIfxConn.Close();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
Padu
This one works good as well....
So now I wonder... the '?' thing... is a requirement from who? The
informix driver? Or the DB itself?
Console.WriteLine("Test Parameter");
Console.WriteLine("===========================");
Database db =
DatabaseFactory.CreateDatabase(_cmecfDatabaseName);
try
{
string sqlCommand = "SELECT * FROM USERS WHERE PRID
= ?;";
DbCommand dbCommand =
db.GetSqlStringCommand(sqlCommand);
int prid = 386;
db.AddInParameter(dbCommand, "prid", DbType.Int32,
prid);
Art Kagel
Art
}
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.