Re: Trouble using EXECUTE statement to run Oracle function

["Walcker, Paul" <pwalcker@ISD.SBCOUNTY.GOV>]

I would like to append on to George's thought.  I would do the ODBC trace
for both methods of executing the program.  It seems like Visual Scribe may
be making some ODBC settings behind the scenes that you are not aware of.
Capturing and comparing both traces may provide some useful insights (if you
can wade through all the ODBC API calls).

Paul

-----Original Message-----
From: George Jansen [mailto:GJANSEN@AFLCIO.ORG]
Sent: Tuesday, July 02, 2002 5:01 AM
To: SQR-USERS@list.iex.net
Subject: Re: Trouble using EXECUTE statement to run Oracle function


a. Is it possible that the effective path is different at your command
prompt (odbc needs %0RACLE_HOME%\BIN on the path)?
b. Shouldn't you be using a BEGIN-SQL paragraph, this being Oracle?
c. But "b" isn't the cause of your failure; you aren't getting that
far.
d. From your control panel you can turn on ODBC logging...


>>> whsmith@SRPNET.COM 07/01/02 06:26PM >>>
I've got an SQR program that reads preventative maintenance records
(PMs) in
a Maximo database and generates workorders for any PMs that are due.
The
outer logic of the program selects the PMs that are due. Within this
select
block are several procedures that update several other tables in the
database as needed for each particular workorder. One of these
procedures
calls the SQR Execute statement to run an Oracle stored function. The
final
step in the process is to write the new workorder itself.

The only COMMIT statement in the code is after the workorder is
successfully
written - all other table updates are dependant upon the workorder
getting
written, so if any step fails, all other processing for the current
workorder must be rolled back. There is no transaction processing in
the
Oracle procedure.

Boring tech stuff: SQR Version is ODBC 4.3.4, running against Oracle
8i.
Platform is Win 2000 and an HPUX database server.

What's failing:
When the compiled version of this program is run from our production
job
scheduler or a DOS prompt on my development PC, the program fails on an
ODBC
login failure. The message is "(SQR 1803) CONNECT failed.  Perhaps
username/password incorrect." I've loaded the program with #debug show
statements to create a log, and found that the login failure is
occurring
the first time the program attempts the EXECUTE statement. The DOS
command
line that I'm using is:

C:\SQRIBE\SQRServer\ODBC\ODB\BINW\sqrwt
y:\SQR43~1.4\NGSPMG~1\NGSWOGEN.sqt
DSN=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx -XCB -E
-Oy:\SQR43~1.4\NGSPMG~1\NGSWogen.log -XMB
-Fy:\SQR43~1.4\NGSPMG~1\NGSWogen.lis


What's working:
If I load the source for this program into Visual Sqribe and execute
it
using the preview window, it runs successfully. If I run the compiled
version using the "SQR Execute" utility (sqrwtj.exe), it runs
successfully.


What I've tried:
I've verified that the DSN name, uid and password are all correct in
the DOS
command line as well as the ODBC connect strings within the program.
I've
tried changing the DOS 8.3 representations of the path to the full
folder
names in the DOS batch file. I've tried adding a separate COMMIT
statement
in the Oracle function and setting it to an autonomous transaction so
that
it's transaction processing wouldn't affect the calling program. I've
tried
beating my head against my desk. None of these attempts have had any
effect
except for the head thing, which hurt like heck.

Any suggestions for where to go next would be much appreciated...