Progress® DataDirect® OpenAccess SDK FAQ for ODBC

ALL AND ASC AVG BETWEEN BY COUNT DESC DISTINCT ESCAPE EXISTS FOR FULL GROUP HAVING HINT IN INNER LEFT LIKE MAX MIN NOT NULL ON OR ORDER RIGHT SELECT SUM WHERE

select e.all from emp e

Q4 How to get Parent ODBC application windows handle?

The OAIP_connect routine can be modified to access the window handle:

extern HWND gODBCParentWindow;

sprintf(dam_msgBuf, "Parent handle:%lx", (long)gODBCParentWindow);

tm_trace(mem_tm_Handle, UL_TM_ERRORS, "%s\n", (dam_msgBuf));

Q5 Explain significance of some OpenAccess system tables from application point of view?

OA_COLUMNS - Include the column definitions and mark the primary key column(s) as BEST_ROWID. If your database has a pseudocolumn specifically designed for this purpose (as in Oracle ROWID) you can expose these columns. If your database supports ROWVER field (columns that are automatically updated by the datasource when any value in the row is updated) expose that information.

OA_STATISTICS - Expose all the unique and non-unique indexes.

Expose the primary key Unique index first followed by other unique and non-unique indexes.

The first unique index is used as primary key by some applications and DAM uses all the index information in its optimizer to determine the best index for a query execution.

OA_FKEYS - Expose the Primary key information (even if some applications rely on OA_STATISTICS to get primary key). Also expose Foreign key information (even if some applications rely on matching column names to determine auto join conditions) since the DAM planner uses it to implement JOIN processing.

Keep the column names in Primary and Foreign tables the same since most applications use it to identify auto join conditions.

Q6 How does MSQuery identify a unique index?

MSQuery uses ODBC call SQLSpecialColumns(SQL_BEST_ROWID) to detect the primary key fields for a table. A column can be marked as ROWID column in the OA_COLUMNS definition. Please refer to pages 3-11 & 3-12 in the ODBC Driver Development Kit Programmer's Guide for details on how to mark the column as ROWID.

Q7 How does MSQuery identify autojoins?

MSQuery says that it will auto setup the inner join if the field names match and the field is defined as the "primary key" to one of the files.

Q8 How MSQuery read data?

When fetching data for a table, Query reads data for all the columns

Q9 How MSQuery modify data?

Query uses unique index and the old value is supplied

Q10 How does MSAccess identify a unique index?

When attaching an ODBC table, Access calls SQLStatistics to collect index information. The first unique index returned is marked as the "primary" index. Unless a "primary" index is found, the attached table will not be updatable.

Q11 How does MSAccess identify autojoins?

Microsoft Access automatically creates joins if you add two tables to a query and the tables each have a field with the same or compatible data type and if one of the join fields is a primary key.

Q12 How does MSAccess read data?

When fetching data for a table, Access first queries for the primary index fields and later uses the value of the index fields to read data for the remaining fields.

Q13 How does MSAccess modify data?

If SQLSpecialColumns(ROWVER) reports a ROWVER column (say, "RV"), Access uses it to implement optimistic concurrency during datasheet updates. On datasheet Update/delete, the UPDATE/DELETE query is appended with "AND RV = ?", and the old value supplied.

If there is no ROWVER column, then all updatable columns are compared to their old values, as in:

UPDATE MyTable SET col1 = ?, col2 = ?, col3 = ?

WHERE PrimKey = ? AND col1 = ? AND col2 = ? AND col3 = ?

Q14 How MSAccess access LONGVARBINARY data?

LONGVARBINARY fields are mapped as OLE objects. If the table has index information, Access passes the literal string ‘#S_C_H#’ as the column for LONGVARBINARY field. It then tries to read the string value as BINARY data

SQLPrepare called:

hstmt=0x165100

szSqlStr=SELECT "NAME",'#S_C_H#' FROM "OAUSER"."EMP_TABLE" WHERE "NAME" = ?
 OR "NAME" = ? OR "NAME" = ? OR "NAME" = ? OR "NAME" = ? OR "NAME" = ? OR 
 "NAME" = ? OR "NAME" = ? OR "NAME" = ? OR "NAME" = ?

cbSqlStr=SQL_NTS

SQLPrepare: returns SQL_SUCCESS

SQLGetData called:

hstmt=0x162be8

icol=2

fCType=SQL_C_BINARY

cbValueMax=16000

SQLGetData: returns SQL_ERROR

SQLState=S1003, ErrorMsg=CLI error: Program type out of range

If the table does not have index information, it just displays 'Long binary data' as the field value but calls to get max of 16K data

SQLGetData called:

hstmt=0x16a260

icol=2

fCType=SQL_C_BINARY

cbValueMax=16000

Q15 Why Crystal Reports shows TimeStamp fields as Date fields?

You need to make sure that before you design the report, you set the Options for Reporting properly. Choose File|Options menu and go to the Reporting Tab. Select option to "Keep date-time type" for Date-Time field.

Q16 Why MS Access shows #deleted for some records?

This occurs when MSAccess does not find matching records for the unique index exposed by the table. MSAccess first runs a query to get the unique index values. It later uses the value of the unique index field to query for the other column values. It tries to lookup 10 matching records each time.

For example, if EMP table has unique index on EMPNO, the second query contains 10 conditions on the EMPNO field:

select EMPNO from emp;

select EMPNO, ENAME, SAL, HIREDATE, DEPTNO FROM EMP WHERE EMPNO = ? OR
 EMPNO = ? OR EMPNO = ?... EMPNO = ?

When MSAccess does not find matching records for all the EMPNO values, it shows #deleted for the corresponding records.

This occurs if IP is not processing the Index conditions returned by dam_getOptimalIndexAndConditions correctly. If IP is only checking the first condition list, MSAccess displays only every 10th row correctly and shows a block of 9 #deleted rows. If IP fails to return matching records for some of the conditions, MSAccess displays #deleted for the corresponding rows.

Q17 How to use SQL.REQUEST in Excel?

To use the SQL.REQUEST array formula:

SQL.REQUEST(connection_string,output_ref, driver_prompt,query_text,col_names_logical)

the format for the connection_string is:DSN=test_server;UID=name;PWD=password

When you enter the array formula, Microsoft Excel automatically inserts the formula between { } (braces).

If the array formula will return one result, click the cell in which you want to enter the array formula. If the array formula will return multiple results,

select the range of cells in which you want to enter the array formula.

Type the array formula.

Press CTRL+SHIFT+ENTER.

Q18 How MSEXCEL & MSQUERY interact?

When you are running EXCEL and you invoke the Data-GetExternal Data-Create New Query option then you will get Microsoft Query's window handle in SQLDriverConnect() function because first dialog box pop-up to select the ODBC driver.

When you are running EXCEL, and you invoke the Data-Get External Data-Run Database Query to run a saved query then you will get Open File's window handle in SQLDriverConnect() function because first it will asked for file to run.

When you are running EXCEL, and you invoke the Data-Get ExternalData-Edit Query option then you will get Excel's window handle in SQLDriverConnect() function because there is not any pop-up intermediate window .

Q19 How to use OpenAccess ODBC Driver from Linked Server?

Run the Enterprise Manager from the SQL Server.
Select security -> Link server.
Right click on link server to create a new Linked server. Enter linked server name as "TEST_OPENRDA".
Select the Provider name as 'Microsoft OLEDB Provider for ODBC drivers.'
Specify the DataSource name eg: test_local
Under the Provider Options

Enable : Dynamic Parameters, Allow InProcess
Disable: Nested queries, Level zero only, Non-transacted updates, Index as access path

Under Server Options.

Enable: "Collation compatible" option. - This option will delegate filter condition to provider.
Disable: RPC, RPC Out
Leave Collation Name empty
Leave Connection Timeout and Query Timeout at 0.

Under the Security tab, select "Be made using this security context" and specify the Remote Login and Password for the Provider.
Once the linked server is created, you should be able to query your database tables.

select * from "TEST_OPENRDA"."SCHEMA".OAUSER.EMP;

select * from OPENQUERY(Test_OpenRDA, 'select * from emp');

select * FROM OPENROWSET('MSDASQL','DSN=test_local', 'SELECT * FROM emp')

Q20 Do you have list of Reserved keywords?

add, all, alter, and, any, as, asc, avg, between, binary, by, call, cascade, char, close, commit, count, create, current, decimal, delete, desc, distinct, divide, double, drop, escape, exists, exit, false, fetch, float, for, from, full, grant, group, having, hint, in, index, inner, insert, integer, into, is, join, key, left, like, longvarbinary, longvarchar, max, min, not, nowait, null, numeric, of, on, open, or, order, outer, primary, procedure, public, query, quit, real, references, restrict, revoke, right, rollback, select, set, smallint, sum, table, timestamp, to, true, union, unique, unknown, update, userdata, values, varbinary, varchar, view, where, work

Q21 What is the significance of SQLDescribeParam option?

The checkbox in ODBC data source setup allows you to enable/disable support for SQLDescribeParam(), a function that is part of the ODBC API. Based on this setup, our ODBC driver either exposes this optional function or not. Our current implementation of this function is not complete and is provided to work with some tools that require this functionality. For example Visual Basic application using RDO require support for this function.

Typically you are not supposed to enable this function. When an application starts to report errors that "SQLDescribeParam" is not supported, you should contact our support staff and we will recommend if you should enable this feature.

Q22 What is the significance of OA_INFO table?

Our ODBC clients do the queries on OA_INFO table to lookup database specific information. This is done once during the connection and if we do not find the table, we assume default values. OA_INFO is a catalog table and contains information required by the ODBC API SQLGetInfo(). SQLGetInfo is typically not used by custom applications like the ones you are building.

In our next release we plan to provide a configuration option to indicate to the client not to read this information. If you want to avoid these error messages, please setup a table named OA_INFO with the following columns:

INFO_NAME CHAR(128),

INFO_NUM INTEGER,

INFO_INT INTEGER,

INFO_BITMASK INTEGER,

INFO_TEST CHAR(128),

REMARKS CHAR(128)

Create the above table and leave it empty.

Q23 How to change the value of SQL_TXN_CAPABLE transaction support?

IP can change the value returned for SQLGetInfo(SQL_TXN_CAPABLE). You can update the damipex.h to change the value for SQL_TXN_CAPABLE, info_num=46, in gColArray_Info. The info_int field value for this entry should be changed from 2 to 0.

Once you update the damipex.h, rebuild the OpenAccess Server (or in case of Local SDK, rebuild the odbc driver). Delete the existing oainfo.ini that contains cached driver information from the client systems. oainfo.ini (oaodbc\schema\oainfo.ini) is located in the schema folder of the driver install.

You may also want to modify your IP to return DAM_FAILURE for ROLLBACK transactions. We do not have enough information about how different ODBC applications use the SQL_TXN_CAPABLE option but this is how ODBC driver is expected to report.

Q24 How do I Install and Use OpenAccess SDK on OpenVMS 7.0 , 7.1, 7.2 or 7.3?

The OpenAccess SDK 4.8 for OpenVMS was built on OpenVMS 6.2. You must perform the following steps to install and use it on a OpenVMS 7.x system.

1. Please follow the instructions in Chapter 17 "OpenVMS Installation Notes" in the Installation and Configuration Guide. Complete steps 1-7 in the installation guide and then continue with steps below.

NOTE: Some customers have had trouble using the command EXCHANGE /FDL. If you get and error with this command then please do the following:

set file /attr=(rfm:fix,lrl:32256,mrs:32256) avms.sav

2. After extracting the save set file in step 7. Change to AVMS folder

$ set def oa_root:[AVMS]

3. Under OpenVMS V7.x and higher, to link object files that were compiled under OpenVMS V6.2, Modify the oaisql.opt, odbcisql.opt and oaserver.opt command files.

Look for:
SYS$SHARE:CMA$OPEN_RTL/SHARE
SYS$SHARE:POSIXC$SHARE/SHARE

Replace with:
oa_root:[avms72]CMA$OPEN_RTL/SHARE

4. Create the AVMS72 folder under OA_root folder.

$ set def OA_root:[000000]
$ create /dir [.AVMS72]

5. Also add this line at the end of disql_o.opt file.

oa_root:[avms72]CMA$OPEN_RTL/SHARE

6. Transfer the CMA$OPEN_RTL.EXE (Click on the file name to get it) under [.AVMS72] folder.

$ Set def OA_root:[AVMS72]

7. Continue with the instruction in our installation and Configuration Guide step 8.

To Build the Memory IP

Follow the instructions in step 3 above to Modify the AVMS.OPT command file at the following folder to build the Memory IP

$ Set def OA_root:[sdk.damip.memory]

You will have to change the permissions to modify this file using

SET FILE/PROTECTION=(S:RWED,O=RWED,G:RWE,W:RWE) avms.opt

To Build the Memory Server

Follow the instructions in step 3 above to Modify the AVMS.OPT command file at the following folder to build the Memory Server.

$ Set def OA_root:[sdk.server]

You will have to change the permissions to modify this file using

SET FILE/PROTECTION=(S:RWED,O=RWED,G:RWE,W:RWE) avms.opt

Q25: How do I use the interval syntax [between now() - {interval '1' DAY} and now()] while building a query in MS ACCESS, MS EXCEL or Crystal Report.

Solution: You can write the query in SQL Window of MS EXCEL and CRYSTAL REPORT. In MS ACCESS use the Query Pass through mode to Design the query using interval options. Here are the sample queries for each application.

MS Access Query Pass Through Mode

SELECT OAUSER.ARCHIVE.NODE, OAUSER.ARCHIVE.TAG, OAUSER.ARCHIVE.TIME,
 OAUSER.ARCHIVE.VALUE FROM OAUSER.ARCHIVE WHERE
 (((OAUSER.ARCHIVE.TAG)= 'TEST_AA') AND ((OAUSER.ARCHIVE.TIME)
 Between now() - {interval '1' DAY} And now()) AND
 OAUSER.ARCHIVE.DURATION='1:00:00');

MS Excel

SELECT ARCHIVE.NODE, ARCHIVE.TAG, ARCHIVE.TIME, ARCHIVE.VALUE, 
 ARCHIVE.STATUS, ARCHIVE.ALARM, ARCHIVE.DURATION, ARCHIVE.INTERVAL,
 ARCHIVE.MODE, ARCHIVE.DATASET FROM OAUSER.ARCHIVE ARCHIVE WHERE 
 (ARCHIVE.TIME between now() - {interval '1' DAY} and now()) and 
 (ARCHIVE.TAG='TEST_AA') and ARCHIVE.INTERVAL='1:00:00'

Crystal Report

SELECT ARCHIVE."NODE", ARCHIVE."TAG", ARCHIVE."TIME", ARCHIVE."VALUE"
 FROM "SCHEMA"."OAUSER"."ARCHIVE" ARCHIVE WHERE ARCHIVE."TIME"
 between now() - {interval '1' DAY} and now() AND 
 ARCHIVE."TAG" = 'TEST_AA' AND ARCHIVE."INTERVAL" = '1:00:00'

Note: You can not use the Query Builder to build the query using interval options.

Q26 Unable to connect through Microsoft Access using ODBC.

Microsoft Access returns -77xx error messages when Access determines that a driver has failed to comply with the ODBC specifications. Please click here and scroll down to look for the topic Microsoft Access ODBC Spec-Compliance Error Codes for the complete details.

Q27 We have a problem generating a report using Crystal Report.

This seems to be a Bug in Crystal report.

Workaround: Once you generate the report do some editing in the query manually. Open Show SQL Query Window. Now insert some characters and then delete them (i.e. just touch the query). Now either save the report without data or preview the report, it will work.

Q28 How do I setup the Oracle Generic Connectivity using OpenAccess ODBC Driver?

Please download (Generic Connectivity Using ODBC.pdf) for Windows and Unix for step-by-step guide on setting up and troubleshooting generic connectivity using OpenAccess ODBC Driver.

Q29 When I run a certain query through your OpenAccess ODBC SDK, I will periodically get a "Transaction Rollback - Statement Completion Unknown" error.

Check that the 'Response time' defined in the Openrda.ini file is sufficient for the type of operations performed. Default Response time out is 3600 seconds. This value should be less than 32767.

[CLIENT]
RESPONSE_TIME_OUT=3600

Q30 ADO connection deadlock problem with SQLServer.

The problem is most likely related to the version of your MDAC. If you are using MDAC 2.5 or earlier versions, there seems to be a bug with OLE DB Service Components which causes the connection to hang. Please refer to the article on Microsoft site: http://support.microsoft.com/support/kb/articles/Q267/5/66.ASP

Q31 We don't have UCX running on our VMS box, instead we use Multinet for our TCP/IP, When I install the OpenRDA ODBC Driver it gives following error's when linking executables.

Please link with the Multinet IPC.OLB library which provides the Multi socket function, which we are using from this UCX library.
sql$user.olb are optional library files. Please run the setup again and once it fails, edit the oaisql.opt and odbcisql.opt files under install/vvms to remove the SQL$USER.OLB and run build.com.

Q32 Installing OpenAccess on Windows XP gives the following error.

The user you are installing it as should have local Admin rights, please either login as local administrator and then install the OpenAccess product or give that user the local Admin rights.

DataDirect ODBC

DataDirect OpenAccess Troubleshooting FAQs

See all DataDirect FAQs DataDirect Overview

DataDirect OpenAccess SDK

Connect to your application with enterprise level connectivity

About DataDirect OpenAccess SDK

MarkLogic

Semaphore

OpenEdge

DataDirect

Sitefinity

Telerik

Kendo UI

Corticon