ODBC – Support FAQ

How do I find what version I am using?

From the ODBC Administrator (the same dialog that configures your datasource), .select the “Drivers” tab. The driver is “HP3000 Data Access Driver”.  The version column lists the current version as a four part number. Leading zeros in each part are not significant.

Or on the host, logon:

:hello mgr.minisoft,mm
:setvar mstrace 7
:odbcsrvr

What are the parameters and options used to start both the client and server portions of this product?

Click on the below link for a list of all the parameters and options used to start both the client and server portions of ODBC:

How do I use ManMan dates?

     Click Here

What causes the message “REMOTE ABORTED THE CONNECTION” or “NET READ FAILED”?

     Click Here

How do I join more than two datasets in Crystal Reports 6?

Seagate attempts to use a non-standard (SQL-92) syntax when joining more than two tables. According to Seagate, the workaround is to copy the P2SODBC.DLL file from version 5 and replace the file with the same name in version 6.

For version 7, Seagate is using more than 10 different syntax options for producing joins. The default, which uses the {oj} syntax should work correctly.

How do I use write access?

Currently we ship ODBC as Read access only. To enable Write access edit MSJOB.MM.MINISOFT and change the line that looks something like this:

!SETVAR MSSERVER000004 “30006 0 ODBCSRVR.MM.MINISOFT;PRI=DS;INFO=’ S'”

To:

!SETVAR MSSERVER000004 “30006 0 ODBCSRVR.MM.MINISOFT;PRI=DS;INFO=’ S W'”

Next, make sure you have MPE Write access to the database. Lastly your DSN or Schema Editor file must specify an updateable access mode (1-4) and the TurboImage password must be one that grants write access. Image passwords are case-sensitive, unlike MPE passwords.

How do I enable write access?

As shipped, the ODBCSRVR does not permit write access. To enable this feature you must add the parameter ‘W’ to the ODBCSRVR line in the MSJOB file.

Change:

!SETVAR MSSERVER000004 “30006 0 ODBCSRVR.MM.MINISOFT;PRI=DS;INFO=’ S'”

To:

!SETVAR MSSERVER000004 “30006 0 ODBCSRVR.MM.MINISOFT;PRI=DS;INFO=’ S W'”

How do I configure the limit on size or time of a SELECT?

Add one or both of the following statements to your MSJOB file (before the RUN command):

Maximum records per query set with:
!SETVAR MSQUERYRECLIMIT <records>

Maximum cpu time per query set with:
!SETVAR MSQUERYCPULIMIT <seconds>

How do I use MSOffice, MSExcel, MSWord, MSQuery/97 and 2000?

The ’97 version of these products operates differently than ’95. You must have a ‘File Data Source’ in order to use MSQuery. If you have installed Service Pack 2 you can use either a System or User DSN.

Please view the page on Creating File DSNs.

Office 2000 allows you to choose either a System or User DSN.

How do I search on my TPI keys?

If upgrading to a version of ODBC/32 that supports TPI (1.1.0.6 or later) from an earlier version (1.1.0.5 or earlier), you must re-create the links to your datasets. This step is required to pass the key information to the client application where it is stored.

Please view the page on using TPI Keys.

How do I run more than one version of ODBCSRVR?

You can start more than one version of the ODBCSRVR program at the same time by specifying both on the same MSSERVER000004 line.

Change the line:

!SETVAR MSSERVER000004 &
! "30006 0 ODBCSRVR.MM.MINISOFT S"

to:

!SETVAR MSSERVER000004 &
! "30006 0 ODBCSRVR.MM.MINISOFT S"+&
! "|31006 0 ODBCNEW.MM.MINISOFT S"

Port number 30006 in the DSN will start ODBCSRVR. Port number 31006 in the DSN will start ODBCNEW.

Do not add spaces before or after the pipe (|) character.

Alternatively, you can use an indirect file.  Each line of the file would contain the specification for connection. MSJOB would contain the following line:

!SETVAR MSSERVER000004 "^ODBC0001"

The file ODBC0001 can contain the following lines:
30006 0 ODBCSRVR.MM.MINISOFT;PRI=DS;INFO=" S"
31006 0 ODBC2188.MM.MINISOFT;PRI=DS;INFO=" S"


How do I Insert/Update large amounts of data?

By default the driver is set to Item Level Locking. Trying to do a mass Insert/Update of a Detail dataset will usually cause an error because the Image Lock Descriptor Buffer is filled up. This is an Image limitation.  To get around this problem you need to use Set Level Locking.

1. Edit your DSN and click on the Options tab.
2. Uncheck the “Enable item-level locking” box then save your DSN. This will force Set Level Locking.

If you are trying to Insert more than 1,500 or so records you may get an error. The normal cause of this is the HP3000 transaction log was filled up.  To correct this problem you need to disable the HP3000 transactions.

1. Edit your DSN and click on the Options tab.
2. Check the “Disable HP3000 transactions” box then save your DSN.

How do I run a server outside the MSJOB?

How can I run a single instance of my host software with tracing turned on so I don’t impact my production users?

Log on as an MPE user with WS92. Run a command file such as:

SETVAR MSTEMPFM 1
SETVAR MSSERVER000004 "11006 0 ODBC2153.EXE.MINISOFT S W"
SETVAR MSTRACE 7
RUN MSSERVER.MM.MINISOFT;INFO="11000 SERVER.MSTRACE.MINISOFT"

Modify your DSN to use the same TCP port 11006 and run your client application.

How do I explicitly join two or more tables?

To explicitly join two or more tables you must use the {oj } syntax in the FROM clause. An example of an inner join is as follows:

select td.problem_id,
td.company,
td.tech_code,
tm.name_last
from   {oj track_detail td inner join techs_m tm on td.tech_code = tm.tech_code}
where td.problem_id = 1001Next is an  example of an outer join. We only support Left Outer Joins:

select td.problem_id,
td.company,
td.master_id,
md.master_desc
from   {oj track_detail td left outer join master_detail md on td.master_id = md.master_id}

where td.problem_id = 1001

You can combine inner and outer joins along with referencing the same table multiple times. The example below opens the table techs_m twice.  Also notice you need parentheses to separate each join condition:

select td.problem_id,
td.company,
pm.product_desc,
td.tech_code,
tm.name_last,
td.master_id,
{fn rtrim(md.master_desc)},
md.tech_code,
tm1.name_last
from   {oj (((track_detail td inner join products_m   pm on td.product_code = pm.product_code)
inner join techs_m tm               on td.tech_code = tm.tech_code)
left outer join master_detail md on td.master_id = md.master_id)
left outer join techs_m tm1       on md.tech_code = tm1.tech_code}

where td.tech_code = ‘MARK’

How do I do inner joins with Crystal Reports 9?

The default SQL syntax generated by Crystal Reports no longer uses the {order to restore this functionality a registry entry needs to be made.1. Open Notepad then copy and paste the following four lines.

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\JoinBuilder]
OJSyntax
“OracleJoinBuilder2″=”3kodbc”

2. Save the file as Joinbuilder.reg.

3. In Windows Explorer double-click Joinbuilder.reg.  You will be asked if you want to add this information to your registry. Click on ‘Yes’.

Crystal Reports 9 will now work the same as previous versions.

How do I shut down MSJOB?

While you can just use OB, a more graceful way to bring it down is to run the following program.

Stops MSJOB regardless of any user processes

:RUN MSJOBCMD.MM.MINISOFT;PARM = 2

Stops MSJOB if there are no user processes

:RUN MSJOBCMD.MM.MINISOFT;PARM=1

Displays the listeners that are running and user processes associated with those listeners.

RUN MSJOBCMD.MM.MINISOFT

How can dates be returned from SQL select calls?

Image supports only one data format (implicit). The K8 data type should only used for dates. You must provide details for other than the default conversion of other dates. The Minisoft ODBC and JDBC drivers convert data types from Image to SQL using a set of default maps. These defaults can be replaced by user defined conversions using the Schema Editor utility. This utility can be found in the Administrative tools installation for Minisoft ODBC and JDBC products. Documentation can be found at <Schema Editor>

The Schema Editor can provide more than simple data conversion. You can redefine any section of the record buffer into other data types. For example some Image applications use an X100 to store a series of numbers. In addition, you can define the scale factor if your data is stored in whole number fractions (such as storing currency in an I4 as cents with scale factor of 2).

Why do I get a logon error for Security/3000 when I am not using it?

For all Security/3000 related problems, please review the $STDLIST of the MSJOB. The complete text of the VESOFT provided error message will be printed following the prefix (VECHECKLOGON failed with).

Why do I get a logon error for Security/3000 when I use the correct password?

When you see the error message: VECHECKLOGON failed with : ERROR: lockfile open failed. The problem is that the HELLO PE trap is not enabled. According to VESOFT you must enable the HELLO PE trap with the following command:

%BACKG STARTJOB
%BACKG START,HELLO Read this for more information.

If you are NOT using VESOFT Security/3000, then the Security/3000 intrinsics should be removed. While they are present, we MUST assume that the system manager wants us to use Security/3000.  To remove the intrinsics, if you are on support with VESOFT, contact VESOFT.

If you follow one the two procedures below we do not know what effect this will have on other VESOFT products on you system. You do not need to follow both procedures.

1. Rename the file ‘HELLO.PE.VESOFT’.
2. Remove the VECHECKLOGON intrinsic.:HELLO MANAGER.SYS
:SEGMENTER
-SL SL.PUB.SYS
-PURGESL SEGMENT,VESOFT'PROC'P
-EXIT

Why do I see the error message: Unable to create server process.

You can view the full error message by running the ODBCSRVR.  Normal operation should produce the message: Failed to get socket.

If the problem results from insufficient capabilities, you should add Multiple RINS (MR) capability to the MM group.

ALTGROUP MM;CAP=+MR

If the file is not of type NMPRG, you must replace the executable with a new copy. Use only MS92 or NFT to upload the replacement file.

Please contact your support for information on resolving other problems.

Why does I see an error while starting the Schema Editor fail with a comment about OLEAUT32.DLL?

The Schema Editor may need updated 32-bit OLE files for some users of the original Win95. WinNT and OSR2 users do not need these files. Updated versions of these files are in use on most Win95 systems.

Why do I see #DELETED in every field when I open a (MASTER) table?

Some Image numeric type values are treated as signed values. If they are not signed and are key values, your results will not be as expected.

Why can I open and see data in a (DETAIL) table, but searches return no data?

You must create a database view using the Schema Editor to set the type of the value to signed (NO).

Why do I see the error message: [Minisoft][3kodbc.dll] connect: Connection refused (#10061)?

Periodically the demonstration copies and some licensed copies of the ODBC/32 driver expire. Please contact your Minisoft sales representative about getting an extension.

The job MSJOB.MM.MINISOFT must be streamed prior to making client connections.

The port number specified on the client must match the port number in the MSJOB file for the ODBCSRVR process. As shipped, this number is 30006.

The address given in the DSN is not that of the HP 3000 CPU. Most TELNET access to the HP e3000 is via an external box (DTC) and is a separate address from the HP e3000.

A fire wall or proxy server is not allowing connections on port 30006. Contact your network administrator or use an allowed port.

Why do I get a non-existent permanent file error message?

One of the following files does not exist or was not completely qualified: SCHEMA, DATABASE, or any of the tables specified by the SCHEMA. Remember that the SCHEMA referenced by the Data Source Name (DSN) refers to a files created by the Schema Editor application and not the Image Schema file. Check that ALL the tables in your schema exist and are accessible.

Why is the list of available tables empty (or incomplete)?

The TurboImage password is incorrect. This password is case-sensitive and must be entered carefully. There can be several passwords that allow access to more or fewer of the datasets and fields.

If you are unaware of the password, these instructions.

Why do I see the error message: ODBCSRVR is not enabled for write access?

MSJOB has not been modified for write access and you are trying to open a database with Mode 1,2,3, or 4 or are trying to open a KSAM or MPE file for write access. You need to either change MSJOB to allow write access or change the open Mode for your database, KSAM or MPE files.

Why does the message Overlapped I/O operation is in progress appear?

Complete message:

“[MiniSoft] [3kodbc.dll] In (NetConnect), error 997 Overlapped I/O operation is in progress. – Connect() Cannot connect to (192.168.23.7022644)”

This message will occur if a connection cannot be made to the ODBC Server process. Common causes are

1. MSJOB is not running.
2. The product is not currently licensed (Evaluation expired)
3. Incorrect port number
4. Incorrect host name or address
5. The port is blocked by a firewall

SQL Date Format

The escape sequence for date, time, and timestamp literals is:

{literal-type ‘value’}

where literal-type is one of the following options:

literal-type	Description	Value Format
d	Date	yyyy-mm-dd
t *	Time	hh:mm:ss [1]
ts *	Timestamp	yyyy-mm-dd hh:mm:ss[.f…]

* Not fully supported in the current release.

Example:

UPDATE NOTES SET DATE1={d '2006-07-11'}
WHERE DATE1={d '2006-06-11'}