ODBC Woes, how to overcome  
Author Message
haroldse





PostPosted: Fri Feb 06 12:58:38 CST 2004 Top

Exchange Servers >> ODBC Woes, how to overcome

Hi gang,

We are setting up a web portal for our production data to be made available
to our clients, and I have to setup a data exchange so that web updates are
made into our tables. For lots of reasons we do not want the web stuff to
touch our actual tables so we have a change log that will accomplish this.

The problem: We need to have a transaction ID field on each record in the
log table. I thought (along with the web developer) that an autoincrement
field would be the ticket. NOT! As you may or may not know, the ODBC drivers
for VFP will not read a table if it has an autoincrement field, you get a
'not a table' error

Next solution is to have a table that stores the PK, and is incremented
during the INSERT Trigger. This has proved problematic because VFP passes a
non updatable cursor to the INSERT trigger. Is there a way to update a field
in the new record before the insert occurs? I would like to do this during
an insert.

Pull PK Value from the PK table, increment value and write back to the PK
table.
Use this value as a PK for the log file.

Is this possible?

TIA,

Marc

Exchange Server20  
 
 
Dan





PostPosted: Fri Feb 06 12:58:38 CST 2004 Top

Exchange Servers >> ODBC Woes, how to overcome Most people generate PK's with a custom function in the DEFAULT value of the
field.

You can't do it from an insert trigger because writing to the table would
just fire the insert trigger, which would cause infinite recursion. <s>

Dan



> Hi gang,
>
> We are setting up a web portal for our production data to be made
> available to our clients, and I have to setup a data exchange so that
> web updates are made into our tables. For lots of reasons we do not
> want the web stuff to touch our actual tables so we have a change log
> that will accomplish this.
>
> The problem: We need to have a transaction ID field on each record in
> the log table. I thought (along with the web developer) that an
> autoincrement field would be the ticket. NOT! As you may or may not
> know, the ODBC drivers for VFP will not read a table if it has an
> autoincrement field, you get a 'not a table' error
>
> Next solution is to have a table that stores the PK, and is
> incremented during the INSERT Trigger. This has proved problematic
> because VFP passes a non updatable cursor to the INSERT trigger. Is
> there a way to update a field in the new record before the insert
> occurs? I would like to do this during an insert.
>
> Pull PK Value from the PK table, increment value and write back to
> the PK table.
> Use this value as a PK for the log file.
>
> Is this possible?
>
> TIA,
>
> Marc


 
 
Marc





PostPosted: Fri Feb 06 13:01:29 CST 2004 Top

Exchange Servers >> ODBC Woes, how to overcome I GOT IT!!

You cannot use the INSERT TRigger to assign values to the newly created
record, but you *can* have a UDF for the DEFAULT Value.

I simply created a UDF That locks/grabs/updates the next PK, and then
returns that value. Place said UDF into default value, and insert from
Webpage creates a faux autoincrement field.

Regards,

Marc




> Hi gang,
>
> We are setting up a web portal for our production data to be made
available
> to our clients, and I have to setup a data exchange so that web updates
are
> made into our tables. For lots of reasons we do not want the web stuff to
> touch our actual tables so we have a change log that will accomplish this.
>
> The problem: We need to have a transaction ID field on each record in the
> log table. I thought (along with the web developer) that an autoincrement
> field would be the ticket. NOT! As you may or may not know, the ODBC
drivers
> for VFP will not read a table if it has an autoincrement field, you get a
> 'not a table' error
>
> Next solution is to have a table that stores the PK, and is incremented
> during the INSERT Trigger. This has proved problematic because VFP passes
a
> non updatable cursor to the INSERT trigger. Is there a way to update a
field
> in the new record before the insert occurs? I would like to do this during
> an insert.
>
> Pull PK Value from the PK table, increment value and write back to the PK
> table.
> Use this value as a PK for the log file.
>
> Is this possible?
>
> TIA,
>
> Marc
>
>