• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

SQL calls

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.
 

DSauve

Legendary Poster
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!
 

chemker

Well Known Member
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.
 

yit1010

Member
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.
 
Top