Note the following in the code:

  • Turn off transaction processing. Warehousing involves sending many SQL statements (Drop Table, Create Table, and Insert statements). When a connection is established, by default, transaction processing is turned on. This means that unless an explicit Commit is sent to Access, the changes will not be made. If transaction processing is turned off, the changes made by each SQL statement take effect immediately. In this example we will turn transaction processing off by sending the XO_NOTRANS$ code (found in the XO_EQUATES $Insert record) when calling XOInstance(). The XOInstance() call is shown below:

hXO = XOInstance ("NWIND", "ODBC", '', '', '', XO_NOTRANS$)

  • Drop the target table first. If the warehouse procedure is to be used repeatedly, the easiest method is to drop the target table first and then rebuild the table from scratch, as in the code below. Comment the call to msg() if you want the procedure to continue without notifying the user.

Script = "drop table oi_products"
Flag = QryMethod(hQry, QRY_EXECUTE$, Script)
if Flag then
   call msg(@window, 'OI_PRODUCTS table dropped successfully!')
end else
   gosub error
end

  • Be aware of the Microsoft Access data types when creating the table. The Create Table query is built in a variable before it is executed. Note that in Access, monetary type fields are assigned the Currency data type and date/time type fields are assigned the Date data type. Also, the Text data type allows a data length and is essentially an equivalent to Varchar. This query will not work in SQL Server, (and certainly not in Oracle) as we will discover in the next example. Below is the code to build the query, with the data types highlighted:

Script  = "create table oi_products ("
Script := "       id varchar(22) primary key not null, "
Script := "       description varchar(50), "
Script := "       price currency, "
Script := "       image_filename text(50), "
Script := "       order_date date);"

  • Code the proper Oconv() calls when converting the OpenInsight values.  In OpenInsight, monetary and date type fields are stored unconverted. Without the proper Oconv(), the data will be misinterpreted in Access. Character fields need not be converted. The rules for passing a null value also vary. These conversion rules also vary from one database to another. Below is the code to convert the OpenInsight data as each row is read:

Script  =  "insert into oi_products "
Script :=  "(id, description, price, image_filename, order_date)   values ("
Script :=  "'" : @ID                   : "', "
Script :=  "'" : {DESCRIPTION}         : "', "
Script :=  "'" : oconv({PRICE},'MD2,$'): "', "
Script :=  "'" : {IMAGE_FILENAME}      : "', "
order_date = {ORDER_DATE}
if order_date = '' then
    Script :=  "NULL)"
end else
    Script :=  "'" : oconv({ORDER_DATE},"D4/") : "')"
end
  • No labels