Page History
...
Below is sample code to warehouse the PRODUCTS table, in the EXAMPLES sample application, to a Microsoft SQL Server table named OI_PRODUCTS in the PUBS database. The The code is similar to Warehousing the PRODUCTS Table to Microsoft Access, with some subtle differences. These These differences are highlighted below and discussed in the next topic.
| Code Block |
|---|
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 ("PUBS", 'ODBC', 'sa', '', '', 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 money, "
Script := " image_filename varchar(50), "
Script := " order_date datetime);"
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 := "CONVERT(Money, '" : oconv({PRICE},'MD2,$'): "'), "
Script := "'" : {IMAGE_FILENAME} : "', "
order_date = {ORDER_DATE}
if order_date = '' then
Script := "NULL)"
end else
Script := "'" : oconv({ORDER_DATE},"D4/") : "')"
end
call msg(@window, Script)
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$> = "ExecuteScript Error"
Def<MICON$> = "!"
Msg(@window, Text)
Results = ""
return
RETURN 0 |
