What is the fastest method to update a MS SQL table with the following.

OI Table

Key=UPC Code

pos1=price

SQL Table

Key= random numbers and letters

Pos1=UPC Code

Pos2=price

Using OI 9.2 with the new SQL, I need to update the SQL price from the OI Table.

The SQL table is huge and contains multiple keys with the same UPC code

There should be a routine named DSBFS_EXEC_SQL that will pass sql through. So, define a connection to the database using the SQL Wizard. You do not need to define any datasets. Then, create an sql statement that will update the table and execute it. Assuming you want to change all instances of the price for the upc to have the same value you would use something like the following:

0001  Function update_sql_price(upc, price)
0002  /*
0003  * use dsbfs_exec_sql(connection, sql, demote_levels, result)
0004  */
0005  
0006  
0007  
0008  connectionName = 'MYCONNECTION' ; * name of your sql connnection
0009  
0010  sql = "update price_table"
0011  sql := " set price=" : price : "'"
0012  sql := " where upc=" : upc : "'"
0013  
0014  Call dsbfs_Exec_sql(connectionname, sql)
0015  
0016  Return ''

And what about 'demote_levels' and 'results'

You can use the pass-through sql mode to run your own queries that return results. The results return as a delimited block of text.

The default return format is @rm/@fm row/column delimited.

Similar to Xlate you can pass in a number to change the delimiters to @fm/@vm or @vm/svm, and so on.

So, you can send in a query, demote once and throw the results in an edit table, or you can write calculated columns that request a columns data from the sql server and report it in oi, something like

cmd =SELECT TITLE_ID FROM TITLEAUTHOR WHERE AU_ID=": {AU_ID} : "'"

call dsbfs_exec_sql('myconnection', cmd, 2, @ans )

View this thread on the Works forum...