Solved

Executing a program in AS/400 using ODBC

Posted on 2011-09-28
13
476 Views
Last Modified: 2012-05-12
Hello Experts.

Is it possible to execute an AS/400 program using ODBC driver from a Windows program? I have been told this is a program and NOT a stored procedure nor stored procedure wrap that launches the program.

I have read that the execution can be done using OLE DB or .NET provider but I want to know if it can be done using ODBC driver.

If it is possible to do that, it would be helpful a link or an small example.

Thank you in advance.
0
Comment
Question by:arturosm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
13 Comments
 
LVL 18

Accepted Solution

by:
daveslash earned 250 total points
ID: 36815837

I would probably be easiest to wrap the program as a stored procedure:

CREATE PROCEDURE MySchema/MyProgram
  LANGUAGE RPG 
  NOT DETERMINISTIC 
  MODIFIES SQL DATA
  EXTERNAL NAME MySchema/MyProgram
  PARAMETER STYLE GENERAL WITH NULLS

Open in new window

0
 
LVL 27

Expert Comment

by:tliotta
ID: 36818113
If there all parameters are input only, you should be able call a program with ODBC just like a stored procedure. The DB2 server will execute a simple program call the same way it would execute a stored procedure call.

If any parm values need to be returned to the client, a full stored proc definition is required because the server needs to know how to present the returned values across the network.

A common example would be a call to the QCMDEXC API to execute a command.

Tom
0
 

Author Comment

by:arturosm
ID: 36881128
Hi tliotta:

Yes, there is only one input parameter, I tryed to call the program with call but got an exception, something like "the stored procedure has been prepared". I'm not sure about the syntax of the call, I'm using: "CALL LIBRARY.PROGRAMNAME(CHARPARAM)".

---------------------------------------------

Thank you daveslash.

I have to call this program as part of another not related project, so the people in charge of the AS400 will not even do that simple stored procedure. I think I can't create it with the user I have, and my knowledge of AS400 is almost null, I said "almost" so I don't make myself feel bad. I will try to create it using the text in a SQL statement.

-----------------

Regards.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:tliotta
ID: 36890049
There are the CHAR and the VARCHAR parameter types possible in this type of case. Without a declaration, it takes some care to ensure that any value that you supply gets across the network so that the database server knows how to present the value to the target program.

Do you know what how target program declares the variable? How is CHARPARAM actually supplied in the client? I.e., what is the exact statement in the client? And the actual exception will probably be needed.

Tom
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 36893740
The correct format is:

CALL LIBRARY/PROGRAMNAME PARM('CHARPARAM')

If passing this string directly doesn't work, you'll need to wrap it in a call to the system stored procedure QCMDEXEC (whci in turn is just a wrapper for the system API QCMDEXC:

CALL QSYS.QCMDEXC('CALL LIBRARY/PROGRAMNAME PARM(''CHARPARAM'')',   0000000044.00000)
-or-
CALL QSYS/QCMDEXC('CALL LIBRARY/PROGRAMNAME PARM(''CHARPARAM'')',   0000000044.00000)

Depending on the SQL naming format used by the ODBC connection (*SQL =".", and *SYS ="/").   Note that this only applies to SQL commands and not system commands.  Confusing, since the leftmost CALL is a SQL CALL command, and the rightmost CALL is an OS CALL command.

Note that all of the quotes are 'single' quotes, including the doubled single quotes around CHARPARAM.  The "44" is the length of the system command in bytes.  This value must be padded out to 10 digits on the left of the decimal and five digits to the right.

Note that this technique can be used not only to execute programs using the OS "CALL" command, but can be used to execute any OS command.

http://www.itjungle.com/mgo/mgo080803-story01.html

- Gary Patterson



0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 250 total points
ID: 36929013
No, the correct format is probably:

CALL LIBRARY.PROGRAMNAME('CHARPARAM')

But it depends on the declaration of CHARPARAM in program PROGRAMNAME.

If it is declared as a CHAR(9), then the above CALL should work correctly. But if it's defined as a CHAR(10), then there needs to be a blank at the end:

CALL LIBRARY.PROGRAMNAME('CHARPARAM ')

In short, the value of CHARPARAM can be passed unquoted if it is defined as a VARCHAR type. But if it is a CHAR type, it must be passed as the declared length quoted and padded with blanks or truncated, whichever gives the exact length. A CHAR(20) would require eleven trailing blanks all within quotes.

There is no need to call PROGRAMNAME through any intermediate stored proc. There is no need for a wrapper for any program that only has inbound parms -- including QCMDEXC.

That's always been true for DB2 on AS/400s.

Tom
0
 
LVL 27

Expert Comment

by:tliotta
ID: 36934520
Note that "CHARPARAM" would refer to the value represented rather than a variable in the client when quoted. The value must be resolved and quoted as the appropriate length when it is declared as a character data type. A variable-length character data type wouldn't require quotes, nor is there an actual declared length in the called program (i.e., it's "variable").

Without quotes, most clients will structure the request to the remote database so that the DBMS will go looking for a signature match that has a VARCHAR parm. That's not a common case when calling an undeclared stored proc. Most bare program objects will declare fixed-length character parms.

Tom
0
 

Author Comment

by:arturosm
ID: 37004047
Hello.

Next Monday I will go to the site to try your solutions.

Thank you.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37167283
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 37167284
Recommend you split points to 36929013 and 36815837.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question