.net, parameters and ms enterprise library

Hi, 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

Hi, 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:public void AddInParameter ( DbCommand command, string name, DbType dbType, Object value ) Looking at your code, you wrote: > 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 > From: pmerloti@gmail.com> Subject: .net, parameters and ms enterprise library> Date: Wed, 18 Jun 2008 17:39:14 -0700> To: informix-list@iiug.org> > Hi,> > 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> _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ The i’m Talkathon starts 6/24/08.  For now, give amongst yourselves. http://www.imtalkathon.com?source=TXT_EML_WLH_LearnMore_GiveAmongst

Padu wrote: > > 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: > > string sqlCommand = "select * from users where id = :id"; > 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

[SNIP]> If the output of Gumby's impressive crystal ball didn't work, I'd ask in > news:microsoft.public.dotnet.languages.csharp> > -- > RGBWell if you bothered to use a tool like Google and input a good search string you might find some examples.... 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 Microsoft.Practices.EnterpriseLibrary.Data; 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. _________________________________________________________________ Introducing Live Search cashback . It's search that pays you back! http://search.live.com/cashback/?&pkw=form=MIJAAF/publ=HMTGL/crea=introsrchcashback

oh and here's one more example: 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, "bill@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); But hey! What do I know? (Python, Java, C/C++, Objective C, and others that I seem to remember but would proably want to take a refresher course...) Naw, I don't know a thing. ;-) HTH -G> From: pmerloti@gmail.com> Subject: .net, parameters and ms enterprise library> Date: Wed, 18 Jun 2008 17:39:14 -0700> To: informix-list@iiug.org> > Hi,> > 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> _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Introducing Live Search cashback . It's search that pays you back! http://search.live.com/cashback/?&pkw=form=MIJAAF/publ=HMTGL/crea=introsrchcashback

Ian Michael Gumby wrote: > > > If the output of Gumby's impressive crystal ball didn't work, I'd ask in > > news:microsoft.public.dotnet.languages.csharp > > > Well if you bothered to use a tool like Google and input a good search > 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

Ah but sarcasm is my middle name? 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: RedGrittyBrick@SpamWeary.foo > Subject: Re: .net, parameters and ms enterprise library > To: informix-list@iiug.org > > Ian Michael Gumby wrote: > > > > > If the output of Gumby's impressive crystal ball didn't work, I'd ask in > > > news:microsoft.public.dotnet.languages.csharp > > > > > Well if you bothered to use a tool like Google and input a good search > > 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 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Need to know now? Get instant answers with Windows Live Messenger. http://www.windowslive.com/messenger/connect_your_way.html?ocid=TXT_TAGLM_WL_Refresh_messenger_062008

On Jun 19, 3:58 am, RedGrittyBrick <RedGrittyBr...@SpamWeary.foo> wrote: > Padu wrote: > > > 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: > > >    string sqlCommand = "select * from users where id = :id"; > >    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- Hide quoted text - > > - 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...

On Jun 19, 9:13 am, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > Ah but sarcasm is my middle name? <snip> > ...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 :-)

On Jun 19, 3:44 am, Ian Michael Gumby wrote: > 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... ;-) 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.

On Jun 19, 12:38 pm, Padu <pmerl...@gmail.com> wrote: > On Jun 19, 3:44 am, Ian Michael Gumby wrote: > > > 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... ;-) > > 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. 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.

On Jun 19, 4:40 am, Ian Michael Gumby > 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 Microsoft.Practices.EnterpriseLibrary.Data; >  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: E42000: (-201) A syntax error has occurred.

Ok, I think I found a way... 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); }