perv. READ-ONLY error? how can it be? 1

[davemeng]

I have a SP that takes two numeric parameters. the SP works perfectly using the Pervasive Control Center.But if i use Crystal Reports 2008 and this SP as my datasource, it will prompt me for the parameters just fine, ......but sending the parameter values back and returning the dataset is not, pervasive sends an error message saying the table is "read-only"which is B.s.security is not restricted, even for tables, the table even has an identity as the pk.looking for ideas?thanks

CREATE PROCEDURE SODLOOP (IN :SONUM INTEGER, IN :NUMBOX INTEGER)
RETURNS(
RECNUM IDENTITY,
SONUM INTEGER,
SHIPNAME VARCHAR(30),
SHIPA1 VARCHAR(30),
SHIPA2_1 VARCHAR(30),
SHIPA2_2 VARCHAR(30),
SHIPCITY VARCHAR(30),
SHIPST VARCHAR(30),
SHIPZIP VARCHAR(30),
SHIPVIA VARCHAR(15),
SHIPATTN VARCHAR(30),
CUSORD VARCHAR(25),
FOB VARCHAR(15),
JOBNUM VARCHAR(15),
INVCODE VARCHAR(1),
SODESC VARCHAR(30),
PHONE_1 VARCHAR(25),
FAX VARCHAR(25)
);

BEGIN
--CLEAR PREVIOUS DATASET
DELETE FROM ASHIPLABEL;
--START LOOP BASED ON NUMBER OF LABELS
DECLARE :LOOPCOUNT INTEGER;
SET :LOOPCOUNT = 1;
INSIDELOOP:
LOOP
INSERT INTO ASHIPLABEL(SONUM,SHIPNAME,SHIPA1,SHIPA2_1,SHIPA2_2,SHIPCITY,SHIPST,SHIPZIP,SHIPVIA,SHIPATTN,CUSORD,FOB,JOBNUM,INVCODE,SODESC,PHONE_1,FAX)
SELECT
SO.BKAR_INV_SONUM,
SO.BKAR_INV_SHPNME,
SO.BKAR_INV_SHPA1,
SO.BKAR_INV_SHPA2_1,
SO.BKAR_INV_SHPA2_2,
SO.BKAR_INV_SHPCTY,
SO.BKAR_INV_SHPST,
SO.BKAR_INV_SHPZIP,
SO.BKAR_INV_SHPVIA,
SO.BKAR_INV_SHPATN,
SO.BKAR_INV_CUSORD,
SO.BKAR_INV_FOB,
SO.BKAR_INV_JOBNUM,
SO.BKAR_INV_INVCD,
SO.BKAR_INV_DESC,
CU.BKAR_TELEPHONE_1,
CU.BKAR_FAX_PHONE
FROM BKARINV SO INNER JOIN BKARCUST CU
ON SO.BKAR_INV_SHPCOD = CU.BKAR_CUSTCODE
WHERE SO.BKAR_INV_SONUM = :SONUM;

IF :LOOPCOUNT = :NUMBOX THEN
LEAVE INSIDELOOP;
END IF;
SET :LOOPCOUNT = :LOOPCOUNT +1;
END LOOP;

SELECT * FROM ASHIPLABEL;

END;

 

[mirtheil]

SOunds like Crystal Reports is making a Read Only connection. To verify this, start the PSQL monitor and look at the Microkernel Active Files option to see the files opened by the Crystal Reports connection. Check the data file you are trying to insert/update to see if it shows a Read Only handle.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[davemeng]

Thank you for responding.
the monitor shows no read only flag, no locks. was testing on perv 10.3 wge, even now on 11.x wge.
am running on my win7 laptop using local sample db.

seems the problem is coming from the basis that i'm doing a select into ...... table, and then select * from table.
i.e.
did another SP just to prove..... works great in PCC...

CREATE PROCEDURE AATEST()
RETURNS(
RN INTEGER,
SO INTEGER,
CU CHAR(30)
);
BEGIN
INSERT INTO AA( SONUM,CUST)
SELECT BKAR_INV_SONUM, BKAR_INV_CUSNME
FROM BKARINV
WHERE BKAR_INV_INVCD IN ('','X');

SELECT * FROM AA;

END;
but when i put this SP into a Crystal report, it errors
and pervasive still says the big long notificaiton of :
failed to retrieve data from the db.
details: 42000: Pervasive Odbc client.....etc....Access denied to a READ-ONLY data source. db vendor code: -5099.

naturally i abbreviated the entire dialog. i'm using the 32bit odbc driver.

i know i used to do this just fine.
thanks again,

 

[mirtheil]

I didn't have Crystal Reports 2008 installed so I downloaded the trial and then created a stored procedure using:

create table sptest (f1 char(10), f2 char(10))#
create procedure sp1 ()
returns (
f1 char(10), f2 char(10));
begin
insert into sptest (select name, section from class where id < 5);
select * from sptest;
end;

I installed Crystal, created a report, pointed at the stored procedure and got the error you were seeing. I had turned on MKDE tracing and found the following in the trace:

<In> 0455 Opcode : 0000 Crs ID : 0x00000000 Db Length : 00001 Keynum : fe Clnt ID : 00000000000000000000BC0B 5245 0180 Timer : 0000000000049D5E Time : Tue Feb 15 19:31:28 2011
DBuf: 4e 4f 54 53 48 4f 57 4e - 00 NOTSHOWN.
KBuf: 6d 69 72 74 68 65 69 6c - 76 6d 65 76 5c 43 24 5c \\mirtheilvm\C$\
50 52 4f 47 52 41 4d 44 - 41 54 41 5c 50 45 52 56 PROGRAMDATA\PERV
41 53 49 56 45 20 53 4f - 46 54 57 41 52 45 5c 50 ASIVE SOFTWARE\P
53 51 4c 5c 44 45 4d 4f - 44 41 54 41 5c 43 6c 61 SQL\DEMODATA\Cla
73 73 2e 6d 6b 64 - ss.mkd
File: "\\mirtheilvm\C$\PROGRAMDATA\PERVASIVE SOFTWARE\PSQL\DEMODATA\Class.mkd"
Target: Local engine

Now, the keynum FE translates to a -2 which is a read only file. It's not the file that's being written to but further in the trace is:

<In> 0607   Opcode : 0000   Crs ID : 0x00000000  Db Length : 00001   Keynum : fe   Clnt ID : 00000000000000000000BC0B 5245 0C80  Timer : 000000000010EF70   Time : Tue Feb 15 19:44:55 2011
DBuf:       4e 4f 54 53 48 4f 57 4e - 00                        NOTSHOWN.       
KBuf:       6d 69 72 74 68 65 69 6c - 76 6d 65 76 5c 43 24 5c   \\mirtheilvm\C$\
            50 52 4f 47 52 41 4d 44 - 41 54 41 5c 50 45 52 56   PROGRAMDATA\PERV
            41 53 49 56 45 20 53 4f - 46 54 57 41 52 45 5c 50   ASIVE SOFTWARE\P
            53 51 4c 5c 44 45 4d 4f - 44 41 54 41 5c 73 70 74   SQL\DEMODATA\spt
            65 73 74 2e 6d 6b 64    -                           est.mkd         
File:       "\\mirtheilvm\C$\PROGRAMDATA\PERVASIVE SOFTWARE\PSQL\DEMODATA\sptest.mkd"
Target:     Local engine

This is the file that's being written to and it's being opened read only (with keynum FE).

This tells me that Crystal is telling the engine to open the files Read Only.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[davemeng]

Thank you for testing and discovering the actual culprit! i don't know why or when Crystal, aka BO, aka SAP changed this functionality. i will either go back to an earlier version of Crystal, or figure out another method to produce the results i need.

i really do appreciate you taking the extra steps in testing and explaining the results of such.

have a great day.