Questions about filtering

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Questions about filtering

Postby OBEXTO » Thu Jan 21, 2010 6:59 am

Hello,

Is it possible to filter verticaly and horizontally ?
Example : I want to include a column when the row is of type In1, but this column should be excluded from synchronization when the row is of type Different.
I see a way to do this by generating two execution blocks with two different SelectionDelegate (one for the In1 and one for the Different) so that I can modifiy the TableDifference.ResultStore.Fields between the two block generation. Is it correct to try this ?

Is it possible to modify the type of a row ?
Example : I get a row of type Different or Same but I want to delete it in the second base, according to external criterias.

Thank you for advice.
OBEXTO
 
Posts: 4
Joined: Thu Jan 21, 2010 6:39 am

Precisions

Postby OBEXTO » Fri Jan 22, 2010 6:11 am

I tried what I mentionned before, but I have a serious problem.

I use this method http://labs.red-gate.com/index.php/Vertical_SQL_Filter to remove a FieldPair.

Why is this code nearly the same as here http://labs.red-gate.com/index.php/Vert ... lts_Filter ?
The links mention Fields.Remove and FieldMapping.Remove here http://labs.red-gate.com/index.php/Filt ... a_overview


Then I use this method to filter the rows : http://labs.red-gate.com/index.php/FilterSQLExample

Now the result I get.
If I have a table with 3 columns A, B, C and I want to exclude B from insertion scripts, the script generated is :
INSERT(A, C) -- OK here
VALUES(a, b, c) -- a problem here

For update, it is even worth because the values are shifted, and there is not necessraly an error :
UPDATE T
SET A = a,
B = c
...

Any advice and workaround ?


Thanks
OBEXTO
 
Posts: 4
Joined: Thu Jan 21, 2010 6:39 am

Postby OBEXTO » Fri Jan 22, 2010 9:33 am

Trying to generate my own scripts I think I pointed out something.
Removing a FieldPair from the StoreFields change the content of the reader (a row doesn't contain values for the removed KeyPair).
Unfortunatly, it seems that the OrdinalInResults1 are not relocated.
OBEXTO
 
Posts: 4
Joined: Thu Jan 21, 2010 6:39 am

Postby Brian Donahue » Mon Jan 25, 2010 5:23 pm

Using the sample databases (WidgetStaging, WidgetProduction) and the "horizontal filter example" and adding the vertical filter by inserting the following lines after line #46
Code: Select all
 TableDifference diff = m_TableDifferences["[dbo].[Widgets]"];
diff.ResultsStore.Fields.Remove(diff.ResultsStore.Fields["Description"]);

Before the change, the resulting script is:
INSERT INTO [dbo].[Widgets] ([RecordID], [Description], [SKU]) VALUES (8, 'New widget', 'NW')
After adding the code from the vertical filter example, the insert changes but the number of columns and the number of values is consistent:
INSERT INTO [dbo].[Widgets] ([RecordID], [SKU]) VALUES (8, 'New Widget')
I am not getting more values than columns in my case, but the "description" value has moved to the "SKU" column. Taking the SelectionDelegate out of the equation still yeilds the same result for row 8.

I'd assume this is a bug -- clearly the Engine is making an effort to remove the value from the results as well as the field name, but it's not removing the right value. I'll have to get back to you on this one.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby OBEXTO » Mon Jan 25, 2010 5:35 pm

I probably made a mistake reporting the issue with Insert. I have about 50 columns so maybe I didn't see that one was removed at the end. I focused on the fact that values were shifted.

If there is a bug to correct, there is no emergency as I wrote my own scripts (quite simple scripts) to meet our requirements.

Thanks.
OBEXTO
 
Posts: 4
Joined: Thu Jan 21, 2010 6:39 am

Postby Brian Donahue » Wed Jan 27, 2010 6:56 pm

Thanks! Our internal reference number for this bug (if it is a bug) is CSD-129.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests