Prev: Register problem Report Pro
Next: Error in OLE Client.
From: johan.nel on 19 Oct 2007 00:32
Hi all,

I have a system that I am currently moving from DBF to SQL and have
the following scenario:

Parent table with a child table linked on KeyFld.

Window for simultaneous modification to both Parent and related Child
data on one DataDialog window since Child can have a know maximum
records (50) or less.

At Scatter stage of the form I set an instance var on the form to hold
current number of Child Records, nChildCountStart and as values are
entered / deleted on the form I keep incrementing / decrementing
nChildCountCurrent instance var.

My problem is this. In the DBF solution when the user edit existing
data, when the Save button is pressed I could do:

oDbChild:Seek(self:oDCKeyFld:Value, FALSE)
FOR nRec := 1 UPTO 50
symStage := #Error
IF nRec <= nChildCountCurrent
symAction := iif(nRec <= nChildCountStart, #Update, #Append)
ELSE
symStage := iif(nRec <= nChildCountStart, #Delete, #Nothing)
ENDIF
IF symStage = #Append
oDbChild:Append()
oDbChild:FieldPut(#KeyFld, self:oDCKeyFld:Value)
ENDIF
IF symStage = #Append .OR. symStage = #Update
oDbChild:FieldPut() // All non-Key Field data from controls
ELSEIF symStage = #Delete
// Flag this record for re-use or delete
ELSEIF symStage = #Nothing
EXIT
ELSE // #Error
TextBox{SELF, ProcName(), 'Something weird in the coding' + CRLF
+;
NTrim(nRec) + NTrim(nChildCountStart) +
NTrim(nChildCountCurrent);
}:Show()
EXIT
ENDIF
oDbChild:Skip()
NEXT

My question therefor:

How do I go about in SQL to get the same results?

As a start I tried seeing that I cant traverse like I do a DBF to
delete the records in child table and then do an insert without
committing the delete statement until all are updated, before
committing the delete or do a rollback if an errorflag is set:

// oDeleteStatement 'Delete * from Child where KeyFld match control
value'
// Don't commit/rollback
FOR nRec := 1 UPTO nChildCountCurrent
// Do insert statement for each record
// If no error in execute
// commit
// else
// rollback and set errorflag
// exit
NEXT
IF lErrorFlag
oDeleteStatement:Rollback()
ELSE
oDeleteStatement:Commit()
ENDIF
oDeleteStatement:FreeStmt(SQL_DROP)


I hope I gave enough info to provide you with what I am trying to do.
Any ideas of how to go about would be highly appreciated.

Thanks in advance.

Johan Nel
Pretoria, South Africa.

From: Geoff Schaller on 20 Oct 2007 04:14
Johan.

You have confused me: what is the problem?

Apart from the fact that the process seems awfully convoluted (just to
add/save etc), I don't follow where you think a problem is. For
instance, do you have primary and foreign keys set up? Because the
answer is different. Let me assume you have.

To get a count of records at any time, simply execute;

Select count(*) from oChild.

To delete all records from the child you just do:

Delete oChild where fieldx = xxxxx

You compile the statement to execute so you simply choose whether it is
update or insert and structure the statement accordingly. You will not
be able to re-use deleted records but then you wouldn't want to in SQL.
You don't need to.

So, have I missed something?

Geoff



From: johan.nel on 20 Oct 2007 04:42
Hi Geoff,

I think I have sorted the problem, but still don't know if its the
best way of doing it.

Parent has a primary key on KeyFld
Child has primary key on KeyFld,ChildFld
ChildFld is also unique.

The issue is this:
Until a "processed flag" is set, the user can make modifications to
ChildFld and the data in the Child Table.

As part of the changes, records can be added, modified or deleted in
the Child Tabble.

These changes can change the ChildFld so that one records ChildFld
value becomes the value for another record.

So my first idea trying to think in a SQL way was:

Delete all records for KeyFld = criteria, but don't commit.

Then to INSERT INTO ChildTable all records on form.

If successful Commit the delete, else Rollback.

Not sure if I make myself more clear on this one.

Johan
On Oct 20, 10:14 am, "Geoff Schaller"
<geo...(a)softwareobjectivesx.com.au> wrote:
> Johan.
>
> You have confused me: what is the problem?
>
> Apart from the fact that the process seems awfully convoluted (just to
> add/save etc), I don't follow where you think a problem is. For
> instance, do you have primary and foreign keys set up? Because the
> answer is different. Let me assume you have.
>
> To get a count of records at any time, simply execute;
>
> Select count(*) from oChild.
>
> To delete all records from the child you just do:
>
> Delete oChild where fieldx = xxxxx
>
> You compile the statement to execute so you simply choose whether it is
> update or insert and structure the statement accordingly. You will not
> be able to re-use deleted records but then you wouldn't want to in SQL.
> You don't need to.
>
> So, have I missed something?
>
> Geoff


From: Pierre on 20 Oct 2007 04:59
Hello Johan,

First, as certainly many developpers leaving dbf to sql, I have had this
kind of questions, and have searched the way to apply the same way of
thinking and the same constructions in the new environnement.
But quickly, at least for me, it turned out to be that it could not be done
this way. I had to change things, and fist of all, I had to organize the
data in the way SQL drove me to manipulate data.

For example if I have two tablesin a parent to child one to many relation, I
could have for instance :

Parent_1 table : id, data in parent...
Child_1 table : idParent, data in child...

Parent_2 table : id, data in parent...
Child_2 table : idParent, data in child...

and use the records in childs as a counter, because in dbf it is easy, and
lends itself to the record counting.
Of course, the records should be in the same order in child_1 and child_2.

In SQL I would rather organize the table roughtly this way :
Parent_1 table : id, data...
Child_1 table : idParent, childNumber, data...
Parent_2 table : id, data...
Child_2 table : idParent, childNumber, data...
Note that I write childNumber to indicate that this could not be an IDENTITY
column that would not allow easily an update from child_1 on child_2.

childNumber in both tables would be the same (1-1, 2-2, ...)
That way I can do things like the following (MS-SQL) :
Ask myself : what are the rows in child_1 that are missing in child_2 where
child_1 and child_2 rows have the same Parent.id.

SELECT *
FROM child_1 c1
LEFT OUTER JOIN child_2 c2 ON c2.idParent = c1.idParent and
c2.childNumber=c1.childNumber
WHERE c2.childNumber is NULL

This way I can append in child_2 what is missing from child_1 :

INSERT INTO child_2
SELECT *
FROM child_1 c1
LEFT OUTER JOIN child_2 c2 ON c2.idParent = c1.idParent and
c2.childNumber=c1.childNumber
WHERE c2.childNumber is NULL

And update child_2 from child_1

UPDATE child_2
SET child_2.data=c1.data
FROM child_1 c1
WHERE child_2.idParent=c2.idParent and child_2.childumber=c2.childNumber

And surround these instructions with BEGIN TRANSACTION / COMMIT
Of course, all that has to be tested.

HTH, and I hope that my understanding of your question is correct.

Pierre


From: johan.nel on 21 Oct 2007 04:17
Lol,

Geoff maybe I should explain exactly why I am doing things the way it
is done....:

The table is part of a timber sales monitoring system.

KeyFld is the Form No relating to a form with log data captured in
field, each Form can contain 50 logs, each log gets a unique tag
(ChildFld) related to a bar coded/plastic tag with a number on it.
Numbers on tags "normally" runs in sequence. Therefor the PK (KeyFld +
ChildFld) and unique ChildFld relating to tag numbers.

However it sometimes happen that field personnel "miss a tag" and the
sequence in tag numbers is broken, which is trapped when duplicate Tag
numbers are entered into the system, hence the "processd flag", which
is used to indicate field personnel can go back and verify the correct
tag numbers, etc. Therefor ChildFld can be modified on a number of
forms to correct the errors.

Does this help to make it more clear relating to my question?

Johan
PS: So let me rephrase the question.
Can I get some ideas about how to go about doing this in SQL as the
DBF way is working fine, however the system is really getting too
large and complex for DBF.

 |  Next  |  Last
Pages: 1 2 3 4 5 6
Prev: Register problem Report Pro
Next: Error in OLE Client.