Highlighted
Absent Member.
Absent Member.
3615 views

sql default schema

Jump to solution

Hello,

when using embedded SQL in JVM-Cobol I do have the problem that most of the existing Cobolsources (> 8000) do not have the "FullQualified"  Tablename in the FROM Clause of their SQL-Statements.

With "native" Cobol it takes the username as the default Schema Qualifier. And if this is not sufficent it is possible to set QUALIFIER (as an SQL-Compiler Option).

This is not working with managed Cobol anymore. 

I do want to avoid the need to change all SQL-Statements to fully qualify the tablename. 

Does anybody have the same problem and if yes .... maybe a solution ?

Kind regards

Michael

Tags (2)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

RE: sql default schema

Jump to solution

The Schema specified in the Datasource URL is case sensitiv doing

public void setUp() throws Exception {
Class.forName("com.ibm.db2.jcc.DB2Driver");
connection = DriverManager.getConnection(
"jdbc:db2://dbserver:50003/vvst1:currentSchema=TEST;", "user",
"passwordt");
}

does the job. This is also valid for the JBOSS-Datasource. 

<?xml version="1.0" encoding="UTF-8"?>
<datasources xmlns="http://www.jboss.org/ironjacamar/schema">

<datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4"
pool-name="java:jboss/jdbc/DB2LV1871_TYPE4" enabled="true" use-java-context="true" use-ccm="false">

<connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1</connection-url>


<connection-property name="currentSchema">TEST</connection-property>
<connection-property name="dateFormat">3</connection-property>
<driver>com.ibm.db2.jcc</driver>
<security>
<user-name>test</user-name>
<password>tset</password>
</security>

</datasource>

</datasources>

Does the job. Note "TEST" instead of "test". Thanks to Kim Hoskin from MF for helping me on that issue.

Michael

View solution in original post

0 Likes
6 Replies
Highlighted
Micro Focus Frequent Contributor
Micro Focus Frequent Contributor

RE: sql default schema

Jump to solution

Hi

Does this syntax help

url=jdbc:db2://test1:60000/testdbprod:currentSchema=myschema1;

currentSchema

Tony

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: sql default schema

Jump to solution

Hello,

I have checked your proposal. The database is now specified as a JBOSS Datasource like this :

<?xml version="1.0" encoding="UTF-8"?>

<datasources xmlns="http://www.jboss.org/ironjacamar/schema">

    <datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4"
        pool-name="java:jboss/jdbc/DB2LV1871_TYPE4" enabled="true" use-java-context="true" use-ccm="false">

        <connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1:currentSchema=test;</connection-url>

        <!-- this must me commented out. Otherwiese youl receive "Property cannot be overwritten by url" (Which is plausible)

        <connection-property name="currentSchema">test</connection-property>

        It is not working with this setting either -->


        <connection-property name="dateFormat">3</connection-property>
        <driver>com.ibm.db2.jcc</driver>
        <security>
            <user-name>XXXX</user-name>
            <password>XXXX</password>
        </security>

    </datasource>

</datasources>

Even with the Schema as a Property in the URL the SQL-Statements in Cobol are returning "-204" (Table or view does not exist).

I do have to hardcode in Cobol :

select bla

from test.myfinetable;

using just

select bla

from myfinetable;

Results in error code "-204"

Kind regards

Michael

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: sql default schema

Jump to solution

Hello,

for preprocessed programs I could imagine compiler options like this :

SQL(DBMAN=JDBC) SQL(NOAUTOCOMMIT) SQL(CHECK=true) SQL(DB=jdbc:db2://zzzzz.muc.xxxxx.de:50003/vvsk1) SQL(PASS=xxxx.xxx) SQL(TARGETDB=DB2) 

SQL(QUALIFIER=test)

The DB2 Option QUALIFIER is the point. This sets the "default" schema name for Statements that do not include the fully qualified Table Name.

Kind regards

Michael

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: sql default schema

Jump to solution

Hi Michael,

I spoke to development regarding this and for managed JVM code the connection properties are not controlled by OpenESQL but need to be set within the connection definition itself.

Using ‘schemaName=x;’ in the connection URL works with Simple-JNDI data sources, We have tested this configuration in-house.

Looking at the JBoss AS 7 docs it looks as though JBoss data sources have a very restricted set of properties that you can set, so this may not be possible as a simple ‘pass through’ property.

We did notice a “new-connection-sql”  tag in www.ironjacamar.org/.../datasources_1_0.xsd , where you can pass off commands to the DBMS vendor before giving the connection to the application.

That may help, if this cannot be set-up as a connection attribute/property (as you are trying to do now)

Question: have you tried to use this same connection from a Java application instead of COBOL?

If the same problem occurred then that would indicate that this was a JBoss problem instead of an OpenESQL issue.

We would then recommend that you contact the folks at JBoss to see what they recommend.

Thanks.

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: sql default schema

Jump to solution

I have tested this with pure Java using the following Connection. Same Results. I do have to specify test.<tablename> in Cobol.

public void setUp() throws Exception {

Class.forName("com.ibm.db2.jcc.DB2Driver");
connection = DriverManager.getConnection(
"jdbc:db2://dbserver:50003/vvst1:currentSchema=test;", "user",
"passwordt");

}

Can you provide me with your test sources. To perform some further tests.

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: sql default schema

Jump to solution

The Schema specified in the Datasource URL is case sensitiv doing

public void setUp() throws Exception {
Class.forName("com.ibm.db2.jcc.DB2Driver");
connection = DriverManager.getConnection(
"jdbc:db2://dbserver:50003/vvst1:currentSchema=TEST;", "user",
"passwordt");
}

does the job. This is also valid for the JBOSS-Datasource. 

<?xml version="1.0" encoding="UTF-8"?>
<datasources xmlns="http://www.jboss.org/ironjacamar/schema">

<datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4"
pool-name="java:jboss/jdbc/DB2LV1871_TYPE4" enabled="true" use-java-context="true" use-ccm="false">

<connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1</connection-url>


<connection-property name="currentSchema">TEST</connection-property>
<connection-property name="dateFormat">3</connection-property>
<driver>com.ibm.db2.jcc</driver>
<security>
<user-name>test</user-name>
<password>tset</password>
</security>

</datasource>

</datasources>

Does the job. Note "TEST" instead of "test". Thanks to Kim Hoskin from MF for helping me on that issue.

Michael

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.