Warehousing the PRODUCTS Table to Microsoft SQL Server
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 code is similar to Warehousing the PRODUCTS Table to Microsoft Access, with some subtle differences. These differences are highlighted below and 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 ("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