[sqr-users] RE: Why my COMMIT doesn't work??

["White, Denise" <DEWhite@vicr.com>]

Hola Manuel,

I don't think that the "Posting" status has anything to do with the data not 
being committed.  As someone else mentioned, this is a status that is seen 
AFTER the SQR has completed and is connected with posting the output to the 
report repository.

You don't mention what database platform you are on, which may be significant, 
but in general you do not issue commits within the BEGIN-SQL paragraph in an 
SQR program.  You might want to check your SQR documentation on the "commit" 
command; in my documentation (ver. 4.3.2) it is as follows:

"COMMIT is an SQR command and should not be used within a SQL paragraph (see 
note below).  If used in a SQL paragraph, unpredictable errors may occur.

Note:   The COMMIT command can be used with Oracle, SQL Base, ODBC, DB2, 
Informix, and Ingres. For Sybase and Microsoft SQL Server, use BEGIN 
TRANSACTION and COMMIT TRANSACTION within SQL paragraphs as in the code segment 
shown below."

However, there are also issues with using the SQR commit command if you are on 
Oracle:

"For example, when used with Oracle, it closes SELECT cursors.  For this 
reason, it should not be used within the scope of an active SELECT paragraph or 
unpredictable results may occur."

I know you said that this works fine in another environment, but that may be 
what "unpredictable" is referring to!

HTH,

Denise M. White
Sr. Software Engineer
Vicor 

--__--__--

Message: 21
Subject: Re: [sqr-users] Why my COMMIT doesn't work??
To: sqr-users@sqrug.org
From: Manuel_Garcia@hdm.honda.com
Date: Wed, 30 Apr 2003 16:02:08 -0500
Reply-To: sqr-users@sqrug.org

Hello Prasanna, first of all, thanks a lot for your response.

I think no error is happening within my program because it runs well in
another environment, my problem is that I'm not seeing the COMMIT effect

Some part of the program goes as follows:


begin-procedure llenar_tabla_ps_hdm_voucher_tmp

show 'Llenando tabla PS_HDM_VOUCHER_TMP'

BEGIN-SQL

INSERT into ps_hdm_voucher_tmp using
SELECT
A.VOUCHER_ID,
B.INVOICE_ID,
B.VENDOR_ID,
B.INVOICE_DT,
A.ACCOUNTING_DT,
A.FOREIGN_AMOUNT,
A.FOREIGN_CURRENCY,
A.MONETARY_AMOUNT,
B.CLOSE_STATUS,
B.MANUAL_CLOSE_DT
FROM PS_HDM_VCHR_ACCTG A, PS_VOUCHER B
WHERE A.VOUCHER_ID = B.VOUCHER_ID
AND B.VENDOR_ID >= $vndr_fr
AND B.VENDOR_ID <= $vndr_to
;


COMMIT;


END-SQL


show 'Tabla PS_HDM_VOUCHER_TMP Creada.'

end-procedure !llenar_tabla_ps_hdm_voucher_tmp


This procedure is executing well, but after the whole program finishes
(although the process status in PS Process Monitor is never set to Success
in my test environtment), no new data is inserted in my table. I checked
the select statement outside SQR and it works.

Thanks a lot !

Juan Manuel García
Tecnología de Información
Honda de Mexico S.A. de C.V.
Tel: (01 33) 328 40135

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users