Warehousing the PRODUCTS Table to Microsoft Access
Below is sample code to warehouse the PRODUCTS table, in the EXAMPLES sample application, to a Microsoft Access table named OI_PRODUCTS in the NWIND database. The logic flow (creating the connection handle and the query handle, building the query string, executing the query, and destroying the connection handle and query handle) is similar to previous examples. Specific considerations for warehousing to Microsoft Access are discussed in the next topic.
declare subroutine msg
$insert XO_Equates
$insert Msg_Equates
$insert logical
* returns @rm/@fm delimited results
Results = ""
Equate CRLF$ to char(13):char(10)
hXO = XOInstance ("NWIND", "ODBC", '', '', '', XO_NOTRANS$)
hQry = 0
if hXO then
* create the query handle for the connection handle
hQry = QryInstance(hXO)
if hQry then
* drop OI_PRODUCTS
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
* create the OI_PRODUCTS table
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);"
Flag = QryMethod(hQry, QRY_EXECUTE$, Script)
if Flag then
call msg(@window, 'OI_PRODUCTS table created successfully!')
end else
gosub error
end
* populate the OI_PRODUCTS table from the PRODUCTS table.
open 'PRODUCTS' to products_filevar then
open 'DICT.PRODUCTS' to @dict then
select products_filevar
done = FALSE$
loop
readnext @id else done = TRUE$
until done = TRUE$
read @record from products_filevar, @id then
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
Flag = QryMethod(hQry, QRY_EXECUTE$, Script)
if Flag else
gosub error
end
end
repeat
end
end
* close the query handle
Flag = QryMethod(hQry, QRY_DESTROY$)
end else
gosub error
end
* close the connection
Flag = XOMethod(hXO, XO_DESTROY$)
end else
gosub error
end
return 0
* Error handling
Error:
if hQry then
Flag = QryMethod(hQry, QRY_GETERROR$, "", "", "",|
"",Text)
end else
Flag = XOMethod(hXO, QRY_GETERROR$, "", "", "", |
"",Text)
end
convert @vm to @tm in Text
Def = ""
Def<MTEXT$> = Text
Def<MCAPTION$> = "Script Execution Error"
Def<MICON$> = "!"
Msg(@window, Text)
Results = ""
return
