Page 1 of 1
SQL count to variable
Posted: Tue Dec 23, 2014 8:05 pm
by sforsythe
I need to check the data in a file to see if it exists in the database and insert the records that don't.
The data is "Date", "Time", and "Temperature" and I need to see if there is already a record in the database for the "Date" and "Time" of each line in the file.
I tried using SQL Query to return a count for records, but don't seem to be able to get it to work. The connection to the database works and I can insert records, but don't understand how to return the results of a select statement (tried using the "assign rows affected into variable").
The simple test I tried was:
create a variable called "VCOUNT" with value 0,
"Select count(*) from table" (the answer should be 671)
assign to {vCount}
The answer is still zero. I've run the query from inside MS Access and it works.
Re: SQL count to variable
Posted: Tue Dec 23, 2014 9:01 pm
by sforsythe
Dataset Loop seems to work (but don't really need a loop) the result is a single value.
Now having problems with variables in the SQL statement.
The insert statement works:
INSERT into Lake_Test(fDate, fTime, fTemperature)
values('{vDate}','{vTime}',{line(3)})
But the select statement returns "data type mismatch in criteria expression"
SELECT
count(*)
FROM
Lake_Test
WHERE
fDate = '{vDate}' AND fTime = '{vTime}'
The database table Lake_Test has 3 fields
fDate = short date (DD/MM/YYYY)
fTime = short time (HH:MM)
fTemperature = double (9999.9)
the variables are and the first line results are:
vDate = 17/12/2014
vTime = 07:35
vline3 = 15.0
Any ideas?
Re: SQL count to variable
Posted: Wed Dec 24, 2014 10:50 am
by Oleg
See my example below. It works.
I optimized it for MySQL database.
Pay attention that you specify DATE and TIME values (in
WHERE clause) as strings. The acceptable format of these strings can depend on Database kind and your regional settings.
MySQL can accept (independently of regional settings):
- DATE as 'YYYY-MM-DD'
TIME as 'hh:mm' or 'hh:mm:ss'
DATETIME as 'YYYY-MM-DD hh:mm:ss'
Code: Select all
;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|317955339
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task259"
Hide=INTEGER|0
ID=INTEGER|326707182
LogOnAsUser=INTEGER|1
Name=STRING|"Test DB filters"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER
[Actions\Action1]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//without filter"
Params=FOLDER
[Actions\Action1\Params]
comment=STRING|"without filter"
[Actions\Action10]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""VCOUNT"" with value ""{row(0)}"""
Params=FOLDER
[Actions\Action10\Params]
expand=STRING|"1"
varname=STRING|"VCOUNT"
varvalue=STRING|"{row(0)}"
[Actions\Action11]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action12]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{vCount}"""
Params=FOLDER
[Actions\Action12\Params]
icon=STRING|"1"
msg0=STRING|"{vCount}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"
[Actions\Action2]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action2\Params]
commatext=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=MySQLtest"
override=STRING|"0"
password=STRING|"2031219857201061985019088"
sql=STRING|"""Select count(*) from temperatures"""
timeout=STRING|"60"
var2=STRING|"ROW"
withmemo=STRING|"0"
[Actions\Action3]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""VCOUNT"" with value ""{row(0)}"""
Params=FOLDER
[Actions\Action3\Params]
expand=STRING|"1"
varname=STRING|"VCOUNT"
varvalue=STRING|"{row(0)}"
[Actions\Action4]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action5]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{vCount}"""
Params=FOLDER
[Actions\Action5\Params]
icon=STRING|"1"
msg0=STRING|"{vCount}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"
[Actions\Action6]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//with filter"
Params=FOLDER
[Actions\Action6\Params]
comment=STRING|"with filter"
[Actions\Action7]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""VDATE"" with value ""2014-12-09"""
Params=FOLDER
[Actions\Action7\Params]
expand=STRING|"0"
varname=STRING|"VDATE"
varvalue=STRING|"2014-12-09"
[Actions\Action8]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""VTIME"" with value ""16:20"""
Params=FOLDER
[Actions\Action8\Params]
expand=STRING|"0"
varname=STRING|"VTIME"
varvalue=STRING|"16:20"
[Actions\Action9]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action9\Params]
commatext=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=MySQLtest"
override=STRING|"0"
password=STRING|"2031219857211351959521137"
sql=STRING|"""Select count(*) from temperatures "",""where fdate = '{vdate}' and ftime='{vtime}'"""
timeout=STRING|"60"
var2=STRING|"ROW"
withmemo=STRING|"0"