ODBC and weird Progress / QAD Behavior

[gatsbyinca]

Hi,

We recently upgraded our QAD system to run on Progress 10.1A. We have been struggling with the ODBC part ever since!

In our old system (9. something), we had a Progress user ID (_User record ) called “sysprogress”. We used this to connect via ODBC. As I understand it, we manually created this user ID. As part of the upgrade we created the same user ID using the Progress Dictionary. What we have done is to confuse the heck out of QAD so that it (QAD) cannot correctly identify who is locking who (or is that whom!) anymore.

QAD technical support told us that we made a mistake by creating the user ID in Progress. We should have done it in SQL Explorer. The question we have is…what is SQL Explorer and why is there a difference?

Thanks!

 

[W.Wulmsen]

Hai gatsbyinca,
Sorry for the late response. I still hope that this text can help you in future.

When connecting to an ODBC database be aware to the following:
- The userid used when creating a progress database. This userid is the OWNER of the database, Schema, etc..
- Depending on PROGRESS licentie you have to use the -Mn, -Mi parameters. For a ODBC connection is must be possible for the Progress server to create a SQL server-broker process.


---
SQL expolorer is a Java ODBC tool to access a database with SQL-92 statements. You connect to a database with ODBC parameters.

---

Since a few months I have used the following program to create a file with all accessable files. Even when there are no users defined in _user the database should be accessible. The "world" via PUBLIC are given read (SELECT) rights to the database.


/** Start coding **/
DEF VAR usrid AS CHAR NO-UNDO.
DEF STREAM exstream.

ASSIGN
usrid = "PUBLIC"
.

OUTPUT STREAM exstream TO grantdb.sqlb.

FOR EACH _file
WHERE _file-number > 0
AND NOT _file-name BEGINS "sys"
:

DISPLAY _file-name.
PAUSE 0.

PUT STREAM exstream
UNFORMATTED
"GRANT SELECT ON PUB."
_file-name
" TO "
usrid
";" SKIP.
END.

PUT STREAM exstream "COMMIT;" SKIP.
OUTPUT CLOSE.
/** end coding */

Start-up SQL explorer and run the file with <CTRL>-R.

William W.