SQL calls

TRathjen

TRathjen

Active Member
Has anyone else tried SQL calls to Oracle from Optio? If you have succeeded all the better. My call is getting no rows returned and I'm not sure what I'm doing wrong.
I'll be pleasantly surprised if I get a response to this but I'll try anyway.
 
Tim,

We use Optio and make SQL calls in our Purchase Order document to update a user-defined code in the F4301 table (to indicate that it's been sent). Below is the code I have in our document file:

SQLRUN "~chn\POUpdate_PD.chn"

Here's the content of the above .chn file:

!! Name: Purchase Order Header Update - PD
!! Date: 06-04-2002
SQL "POUpdate_PD"
VENDOR "OCI"
DATABASE "<connect-string>"
ACCESSKEY "POUPDATE"
UPDATE PRODDTA.F554301_VIEW
SET UserReservedCode_PHURCD='Y '
WHERE OrderNumber_PHDOCO=$po_no_numeric and OrderType_PHDCTO=$type;
END SQL

You need to define the ACCESSKEY (in this example it's POUPDATE) in the Optio Control Panel. This is where you specify the Oracle username and password to be used for connecting to the database.

Finally, be sure that in Oracle, you've granted the appropriate access privileges to the user ID defined in the ACCESSKEY.

Hope this helps!
 
We currently have a number of forms that have at least one SQL call, most have three calls for different types of information, the customer's fax/email code, fax numbers or email addresses, a call to get the user information for the user submitting the form, etc. If you have not gotten your's to work please email me directly.
 
May I know how will be the connection string like? is it tnsname? I have tried to run SQL from optio, but it has problem. Is this method only applicable to Unix platform? Currently the optio i using is in windows platform.
 
Back
Top