I am looking at a project using the OpenInsight ODBC Driver but I am having difficulty with the limited documentation. I have used most of the "Pick" vendors ODBC drivers and many RDBMS ODBC drivers and I am trying to make the OpenInsight perform in the same manner.

I have a number of questions that I hope someone can answer.

1. I created an entry in the SYSVIEWS table and this displays under "Views" through the ODBC client, so I know this is the corect location to store views. There is however no dictionary structure in DICT.SYSVIEWS to give any clues on how to enter a view into this system table. Using the "view" on the ODBC client I get the following error Revelation ODBC DriverOpenEngineSQLExecute error - OpenEngine has reported the following exception: SQL-775]

Any suggestions please?

2. The client's application runs a create event on the start up form to attach tables. The ODBC engine does not of course run this event. Is there any equivalent to a Pick environment "Logon Proc". I believe that AREV had this facility, but is it available in OpenInsight. I could then call the clients start up event to attach the required tables.

3. I don't really want to change the "live table" dictionaries to change the datatypes and lengths. Is there any way in OpenInsight that I can make a new dictionary (with limited data column definitions) with a data section still pointing to the real data file?

4. I also don't see any way to run a stored procedure thriugh ODBC using the {call} sytnax. I do notice from the ODBC trace that SQLExecDirect is used, so I am assuming that the OpenInsight driver is using pass through queries. Would any one know what SYS??? table the ODBC driver would look in to see ODBC procedures on the ODBC client? and if this is possible what structure is needed for the parameters being passed in and the defintion of the resultset being passed back.

I don't know if the "stored procedure" ability has not been included because of its WRITE potential, but producing a result set in the host database and producing a formated result set programmatically is often the safest way of producing results that match the application business rules.

I would appreciate any feed back on any or all of these questions.

Graeme,

1. I created an entry in the SYSVIEWS table and this displays under "Views" through the ODBC client, so I know this is the corect location to store views. There is however no dictionary structure in DICT.SYSVIEWS to give any clues on how to enter a view into this system table. Using the "view" on the ODBC client I get the following error Revelation ODBC DriverOpenEngineSQLExecute error - OpenEngine has reported the following exception: SQL-775 Any suggestions please?]

SQL views are supported in OI, but OI's SQL functionality hasn't really been documented since the days of OI 2.0 (and Arev 3.12 - they share the same basic SQL parsing engine) in the Advanced Developer's Guide.

Basically the syntax is:

CREATE VIEW CUSTOMER_CONTACT AS

 SELECT LAST_NAME, ADDRESS, PHONE FROM CUSTOMERS

or

CREATE VIEW CUSTOMER_CONTACT (SURNAME, LOCATION, PHONE) AS

 SELECT LAST_NAME, ADDRESS, PHONE FROM CUSTOMERS

I don't know if Revelation have any plans to make this documentation available on-line, but you can use the OI SQL window to create your views using the syntax as above.

2. The client's application runs a create event on the start up form to attach tables. The ODBC engine does not of course run this event. Is there any equivalent to a Pick environment "Logon Proc". I believe that AREV had this facility, but is it available in OpenInsight. I could then call the clients start up event to attach the required tables.

Field 32 of your application's environment record (ENV_ in the SYSENV table) contains the name of a Stored Procedure to execute when the engine starts. See the ENVIRON_CONSTANTS insert for more details.

3. I don't really want to change the "live table" dictionaries to change the datatypes and lengths. Is there any way in OpenInsight that I can make a new dictionary (with limited data column definitions) with a data section still pointing to the real data file?

Would creating synonym columns for your original dictionary columns work?

4. I also don't see any way to run a stored procedure thriugh ODBC using the {call} sytnax. I do notice from the ODBC trace that SQLExecDirect is used, so I am assuming that the OpenInsight driver is using pass through queries. Would any one know what SYS??? table the ODBC driver would look in to see ODBC procedures on the ODBC client? and if this is possible what structure is needed for the parameters being passed in and the defintion of the resultset being passed back.

The call syntax is not currently supported.

The Sprezzatura Group

World leaders in all things RevSoft

Many thanks for responding.

I have created a view as per your suggestion, which has created an entry in the SYSVIEWS tables and when I look at the catalogue of the ODBC schema it is accepted as an ODBC view. The catalogue has recorded the result set columns for the view, and data types, etc as per the ODBC spec.

However, when I run a query against the view I get the following error message "Error: SQLPrepare error - OpenEngine has reported the following exception: FS401 - The "CUST_VIEW" table has not been created or attached. (State:S0000, Native Code: 18D0007)"

So obviously, OpenEngine is still trying to treat a view as a table.

Any suggestions?

I'm almost tempted to change my approach and make a bond table link to Universe database and use its ODBC facilities, but I am still hopeful that someone will have an answer so I can use the Revelation ODBC driver solution as initialy planned.

In regard to running stored procedures thru ODBC, Universe has a variation on the {call} syntax which is {call NATIVE xxxx}. I had hoped that Revealtion would have had something like {call RLIST xxx} but that doesn't appear to work.

Graeme,

So obviously, OpenEngine is still trying to treat a view as a table. Any suggestions?

Can you execute the OI "SQL" window and try your SELECT statement against the View there? The RevODBC statement is put through a pre-parser before begin passed to the OE SQL parser, so this would indicate where the problem lies.

The Sprezzatura Group

World leaders in all things RevSoft

Thanks for the comments and help, but I still get the same error message in the ODBC catalogue. I guess I will have to use plan B and wait until Revelation bring out the next version of the ODBC driver

View this thread on the forum...