dickep 0 Posted April 16, 2010 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
JRowe 9 Posted April 16, 2010 (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 April 16, 2010 by JRowe [center]However, like ninjas, cyber warriors operate in silence.AutoIt Chat Engine (+Chatbot) , Link Grammar for AutoIt , Simple Speech RecognitionArtificial Neural Networks UDF , Bayesian Networks UDF , Pattern Matching UDFTransparent PNG GUI Elements , Au3Irrlicht 2Advanced Mouse Events MonitorGrammar Database GeneratorTransitions & Tweening UDFPoker Hand Evaluator[/center] Share this post Link to post Share on other sites
Yoriz 6 Posted April 16, 2010 (edited) 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 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
dickep 0 Posted April 16, 2010 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
Yoriz 6 Posted April 16, 2010 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