Four Js Development Tools Forum

Hi,
we currently work with an Informix DB (v. 11.2) but for a new customer we need to implement an oracle connection (so far we don't yet know the exact version but in so far it is a new setup it must be one one of the latest). We never implemented a setup other than Informix and we read some documents and try something but to no avail.
 
Our configuration is: AIX 7.1.0.0 and Genero BDL 2.50.

Are there someone with some experience on genero on oracle that can explain the setup and what that really matter change between ifx and ora in terms of code writing? 

I'd really appreciate that

Regards, Francesco

Hello Francesco,

Read https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagora_001.html

Genero 2.50 is an old version, you should consider upgrading to 3.20 or 4.01.

Supported Oracle server versions are 18, 19 and 21 (V12 will be de-supported by Oracle in July)

Seb

Hi Seb,
thanks for your reply.
Upgrade to 4 is on schedule for the incoming autumn but unfortunately we must setup this connection in a matter of days.
The oracle version is  19.3.0.0.0 they gave us a tnsnames.ora like that.

The setup at the link you provided apply for genero 2.50 too?

AME =
>     (DESCRIPTION =
>       (ADDRESS = (PROTOCOL = TCP)(HOST =
> foo.bar.corp)(PORT = 1521))
>       (CONNECT_DATA =
>         (SERVER = DEDICATED)
>         (SERVICE_NAME = ame.foo.bar)
>       )
>     )
>
> Utente: foobar, password uguale alla userid.
>
>

Yes basically it's the same setup with 2.50

tnsnames.ora is similar to Informix sqlhosts.

The YNS entry is "AME", so you must define the "source" connection param to this value:

dbi.database.mydbname.source = "AME"

For Oracle 19 (client), you can use the dbmora_18 ODI driver.

dbi.database.mydbname.driver = "dbmora_18"

Then:

CONNECT TO "mydbname" USER <oracle-user> USING <password?

...

But using Oracle instead of Informix is not just about setting up SQL connection parameters.

There are several compatibility issues to take care of, as described in the chapter
https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagora_001.html

I suggest that you contact your support to get more assistance.

Seb

Thanks Seb,
when we are done with the setup i'm going to post the solution just in case someone else stumble on the same problem

Hi Seb,
we follow your suggestions: we installed the client (v. 19.3.0),
setup ORACLE_HOME (client installation path),  LIBPATH ($LIBPATH:$ORACLE_HOME/lib),
ORACLE_SID was not important. Connection is establish with or without ORACLE_SID set.

and setting a FGLPROFILE (here though we used a different driver because under dbdrivers folder we didn't find a dbmora_18 file).
dbi.default.driver = "dbmoraB2x"
dbi.database.AME.source   = "ame"
dbi.database.AME.schema = "ame"
dbi.database.AME.username = "mceusr"
dbi.database.AME.password = "mceusr"


"ame" comes from this tnsnames.ora (host and service are dummy on purpose)

AME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = foo.bar.corp)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ame.foobar.corp)
    )
  )


sqlnet.ora on the other hand was like that:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)



then we connected with sqlplus and everything was ok.

now we got another problem because fgldbsch is unable to convert from some ora types to ifx (ie: NUMBER(22)) and it's impossible to compile 4gl
because if we use -ie some tables are skipped but if we do not use -ie fgldbsch exits with error.

Warning: Table "alimentazione_errata.qta" has not a valid datatype [NUMBER] - table is ignored.
Warning: Table "ame_fuori_formato.batch" has not a valid datatype [NUMBER] - table is ignored.
...

SQL: FETCH
 | 4gl source      : fgldbslib.4gl line=1657
 | sqlcode         : 0
 | curr driver     : ident='dbmoraB2x'
 | curr connection : ident='fgldbsch' (dbspec=[ame+driver='dbmoraB2x'])
 | sql cursor      : ident='cu3' (fglname='ctc_ora',module='fgldbslib')
 |   fgl stmt      : SELECT  C.TABLE_NAME,  C.COLUMN_NAME,  C.DATA_TYPE,  C.DATA_LENGTH,  C.CHAR_LENGTH,  C.DATA_PRECISION,  C.DATA_SCALE,  C.NULLABLE,  C.COL
UMN_ID FROM ALL_TAB_COLUMNS C WHERE C.TABLE_NAME NOT LIKE 'BIN$%'  AND UPPER(C.OWNER)='MCEUSR' ORDER BY C.TABLE_NAME, C.COLUMN_ID
 |   sql stmt      : SELECT  C.TABLE_NAME,  C.COLUMN_NAME,  C.DATA_TYPE,  C.DATA_LENGTH,  C.CHAR_LENGTH,  C.DATA_PRECISION,  C.DATA_SCALE,  C.NULLABLE,  C.COL
UMN_ID FROM ALL_TAB_COLUMNS C WHERE C.TABLE_NAME NOT LIKE 'BIN$%'  AND UPPER(C.OWNER)='MCEUSR' ORDER BY C.TABLE_NAME, C.COLUMN_ID
 | into(tmp): 9
 |  t:                  VARCHAR(200) f:04 v:'ALIMENTAZIONE_ERRATA'
 |  t:                  VARCHAR(200) f:04 v:'QTA'
 |  t:                  VARCHAR(100) f:04 v:'NUMBER'
 |  t:                       INTEGER f:04 v:'22'
 |  t:                       INTEGER f:04 v:'0'
 |  t:                       INTEGER f:04 v:''
 |  t:                       INTEGER f:04 v:''
 |  t:                       CHAR(1) f:04 v:'N'
 |  t:                       INTEGER f:04 v:'3'
 | Execution time  :   0 00:00:00.00138



Is there a way to force fgldbsch to translate numbers? we played with -cv option but so far to no avail.

Thanks Seb

FYI the source for fgldbsch can be found in FGLDIR/src/fgldb*.4gl (look for _fgldbsch_convert_datatype_ora in fgldbslib.4gl)

Interestingly the case for oraprec<32 and orascal is null, is not catered for.  I'd probably expect it to map to DECIMAL(oraprec,0).  If you needed to get out of a bind you could amend and recompile a copy of fgldbsch that catered for this combination.  You should raise a support call so that it gets investigated properly.

Reuben

Best way is to contact the support.

Provide the original CREATE TABLE statement with Oracle types.

I did the following test and could extract the table schema without patching fgldbsch:

(Server is Oracle 19c)

sf@toro:~/genero/devel/fgl/tests$ sqlplus orauser/fourjs@test1orne
...
SQL> create table tab1 ( pkey number(20,0), num_22 number(22), num_22_0 number(22,0) );   
 
Table created.
 
sf@toro:~/genero/devel/fgl/tests$ fgldbsch -db test1orne -dv dbmora -un orauser -up fourjs -tn tab1 -v
 
fgldbsch
----------------------------------------------------------------
 
Extracting database schema from 'test1orne'
  Database server type detected              : ORA
 
Warning: No table owner/schema specified: Using -up login 'orauser' as default.
 
Table: tab1
  Column   1: pkey                           NUMBER(20,0) => (52/8:BIGINT)
  Column   2: num_22                         NUMBER(22,0) => (5/5632:DECIMAL(22,0))
  Column   3: num_22_0                       NUMBER(22,0) => (5/5632:DECIMAL(22,0))
 
Found 1 table definition(s).
 

Seb

Hi Seb,
thanks for you reply.

We got hundreds of tables with this problem. here is the ddl of one:

  CREATE TABLE "MCEUSR"."CODCESTE"
   (    "IDCESTA" NUMBER,
        "STATO" NUMBER,
        "DTINST" DATE,
        "COD_USCITA" NUMBER(*,0),
        "FL_FF" VARCHAR2(1) DEFAULT 0,
        "BAIA" NUMBER
   ) SEGMENT CREATION IMMEDIATE
...
...


and here is just two lines of fgldbsch log which is full of warning like that on other table:
Table: codceste
  Column   1: idcesta                        NUMBER => (-1/0)
Warning: Table "codceste.idcesta" has not a valid datatype [NUMBER] - table is ignored.


we cannot tamper with ddl because is a remote customer db.
i tried to create a simple table with NUMBER type to but the outcome is the same.


Here's fgldbsch version info:

fgldbsch 2.50.16 build-2343.36
Genero database schema tool
Target a640610

Four Js*
Licensed Materials - Property of Four Js
(c) Copyright Four Js 1995, 2014. All Rights Reserved.
* Trademark of Four Js Development Tools Europe Ltd
  in the United States and elsewhere
 

and the command used:
fgldbsch -db ame -dv "dbmoraA2x" -un mceusr -up mceusr  -v -ie

and the all dbmora driver we got in $FGLDIR/dbdrivers:
-rwxr-xr-x    1 informix informix      89332 Oct 01 2014  dbmoraA2x.so
-rwxr-xr-x    1 informix informix      89276 Oct 01 2014  dbmoraB1x.so
-rwxr-xr-x    1 informix informix      89276 Oct 01 2014  dbmoraB2x.so

we tried each one of them but the result is the same. maybe they're old and must be upgraded?


to some extent the problem can be overcome not using definition like $tableName.$tableColumn and giving a generic type like INT, CHAR etc

Francesco

Seb, 

Check your output for column 2 ...

Column   2: num_22                         NUMBER(22,0) => (5/5632:DECIMAL(22,0))

... shouldn't that say NUMBER(22) ?

Reuben

Reuben,


Seems that when you create a NUMBER(22) column in Oracle, the schema tables return this a "NUMBER", with precision 22 and scale 0, as NUMBER(22,0).

So there is no problem in this case.

Seb

Francesco, (just answering emails and forum discussions, I has no access to Oracle server these days)

First I strongly suggest you to upgrade to Genero 3.20 or 4.01 to get latest bug fixes and enhancements.
Except critical / blocking bugs there will be no changes in 2.50, or 3.10

About the numeric types of the original tables you have to deal with, please contact the support so we can better help.

Seb

Francesco,

Please also read carefully

https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagora_030.html

Try to install Genero 3.20 or 4.01, and try again with fgldbsch and -cv option B at position 22 and 23.

fgldbsch -cx ora
...
  22 NUMBER                             <error>                            DECIMAL(32)                        
  23 NUMBER(p>32,s)                     <error>                            DECIMAL(32,s) 

The Oracle ODI driver dbmora* must match the Oracle client your are using.

Seb

Hi Everybody,
we succeded in installing Oracle client and in running our first application in development env. Now the same application deployed in production do not work and the error message is:

SQL: DATABASE
 | 4gl source      : OraOmega.4gl line=43
 | loading driver  : [/informix/fourjs/fgl25016/dbdrivers/dbmoraB2x]
 | Dynamic linker error: [Permission denied]...
Program stopped at 'OraOmega.4gl', line number 43.
SQL statement error number -6366 (0).
Could not load database driver dbmoraB2x. Set FGLSQLDEBUG to get more details.

(FGLSQLDEBUG is already set)

We double checked every environment variables from 4js to oracle client but nothing. the two oracle setup are identical. we are literally banging our heads against the wall once again :-) in unison but nothing happens!
we are sure 4js reach dbmoraB2x and even with chmod 777 on every folder level (actually permissions are u=rwx,og=rx) nothing change.
If we rename dbmoraB2x, error is different, something on the tone of not found.

Any idea? 
Thanks in advance.

fglrun 2.50.16 build-2343.36
Genero virtual machine
Target a640610


drwxr-xr-x   17 informix informix       4096 Jun 29 2015  fgl25016
drwxr-xr-x   14 informix informix       4096 Jun 29 2015  gas25031
drwxr-xr-x   10 informix informix       4096 Sep 30 2015  gre25018

active keys in fglprofile are:
dbi.default.driver = "dbmoraB2x"
dbi.database.AME.source   = "ame"
dbi.database.AME.schema = "ame"
dbi.database.AME.username = "mceusr"
dbi.database.AME.password = "mceusr"

Have you used and compared output of ldd (I think AIX equivalent is dump -H) ?  You may have everything setup correctly to find dbmoraB2x but is everything that dbmoraB2 depends upon in the right place ?

Also are you aware that AIX has some peculiarities with regards shared libraries http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_installation_017.html ?

Reuben

Hi Reuben,
I didnt know this ldd command, and luckily we got that in AIX too.
From the output we happened to know that there was a couple of lib*so under $ORACLE_HOME/lib without permission for group and others.
Fixed that now it seems to work like a charm.

I Don't know how to thank you!