Prev: Register problem Report Pro
Next: Error in OLE Client.
From: Mike Pitcher on 21 Oct 2007 15:45
On 21 Oct, 16:03, johan....(a)xsinet.co.za wrote:
> Hi Karl,
>
> Tags used in our timber industry can be of two kinds, barcoded, or a
> plastic tag attached to some form of nail that is driven into the
> thin end of a log by a special hammer.
>
> http://www.hangtags-labels.com/pics/photo7.jpgis an example.
>
> Tags are prefabricated and gets ordered in sequential ranges of
> numbers. So each log sold is recorded against its tagno. And yes the
> number is only there for uniqueness.
>
> However the sequential numbering is just a convenience from a data
> capturing point of view infield (just enter the first number on form
> and only when there is a break in the sequence), as well as computer
> capturing, as it can then be auto-created given a starting value when
> a form is added instead of having as part of the capturing to also
> capture the tagno.
>
> The problem arise not so much from the tag numbers, it has to do with
> human error where a tag number is recorded wrongly and hence it
> generates "duplicates".
>
> Not all companies use barcode scanners and dataloggers which limits
> the human error factor to a large extent. The system should however
> cater for the smaller companies too that capture still using paper log
> tally sheets.
>
> HTH,
>
> Johan

<Reality on>
You deserved to win ... and probably would still have done so if the
Aussie Fourth Official had given us that try! BTW I think he was
right not to do so.

Wasn't it great to see two teams who know how to scrummage? If the
Aussies get their way they'll turn Rugby Union into Rugby League.

Do you know the difference between a 747 and an Aussie supporter
returning from Paris?

The 747 stops whining when it lands.

Seriously, are you going to have a racial quota next time? If
everyone is as good as Bryan Habana the rest of us wont stand a
chance.

Well done RSA.
<Reality off for another 4 years>

Mike Pitcher


From: johan.nel on 21 Oct 2007 17:19
Hi Mike,

Thanks for the kind words. I am really proud of our boys, but credit
to your team too. Not many teams that would have reached a final
without the guts you showed there and you went down fighting in the
final too.

> Do you know the difference between a 747 and an Aussie supporter
> returning from Paris?
> The 747 stops whining when it lands.

< Reality on >
I heard Playtex actually released a new bra called the KangoKiwi...
Lots of apparent support but no cups.
< Reality off >

> Seriously, are you going to have a racial quota next time? If
> everyone is as good as Bryan Habana the rest of us wont stand a

Quota systems will be a reality in SA for a long time, not just in
sport but in the work place too and is having an effect on small
business, however we stick to our guts and just have to work with the
system without spending energy on "whining" (no pun intended.

However on a lighter note, I still don't have a real answer on my
question yet...<VBG>

Regards,

Johan Nel
Pretoria, RWC country.

From: Geoff Schaller on 21 Oct 2007 17:53
Johan,

> Well again, my question was give me example of how you would update a
> table where you have PK of KeyFld + ChildFld and a unique ChildFld and
> the update/insert could lead to duplicate ChildFld values due to human
> error, and most of what I got back was a discussion around why on
> earth do I need a unique ChildFld.

Then you didn't read! Your PK design is incorrect - it is not needed.
Equally, FK use like this is not mandatory and you could do with an SP
exactly what a FK would do, except after the fact. I can't give you code
because you haven't given us any. You have a choice.

Your argument goes like this:

'I have a process in DBF which I cannot do in SQL so therefore I need
another way. Please Help.'

We then suggested an alternate an you just sat there and complained that
it would not work because we could not understand the complexities of
your needs. Well explain them!

You still have not said whether or not it is allowed to adjust the
computer records to what "should" be and what you now do about the
incorrect log tags and accompanying records.

Adjusting the tags in the computer is no problem at all.
Allowing duplicates temporarily or otherwise is no problem at all.
Using an array to sort and manage the outcome provides some
efficiencies.

Show us the code that you say cannot work in SQL and let us work from
there. Otherwise you are speculating as much as we have to from lack of
detail.

Geoff

From: johan.nel on 25 Oct 2007 12:20
Geoff,

Sorry for late reply, think there was something wrong with my news
server and I only picked up your response when I googled.

> Then you didn't read! Your PK design is incorrect

> because you haven't given us any examples. You have a choice.
[snip]
> your needs. Well explain them!
>

Well lets start with a normal order/order detail example. Each order
can contain multiple order items.

In real life we will have an Order table with a PK of OrderNo, and an
order item table with FK OrderNo and a PK of OrderNo + OrderItemTypeNo
to ensure we don't have the same order item type duplicated on the
order detail, but use the quantity field rather to indicate more than
1 of the order item type is required.

In my example very simalar. I have a Form (Order) with a PK FormNo,
where I record timber logs on (my Order detail), each timber log is
uniquely identified by a barcoded/numbered tag, therefor I have a PK
on my Form detail table of FormNo + TagNo, similar to OrderNo +
OrderItemTypeNo or OrderLineNo. Nothing strange here.

However my quantity field cannot be more than 1 as I cannot tag more
than 1 log with the same tag and each tag has a unique ID. Therefor
my form detail contains an additional unique setting on TagNo too on
top of the PK, and I then don't need another FK from a table where I
store each individual TagNo that is in my possession. These unique
TagNo's are used to trace back if there are queries regarding the
individual log grading and hence it can be related back to the form it
was recorded on.

> You still have not said whether or not it is allowed to adjust the
> computer records to what "should" be and what you now do about the
> incorrect log tags and accompanying records.
I write the duplicate LogTags to an error file with the same structure
as Form details, without the Unique TagNo set, but only an index on
FormNo.

>
> Adjusting the tags in the computer is no problem at all.
Yes I agree, however it cannot just be adjusted without first
confirming with field personnel what the right tag numbers should be.
I hope my sample code will try and explain what I do.

> Show us the code that you say cannot work in SQL and let us work

Well I got it working, however I would still like some ideas on where
I can maybe use a more efficient method (Except moving to ADO) which I
will do soon <VBG>.

Here is the code:

METHOD GatherLogs() CLASS LTFormTag
LOCAL oSqlLogs, oSqlDelete, oSqlError AS SqlStatement
LOCAL cValues, cStmtDel, cStmtLog AS STRING
LOCAL nFormNo, nLog, nDia, nLen, nCnt, nTag AS LONGINT
LOCAL lUpdated, lGather := TRUE, lError, lLogErr AS LOGIC
LOCAL symTag, symDia, symLen, symStage AS SYMBOL

nFormNo := SELF:oDCm_FormNo:Value

cStmtDel := 'DELETE * FROM FormLog ' + ;
'WHERE FormNo = ' + NTrim(nFormNo)
cStmtLog := 'INSERT INTO FormLog ' +;
'(FormNo, TagNo, LogLen, LogDia) ' + ;
'VALUES (?, ?, ?, ?)'
cStmtErr := 'INSERT INTO FormLogErr ' + ;
'(FormNo, TagNo, LogLen, LogDia) ' + ;
' VALUES (?, ?, ?, ?)'

oSqlDelete := SqlStatement{cStmtDel, SELF:Server:Connection}
IF lGather := oSqlDelete:Execute()
oSqlDelete:Commit()
// This is a possible problem if the server connection goes down
// at this stage. Advise please
nLogCount := SELF:oDCm_LogCount:Value
// Write each of the logs to the detail table
FOR nLog := 1 UPTO 50 // 50 logs on a form
symTag := String2Symbol('m_Tag'+StrZero(nLog,2,0))
symDia := String2Symbol('m_Dia'+StrZero(nLog,2,0))
symLen := String2Symbol('m_Len'+StrZero(nLog,2,0))
IF (nTag := SELF:GetDC(symTag):Value) > 0
// GetDC is a method to retrieve from SELF:aControls
oSqlLogs := SqlStatement{cStmtLog, SELF:Server:Connection}
nDia := SELF:GetDC(symDia):Value
nLen := SELF:GetDC(symLen):Value
IF (lUpdated :=;
oSqlLogs:Execute({nFormNo, cTag, nLen, nDia}))
lUpdated := oSqlLogs:Commit()
ELSE
oSqlLogs:RollBack()
ENDIF
oSqlLogs:FreeStmt(SQL_DROP)
IF !lUpdated
oSqlError := SqlStatement{cStmtErr, SELF:Server:Connection}
IF lUpdated :=;
oSqlError:Execute({nFormNo, cTag, nLen, nDia})
lLogErr := TRUE
oSqlError:Commit()
ELSE
InfoBox{SELF, 'Log : ' + NTrim(nLog), ;
'Error writing log info to database' + CRLF + CRLF + ;
'Database error message : ' + ;
oSqlError:ErrInfo:ErrorMessage}:Show()
oSqlError:RollBack()
lError := TRUE
lGather := FALSE
ENDIF
oSqlError:FreeStmt(SQL_DROP)
ENDIF
ENDIF
NEXT
IF lLogErr
InfoBox{SELF, 'Update transaction failed', ;
'Errors occurred writing form data to database' + ;
CRLF + CRLF + ;
'Problem data saved to error log for rectification'}:Show()
ENDIF
ELSE
oSqlDelete:ErrInfo:ShowErrorMsg()
oSqlDelete:RollBack()
ENDIF
oSqlDelete:FreeStmt(SQL_DROP)
RETURN lGather

Thanks in advance for any suggestions to optimise the process.

Regards,

Johan Nel
Pretoria, South Africa.

From: Geoff Schaller on 25 Oct 2007 18:31
Johan,

I think your primary PK design is wrong at the item level. The way you
described it doesn't really make sense. You either need a unique row
number or ID as the PK or no PK at all. Having no PK is no sin and often
is warranted. In fact there are some people who believe clustered
indexes are evil (Oracle people, for instance <g>).

You certainly shouldn't have a PK on order number plus type and then use
the qty field to indicate multiple types. This just doesn't make sense.
In sales terms, for instance, it means you could never have two items of
the same type with different prices.

> Well I got it working, however I would still like some ideas on where
> I can maybe use a more efficient method (Except moving to ADO) which I
> will do soon <VBG>.

But you haven't said what is inefficient?

As I said several posts ago (and confirmed by another correspondent)
that I would prefer a different process where you didn't delete
everything and then re-insert. Your process prohibits anyone being able
to access this "order" until you complete your process and to me, that
will be a serious contention issue in a busy environment. However I have
acknowledged it can work.

So you are looking for ideas. About what? This is all simple SQL and you
don't like our architecture and methodology suggestions so what else is
there to discuss?

Geoff


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