Re: 3-part SQL and Aliases

"RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx> wrote on 02/15/2018 09:57:00
AM:

By default, your program is connected to the local database. You
can explicitly connect to the remote database and you do not lose your
local database connection. Thus, you can then switch back and forth
between the two connections as needed. Your other SQL doesn't even
have to use 3-part references as long as you don't need to access
tables on both systems at the same time. Alternately just requires
switching connections (SET CONNECTION). But, at the same time
requires 3-part references.

If you need an example, In the following, the local database name
is passed to my program from CL.

exec sql set connection :pSysName; // cleanup just in case
exec sql commit;

callp ConvertLifoCompanies(pSysName);

exec sql set connection :pSysName; // restore normal connection
exec sql commit;


Then, the following edited snippet makes a connection to a
mainframe platform to read data, match it with a joined view on the local
database, and then write or update it to the base table on the local
database.

exec sql disconnect tcAccess; // cleanup just in case

exec sql connect to tcAccess user :tc_user using :tc_pwrd;
if SqlUtl_SqlFailure(sqlstate); // did the connect fail?
SqlUtl_LogSqlFailure( Sql_Connect_Error: '*ESCAPE'
: sqlca: loc_info );
endif;

dow GetTableData( PROC_PGM: store_data: store_ary
: lifo_flag: lifo_ind );
exec sql set connection :pSysName;

if not ImLfCm_GetTableRow( Caller: '0' + iCoNum
: ImLfCmp_View2: ImLfCmp_Ary );
GenUtl_Diagnose( 'WSE9897'
: 'LIFO Company record not found and skipped:
+ iCoNum );
exec sql set connection tcAccess;
iter;
endif;

...snip...

if ImLfCm_ValuesValidated( ImLfCmp_View2: ImLfCmp_Ary
: CrsApp_Messages );
if ImLfCmp_View2.IMLFCMPTID = *zero; // new company record?
isSuccess = ImLfCm_LUW_ValuesInserted( ImLfCmp_View2
: ImLfCmp_Ary
: CrsApp_Messages
: PROC_PGM );
else; // update LIFO Company row
isSuccess = ImLfCm_LUW_ValuesUpdated( ImLfCmp_View2
: ImLfCmp_Ary
: CrsApp_Messages
: *omit: *omit
: PROC_PGM );
endif;
if not isSuccess;
callp GenUtl_Escape( CrsApp_Messages(1).Id
: CrsApp_Messages(1).Data
: CrsApp_Messages(1).File
: CrsApp_Messages(1).FLib
: PROC_PGM );
endif;
else;
callp GenUtl_Diagnose( CrsApp_Messages(1).Id
: CrsApp_Messages(1).Data
: CrsApp_Messages(1).File
: CrsApp_Messages(1).FLib
: PROC_PGM );
endif;

exec sql set connection tcAccess;
enddo;

exec sql commit; // complete the commitment process
exec sql release tcAccess; // release the database
exec sql disconnect tcAccess; // release the connection

Sincerely,

Dave Clark