SQL statement causes error?

[dickep]

OK, I have to connect to a SQL Server 2005 database. I can do that. Now I need to get some data and am using the _SQL.au3 UDF for the functions.

However, I have built, based on the input data that I need, a statement that has a WHERE XXX IN (some numbers) and it, well, give a lot of errors.

Code is below

#include <_sql.au3>
#include <Array.au3>
; start the ADBO system

Global $deviceId [4]= [32925050, 32001396, 32001428, 32925113]
Local $aRow;Variable to store the array data in to
Local $aNames;Variable to store the array data in to
Local $dbFetch = False

_ArrayDisplay($deviceId)
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

If _sql_Connect($oADODB,"SERVER2005","MyDB","me","pwd") = $SQL_ERROR then
    Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf

$sqlIn = ""
for $zz = 0 to UBound($deviceId) - 1
    $sqlIn = "'" & $deviceId[$zz] & "', " & $sqlIn
Next

$select1 = "select CollectorID, DeviceId, DeviceType, master.dbo.fn_varbintohexstr(ReadCoefficient) ReadCoefficient, NumberMessages, MaximumRSSI, EndDateTime from vwDeviceCollectorStatistics ";
$select2 = "where deviceid deviceid in (" & $sqlIn & ") and EndDateTime > dateadd(dd,-1,getutcdate()) order by CollectorID;";

$totalSelect = $select1 & $select2;

Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
$iRval = _SQL_GetTable2D($oADODB,$totalSelect,$aData,$iRows,$iColumns)
If $iRval = $SQL_OK then _arrayDisplay($aData,"2D  (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

_SQL_Close()

Information with error(s) out of Console Window:

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "G:\AutoIt\Scripts\DBTest6.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams

+>12:11:08 Starting AutoIt3Wrapper v.2.0.0.3 Environment(Language:0409 Keyboard:00000409 OS:WIN_XP/Service Pack 3 CPU:X64 OS:X86)

>Running AU3Check (1.54.19.0) from:C:\Program Files\AutoIt3

+>12:11:08 AU3Check ended.rc:0

>Running:(3.3.2.0):C:\Program Files\AutoIt3\autoit3.exe "G:\AutoIt\Scripts\DBTest6.au3"

###############################

err.description is: [Microsoft][ODBC SQL Server Driver][sql Server]An expression of non-boolean type specified in a context where a condition is expected, near 'deviceid'.

err.windescription:

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 262

err.source is: Microsoft OLE DB Provider for ODBC Drivers

err.helpfile is:

err.helpcontext is: 0###############################

+>12:11:12 AutoIT3.exe ended.rc:0

+>12:11:13 AutoIt3Wrapper Finished

>Exit code: 0 Time: 5.359

What am I doing wrong?

E

[JRowe]

What am I doing wrong?

Posting a help and support thread in the Example Scripts, to start with.

Anyway, try querying the server directly. If it's a SQL problem, it's hard to help because we don't know what your database looks like. Building your queries incrementally is also a good idea.

An expression of non-boolean type specified in a context where a condition is expected, near 'deviceid'.

The "where deviceid deviceid" looks wrong, it probably needs a conditional there. Try using "where deviceid = " & $someValue

Edited April 16, 2010 by JRowe

[Yoriz]

expandcollapse popup

;~ #include <_sql.au3>
#include <Array.au3>
; start the ADBO system

Global $deviceId [4]= [32925050, 32001396, 32001428, 32925113]
Local $aRow;Variable to store the array data in to
Local $aNames;Variable to store the array data in to
Local $dbFetch = False

_ArrayDisplay($deviceId)
;~ _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

;~ $oADODB = _SQL_Startup()
;~ If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

;~ If _sql_Connect($oADODB,"SERVER2005","MyDB","me","pwd") = $SQL_ERROR then
;~     Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
;~     _SQL_Close()
;~     Exit
;~ EndIf

$sqlIn = ""
for $zz = 0 to UBound($deviceId) - 1
    $sqlIn = "'" & $deviceId[$zz] & "', " & $sqlIn
Next

$select1 = "select CollectorID, DeviceId, DeviceType, master.dbo.fn_varbintohexstr(ReadCoefficient) ReadCoefficient, NumberMessages, MaximumRSSI, EndDateTime from vwDeviceCollectorStatistics ";
$select2 = "where deviceid deviceid in (" & $sqlIn & ") and EndDateTime > dateadd(dd,-1,getutcdate()) order by CollectorID;";

$totalSelect = $select1 & $select2;

ConsoleWrite($totalSelect & @CR)

;~ Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
;~ $iRval = _SQL_GetTable2D($oADODB,$totalSelect,$aData,$iRows,$iColumns)
;~ If $iRval = $SQL_OK then _arrayDisplay($aData,"2D  (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

;~ _SQL_Close()

; returns = select CollectorID, DeviceId, DeviceType, master.dbo.fn_varbintohexstr(ReadCoefficient) ReadCoefficient, NumberMessages, MaximumRSSI, EndDateTime
; from vwDeviceCollectorStatistics where deviceid deviceid in ('32925113', '32001428', '32001396', '32925050', ) and EndDateTime > dateadd(dd,-1,getutcdate()) order by CollectorID;

After your where you have deviceid twice try one DeviceId there, also you have a comma after '32925050' thats not needed, try tidying those up and see what that gives.

edit - heres a easy way to get the correct formating of your array $deviceId

replace

$sqlIn = ""
for $zz = 0 to UBound($deviceId) - 1
    $sqlIn = "'" & $deviceId[$zz] & "', " & $sqlIn
Next

with

$sqlIn = _ArrayToString($deviceId, "','")
$sqlIn = "'" & $sqlIn & "'"

Edited April 16, 2010 by Yoriz

[dickep]

Yoriz,

Thanks. Sometimes you can't see the forrest for the trees.

Worked and used your suggestion to replace my loop. Much better.

P.S. Yes, I did check this with the SQL server and it worked. Just I did not copy it correctly out of the Server into Autoit.

E

[Yoriz]

No worries, i usually find placing a $sString in a consolewrite to check what its upto usually fells a few trees to make the forest less dense