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 ([email protected])

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

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

Sedick,

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

For example code on executing a SQL statement from BASIC+ using ODBC, look at the example under "QryMethod" in the help file.

Cameron Purdy

[email protected]

View this thread on the forum...