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
#SQL#VisualCOBOLHi
Does this syntax help
url=jdbc:db2://test1:60000/testdbprod:currentSchema=myschema1;
currentSchema
Tony
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
#SQL#VisualCOBOLHello,
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="www.jboss.org/.../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
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
#SQL#VisualCOBOLHello,
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
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
#SQL#VisualCOBOLHi 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.
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
#SQL#VisualCOBOLI 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.
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
#SQL#VisualCOBOLThe 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="www.jboss.org/.../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
