Help with an Action Item SQL Query

Hello,

I am in need of some help from you FSQL veterans out there! :wink:

My overall goal is to convert an array of integers symbolizing the date and time from the plc, into a DateTime which the database can understand.

I’m not too familiar with FSQL, but it seems I can do this using an Action Item to pass my values into a custom function on the database which returns the DateTime I need…

Action Item: CalculateStartTime
Store Result to OPC Item: {StartTime}

dbo.DateTime({startTime_0}, {startTime_1}, {startTime_2}, {startTime_3}, {startTime_4}, {startTime_5});

Custom SQL Functions:

[code]create function Date(@Year int, @Month int, @Day int)
– returns a datetime value for the specified year, month and day
– Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go

create function Time(@Hour int, @Minute int, @Second int)
– Returns a datetime value for the specified time at the “base” date (1/1/1900)
– Many thanks to MVJ for providing this formula (see comments).
returns datetime
as
begin
return dateadd(ss,(@Hour3600)+(@Minute60)+@Second,0)
end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
– returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
go[/code]
When I try to trigger the group I get this error:

08/07/09 11:29:50 Group Execution Error Error executing item CalculateStartTime: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009'.
Does anybody see what I am doing wrong here? Also, if there is an easier way to do this please let me know.

Thank you for your help.

–HandledException

Hi!

I stuck a post in your other topic.

Regards,

I got my query to work in the management studio, so I think it is just a matter of how to call if from the Action Item. Can anyone see what I am doing wrong there?

Thanks for the help.

–HandledException

[code]DECLARE @year Int,
@month Int,
@day Int,
@hour Int,
@minute Int,
@second Int

DECLARE @newDate DateTime

SET @year = 2009
SET @month = 8
SET @day = 7
SET @hour = 10
SET @minute = 30
SET @second = 10

SET @newDate = dbo.DateTime(@year, @month, @day, @hour, @minute, @second);
PRINT @newDate[/code]

Yeah, it’s strange that your call isn’t working from FSQL. I can’t see anything immediately wrong with it… However, before I actually try creating the procedure and calling it from FactorySQL, I want to suggest the first thing that came to mind when I read the problem:

Use the FactorySQL “ToDate” expression function to parse a string that you’ve built from the parameters (In the form of “year-month-day hour:minute:second”).

That is, create an action item whose mode is “expression” and whose text is:

[quote]ToDate(Concat({startTime_0},"-",{startTime_1},"-",{startTime_2}," “,{startTime_3},”:",{startTime_4},":",{startTime_5}))
[/quote]

The main benefit of this, if it works, is that it doesn’t need to round-trip to the database for the value, and thus will be much faster.

Let me know if this works or not!

Hi Colby,

Your suggestion works for the most part, except that I am getting a DateTime field overflow now. I am guessing that this is because the field in question is a SmallDateTime instead of a DateTime. (We decided to go with this since we only need that particular information up to the minute, so we could save some space in the db)

With that being said, is there an easy way to cast the DateTime returned by ToDate() to a SmallDateTime?

If this was a new design I would simply change the relevant table fields to DateTime, but I am trying to replace an existing solution, so altering the database is less than ideal.

Thank you for the continued help.

–HandledException

Just in case there isn’t an easy way to cast to a SmallDateTime, I was trying to get the database function solution to work. I made a new function which returns a SmallDateTime, but I am running into the same problem I had earlier.

[quote]08/13/09 11:29:04
Group Execution Error
Error executing item GetStartTime: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ‘2009’.[/quote]
Any ideas on what FSQL is complaining about? Here is what I am trying now…

dbo.SmallDateTime({startTime_0}, {startTime_1}, {startTime_2}, {startTime_3}, {startTime_4});

[code]-- returns a SmallDateTime value for the date and time specified.
CREATE FUNCTION SmallDateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int)
RETURNS SmallDateTime

AS
BEGIN
RETURN dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute, 0)
END
GO[/code]
Here is the stack trace if that helps any…

[quote]Error executing item GetStartTime: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ‘2009’.
at FactorySQL.Items.AccessoryActionItem.Execute(EvaluationToken Token)
at FactorySQL.Groups.FSQLGroup.EvaluateActionItems(Boolean Triggered, EvaluationToken EvalToken)
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ‘2009’.
at FactorySQL.Items.AccessoryActionItem.Execute(EvaluationToken Token)
at FactorySQL.Groups.FSQLGroup.EvaluateActionItems(Boolean Triggered, EvaluationToken EvalToken)
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
[/quote]Thanks for the help.
–HandledException

Ok, I solved the syntax problem stated above by simply adding “SELECT” in front of it (d’oh), but I am still running into that datetime field overflow problem.

The action item seems to run successfully, since I see a date in the value column, but I am still running into the overflow when the group is triggered. I’m thinking that there must be some casting behind the scenes when the insert statement is assembled, since you have to choose the type when mapping your opc items to table columns. Any ideas about this?

I’m starting to think that I’m going to have to alter the table columns to datetimes to get things to work right… :frowning:

–HandledException

Yes, FactorySQL is certainly going to cast it to a normal date in order to work with it, and is then going to write it back as such. However, the database driver should be able to truncate it without a problem…

I just did a quick test and was able to write to a SmallDate column from an action item without a problem… is it possible that one of your values is in fact out of range (that is, you’ve got the days mixed up with the months or something?)

It’s probably something simple…

Regards,

Just to add a bit more info…

I just tried a test from top to bottom using all of your custom functions, and everything seemed to work out well. So, I’d double check that all of your parameters are correct all the way back to the PLC, and in the correct order when calling the function.

Good luck!

[quote=“Colby.Clegg”]Just to add a bit more info…

I just tried a test from top to bottom using all of your custom functions, and everything seemed to work out well. So, I’d double check that all of your parameters are correct all the way back to the PLC, and in the correct order when calling the function.

Good luck![/quote]

Colby,

What database engine did you test it with? MySQL? MS MSQL Server?

–HandledException

SQL Server 2005.

Thanks for your continued help Colby…

I still can’t seem to get things to work. I thought it might be a problem with the driver I was using, so I switched to a native connection from a DSN one, but that didn’t work either.

Database Engine in Question: [quote]Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2) [/quote]
Now you mocked-up the database solution that I was working on earlier right? Wouldn’t I be better off just using ToDate() like you mentioned earlier? It doesn’t look like I gain anything from using the database function as opposed to FSQL. Or did I miss something?

I made a separate action item for each date which constructs the string so I could see what was getting passed to ToDate(), and things look ok to me. All the numbers seem to be in the correct order. :scratch:

StartTimeString:Concat({startTime_year},"-",{startTime_month},"-",{startTime_day}," ",{startTime_hour},":",{startTime_minute},":",{startTime_second})GetStartTime:ToDate({StartDateString})
What other troubleshooting do you recommend? Could you send me the example you threw together?

Thanks again.

–HandledException

P.S. I’m attaching some screenshots in the hopes that you might see something that I have missed.
FSQL Screenshots.zip (154 KB)

I think my problem might be with how I have the items set up in the group. It looks like the date I compute in the action item never makes it into the insert statement. I’m guessing those empty items are what are giving me the DateTime overflow errors.

After realizing this I tried to assign the results of my action items directly to the table fields but then I just get errors about the fields being null.

So, how does one go about setting up action items which compute items mapped to table fields?

Thanks for hangin’ in there with me through all this! :thumb_right:

–HandledException

Yeah, in those screen shots it looks like you’re trying to write the action items back to opc items, which aren’t really opc items, 'cause they don’t actually map to opc… which isn’t going to work.

If you want to map an action item to a database column, just type the column name in the box (“LogEnd” and “LogStart”, without quotes). Then delete the unnecessary opc items.

It sounds like you kind of tried this, but ran into a different problem… my guess is that you have your table set to not allow nulls, and when you started the group it tried to write a null on the first execution, before it had all of the values. That’s actually fine for a table set up like that, the subsequent executions should be fine.

So, I’d give it another shot, and see if it works at all. If you keep getting errors, we can look further into those.

Regards,

Also, more in general as you start to use FSQL more and more… have you seen the video library? You might find a few of the videos helpful, for example the one on Action Items

Just thought I’d mention it because the tiny little link up there often gets overlooked, and people seem to find it helpful.

Regards,

[Angelic Chorus] It worked! What a round-a-bout way to figure it out. I learned a lot more about FSQL along the way I guess. :laughing:

Thanks again for your help! :smiley:

–HandledException

P.S. I did watch all the videos awhile ago, but maybe I should go watch em again, so I can avoid things like this