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

[interchange-users@icdevgroup.org (Jonathan Clark)]

> 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.

Jonathan.