[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]
==== Writing rows to a Access Database (OpenInsight Specific) ====
=== At 15 APR 1998 05:50:41AM Sedick Cloete wrote: ===
{{tag>"OpenInsight Specific"}}
I have created a connection and ODBC data set to the Access database. Accessing the database works fine thru query and via a window. What I now need to do is to write a stored procedure to write rows from one of my LH table to a table in the Access database.
Please help!
Thanks.
e-mail (sedick@wsa.co.za)
----
=== At 15 APR 1998 08:35AM Dave Pociu wrote: ===
Sedick,
Under the C/S Workspace , Tools you can Create a warehouse procedure.
The wizard there will help you create a procedure for updating/creating relational database tables based. It will create a function called WP_xxxx. You can open that through the System editor.
Obviously you can learn how things are done by looking at the code there.
Please note that the procedure that gets created uses GENERIC SQL and that you will have to change that according to the ODBC driver that you are using (for example ACCESS does not like the TEXT type, it likes VARCHAR, etc. )
Here's a function that I just created:
function WP_My_Contacts(Instruction, Handle, Info)
*****************************************************************************
*
* Name : WP_My_Contacts
* Description: Warehouse Procedure
*
* LH Table : MY_CONTACTS
* DataSets : WP_MY_CONTACTS
* SQL Tables : my_contacts
*
* History (Date, Initials, Notes)
* 04/15/98 cwp Generated by the Create Warehouse Procedure wizard.
*
*****************************************************************************
declare function SQLExp_Select
$insert Logical
$insert DS_Equates
$insert XO_Equates
equ CRLF$ to \0D0A\
******
* Main
******
Ret=TRUE$
on Instruction gosub CreateTables, ClearTables, DropTables, ProcessInit, ProcessRecord, ProcessTerm
return Ret
*******************
* Create SQL Tables
*******************
* Instruction in - 1
* Handle in - Query Handle (hQry)
* Ret out - TRUE$ on success, FALSE$ otherwise
CreateTables:
Script =create table my_contacts": CRLF$
Script := " (": CRLF$
Script := " name varchar not null primary key,": CRLF$
Script := " phone varchar null": CRLF$
Script := " )"
gosub ExecuteScript
return
******************
* Clear SQL Tables
******************
* Instruction in - 2
* Handle in - Query handle (hQry)
* Ret out - TRUE$ on success, FALSE$ otherwise
ClearTables:
Script =delete *": CRLF$
Script := "from my_contacts"
gosub ExecuteScript
return
*****************
* Drop SQL Tables
*****************
* Instruction in - 3
* Handle in - Query handle (hQry)
* Ret out - TRUE$ on success, FALSE$ otherwise
DropTables:
Script =drop table my_contacts"
gosub ExecuteScript
return
**********************************
* Initialize Warehousing Procedure
**********************************
* Instruction in - 4
* Handle in - Connection handle (hXO)
* out - @fm-delimited DataSet handles (hDS)
* Info in - Requested warehouse type (e.g. FULL, UPDATES)
* out - LH table name
* cursor #0 out - A select list of keys to warehouse
* @reccount out - Number of keys in the select list
* Ret out - TRUE$ on success, FALSE$ otherwise
ProcessInit:
Table =MY_CONTACTS"
DSNames =WP_MY_CONTACTS"
DSHandles ="
* the SQLExp_Select function selects the LH records to warehouse;
* by encapsulating this functionality in a separate procedure,
* addition warehouse types can be added without requiring changes
* to individual Warehouse Procedures
clearselect 0
Ret=SQLExp_Select(Table, Info)
if Ret then
* create DataSets
Count=count(DSNames, @fm) + (DSNames # "")
for i=1 to Count
hDS=DSInstance(DSNames[i], Handle)
if hDS else
Ret=FALSE$
end
while Ret
DSHandles[i]=hDS
next i
* if one DataSet failed creation, destroy all DataSets
if Ret else
for i=1 to Count
hDS=DSHandles[i]
if hDS then
DSMethod(hDS, DS_DESTROY$)
DSHandles[i]=0
end
next i
end
end
* return DataSet handles in Handle parameter
Handle=DSHandles
* return LH table name in Info parameter
Info=Table
return
************************
* Warehouse an LH Record
************************
* Instruction in - 5
* Handle in - @fm-delimited DataSet handles (hDS)
* Info in - if TRUE$, the Warehouse Procedure must first remove
* any existing data from the warehouse which corresponds
* to this record
* @id in - the ID of the LH record
* @record in - the LH record
* @mv in - zero (all)
* @dict in - the table's dictionary file handle
* Ret out - TRUE$ on success, FALSE$ otherwise
ProcessRecord:
* calculate "base" key fields
Val_Name={NAME}
* delete existing data
if Info then
* set DataSet arguments (the base key fields)
Args ="
Args=name_arg"
Vals ="
Vals =Val_Name
Count=count(Handle, @fm) + (Handle # "")
for i=1 to Count
Ret=DSSetProperty(Handle[i], DS_ARG$, Vals, Args)
next i
* execute DataSets
for i=1 to Count
Ret=DSMethod(Handle[i], DS_EXECUTE$)
while Ret
next i
* delete all rows from DataSets
if Ret then
for i=1 to Count
DSGetProperty(Handle[i], DS_ROWCOUNT$, RowCount)
DSSetProperty(Handle[i], DS_ROWID$, 1)
for iRow=1 to RowCount
DSMethod(Handle[i], DS_DELETE$)
next iRow
next i
end
end
* insert new data
if Ret and len(@record) then
* calculate all used fields
Val_Phone={PHONE}
* populate the WP_MY_CONTACTS DataSet (single-valued data)
Row="
Row=Val_Name
Row=Val_Phone
Ret=DSSetProperty(Handle, DS_WORK_ROW$, Row)
if Ret else return
Ret=DSMethod(Handle, DS_APPEND_WORK$)
if Ret else return
end
return
*********************************
* Terminate Warehousing Procedure
*********************************
* Instruction in - 6
* Handle in - @fm-delimited DataSet handles (hDS)
* Ret out - TRUE$ on success, FALSE$ otherwise
ProcessTerm:
* destroy all DataSets
Count=count(Handle, @fm) + (Handle # "")
for i=1 to Count
hDS=Handle[i]
if hDS then
DSMethod(hDS, DS_DESTROY$)
Handle[i]=0
end
next i
return
**********************
* Execute a SQL Script
**********************
* Handle in - Query Handle (hQry)
* Script in - SQL Script
* Ret out - Cumulative result: Assumed to start as TRUE$, failure sets to FALSE$, never reset to TRUE$
ExecuteScript:
Ret=(Ret and QryMethod(Handle, QRY_EXECUTE$, Script))
QryMethod(Handle, QRY_CANCEL$)
return
----
=== At 15 APR 1998 10:04AM Tracy graves wrote: ===
Wow!! That's a long procedure!
Sedick-
If you notice that the warehousing procedure is using the DS functions to write to the database, the general idea lies there. Warehousing is great if you are only transferring the data over, however if you want to interface directly with Access tables from OI, you can do that as well.
Make sure that in your Dataset (client/server workspace) you have created the SQL scripts for writing and updating the Access tables. Also, in your DB Selection Criteria ( in the form designer ) , you have checked to allow updates, writes, etc. This allows you to write to the tables from the FORM in the same way you would an LH table.
And for reading values and writing them programmatically, the procedure that Dave posted is a great reference for using the DS functions.
Tracy
----
=== At 15 APR 1998 10:15AM Cameron Revelation wrote: ===
Sedick,
[i]I have created a connection and ODBC data set to the Access database. Accessing the database works fine thru query and via a window. What I now need to do is to write a stored procedure to write rows from one of my LH table to a table in the Access database[/i]
For example code on executing a SQL statement from BASIC+ using ODBC, look at the example under "QryMethod" in the help file.
Cameron Purdy
info@revelation.com
[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=952AFD83B3CD8151852565E700361487|View this thread on the forum...]]