Oracle count result - wrong

[mitchellm44]

I have a query that returns a result, however the COUNT value that is returned is completely different than what I see when running in a Query Tool.

I am running AutoIT 3.2.4.9

The query should return 2 rows, with value of count in the first row currently @ 549 , but the return value from AutoIT is like 234596 (not exact data here - but in that range)

I have spent a good amount of time trying to get this to work and am turning to the forum for some advice on what is wrong here.

I have also had to modify some parts of the code to obfuscate it. - you understand.

I found another post on the forum about an issue withn Numbers coming back and using the TO_CHAR() function - in Oracle - but that had zero effect -

I had previously used an object to get at the value of $rs(1).Value and got the same result (I added the $rs.Fields(1).Value) after with zero effect).

;_________________________CODE START______________

$Connection = ObjCreate("ADODB.Connection")

$Connection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = computerd016)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = servicename.redacted.com) ));User Id=myusername;Password=redacted;"

$Connection.Open

if $ExitFlag == 0 Then

$rs = objcreate("ADODB.Recordset")

$cmd = ObjCreate("ADODB.Command")

$cmd.ActiveConnection = $Connection

$cmd.CommandType = $adCmdText

$cmd.CommandText = "select distinct HIST_DATA2, count(1) from IMPC_HIST_TB where HIST_CD_FK = 'MEDBPLMUPD' and HIST_DT > '" & @MDAY & "-" & CurrentMonthLiteral() & "-" & @YEAR & "' GROUP BY HIST_DATA2"

;** RUNNING THIS IN TOAD TOOK 5 MINUTES TO GET BACK VALUES

SplashTextOn("Oracle Reporting","This process to retrieve New and Updated Counts takes over 5 minutes Please wait" & @CRLF & $cmd.CommandText & @CRLF & @CRLF & "Start Time " & @HOUR & ":" & @MIN & ":" & @SEC ,400,200,300,300)

$rs = $cmd.Execute

SplashOff();

;MsgBox(0,"TEST", $rs(0).Value)

for $i = 0 to $rs.Fields.Count - 1

$values = $values & $rs.Fields($i).Value() & " : "

next

$newreccount = $rs.Fields(1).Value

if @error Then

msgbox(0,"Error ","After Recordset Value Error",10)

Exit(1)

EndIf

$rs.MoveNext()

$values = $values & @CRLF

for $i = 0 to $rs.Fields.Count - 1

$values = $values & $rs.Fields($i).Value()

next

if @error Then

msgbox(0,"Error ","After Recordset MoveNext Error",10)

Exit(1)

EndIf

$updatedcount = $rs.Fields(1).Value

MsgBox(0,"TEST", "New rec Count = " & $newLoans & " Updated count = " & $updatedcount & @CRLF & $values)

if @error Then

msgbox(0,"Error ","After Recordset Error",10)

Exit(1)

EndIf

$rs.Close()

EndIf

$Connection.Close()

$rs = ""

$cmd = ""

$Connection = ""; Destroy the object

[DaRam]

Why use a Command Object, can't you directly use a $adoRs.Open("SELECT ....") ?

Also, what is CurrentMonthLiteral().

Try displaying the SQL in a MsgBox and verify it is the exact same SQL you see returning the correct COUNT

[mitchellm44]

Why use a Command Object, can't you directly use a $adoRs.Open("SELECT ....") ?

Also, what is CurrentMonthLiteral().

Try displaying the SQL in a MsgBox and verify it is the exact same SQL you see returning the correct COUNT

Yes - you could go that way -- The SQL, is put into the splash screen specifically to verify that what I put in there is what I am running. The results are very different.

I will try putting it in an $adoRs. to see if there is a different behavior.

[mitchellm44]

I changed the code to Eliminate the Command - and went directly to the $rs = $Connection.Execute($strSQL)

The same result is happening.

I tried playing around with other was of putting in the date, and still no luck.

The standard format string for an Oracle date DD-MON-YYYY -- is used within the quotes '13-FEB-2009'

and that gives me records that are from Midnight onward. Time portion starts at midnight -- which fits my needs for the processing.

If you have another way of doing the date -that is more efficient - I would like to see that.

The extact numbers coming back from the AutoIt version of this 269349 - Should be 549

the second row is returning 31315516 - should be 83479

The first column value is correct - which is text - the count value is what is screwy.. I am just not seeing why

[M a k a v e l !]

I changed the code to Eliminate the Command - and went directly to the $rs = $Connection.Execute($strSQL)

The same result is happening.

I tried playing around with other was of putting in the date, and still no luck.

The standard format string for an Oracle date DD-MON-YYYY -- is used within the quotes '13-FEB-2009'

and that gives me records that are from Midnight onward. Time portion starts at midnight -- which fits my needs for the processing.

If you have another way of doing the date -that is more efficient - I would like to see that.

The extact numbers coming back from the AutoIt version of this 269349 - Should be 549

the second row is returning 31315516 - should be 83479

The first column value is correct - which is text - the count value is what is screwy.. I am just not seeing why

I don't know if this will help you but I also make queries in ORACLE thru ODBC. Here's a couple lines I use:

; ADO cursortype and locktype
Global Const $i_adOpenDynamic   = 2
Global Const $i_adLockOptimistic = 3

;=== Objet Connection Oracle ==================
$ObjConn = ObjCreate("ADODB.Connection")
$ObjConn.Open("DSN=oracle_dsn;UID=user;PWD=password")

;=== Recordsets =============================================
$rstTESTdata = ObjCreate ("ADODB.Recordset")
$rstTESTdata.CursorType = $i_adOpenDynamic
$rstTESTdata.LockType = $i_adLockOptimistic
;============================================================


$SQL_ = "SELECT SomeColumn1, SomeColumn2 FROM SomeWhere WHERE SomeColumn = 'Value'"
$rstTESTdata.Open ($SQL_, $ObjConn)

$SomeColum1 = $rstTESTdata("SomeColum1").Value
$SomeColum2 = $rstTESTdata("SomeColum2").Value

MsgBox(0,"ORACLE Result","SomeColum1 Value: " & $SomeColum1 & @CRLF & "SomeColum2 Value: " & $SomeColum2)

$rstTESTdata.Close

• Try showing just one record with the date field
• Maybe some EPOCH time conversion
• Does your user has privilege for count(*) queries

Your query in your 'ORACLE tool' should be the pretty similiar to the query in $SQL_

Edited February 14, 2009 by M a k a v e l !

[mitchellm44]

I don't know if this will help you but I also make queries in ORACLE thru ODBC. Here's a couple lines I use:

; ADO cursortype and locktype
Global Const $i_adOpenDynamic   = 2
Global Const $i_adLockOptimistic = 3

;=== Objet Connection Oracle ==================
$ObjConn = ObjCreate("ADODB.Connection")
$ObjConn.Open("DSN=oracle_dsn;UID=user;PWD=password")

;=== Recordsets =============================================
$rstTESTdata = ObjCreate ("ADODB.Recordset")
$rstTESTdata.CursorType = $i_adOpenDynamic
$rstTESTdata.LockType = $i_adLockOptimistic
;============================================================


$SQL_ = "SELECT SomeColumn1, SomeColumn2 FROM SomeWhere WHERE SomeColumn = 'Value'"
$rstTESTdata.Open ($SQL_, $ObjConn)

$SomeColum1 = $rstTESTdata("SomeColum1").Value
$SomeColum2 = $rstTESTdata("SomeColum2").Value

MsgBox(0,"ORACLE Result","SomeColum1 Value: " & $SomeColum1 & @CRLF & "SomeColum2 Value: " & $SomeColum2)

$rstTESTdata.Close

• Try showing just one record with the date field
• Maybe some EPOCH time conversion
• Does your user has privilege for count(*) queries

Your query in your 'ORACLE tool' should be the pretty similiar to the query in $SQL_

The user for both queries -- is the same -- and the Statements are the same

For performance - the Ordinal numbers instead of names are used.

The Epoch time issue - should have shown up in the TOAD/SQL query - but did not..

I will have to review your code example more and see if there might be some specific I can glean from it. - Way past my end of work day - but will have to look at it again in the morning.