It is currently Thu Apr 24, 2014 5:35 am




Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next
 Help with an Action Item SQL Query 
Author Message
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Help with an Action Item SQL Query
Hello,

I am in need of some help from you FSQL veterans out there! ;)

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}
Code:
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,(@Hour*3600)+(@Minute*60)+@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

When I try to trigger the group I get this error:
Code:
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


Fri Aug 07, 2009 8:33 am
Profile
General
General

Joined: Tue Mar 24, 2009 9:14 am
Posts: 785
Location: Hudson, MI
Post Re: Help with an Action Item SQL Query
Hi!

I stuck a post in your other topic.

Regards,

_________________
Jordan

Duct tape is like The Force. It has a light side, a dark side, and it holds the universe together.


Fri Aug 07, 2009 9:44 am
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
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


Fri Aug 07, 2009 11:14 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2417
Post Re: Help with an Action Item SQL Query
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}))


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!

_________________
Colby Clegg
Inductive Automation
Software Development


Fri Aug 07, 2009 1:30 pm
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
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


Thu Aug 13, 2009 8:11 am
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
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'.

Any ideas on what FSQL is complaining about? Here is what I am trying now...
Code:
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

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)
Thanks for the help.
--HandledException


Thu Aug 13, 2009 8:47 am
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
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... :sad:

--HandledException


Thu Aug 13, 2009 8:59 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2417
Post Re: Help with an Action Item SQL Query
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,

_________________
Colby Clegg
Inductive Automation
Software Development


Thu Aug 13, 2009 10:23 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2417
Post Re: Help with an Action Item SQL Query
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!

_________________
Colby Clegg
Inductive Automation
Software Development


Thu Aug 13, 2009 10:34 am
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
Colby.Clegg wrote:
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!


Colby,

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

--HandledException


Thu Aug 13, 2009 11:59 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2417
Post Re: Help with an Action Item SQL Query
SQL Server 2005.

_________________
Colby Clegg
Inductive Automation
Software Development


Thu Aug 13, 2009 12:58 pm
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
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)

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:
Code:
Concat({startTime_year},"-",{startTime_month},"-",{startTime_day}," ",{startTime_hour},":",{startTime_minute},":",{startTime_second})
GetStartTime:
Code:
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.


Attachments:
FSQL Screenshots.zip [153.83 KiB]
Downloaded 249 times
Thu Aug 13, 2009 1:25 pm
Profile
Sergeant
Sergeant

Joined: Thu Aug 06, 2009 11:30 am
Posts: 54
Location: Grand Forks, ND
Post Re: Help with an Action Item SQL Query
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


Thu Aug 13, 2009 2:19 pm
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2417
Post Re: Help with an Action Item SQL Query
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,

_________________
Colby Clegg
Inductive Automation
Software Development


Fri Aug 14, 2009 8:15 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2417
Post Re: Help with an Action Item SQL Query
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,

_________________
Colby Clegg
Inductive Automation
Software Development


Fri Aug 14, 2009 8:23 am
Profile
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: