I've got a problem with odbc access to a particular account.
I've traced the error to it's source, and i'm trying to figure out if I can
fix without dropping the schema.
Some time ago we created a new account and used the CREATE SCHEMA command.
We then used a series of CREATE TABLE commands.
I suspect that at some point I moved a file from residing in that account to
another account, and then probably deleted the VOC entry in the original
account whilst creating a new entry in the new account.
So what my little handiwork did was leave intact entries in UV_TABLES and
UV_COLUMNS to a file that no longer exists at the original path name.
However, this oversight is causing problems with odbc access to other files
in the account.
We've got a in-house program written called "dbexplore" and it's sole
function is to allow execution of sql statements against odbc databases and
display the results - it works fine for all other accounts except this one.
I mention this because this is how we're doing the testing and this program
is not at fault.
Using dbexplore, I can connect to the account fine - no problems at all.
uvodbc log file show connection.
However when I issue a statement like "SELECT * from tblJob", what the log
file shows is:
----------------------------------------------------------------------------
---
Preparing UniVerse SQL statement:
UV/ODBC cursor = 0x03FAF580
UCI hstmt = 0x00EB2BD0
statement = SELECT
TABLE_SCHEMA,TABLE_NAME,BASE_TABLE,NULL,NULL,TABLE_TYPE,NULL FROM UV_TABLES
Preparing UniVerse SQL statement:
UV/ODBC cursor = 0x03FAF580
UCI hstmt = 0x00EB28E0
statement = SELECT
TABLE_SCHEMA,NULL,TABLE_NAME,COLUMN_NAME,NULL,NULL,NULL FROM UV_COLUMNS
WHERE (IN_ASSOCIATION IS NULL OR IN_ASSOCIATION = '') AND MULTI_VALUE = 'M'
Preparing UniVerse SQL statement:
UV/ODBC cursor = 0x03FAF580
UCI hstmt = 0x00EB2700
statement = SELECT @ID FROM &SAVEDLISTS&
----------------------------------------------------------------------------
---
And it then loops through each ID in the above query and does statements
like:
Finding select lists for table: tblJob
Preparing UniVerse SQL statement:
UV/ODBC cursor = 0x03FAF580
UCI hstmt = 0x00EB29A0
statement = SELECT F2 FROM DICT "tblJob" WHERE @ID =
'HS.ODBC.LISTS'
However when it gets to PARCELS.SIGNATURE, the file I deleted:
Finding select lists for table: PARCELS.SIGNATURE
Preparing UniVerse SQL statement:
UV/ODBC cursor = 0x03FAF580
UCI hstmt = 0x00EB2DF0
statement = SELECT F2 FROM DICT "PARCELS.SIGNATURE" WHERE @ID =
'HS.ODBC.LISTS'
UCI SQLPrepare(DML) returned -1
SQLSTATE : S0002 Native Error : 950390 [IBM][SQL
Client][UNIVERSE]UniVerse/SQL: Table "PARCELS.SIGNATURE" does not exist.
Facility: DBCAPERR Severity: ERROR Error ID: 27 Extern error: 950390
Message: SQL statement referenced a nonexistent table or view.
Now, I accept that I deleted the file and thus, that's why this part of the
process is failing.
But my next problem is: How do I now clean up after myself ?
I figure that by looking in the ../uv/sql/catalog account there are a few
files worth checking out. I figure that if I delete records in UV_COLUMNS
and UV_TABLES, all should be well ?
When I try to delete the records pertaining to file PARCELS.SIGNATURE I get:
You are about to DELETE the ENTIRE record from the file. OK (Y) ? Y
Program "ED": pc = 204C, Insufficient SQL permissions to delete
IMED{PARCELS.SIG
NATURE{DRIVERID
Failed to delete "IMED^251PARCELS.SIGNATURE^251DRIVERID" from file
"UV_COLUMNS".
STATUS = 0
I get that error message if I do it under my own login name, the owner of
the file; as uvsql, the sql super-user on our system; and as root.
For good measure, here's a partial output of UV_USERS:
User Name.......... root
DBauth Privilege... NO
Resource Privilege. YES
Author............. uvsql
User Name.......... uvsql
DBauth Privilege... YES
Resource Privilege. YES
Author............. uvsql
User Name.......... andrew
DBauth Privilege... NO
Resource Privilege. YES
Author............. uvsql
And the file permissions are:
4 drwxrwsrwx 2 uvsql staff 512 Feb 16 2001 UV_COLUMNS
4 drwxrwsrwx 2 uvsql staff 512 Feb 16 2001 UV_SCHEMA
4 drwxrwsrwx 2 uvsql staff 512 Feb 16 2001 UV_TABLES
I'm a member of staff; uvsql is not; root is not. Could this be it ?
So... in a nutshell, is there a way of fixing this error short of copying
the files, dropping the schema, re-creating the schema, re-creating files,
copying data from the old-renamed files to the newly created ones then
deleting the old-renamed files ?
This is a long email - and i've you've gotten this far, I thank you for your
time !
Andrew Gissing
CTI Logistics
-------
u2-users mailing list
[EMAIL PROTECTED]
http://www.u2ug.org/listinfo/u2-users
