Fernando


Thinking about it I think (but I'm not sure) I can see a way to do it, but it's not going to be easy. Unless its sneaked in whilst I wasn't looking the main lack will be the equivalent of table.Fields[xx]

>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.

I was mightily pissed off when I had to add dlls into the mix but I got over it fairly rapidly if for no other reason than to add ssl into my email subsystem I had no choice other than to do that and once you've done it for one adding a second or a third isn't a big deal.

Roy

Gruetzmacher,

I see... I was thinking you only wanted to test if they are equal or not, but yeah, it's doable in any case.
It's still not clear to me what do you expect to be the result...
You said a string containing the differences yeah but in what format?
Perhaps a list of pairs <ColumnName> = <NewValue> separated by a delimiter char like #13, for columns where Value2<>Value1 ?
If that is the case I might come up with something tomorrow (I'm busy right now with a project ).

--
Fernando Dias
[Team Elevate]

Roy,

<<Thinking about it I think (but I'm not sure) I can see a way to do it, but it's not going to be easy. Unless its sneaked in whilst I wasn't looking the main lack will be the equivalent of table.Fields[xx]>>

That's it, there is a way and it's not hard, I just can't recall the details now, I'll have to dig my own code because I'm pretty sure I've done something similar. I'm in a hurry today, but I'm pretty sure I'll be able to come up with something by tonight or tomorrow.

<< I was mightily pissed off when I had to add dlls into the mix but I got over it fairly rapidly if for no other reason than to add ssl into my email subsystem I had no choice other than to do that and once you've done it for one adding a second or a third isn't a big deal.>>

Yeah, I have been resisting but sooner or later I'll have to do it too... there are some important cryptography functions that I need to generate signatures that I would like to move to the server and I can't.

--
Fernando Dias
[Team Elevate]

Gruetzmacher,

Here it is.
The performance is for sure not good because it takes 2 selects for each column in the table, but the idea is simple. Also, depending on the way you want to deal with NULL values, you might want to remove the 3 COALESCE calls in the code.


ALTER FUNCTION "CompareRow" (IN "TableName" VARCHAR COLLATE ANSI, IN "RowID1" INTEGER, IN "RowID2" INTEGER)
RETURNS VARCHAR COLLATE ANSI
BEGIN
  DECLARE Cur1 SENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
  DECLARE ColName VARCHAR;
  DECLARE Diff VARCHAR;
  DECLARE St1 VARCHAR;
  DECLARE St2 VARCHAR;
  DECLARE I INTEGER;

  -- The result of this query is ignored, it's only used to retrieve the column names
  -- and it could be replaced with a query on Information.TableColumns
  PREPARE Sql1 FROM 'SELECT * FROM "' + TableName +'" WHERE ID=0';

  OPEN Cur1;
  SET I = 1;
  SET Diff = '' ;
  WHILE I <= COLUMNCOUNT(Cur1) DO
    SET ColName = COLUMNNAME(Cur1, I) ;
    IF ColName NOT IN ('ID', 'VERSION') THEN
      EXECUTE IMMEDIATE
        'SELECT
           CAST((SELECT "' + ColName + '" FROM "' + TableName + '" WHERE ID = ?) AS VARCHAR) AS V1,
           CAST((SELECT "' + ColName + '" FROM "' + TableName + '" WHERE ID = ?) AS VARCHAR) AS V2
         INTO
          ?, ?'
        USING RowID1, RowID2, St1, St2;
      IF COALESCE(St1, '') <> COALESCE(St2, '') THEN
        SET Diff = Diff + ColName + '=' + COALESCE(St2, '') + #13;
      END IF;
    END IF ;
    SET I = I + 1;
  END WHILE;
  CLOSE Cur1;
  RETURN Diff ;
END


--
Fernando Dias
[Team Elevate]

Em 17/05/2013 12:02, Gruetzmacher escreveu:
> 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
>>
>

oha,
thank you fernando - i will try this out tomorrow.
it actually is much short than expected
which is a good sign

Fernando Dias wrote:

Gruetzmacher,

Here it is.
The performance is for sure not good because it takes 2 selects for each column in the table, but the idea is simple. Also, depending on the way you want to deal with NULL values, you might want to remove the 3 COALESCE calls in the code.


ALTER FUNCTION "CompareRow" (IN "TableName" VARCHAR COLLATE ANSI, IN "RowID1" INTEGER, IN "RowID2" INTEGER)
RETURNS VARCHAR COLLATE ANSI
BEGIN
  DECLARE Cur1 SENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
  DECLARE ColName VARCHAR;
  DECLARE Diff VARCHAR;
  DECLARE St1 VARCHAR;
  DECLARE St2 VARCHAR;
  DECLARE I INTEGER;

  -- The result of this query is ignored, it's only used to retrieve the column names
  -- and it could be replaced with a query on Information.TableColumns
  PREPARE Sql1 FROM 'SELECT * FROM "' + TableName +'" WHERE ID=0';

  OPEN Cur1;
  SET I = 1;
  SET Diff = '' ;
  WHILE I <= COLUMNCOUNT(Cur1) DO
    SET ColName = COLUMNNAME(Cur1, I) ;
    IF ColName NOT IN ('ID', 'VERSION') THEN
      EXECUTE IMMEDIATE
        'SELECT
           CAST((SELECT "' + ColName + '" FROM "' + TableName + '" WHERE ID = ?) AS VARCHAR) AS V1,
           CAST((SELECT "' + ColName + '" FROM "' + TableName + '" WHERE ID = ?) AS VARCHAR) AS V2
         INTO
          ?, ?'
        USING RowID1, RowID2, St1, St2;
      IF COALESCE(St1, '') <> COALESCE(St2, '') THEN
        SET Diff = Diff + ColName + '=' + COALESCE(St2, '') + #13;
      END IF;
    END IF ;
    SET I = I + 1;
  END WHILE;
  CLOSE Cur1;
  RETURN Diff ;
END


--
Fernando Dias
[Team Elevate]

Em 17/05/2013 12:02, Gruetzmacher escreveu:
> 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
>>
>

hello fernando,
this works brilliantly!

thank you very much!

Fernando

Nice - better than the approach I would have used. Maybe Gruetzmacher can let us know how the speed compares with the Delphi version.


Roy Lambert

I'm sure that any Delphi version of the function would be way faster.
Also, I'm still not convinced that my function is the best approach PSM/SQL aproach but it's the best I could think of until now.

--
Fernando Dias
[Team Elevate]

it is difficult to compare the speed ... but generally delphi is faster but did a lot more (lookups, field translation, considered record versions). it is still a great feature since it is good enough for the user and works across all databases ... actually tim could implement this as a standard feature