any method to change schema programmatically?

[t4nu]

Hi,
While doing development my application connect to test schema, but when the application published it has to be connected to production schema.
Is there any method to change the schema easily?

For e.g. this is one of Database Resource I have:

Code: Select all

SELECT SPACTIDS SPID, SPADP11 SPDate, SPAN8 EmployeeID, SPPOS PositionID, d.HPDL01 PositionName, SPMCU DeptID, c.MCDL01 DeptName,
       SPP002 LocationID, b.DRDL01 LocationName, SPADP12 ViolationDate, SPTDAY ViolationTime, SPDL10 ViolationLocation,
       SPKY SPTypeID, SPBEGDATE ValidFrom, SPENDDATE ValidUntil,
       SPAN80 OfficerID, SP2COMMENTS Description, SPNAMEP CorrectionAction, SPHSIDN FailOnCorrection, SPISST FormStatus
  FROM TESTDTA.F598SPFM a
      LEFT JOIN (SELECT DRKY, DRDL01 FROM CRPCTL.F0005 WHERE DRSY = '06' AND DRRT = '02') b ON a.SPP002 = b.DRKY
      LEFT JOIN TESTDTA.F0006 c ON a.SPMCU = c.MCMCU
      LEFT JOIN TESTDTA.F08101 d ON a.SPPOS = d.HPPOS

TESTDTA is the test schema, if the application is published it should be changed to PRODDTA.

Thanks in advance for the help.

[kotinkarwak]

Just gone through a setup of a remote server on digitalocean and had during the implementation wondered how to juggle between development and production database servers.
Should I be using "test" in the tags or would it be best to implement a server similar in all aspects to what will be production but just note when I am using which?
I think this would be the line to use else you will find you have SQL statements dotted all over your application that will need a revisit every time you move from dev to prod.
So my learning from that exercise was to simply view the database commection as the "gateway" for me and the code in the application should just be as what it ought to be in production.

[Steve James]

Hi, we use our database connections as the gateway (as per your comment below) so we never need to make changes when rolling between DEV/TEST and PRODUCTION environments.

It is also possible to use environment variables to substitute at run time. See http://dev-docs.verj.io/ufs/doc/Environ ... iables.htm

Something like.

Code: Select all

select * from &&$ENV_DBENVIRON.TABLENAME

Where you have an environment variable called DBENVIRON which is TESTDTA or PRODDTA depending on the environment. The only thing is that Ebase/Tomcat needs to be restarted every time you create a new environment variable.

[t4nu]

Thank you very much for the pointer, Steve.
I can change the schema easily now.

Hi, we use our database connections as the gateway (as per your comment below) so we never need to make changes when rolling between DEV/TEST and PRODUCTION environments.

It is also possible to use environment variables to substitute at run time. See http://dev-docs.verj.io/ufs/doc/Environ ... iables.htm

Something like.

Code: Select all

select * from &&$ENV_DBENVIRON.TABLENAME

Where you have an environment variable called DBENVIRON which is TESTDTA or PRODDTA depending on the environment. The only thing is that Ebase/Tomcat needs to be restarted every time you create a new environment variable.