Oracle input parameter of type string problem

[Guest]

I created a new Web app and added a label and a button to the page.

I added the following code which compiles and executes correctly and returns
records:

-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim OracleSelectCommand1 As System.Data.OracleClient.OracleCommand
Dim OracleConnection1 As System.Data.OracleClient.OracleConnection
Dim OracleDataAdapter1 As System.Data.OracleClient.OracleDataAdapter
Dim dt As New Data.DataTable("DenseReport")
OracleSelectCommand1 = New System.Data.OracleClient.OracleCommand
OracleConnection1 = New System.Data.OracleClient.OracleConnection
OracleDataAdapter1 = New System.Data.OracleClient.OracleDataAdapter

OracleSelectCommand1.CommandText = "SELECT DRE_YEAR FROM DENSEREPORT
WHERE DRE_YEAR = '2001'"

OracleConnection1.ConnectionString = "user id=blah;data
source=blah;password=blah"
OracleSelectCommand1.Connection = OracleConnection1
OracleDataAdapter1.SelectCommand = OracleSelectCommand1
OracleDataAdapter1.Fill(dt)
Label1.Text = dt.Rows.Count.ToString
End Sub
-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

The query above returns records. The following code does not, but it doesn't
cause an error.

-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim OracleSelectCommand1 As System.Data.OracleClient.OracleCommand
Dim OracleConnection1 As System.Data.OracleClient.OracleConnection
Dim OracleDataAdapter1 As System.Data.OracleClient.OracleDataAdapter
Dim dt As New Data.DataTable("DenseReport")

OracleSelectCommand1 = New System.Data.OracleClient.OracleCommand
OracleConnection1 = New System.Data.OracleClient.OracleConnection
OracleDataAdapter1 = New System.Data.OracleClient.OracleDataAdapter

OracleSelectCommand1.CommandText = "SELECT DRE_YEAR FROM DENSEREPORT
WHERE DRE_YEAR = Year"
OracleSelectCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("pYear", "2001"))

OracleConnection1.ConnectionString = "user id=blah;data
source=blah;password=blah"
OracleSelectCommand1.Connection = OracleConnection1
OracleDataAdapter1.SelectCommand = OracleSelectCommand1
OracleDataAdapter1.Fill(dt)
Label1.Text = dt.Rows.Count.ToString
End Sub
-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

Adding a : character before pYear in the OracleSelectCommand1.Parameters.Add
call makes no difference. I still get no records.

Can anyone tell me what's wrong? The database server is running Oracle 9i. I
have installed Oracle client 8.1.5 on my development machine.

Any help would be much appreciated.

Brian

 

[Guest]

With ADO.NET, your SQL has to be ANSI standard, not Oracle. If you wish to
use Oracle PL/SQL syntax for parameters, consider downloading the ODP.NET
from Oracle (otn.oracle.com - free download after registration). It is very
similar to the MS classes.

My other suggestion is to move your code into stored procedures and call
with parameters that way. You not only benefit from compilation, but do not
have to worry about remembering ANSI-SQL versus PL/SQL.

If you use OracleClient, remember you will have to pass in a null REF_CURSOR
parameter for each returned result set and use REF_CURSORS for output in the
stored procedure (exception being single value output parameters). If you use
OleDb, you still use REF_CURSORS in your sproc, but not in the ADO.NET code
as null params (yes, this is aggrevating).


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

Gregory, thanks for taking the time to help me out.

Are you suggesting that my query is not in the correct syntax? How would I
need to change it? I assumed it was possible to use parameterized queries
with the OracleClient. I have done so before, using a numeric parameter.

As for using stored procedures with Oracle client, do you have a reference
to some resources to explain how to code the solution you suggested?

kind regards

Brian

 

[Cowboy \(Gregory A. Beamer\)]

In particular, this is a PL/SQL specific param:

Year

With the standard OleDb provider, I believe it is safe to use @Year or ?
(know the ? works for Access, but have not tried with Oracle). I prefer the
following:

CREATE OR REPLACE PROCEDURE MyProc
(
year IN int
, OutCur OUT Types.Cursor
)
AS

OPEN OutCur FOR
SELECT DRE_YEAR FROM DENSEREPORT
WHERE DRE_YEAR = pYear;

END;
/

My syntax may be off a bit, but you can then create two params, one for the
year and one for the cursor (if OracleClient (not 10g)). If OleDb, you do
not need an out param for the cursor, as the OleDb namespace objects
automagically hook up.

The ODP.NET download is still the best object. remember the Microsoft
objects are either a) a bit generic (OleDb) or old (8i in the case of
OracleClient).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

 

[Cowboy \(Gregory A. Beamer\)]

BTW, the syntax of my sproc may not be 100% correct. It has been a few
months since I have coded in Oracle and I typed the syntax from memory.
Check it with a PL/SQL manual before running.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

 

[Vladimir Sergeyev [MS]]

Brian,

your syntax for the parameterized query is correct. The "" parameters
should work with parameterized queries like the one that you mentioned. Try
the following things:

1) Use a different overload of Add() method for parameter. There are two
overloads of this method that take two arguments, one that takes a name and
a value and one that takes a name and parameter type. The common problem
with that is that the compiler might be binding your call to the wrong
overload (not the one that you expect). Does not looks like it's the case
here, but I'm not very familiar with VB to say for sure.

2) Explicitly set the parameter type using OracleParameter.OracleType or
OracleParameter.DbType. In your code below the parameter type is not set, so
it is inferred from the value, but the inferred type might not be the one
that you want. For example, if you're selecting from CHAR column, but the
type is inferred as VARCHAR you will have an empty resultset back, since
CHAR values are space-extended on the server, so you're comparing "2001
.... <a bunch of spaces>" to "2001".

Let me know if you still have problems.

--
Vladimir Sergeyev
ADO.Net Test Team
Microsoft Corporation

 

[Guest]

Hi Vladimir

The DRE_YEAR field is of type "varchar2(4) not null".

The following extra line of code does not make any difference:
-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

....

OracleSelectCommand1.Parameters.Add(New
System.Data.OracleClient.OracleParameter("pYear", "2001"))

OracleSelectCommand1.Parameters("pYear").OracleType =
OracleClient.OracleType.VarChar 'LongVarChar doesn't work either


OracleConnection1.ConnectionString = ...

-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/


These two efforts failed also:
OracleSelectCommand1.Parameters.Add("pYear", "2001")

and :
OracleSelectCommand1.Parameters.Add("pYear",
OracleClient.OracleType.VarChar)
OracleSelectCommand1.Parameters("pYear").Value = "2001"

I'm now using the equivalent OLEDB objects and the code is working, but I
would much prefer to use the Oracle objects if possible.

FYI, the working code is:

/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-

Dim OledbSelectCommand1 As System.Data.OleDb.OleDbCommand
Dim OledbConnection1 As System.Data.OleDb.OleDbConnection
Dim OledbDataAdapter1 As System.Data.oledb.OleDbDataAdapter
Dim dt As New Data.DataTable("DenseReport")

OledbSelectCommand1 = New System.Data.Oledb.OleDbCommand
OledbConnection1 = New System.Data.Oledb.OleDbConnection
OledbDataAdapter1 = New System.Data.Oledb.OleDbDataAdapter

OledbSelectCommand1.CommandText = "SELECT DRE_YEAR FROM DENSEREPORT
WHERE DRE_YEAR = ?"
OledbSelectCommand1.Parameters.Add(New
System.Data.Oledb.OleDbParameter("Year", OleDb.OleDbType.VarChar, 4,
"DRE_YEAR"))
OledbSelectCommand1.Parameters("Year").Value = "2002"

OledbConnection1.ConnectionString = "Provider=MSDAORA; Data
Source=ORACLE9i;user id=remv2;data source=remv2;password=sievert2"
OledbConnection1.Open()
OledbSelectCommand1.Connection = OledbConnection1
OledbDataAdapter1.SelectCommand = OledbSelectCommand1
OledbDataAdapter1.Fill(dt)
Label1.Text = dt.Rows.Count.ToString

/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-


Thanks for taking an interest.

Brian

 

[Vladimir Sergeyev [MS]]

Brian,

we would like to follow up on this issue if this is a bug. I created a repro
below that I ran against Oracle 8i, Oracle 9 and Oracle 9.2 servers and it
returns the results correctly. I'm using Oracle client 8.1.7, ADO.Net v1.1
and here is the output I get:

col_str_year value: '2001'
col_str_year value: '2001'

Please modify the repro to illustrate your issue and I'll look into it. You
can use VB if this is more convenient to you.


using System;
using System.Data;
using System.Data.OracleClient;

class App {
public static void Main() {
try {
// set <conn> to be a connection to the database
using(OracleConnection conn = new OracleConnection(" ... your connection
string here ...")) {
conn.Open();

OracleCommand cmd = new OracleCommand("drop table MyTable", conn);

try {
cmd.ExecuteNonQuery();
} catch(Exception) {

}

cmd = new OracleCommand("create table MyTable(col_str_year
varchar2(4) not null)", conn);
cmd.ExecuteNonQuery();


cmd = new OracleCommand("insert into MyTable values('2001')", conn);
cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();

cmd = new OracleCommand("select col_str_year from MyTable where
col_str_year = 1", conn);
OracleParameter curParam = cmd.Parameters.Add("p1", "2001");
curParam.OracleType = OracleType.VarChar;

DataSet newDS = new DataSet();
OracleDataAdapter curAdapter = new OracleDataAdapter();
curAdapter.SelectCommand = cmd;
curAdapter.Fill(newDS);

DataTable curTable = newDS.Tables[0];

foreach(DataRow curRow in curTable.Rows)
Console.WriteLine("col_str_year value: '" +
curRow["col_str_year"] + "'");

}
} catch(Exception e) {
Console.WriteLine(e);
}
}
}

--
Vladimir Sergeyev
ADO.Net Test Team
Microsoft Corporation

 

[Guest]

OK, I did the following.

I added a user DUMMY to my database. I gave him sufficient rights.

I created a new C# Web application from the standard open action within VS.
I added a button and textbox to the page. I added the following code, almost
identical to yours:

-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

private void Button1_Click(object sender, System.EventArgs e)
{
try
{
// set <conn> to be a connection to the database
using(OracleConnection conn = new OracleConnection("user id=DUMMY;data
source=blah;password=blah")) {
conn.Open();
OracleCommand cmd = new OracleCommand("drop table MyTable", conn);
try
{
cmd.ExecuteNonQuery();
}
catch(Exception)
{
}
cmd = new OracleCommand("create table MyTable(col_str_year varchar2(4)
not null)", conn);
cmd.ExecuteNonQuery();
cmd = new OracleCommand("insert into MyTable values('2001')", conn);
cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();

cmd = new OracleCommand("select col_str_year from MyTable
where col_str_year = 1", conn);
OracleParameter curParam = cmd.Parameters.Add("p1", "2001");
curParam.OracleType = OracleType.VarChar;

DataSet newDS = new DataSet();
OracleDataAdapter curAdapter = new OracleDataAdapter();
curAdapter.SelectCommand = cmd;
curAdapter.Fill(newDS);

DataTable curTable = newDS.Tables[0];
foreach(DataRow curRow in curTable.Rows)
Console.WriteLine("col_str_year value: '" + curRow["col_str_year"] + "'");

TextBox1.Text=curTable.Rows.Count.ToString();
}
}
catch(Exception ex)
{
Console.WriteLine(ex);
}
}
-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

The code creates the table and adds the two records. I put a breakpoint on
the line where I set the textbox text and I do a watch on
curTable.Rows.Count. It gives the answer 'error:curTable.Rows does not
exist'. The for loop is never entered.

In VB:
-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/
Dim conn As New System.Data.OracleClient.OracleConnection("user
id=DUMMY;data source=remv2;password=DUMMY")

conn.Open()

Dim cmd As New System.Data.OracleClient.OracleCommand("drop table
MyTable", conn)
cmd.ExecuteNonQuery()

cmd = New System.Data.OracleClient.OracleCommand("create table
MyTable(col_str_year varchar2(4) not null)", conn)
cmd.ExecuteNonQuery()
cmd = New System.Data.OracleClient.OracleCommand("insert into
MyTable values('2001')", conn)
cmd.ExecuteNonQuery()
cmd.ExecuteNonQuery()

cmd = New System.Data.OracleClient.OracleCommand("select
col_str_year from MyTable where col_str_year = 1", conn)
Dim curParam As System.Data.OracleClient.OracleParameter =
cmd.Parameters.Add("p1", "2001")
curParam.OracleType = System.Data.OracleClient.OracleType.VarChar

Dim newDS As DataSet = New DataSet
Dim curAdapter As New System.Data.OracleClient.OracleDataAdapter
curAdapter.SelectCommand = cmd
curAdapter.Fill(newDS)

Dim curTable As DataTable = newDS.Tables(0)
Dim curRow As DataRow
For Each curRow In curTable.Rows
Console.WriteLine("col_str_year value: '" +
CType(curRow("col_str_year"), String) + "'")
Next

Label1.Text = curTable.Rows.Count.ToString()
-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

Here the curTable.Rows object exists (bravo VB), with count = 0.

I guess you'll need my system and setup details.

Dev platform -Oracle8i Enterprise Edition for Windows NT and Windows 95/98
Release 8.1.5 Client only installed

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600
OS Manufacturer Microsoft Corporation
System Manufacturer FUJITSU SIEMENS
System Model CELSIUS R610
System Type X86-based PC
Processor x86 Family 15 Model 2 Stepping 9 GenuineIntel ~2799 Mhz
Processor x86 Family 15 Model 2 Stepping 9 GenuineIntel ~2799 Mhz
BIOS Version/Date Phoenix Technologies Ltd. 1.07.1357, 11/24/2003
SMBIOS Version 2.3
Locale United States
Time Zone W. Europe Standard Time

Microsoft Development Environment 2003 version 7.1.3088

Microsoft .NET Framework 1.1 version 1.1.4322 SP1

Server Oracle 9i Release 2 (9.2.0.1.0) for Windows running on Windows Server
2003

Let me know if you need anything else.

Brian