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

[Kevin Walsh]

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
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/