[ic] DBI table update problem migrating from Minivend to Interchange

Scott B. Gale interchange-users@icdevgroup.org
Thu Oct 31 15:25:01 2002


Thank you very much for your help Jonathan & Kevin!  
The '[time]%Y%m%d[/time]' and
'[value name=name filter=sql]' tag suggestions were especially usefull in
getting the sql code to work.

As it turns out though, even after I got the sql perfect I still kept
getting the frustrating error message "DBI tables must be updated
natively."  When I ran the query directly from the mysql prompt I realized
that the true problem was that I was trying to add a duplicate order
number (which is the primary key) to the Orders table.  (Sheesh!)  I have
no idea what the "DBI tables must be updated natively" message has to do
with trying add duplicate keys to a table but just in case someone
searches the archives for this message my advice is to go to the mysql
prompt.

Thanks again for the kind assistance guys!

Scott B. Gale

On Thu, 31 Oct 2002, Kevin Walsh wrote:

> Jonathan Clark [jonc@webmaint.com] wrote:
> > > Any help with this Minivend to Interchange migration problem would be
> > > greatly appreciated!
> > >
> > > We have been using the code below in pages/ord/receipt.html to update two
> > > mysql tables without any problem under MiniVend V4.03.  Now we are trying
> > > to migrate the site to Interchange V4.8.6 and the code fails with the
> > > error "/cgi-bin/mystore/process SQL query failed for Orders:  DBI tables
> > > must be updated natively."  (Actually it chokes first on CURDATE() untill
> > > I quote it.)
> > >
> > > How is this to be handled under Interchange V4.8.6?  I tried putting it
> > > into a [query sql= ] tag but it doesn't do any better.
> > >
> > > The OS on this machine is FreeBSD 4.4-RELEASE #0.
> > > The perl is version 5.005_03.
> > >
> > > Thanks in advance for any help!
> > >
> > > Scott B. Gale
> > > DPC Systems
> > >
> > > >From pages/ord/receipt.html:
> > >
> > > <!-- BEGIN ORDER WRITE TO DATABASE TABLES -->
> > > [comment][setlocale en_US][/comment]
> > > [comment] Insert the order into MYSQL database [/comment]
> > > [sql interpolate=1]
> > >     insert into Orders values (
> > >         '[value mv_order_number]',
> > >         CURDATE(),
> > >         '[data session source]',
> > >         '[dbh_quote interpolate=1][value name][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value email][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value address1][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value address2][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value city][/dbh_quote]',
> > >         '[value state]',
> > >         '[value zip]',
> > >         '[dbh_quote interpolate=1][value country][/dbh_quote]',
> > >         '[value mv_payment]',
> > > 		'[value mv_credit_card_info]',
> > >         '[value mv_shipmode]',
> > >         '[value phone_day]',
> > >         '[dbh_quote interpolate=1][value b_name][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value b_address1][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value b_address2][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value b_city][/dbh_quote]',
> > >         '[value b_state]',
> > >         '[value b_zip]',
> > >         '[dbh_quote interpolate=1][value b_country][/dbh_quote]',
> > >         '[scratch the_discount]',
> > >         '[subtotal noformat=1]',
> > >         '[salestax]',
> > >         '[shipping]',
> > >         '[total-cost noformat=1]',
> > > 		'',
> > > 		'',
> > > 		'[value mv_username]',
> > >         '[dbh_quote interpolate=1][value company][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value title][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value b_company][/dbh_quote]',
> > >         '[dbh_quote interpolate=1][value b_title][/dbh_quote]'
> > >     )
> > > [/sql]
> > >
> > > [item-list]
> > > [sql interpolate=1]
> > >     insert into Orders_lineitems values (
> > >         '[value mv_order_number]',
> > >         '[item-code]',
> > >         '[item-quantity]',
> > >         '[item-field isbn]',
> > >         '[dbh_quote interpolate=1][item-field title][/dbh_quote]',
> > >         '[item-price]',
> > > 		''
> > >     )
> > > [/sql]
> > > [/item-list]
> > >
> > > [comment][setlocale][/comment]
> > > <!-- END ORDER WRITE TO DATABASE TABLES -->
> > 
> > 
> > Try something like:
> > 
> > [query sql=|insert into Orders values (
> >          '[value mv_order_number]',
> >          [time]%Y%m%d[/time],
> >          '[data session source]',
> >          '[value name=name filter=sql]',
> >          '[value name=email filter=sql]',
> > 
> > etc..
> > 
> >          )
> > 	|][/query]
> > 
> > Whether you need to quote your values will depend on the data type in your
> > MySQL table.
> > 
> I'll agree with that one.  A value filter will always be more efficient
> than calling another tag to do the work.  You can either use the existing
> "sql" filter, or create your own to do whatever you need.
> 
> The [dbh_quote] tag looks a little odd to me.  It's obviously not calling
> the DBI's quote() mechanism.  If it was then you wouldn't need to quote
> the result.  The 'real' quote mechanism also includes the possibility of
> generating a NULL value.
> 
> Next point - I suggest that you name your columns when performing an
> INSERT, like this:
> 
>     INSERT INTO sometable (
>         foo, bar
>     )
>     VALUES (
>         'fooval', 'barval'
>     )
> 
> If your migration caused new tables to be created, with columns in a
> different order, then that could cause all sorts of problems if you use
> use "SELECT *" and implicit INSERT/REPLACE statements.
> 
> -- 
>    _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
>   _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
>  _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
> _/   _/  _/_/_/_/      _/    _/_/_/  _/    _/
> 
> _______________________________________________
> interchange-users mailing list
> interchange-users@icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>