2 replies to this topic

[gritts]

I am attempting to create a script which will poll a SQL database that is part of our internal server monitoring application. The results would then be pushed to the local growl client on my desktop. I am successful at querying the SQL database using the SQL UDF and can send growl notifications fine as well...

What I run into is "Syntax error converting datetime from character string." when I try to query the database with the value from a previous query. (My appologies for making this sound confusing)

For example, I query the database and return the results into a 2D array. I can display the results fine with "_ArrayDisplay". I then take the results from the query and using the next to last row from the results, get the value from the datetime column.

$tmpDate = String($aData[$iRows-1][1])
MsgBox(0,"Date",$tmpDate)

It is when I take the value in $tmpDate and attempt to search with it that I get the syntax error above.

$iRval = _SQL_GetTable2D(-1, "select * from dbo.AllpagesToday WHERE PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL' AND Date > '" & $tmpDate & "' ORDER BY Date;", $aData, $iRows, $iColumns)

I have also attempted to convert the date format from: 20120319090303 to 2012-03-19 09:03:03.000 to no avail.

My conversion pieced together from other posts:

Func _convertToDateTime($dtString)
Local $newdt
$newdt = StringRegExpReplace($dtString, "A(d{4})(d{2})(d{2})(d{2})(d{2})(d{2})(?:.*)","$1-$2-$3 $4:$5:$6")
MsgBox(0,"Convert Results",$newdt)
Return($newdt)
EndFunc

When I search using the converted datetime, it is as if the query ignored the datetime value and returns all values of the 2D array.

$iRval = _SQL_GetTable2D(-1, "select * from dbo.AllpagesToday WHERE PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL' AND Date > '" & _convertToDateTime($tmpDate) & "' ORDER BY Date;", $aData, $iRows, $iColumns)

Any suggestions? If more information is needed, let me know.

Edited by gritts, 19 March 2012 - 05:43 PM.

[Juvigy]

"select * from dbo.AllpagesToday WHERE PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL' AND Date > '"

This should be i think:

"select * from dbo.AllpagesToday WHERE (PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL') AND Date > '"

Test the SQL query somewhere outside outoit to see how/if it works.

Edited by Juvigy, 20 March 2012 - 08:09 AM.

[gritts]

Thank you Juvigy, I had thought about using parenthesis but did not try. Here is my resulting line in my script that works...

$iRval = _SQL_GetTable2D(-1, "select * from dbo.AllpagesToday WHERE (PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL') AND Date > '" & _convertToDateTime($tmpDate) & "' ORDER BY Date;", $aData, $iRows, $iColumns)

Edited by gritts, 20 March 2012 - 01:51 PM.