Oracle VS SQL server oddity

[jeepxo]

Seems when I'm working with the ORACLE database and I use a function in my sql statement I loose the first ordinal in my collection. Same SQL statment works with MS SQL.


For example.

sqlString = "select Max(StudentID) myID from students"
rs.Open sqlString,oConn,adOpenKeyset,adLockOptimistic
bigID = rs("myID").value

works with SQL Server.

To get it to work with Oracle I need to select the same item twice.

sqlString = "select Max(StudentID) useless,Max(StudentID) myID from students"
rs.Open sqlString,oConn,adOpenKeyset,adLockOptimistic
bigID = rs("myID").value

Any thoughts?

To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.

 

[jfdabiri]

our installation has oracle, and the first one works ok.

 

[jeepxo]

The sql statement works fine for me if I use SQL+ worksheet too. That's what's confusing me.

It only fails when I use it in the ASP page.
I'm thinking that it has to be something in ADO that I haven't got set right.

To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.

 

[jfdabiri]

i have tried the first code with vbs and asp. it works in both instances. this is what i tried and it worked ok:

     strCon = "Driver={Microsoft ODBC for Oracle}; " & _
        "CONNECTSTRING=(DESCRIPTION=" & _
        "(ADDRESS=(PROTOCOL=TCP)" & _
        "(HOST=123.123.123.123)(PORT=1521))" & _
        "(CONNECT_DATA=(SERVICE_NAME=myserver))); uid=usr_id;pwd=my_psw;"
  
     Set oConn = WScript.CreateObject("ADODB.Connection")
      
     Set Rs = WScript.CreateObject("ADODB.Recordset")
     oconn.Open strCon
 
sql_str= "select Max(account_code) myID from account_codes"
rs.Open sql_str,oConn,1,3
bigID = rs("myID").value

msgbox bigid    
     
Rs.Close
oConn.Close

 

[jeepxo]

The problem looks like it is in my DSN.

if I use

set oConn = Server.CreateObject("ADODB.Connection")
strCon = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))" & _
"(CONNECT_DATA=(SID=CoopOra)(SERVER=DEDICATED)));User Id=myID;Password=myPassword;"

oConn.Open strCon

I'm fine.

if I use

set oConn = Server.CreateObject("ADODB.Connection")
oConn.open application("connectionString"), "myID", "myPassword"

I get the odd behaviour
where application ("connectionString") is dsn setup for that database.


To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.

 

[jeepxo]

The solution really doesn't make any sense to me but if I use a client side cursor Bob's my uncle everything works.

So when I'm using oracle my code needs to be.

set oConn = Server.CreateObject("ADODB.Connection")
oConn.open application("connectionString"), "myID", "myPassword"
oConn.cursorlocation = 3 
sqlStr = "Select max(studentID) myID from students"
rs.open sqlStr, oConn,adopenkeyset, adlockpessimistic
bigID = rs("myID").value
rs.close
oConn.close

I didn't think that there was any difference between client side and server side cursors except for performance.
Guess I have been wrong all these years.

To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.