hello,
currently i have this feature in the delphi frontend, but
i would like to move it into an sql-function since this gives much more possibilities:

how to compare two records of the same table?

i know i could generate a function dynamically for each table but this solution is not appropriate since there are nearly 200 tables in the db.

the function parameters should be like this: tablename, record-id1, record-version1, record-id2, record-id2
i could query via information.tablecolumns which columns should be investigated.
from this i could create a cursor for each row.
however i can not loop over the cursor colums afaik ... as i could in delphi with the tfield-collection of a tdataset ...

is there a solution for this?
help is greatly appreciated

thank you

Gruetzmacher


The only thing that springs to mind is to write your own function in Delphi and install it in ElevateDB. Its fairly simple if you use Tim's templates that come with the product.

What I can't envisage is what you would do with it in SQL.

Roy Lambert [Team Elevate]

thank you roy,
i totally forgot about that. yes, the extension is simple indeed (i did some for regex-functionality)

great - have a nice day

Gruetzmacher


I'd still like to know what you're going to do with it in SQL - I'm dead nosey.

Roy Lambert

hello roy,
there are some usages for it ... apart from trying to keep most logic into the database ...
most likely would be the usage of some db-triggered email notification that informs a group of people what has changed ...
moreover the users have a report generator, which does not work on tdataset but sql only ... there is a report that shows the record change log. before i did this with generic triggers that put these change informations into a memo field. but i considered this to slow down the system a bit ... so i try to provide this protocol 'on demand'

Gruetzmacher,

In addition to what Roy already suggested, I do believe it also can be done using PSM/SQL.
I'll give it a try and post it here as long as you explain me the meaning of the parameters...

--
Fernando Dias
[Team Elevate]

Em 16/05/2013 07:07, Gruetzmacher escreveu:
> hello,
> currently i have this feature in the delphi frontend, but
> i would like to move it into an sql-function since this gives much more possibilities:
>
> how to compare two records of the same table?
>
> i know i could generate a function dynamically for each table but this solution is not appropriate since there are nearly 200 tables in the db.
>
> the function parameters should be like this: tablename, record-id1, record-version1, record-id2, record-id2
> i could query via information.tablecolumns which columns should be investigated.
> from this i could create a cursor for each row.
> however i can not loop over the cursor colums afaik ... as i could in delphi with the tfield-collection of a tdataset ...
>
> is there a solution for this?
> help is greatly appreciated
>
> thank you
>

Fernando

>In addition to what Roy already suggested, I do believe it also can be done using PSM/SQL.
>I'll give it a try and post it here as long as you explain me the meaning of the parameters...

Brilliant offer. It would be interesting to compare the two different approaches.

Roy Lambert

Roy,

It's also of my own interest as I also have it implemented as a Delphi function and I also want to move it to the server. So far I have been able to avoid external functions, not because of performance or elegance, but only because I have been trying not to add any DLLs to my server setup.

--
Fernando Dias
[Team Elevate]

Em 17/05/2013 10:27, Roy Lambert escreveu:
> Fernando
>
>> In addition to what Roy already suggested, I do believe it also can be done using PSM/SQL.
>> I'll give it a try and post it here as long as you explain me the meaning of the parameters...
>
> Brilliant offer. It would be interesting to compare the two different approaches.
>
> Roy Lambert
>

hello fernando,
thank you for the offer. i try to explain:
every table has these columns: ID - autoincrement, VERSION - integer: indicator how often the record was changed. for the whole scenario this column actually is not relevant. so you can just ignore it.

it would be nice if there can be a function that takes the Tablename, and two record IDs and returns a string with the changes ...

i am very much interested ... thank you in advance

Fernando Dias wrote:

Gruetzmacher,

In addition to what Roy already suggested, I do believe it also can be done using PSM/SQL.
I'll give it a try and post it here as long as you explain me the meaning of the parameters...

--
Fernando Dias
[Team Elevate]

Em 16/05/2013 07:07, Gruetzmacher escreveu:
> hello,
> currently i have this feature in the delphi frontend, but
> i would like to move it into an sql-function since this gives much more possibilities:
>
> how to compare two records of the same table?
>
> i know i could generate a function dynamically for each table but this solution is not appropriate since there are nearly 200 tables in the db.
>
> the function parameters should be like this: tablename, record-id1, record-version1, record-id2, record-id2
> i could query via information.tablecolumns which columns should be investigated.
> from this i could create a cursor for each row.
> however i can not loop over the cursor colums afaik ... as i could in delphi with the tfield-collection of a tdataset ...
>
> is there a solution for this?
> help is greatly appreciated
>
> thank you
>

Gruetzmacher


Can you post a sample of the output you're expecting to see? Better still can you post the Delphi function you're using now?

Roy Lambert