Changing SQL PATH initiated in ODBC settings

[Iaorana]

Hi all,

In one of our application, a program on a PC server calls a RPG program on our iSeries via an ODBC driver connection. The RPG program is performing dynamic SQL on the iSeries database.
It seems that the SQL Path used by the RPG program is initiated by the ODBC settings on the PC server. We want the program to run correctly whatever the SQL PATH initiated in ODBC connection settings are.
So with have to reset the SQL Path in our RPG program. But I couldn?t manage that : I?ve tried such programs lines :
1)
C/Exec Sql SET CURRENT PATH = *LIBL
C/End-Exec
2)
C/Exec Sql SET PATH = *LIBL
C/End-Exec
But neither of them seems to be working. SQLCOD 204 each time?
Does anyone have any idea to help me?

Pat

[Iaorana]

Re: Changing SQL PATH initiated in ODBC settings

Hello

I've just find the solution. Inserting SET OPTION NAMING = *SYS at the beginnig of my RPG program, and SQL will use the files of *LIBL

Pat

[B.Hauser]

Re: Changing SQL PATH initiated in ODBC settings

Just for information:
*SYSTEM Naming uses the library list for unqualified access.

*SQL Naming uses either a CURRENT SCHEMA (to find files/tables, views and indexes) or the SQL Path (to find Stored Procedures, Triggers and User Defined Functions).

With SQL Naming you only can get unqualfied access for files/tables or views only within a single schema/library. The CURRENT SCHEMA is either the library/schema with the same name as the user profile or is set by executing the SQL Command SET CURRENT SCHEMA. If you have to access files/tables or views located within several libraries/schemas, you either need to qualify your database objects (located in the other libraries/schemas) or create aliases for them.

As already pointed out SQL PATH is only used to find "programs" but not "data".

Birgitta

[Iaorana]

Re: Changing SQL PATH initiated in ODBC settings

Hello!

Thank you very much for your answer.

But I don't understand why you wrote
'As already pointed out SQL PATH is only used to find "programs" but not "data".'

I needed to find data, i. e. the files, and it worked by using system namings?

Pat

[Iaorana]

Re: Changing SQL PATH initiated in ODBC settings

It's working only when 'SQL Default Library' in ODBC settings is blank.
So now, 'As already pointed out SQL PATH is only used to find "programs" but not "data"' make sens...
Thanks

Pat