Hi,
last time I gave you the hint to replace the single "EXEC SQL DECLARE
... CURSOR FOR ..." command by the sequence "EXEC SQL PREPARE" and "EXEC
SQL DECLARE" to avoid the out of temporary space problem. Unfortunately
now this sequence is the reason for the error -7008. The "EXEC SQL
PREPARE" doesn't contains a cusorname and therefor the kernel assumes
you are using the unnamed resultset which is not allowed to use the "FOR
UPDATE" clause :(
But, I have another workaround:
EXEC SQL PREPARE SACGBRMIO_REQIO_PREP FROM
"\
DECLARE SACGBRMIO_REQIO CURSOR FOR SELECT \
A.GBRGBRIDN, \
A.GBRGBRNAM, \
A.GBRGBRAFD, \
A.GBRGBROMS, \
A.GBRVRVPER, \
A.GBRVRVDAT, \
A.GBRAUTSRT, \
A.GBRGBRPRT, \
A.GBRMENIDN, \
A.GBRGRPIDN \
FROM SASGBRM00 A \
WHERE \
A.GBRGBRIDN = ? \
FOR UPDATE NOWAIT WITH LOCK (NOWAIT) EXCLUSIVE"
;
EXEC SQL DECLARE SACGBRMIO_REQIO
CURSOR FOR SACGBRMIO_REQIO_PREP ;
EXEC SQL OPEN SACGBRMIO_REQIO USING
:SQLDTA.GBRGBRIDN
;
CHECK_ERROR
This will avoid the temp space bug and also runs without any kernel
error. Hope this is a possible workaround for you.
Regards,
Marco
SAP Labs Berlin
> -----Original Message-----
> From: Nicolay Heymen [mailto:[EMAIL PROTECTED]
> Sent: Mittwoch, 9. Februar 2005 13:44
> To: Paskamp, Marco
> Subject: RE: Problem with prepare in embedded SQL C -7008
> updates of this table not allowed
>
> Hello Marco,
>
> It's not a view that's causing the problem. The definition of
> the table and indexes is below.
> There are no constrains, foreign keys etcetera.
>
> Attached hereby you will find a source of a test program.
>
> If any further information is required, don't hesitate to ask.
>
> Regards,
> Heymen
>
> CREATE TABLE "SASFIL"."SASGBRM00"
> (
> "GBRGBRIDN" Char (10) ASCII NOT NULL,
> UNIQUE(GBRGBRIDN),
> "GBRGBRIDN" Char (10) ASCII NOT NULL,
> UNIQUE(GBRGBRIDN),
> "GBRGBRNAM" Char (25) ASCII NOT NULL,
> "GBRGBRAFD" Char (25) ASCII NOT NULL,
> "GBRGBROMS" Char (25) ASCII NOT NULL,
> "GBRVRVPER" Fixed (3,0) NOT NULL,
> "GBRVRVDAT" Fixed (8,0) NOT NULL,
> "GBRAUTSRT" Char (1) ASCII NOT NULL,
> "GBRGBRPRT" Char (10) ASCII NOT NULL,
> "GBRMENIDN" Char (10) ASCII NOT NULL,
> "GBRGRPIDN" Char (10) ASCII NOT NULL
> )
> CREATE UNIQUE INDEX "SASGBRML0A" ON
> "SASFIL"."SASGBRM00"("GBRGBRIDN" ASC) ;
> CREATE UNIQUE INDEX "SASGBRML0D" ON
> "SASFIL"."SASGBRM00"("GBRGBRIDN" DESC) ;
> CREATE UNIQUE INDEX "SASGBRML1A" ON
> "SASFIL"."SASGBRM00"("GBRGRPIDN" ASC, "GBRGBRIDN" ASC)
> CREATE UNIQUE INDEX "SASGBRML1D" ON
> "SASFIL"."SASGBRM00"("GBRGRPIDN" DESC, "GBRGBRIDN" DESC)
>
>
>
> -----Original Message-----
> From: Paskamp, Marco [mailto:[EMAIL PROTECTED]
> Sent: woensdag 9 februari 2005 13:16
> To: Nicolay Heymen; [email protected]
> Cc: Kampen, van Tom; Lammers Joost
> Subject: RE: Problem with prepare in embedded SQL C -7008 updates of
> this table not allowed
>
>
> Hi,
> could it be that you are selecting from a view "SASGBRM00"?
> Depending on
> the view definition this might not work. If it is not a view could you
> please send the table definition for further analysis?
>
> Regards,
> Marco
> SAP Labs Berlin
> > -----Original Message-----
> > From: Nicolay Heymen [mailto:[EMAIL PROTECTED]
> > Sent: Mittwoch, 9. Februar 2005 09:17
> > To: [email protected]
> > Cc: Kampen, van Tom; Lammers Joost
> > Subject: Problem with prepare in embedded SQL C -7008 updates
> > of this table not allowed
> >
> > Hi there,
> >
> > We have a problem EXEC SQL PREPARE when the statement
> > contains a FOR UPDATE clause. We get a run-time error
> > -7008 Updates of this table not allowed.
> >
> > Without prepare the temporary used area continuely grows:
> > static void sql_open(void){
> > EXEC SQL DECLARE SACGBRMIO_REQIO CURSOR FOR
> > SELECT
> > A.GBRGBRIDN,
> > A.GBRGBRNAM,
> > A.GBRGBRAFD,
> > A.GBRGBROMS,
> > A.GBRVRVPER,
> > A.GBRVRVDAT,
> > A.GBRAUTSRT,
> > A.GBRGBRPRT,
> > A.GBRMENIDN,
> > A.GBRGRPIDN
> > FROM SASGBRM00 A
> > WHERE
> > A.GBRGBRIDN = :SQLDTA.GBRGBRIDN
> > FOR UPDATE NOWAIT WITH LOCK (NOWAIT) EXCLUSIVE
> > ;
> > EXEC SQL OPEN SACGBRMIO_REQIO ;
> > sqlcode = sqlca.sqlcode;
> > if (sqlcode == 0) {
> > sql_fetch();
> > }
> > }
> >
> > To circumvent the filling of the temporary used area and
> > gaining in performance we are now using the EXEC SQL PREPARE.
> > This strategy has proven to be succesfull with other
> > statements but not with the one below.
> >
> > With prepare '-7008 Updates of this table not allowed' occurs:
> > static void sql_open(void){
> > static int sw = 0 ;
> > if( sw == 0 ) {
> > sw = 1 ;
> > EXEC SQL PREPARE SACGBRMIO_REQIO_PREP FROM
> > "\
> > SELECT \
> > A.GBRGBRIDN, \
> > A.GBRGBRNAM, \
> > A.GBRGBRAFD, \
> > A.GBRGBROMS, \
> > A.GBRVRVPER, \
> > A.GBRVRVDAT, \
> > A.GBRAUTSRT, \
> > A.GBRGBRPRT, \
> > A.GBRMENIDN, \
> > A.GBRGRPIDN \
> > FROM SASGBRM00 A \
> > WHERE \
> > A.GBRGBRIDN = ? \
> > FOR UPDATE NOWAIT WITH LOCK (NOWAIT) EXCLUSIVE"
> > ;
> > }
> > EXEC SQL DECLARE SACGBRMIO_REQIO
> > CURSOR FOR SACGBRMIO_REQIO_PREP ;
> > EXEC SQL OPEN SACGBRMIO_REQIO USING
> > :SQLDTA.GBRGBRIDN
> > ;
> > sqlcode = sqlca.sqlcode;
> > if (sqlcode == 0) {
> > sql_fetch();
> > }
> > }
> >
> > We are using MaxDB version 7.5.00.23 on SuSE Linux Enterprise
> > Server for x86.
> >
> > Those anyone have a solution for this.
> >
> > Heymen Nicolaij
> > Technisch Specialist
> >
> > PinkRoccade
> > Luchthavenweg 54, 5657 EB Eindhoven
> > Postbus 57010, 5605 AA Eindhoven
> >
> > T: 040-2562685
> > M: +31 646091122
> > E: [EMAIL PROTECTED]
> > I: www.pinkroccade.nl
> >
> > --------------------------------------------------------------
> > --------------------------------------------------------------
> > -----------
> > Op deze e-mail is een disclaimer van toepassing/ This e-mail
> > is subject to a disclaimer:
> > <http://www.pinkroccade.nl/emaildisclaimer>
> > --------------------------------------------------------------
> > --------------------------------------------------------------
> > -----------
> >
> >
> >
> >
> > --
> > MaxDB Discussion Mailing List
> > For list archives: http://lists.mysql.com/maxdb
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
