Difference between revisions of "NexJ Express Connecting to PostgreSQL"

From CDOT Wiki
Jump to: navigation, search
(→‎- Modifications)
Line 29: Line 29:
 
</source>
 
</source>
 
: After all properties are loaded, it creates the connection through Connection Factory files.
 
: After all properties are loaded, it creates the connection through Connection Factory files.
−
 
−
===- Modifications===
 
−
{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
 
−
!align="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| '''Changes Done to MySQL adapter'''
 
−
|-
 
−
|
 
−
: When a connection is first established, this initial SQL statement should execute in MySQL, which is not the case for PostgreSQL, so it was commented out:
 
−
::
 
−
<source lang=java>
 
−
/* MySQLAdapter.java */
 
−
 
−
public String getInitialSQL()
 
−
  {
 
−
      StringBuffer buf = new StringBuffer();
 
−
 
−
      buf.append("set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')"); // allow using doublequote when quoting column names in "CREATE TABLE" statements
 
−
      buf.append(";set optimizer_search_depth = 0"); // let DB automatically decide on how long it takes to examine plans, improves long planning sessions
 
−
      buf.append(";set max_sort_length = ").append(Math.max(MAX_VARCHAR_PRECISION, MAX_VARBINARY_PRECISION)); // set TEXT/BLOB minimum sorting length to be same as cutoff between varchar/text
 
−
      return buf.toString();
 
−
  }
 
−
</source>
 
−
: Also the same line should be commented out in postgresql_create.sql script, which is being used to create tables in the database 'test':
 
−
::
 
−
<source lang=java>
 
−
/* nexj/core/persistence/sql/etc/postgresql_create.sql */
 
−
set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
 
−
</source>
 
−
: After activating the connection, the database is locked.
 
−
: In MySQL, the storage engine is set to a transactional safe engine such as  [http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#MySQL:InnoDB InnoDB], whereas PostgreSQL has a single built in engine. So, changes needed to be done to this script:
 
−
::
 
−
<source lang=java>
 
−
create table test.Account(
 
−
    id binary(16) not null, contactId binary(16) not null, account Type varchar(16) character set utf8 not null, funds double null,
 
−
    constraint Account_PK primary key(id)
 
−
)engine=innoDB;
 
−
</source>
 
−
 
−
|}
 

Revision as of 22:00, 9 December 2010

PostgreSQL Adapter for NexJ - Create Test Environment


1- Setup the postgresql-connection

For testing the connection, the 'AllTests.java' is being used.

Installed the JDBC3 Postgresql Driver, Version 9.0-801 and added it to the libraries in JDK
The driver class which is being used for NexJ Express Model is: org.postgresql.xa.PGXADataSource
The driver is loaded from RelationalDatabaseFragment.java and RelationalDatabase.java
Metadata is loaded from the particular driver 
   /* MySQLAdapterTest.java */
      try
      {
         s_metadata = loadMetadata(ADAPTER_NAME);
      }

   /* SQLDataTest.java */
   protected static Metadata loadMetadata(String sAdapter)
   {
      Properties props = SysUtil.getConfigProperties();  // sets DEFAULT_CONFIG_URL = '/' + NAMESPACE + "/default.config"
      props = new Properties(props);
      props.setProperty(XMLMetadataLoader.CONNECTIONS_URL_PROPERTY, "/nexj/" + sAdapter.toLowerCase(Locale.ENGLISH) + ".connections");  // sets the properties to postgresql.connection
After all properties are loaded, it creates the connection through Connection Factory files.
Retrieved from "https://wiki.cdot.senecapolytechnic.ca/w/index.php?title=NexJ_Express_Connecting_to_PostgreSQL&oldid=52244"