Sign in to follow this  
Followers 0
dickep

SQL statement causes error?

5 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by JRowe

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

;~ #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 by Yoriz

GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 :(


GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0