ASP.NET ODBC Oracle error “ORA-01008: not all variables bound” solved

The other day I decided to try my hands on using named parameters in a query to query an Oracle database using ODBC when I got this message:

ERROR [07001] [Microsoft][ODBC driver for Oracle][Oracle]
ORA-01008: not all variables bound

From what I’ve found, it looks like if you are querying an Oracle database using the System.Data.Odbc class, you can only use unnamed parameters (defined by "?"). In order to use named parameters, you’ll need to use the System.Data.OracleClient class instead (note you’ll need to add that reference manually to your project).

Here’s what I mean:

public DataTable queryDatabase_odbc () {
	OdbcConnection con = new OdbcConnection("Driver={Microsoft ODBC for Oracle};server=DBSERVER;uid=xxUserxx;pwd=xxPwdxx");
	con.Open();
	OdbcCommand ps = new OdbcCommand("alter session set current_schema = xxdbschemaxx", con);
	ps.Prepare();
	ps.ExecuteNonQuery();

	OdbcCommand ps2 = new OdbcCommand("select * from aTable where field = :AParam", con);
	ps2.Prepare();
	ps2.Parameters.Add("AParam",OdbcType.VarChar).Value="Some String Value";
	DataSet rs = new DataSet();
	OdbcDataAdapter da = new OdbcDataAdapter(ps2);
	da.Fill(rs);	// This will throw an exception
	return rs.Tables[0];
}

// The exact same code as above, just using the the OracleClient class
public DataTable queryDatabase_ora () {
	OracleConnection con = new OracleConnection("server=DBSERVER;uid=xxUserxx;pwd=xxPwdxx;");
	con.Open();
	OracleCommand ps = new OracleCommand("alter session set current_schema = xxdbschemaxx", con);
	ps.Prepare();
	ps.ExecuteNonQuery();

	OracleCommand ps2 = new OracleCommand("select * from aTable where field = :AParam", con);
	ps2.Prepare();
	ps2.Parameters.Add("AParam",OracleType.VarChar).Value="Some String Value";
	DataSet rs = new DataSet();
	OracleDataAdapter da = new OracleDataAdapter(ps2);
	da.Fill(rs);
	return rs.Tables[0];
}

However, I do have to point out there is one draw-back with using the OracleClient class – you can no longer use unnamed parameters

But in a way, you do get more out of using named parameters than unnamed parameters (like when you have a query looking up the same value on multiple fields). Besides, you can always (sort of) hack named parameters to act like unnamed parameters by using an integer string as name…


Here a page I found with details in using parameterized queries in different DBMS:
http://weblogs.asp.net/cibrax/archive/2006/09/28/Parameterized-Queries-_2800_Oracle_2C00_-SQLServer_2C00_-OleDb_2900_.aspx

Windows Live Hotmail POP3 access

Mail

Woohoo! You can now access your Hotmail account via POP3! Here are the details:

POP server:

pop3.live.com:995 with SSL

SMTP Server:

smtp.live.com:587 with TLS

Guess I won’t need my WebMail Thunderbird Add-on anymore!

TV Recommendation – Dollhouse

Dollhouse title screen

Here’s another TV show I’d recommend – Dollhouse.

The story is based on a private underground organization who engages on mission-based tasks for clients using people called "Active"s, who have had their original personalities and memories wiped clean, and replaced with new ones specifically selected at the start of each mission (or "engagement"). At the end of each engagement, the Active’s memories are wiped clean and returned to a child-like state.

The name of the shown comes from the fact that the Actives live inside a house with a controlled environment between engagements during which they are in a child-like state of mind, analogous to Dolls in a Dollhouse.

As the plot develops, the main character "Echo" (played by Eliza Dushku), started to show sings that she may have retained certain character traits of the personas she inherited during her previous engagements. There are of course other interesting twists to do with the ugly history of the organization and the organization’s potential (but currently unknown) enemies.

I have no idea how the story is going to unfold, but I sure am looking forward to the next episode!