Hi! Now I've got some interesting new stuff for the ODBC-hackers! I've got following constellation on a database:
Following users are defined:
DBA
|
\--> TESTUSER
|
\--> TESTACC
The TESTUSER is owned by the DBA and TESTACC is owned by TESTUSER.
TESTUSER has got a table AUFTR which has been created with the
following statement:
CREATE TABLE AUFTR (
ANR FLOAT NOT NULL,
AUFBEW FLOAT,
ERWARBEITSSTD FLOAT,
ERWLOHNKOST FLOAT,
ERWMATKOST FLOAT,
ERWSONSTKOST FLOAT,
TATSARBEITSSTD FLOAT,
TATSLOHNKOST FLOAT,
PROJEKTNR CHAR(8),
DATANL DATE,
DATAEND DATE,
BEARB CHAR(8),
DATGEPLANT DATE,
STDAUFTRAG CHAR(8),
DATENDE DATE,
ARBG VARCHAR(255),
ATYP CHAR(2),
AART CHAR(2),
ASTATUS CHAR(6),
SCHICHT CHAR(6),
MEISTERB CHAR(6),
OBJKOSTST CHAR(8),
VERANTWBER CHAR(4),
BEM LONG,
KOMPONENTE CHAR(18),
ARBEITSPLANER CHAR(20),
STOERZEIT FLOAT,
DOKUMENT CHAR(20),
INSPWART CHAR(4),
AUSFKOSTST CHAR(6),
SPERR CHAR(1),
KARTENNUMMER CHAR(12),
KOSTENTR CHAR(11),
PLANINTERVALL FLOAT,
EINHEIT CHAR(3),
STDFREMDFIRMA FLOAT,
PRIMARY KEY (ANR)
)
---
CREATE INDEX INDEX_ASTATUS ON AUFTR(ASTATUS)
---
CREATE INDEX INDEX_ATYP ON AUFTR(ATYP)
---
CREATE INDEX INDEX_BEARB ON AUFTR(BEARB)
---
CREATE INDEX INDEX_STDAUFTRAG ON AUFTR(STDAUFTRAG)
(BTW: I haven't designed this table/db ;-) )
TESTUSER has created a role TESTROLE which has got SELECT-
privileges on table AUFTR. TESTUSER granted this role to
TESTACC and altered TESTACC so that he will use TESTROLE as
his default role.
Now TESTACC can access this table read-only. After TESTUSER
has put 152572 records in table AUFTR.
When TESTACC connects via MS Access2k the first time he is
able to open the table. The second time he isn't, the third
he is, the forth he isn't and so on.
I've put a ODBC-trace as attachment to this mail.
So. After logging that, I played a bit with the data. Once
I've read a mail about the problems of Access with huge
tables (i.g. a lot of rows). So I deleted first 50000 rows.
TESTACC had the same problem. Then I deleted several times
hundreds of rows. But the problem persisted. After a while
I was getting to only 60 rows in the table. At this point
TESTACC couldn't display the table anymore.
Here's an ODBC-trace (stuff like this <...> means that I've
cut some lines away, which have been nearly the same):
PRODUCT : liveCache C-PreComp Runtime
VERSION : 7.2.5
BUILD : 004-000-246-186
DATASOURCE: LINUXDB_TEST
SESSION : 1;
SQLMODE : INTERN
SERVERDB : TEST
SERVERNODE: linuxdb
CONNECT "TESTACC " IDENTIFIED BY :A ISOLATION LEVEL 1
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
SELECT Config, nValue FROM MSysConf
PARSE : CMD :
SQLCODE: -4004 Unknown table name:MSYSCONF
PARSEID: OUTPUT: 00000000 00000000 00000000
SQLERRD(INDEX_6) : 28 ERROR NEAR ^ : alue FROM ^MSysConf
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
SELECT "TESTUSER"."AUFTR"."ANR" FROM "TESTUSER"."AUFTR"
PARSE : CMD :
mfIndex init : 1
mfIndex init : 2
PARSEID: OUTPUT: 000000A0 00000201 3C002C00
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
SELECT "TESTUSER"."AUFTR"."ANR" FROM "TESTUSER"."AUFTR"
EXECUTE: CMD :
PARSEID: INPUT : 000000A0 00000201 3C002C00
mfIndex init : 2
mfIndex init : 2
WARNING: W-------8-------
SQLRESULTNAME : SQL_CURSOR_0002
SQLERRD(INDEX_3) : -1
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
MASS STATEMENT :
FETCH "SQL_CURSOR_0002"
PARSE : CMD :
WARNING: W--3------------
PARSEID: OUTPUT: 000000A0 00000401 2A002B00
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
MASS STATEMENT :
FETCH "SQL_CURSOR_0002"
EXECUTE: CMD :
PARSEID: INPUT : 000000A0 00000401 2A002B00
OUTPUT : 1: ANR : 9.574020000000000E+05
SQLERRD(INDEX_3) : 1
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
<...>
MASS STATEMENT :
FETCH "SQL_CURSOR_0002"
EXECUTE: CMD :
PARSEID: INPUT : 000000A0 00000401 2A002B00
OUTPUT : 1: ANR : 1.046388000000000E+06
SQLERRD(INDEX_3) : 1
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
MASS STATEMENT :
FETCH "SQL_CURSOR_0002"
EXECUTE: CMD :
PARSEID: INPUT : 000000A0 00000401 2A002B00
SQLCODE: 100 ROW NOT FOUND
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
CLOSE "SQL_CURSOR_0002"
PARSE : CMD :
PARSEID: OUTPUT: 000000A0 00000501 06002800
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
CLOSE "SQL_CURSOR_0002"
EXECUTE: CMD :
PARSEID: INPUT : 000000A0 00000501 06002800
PARSEID: SELECT: 000000A0 00000201 3C002C00
mfIndex init : 2
mfIndex restore : 2
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
SQCDROPPARSID
PARSEID: : 000000A0 00000401 2A002B00
SQCDROPPARSID
PARSEID: : 000000A0 00000501 06002800
mfIndex delete : 2
SQCDROPPARSID
PARSEID: : 000000A0 00000201 3C002C00
SELECT
"ANR","AUFBEW","ERWARBEITSSTD","ERWLOHNKOST","ERWMATKOST","ERWSONSTKOST",
"TATSARBEITSSTD","TATSLOHNKOST","PROJEKTNR","DATANL","DATAEND","BEARB",
"DATGEPLANT","STDAUFTRAG","DATENDE","ARBG","ATYP","AART","ASTATUS","SCHICHT"
,
"MEISTERB","OBJKOSTST","VERANTWBER",'#S_C_H#',"KOMPONENTE","ARBEITSPLANER",
"STOERZEIT","DOKUMENT","INSPWART","AUSFKOSTST","SPERR","KARTENNUMMER",
"KOSTENTR","PLANINTERVALL","EINHEIT","STDFREMDFIRMA" FROM
"TESTUSER"."AUFTR"
WHERE "ANR" = ? OR "ANR" = ? OR "ANR" = ? OR "ANR" = ? OR "ANR" = ? OR "ANR"
=
? OR "ANR" = ? OR "ANR" = ? OR "ANR" = ? OR "ANR" = ?
PARSE : CMD :
SQLCODE: 100 Row not found
PARSEID: OUTPUT: 00000000 00000000 00000000
START : DATE : 2001-12-19 TIME : 0018:21:51
END : DATE : 2001-12-19 TIME : 0018:21:51
S1: DISCONNECT
SQCFINISH
Maybe you can help me? Thanks!
Greets
Christian Jung
PS: I hope that it doesn't bother you, that the ODBC-trace is packed as a
gzipped tar. At the moment I haven't got a zip-tool for Windows...
<<trace.tar.gz>>
trace.tar.gz
Description: Binary data
