Hi,

I can't get the following to work: I try to update all dates in a table by adding one year. The SQL statement I need for this is of course quite simple:

UPDATE TableA SET [endDate] = DATEADD(yy, 1, endDate)

When I want to do this in code however, it all starts to get complicated. I have written the following code:

DbFunctionCall dbFunctionCall = new DbFunctionCall("DATEADD", new object[] {"yy", 1, TableAFields.EndDate });
TableAEntity newValues = new TableAEntity();
newValues.Fields[(int)TableAFieldIndex.EndDate].ExpressionToApply = dbFunctionCall;
TableACollection updater = new TableACollection();
updater.UpdateMulti(newValues, null);

I then get the following SQL statement:

exec sp_executesql N'UPDATE TableA SET [EndDate]=DATEADD(@LOce01b7d71, @LO12, [EndDate]),N'@LOce01b7d71 nvarchar(2),@LO12 int',@LOce01b7d71=N'yy',@LO12=1

This fails with the following error: Invalid parameter 1 specified for dateadd

The problem is that DATEADD does not accept the yy datepart in such a fashion.

How can I resolve this? Note that in the final code, the datepart must be variabel.

Regards,

Patrick

Do you have to be one year out? If 365 days later would be good enough you can format the query to be something like

UPDATE TableA SET [endDate] = endDate + 365

The code would look something like this.

TableAEntity newValues = new TableAEntity();
newValues.Fields[(int)TableAFieldIndex.EndDate].ExpressionToApply = (TableAFields.EndDate + 365);
TableACollection updater = new TableACollection();
updater.UpdateMulti(newValues, null);

I know this means that when the span goes over a leap day you will end up with the next year and one day back, but sometimes 365 days is the desired behavior over 1 year.

It's not directly supported at the moment. One way to work around this is by grabbing the DbFunctionCall sourcecode from the ormsupportclasses sourcecode, copy it into a new class and add a property which accepts a collection of strings which are emitted into the string. The problem currently with DbFunctionCall is that it sees every passed in value as a parameter and also wraps it in an ado.net parameter, but with functions like dateadd, cast etc. some of these parameters should be concatenated into the query string without being wrapped in a parameter.

The new class could live in your own project. If you need help with how to add the code to the DbFunctionCall class, let me know. It's straightforward really: the ToQueryText routine is called when the DbFunctionCall has to convert itself to SQL, so you just have to adjust that.

bclubb wrote:

Do you have to be one year out? If 365 days later would be good enough you can format the query to be something like

UPDATE TableA SET [endDate] = endDate + 365

The code would look something like this.

TableAEntity newValues = new TableAEntity();
newValues.Fields[(int)TableAFieldIndex.EndDate].ExpressionToApply = (TableAFields.EndDate + 365);
TableACollection updater = new TableACollection();
updater.UpdateMulti(newValues, null);

I know this means that when the span goes over a leap day you will end up with the next year and one day back, but sometimes 365 days is the desired behavior over 1 year.

Thanx for your reply.

Alas, this is not the solution. Our application manages payed subscriptions. As you probably know from any subscriptions you have yourself; when a subscription is renewed, the end validity of the renewal has to be exactly the same, regardless of the number of days in a year. So a subscription valid to 2006/12/31 has to be renewed to 2007/12/31. To make things more complicated, subscriptions must be able to be renewed by either days, weeks, quarters, months and years depending on the type of subscription and the requirements of our customers.

Regards,

Patrick

You may try to use Frans suggestion, or you may implement IExpression as in the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829 To generate the query text you need.

I guess the simplest solution for me right now is to go with Frans' proposal.

However, the problem with this is that this gives me some maintenance headaches. If a bug is discovered in the original DbFunctionCall or something else has changed, my code will not be updated accordingly unless I read the release notes very carefully. I've tried subclassing DbFunctionCall but that did not work due to the use of private fields.

Therefore, will DatePart be implemented in LLBLGenPro? As I see it, it would not take more than introducing a new data type (DatePart), checking if an object is of that type and then injecting it in the query text instead of generating a parameter. Correct me if I am wrong.

It would help me big time as working with times and dates is our very core business and I guess I am not the only one who would like this feature.

In v2.1 we'll add more features to dbfunctioncall so textual parts of a function call which shouldn't be in a parameter are supported.

The DbFunctionCall is pretty straight forward, and if you COPY the sourcecode of that class into your own class and call it DbFunctionCallEx or something, you can safely update the runtime you're working with and if your copy works OK, don't worry. There's not a lot that can go wrong with the DbFunctionCall, and no issues are known except this textual snippet issue.

Otis wrote:

In v2.1 we'll add more features to dbfunctioncall so textual parts of a function call which shouldn't be in a parameter are supported.

The DbFunctionCall is pretty straight forward, and if you COPY the sourcecode of that class into your own class and call it DbFunctionCallEx or something, you can safely update the runtime you're working with and if your copy works OK, don't worry. There's not a lot that can go wrong with the DbFunctionCall, and no issues are known except this textual snippet issue.

Yep, I understood the COPY part and implemented it that way and it works.

I meant that if I in the future encounter an error due to a bug in the version of the DBFunctionCall code I have used as the source of my DBFunctionCallEx code, which is already a known bug and already solved by you, it will be hard to debug this.

For example, I encounter a bug while I am using version 20070506 which has already been solved in 20070218 but still is in my code because I use the DbFunctionCall of version 20061106 (am I making any sense here? )

PatrickD wrote:

Otis wrote:

In v2.1 we'll add more features to dbfunctioncall so textual parts of a function call which shouldn't be in a parameter are supported.

The DbFunctionCall is pretty straight forward, and if you COPY the sourcecode of that class into your own class and call it DbFunctionCallEx or something, you can safely update the runtime you're working with and if your copy works OK, don't worry. There's not a lot that can go wrong with the DbFunctionCall, and no issues are known except this textual snippet issue.

Yep, I understood the COPY part and implemented it that way and it works.

I meant that if I in the future encounter an error due to a bug in the version of the DBFunctionCall code I have used as the source of my DBFunctionCallEx code, which is already a known bug and already solved by you, it will be hard to debug this.

For example, I encounter a bug while I am using version 20070506 which has already been solved in 20070218 but still is in my code because I use the DbFunctionCall of version 20061106 (am I making any sense here? )

Yes I understand you, but as I said: if your dbfunctioncallex works fine, you shouldn't worry. You can of course try to keep up to date. If you want, just use a tool like WinMerge (or other diff/merge tool). These tools show instantly where changes are in the original source you based your code on and the latest build. So to keep things easy: keep a copy of the original dbfunctioncall class you base your work on so you can easily spot differences between THAT file and the latest build. Then migrate that change to your own code IF necessary. But only do that if you see your own code doesn't work.

There are two routines which can have bugs: ToQueryText and SetParameters. As it is now, I don't expect anything major in that area also because this code is used in a lot of different situations without any problems.