PHP5 with Apache HTTP Server on windows

After PHP5 and Apache server are installed on the windows machine...

add the following towards the end of httpd.conf

#load the php main library 
Loadfile "C:/php-5.2.10-Win32/php5ts.dll"

#load the sapi so that apache can use php
LoadModule php5_module "C:/php-5.2.10-Win32/php5apache2_2.dll"

#set the php.ini location 
PHPIniDir "C:/php-5.2.10-Win32"

#Hook the php file extensions
AddHandler application/x-httpd-php .php
AddHandler application/x-httpd-php-source .phps

make a copy of php.ini-recommended and rename it to php.ini
restart the http server and test a php page.

Global Temporary Table & JDBC

Few things to keep in mind

1. if no connection caching is enable, data source only has

OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL("jdbc:oracle:thin:@IP:1521:ent10");
dataSource.setUser("asanga");
dataSource.setPassword("asa");

and if the global temporary table (GTT) is created with on commit preserve,
when the connection is closed con.close() the GTT is cleared and subsequent connections will not see any rows.

2. if connection caching is enabled and GTT is on commit preserve rows

Properties prop = new Properties();
prop.setProperty("MinLimit", "4"); 
prop.setProperty("MaxLimit", "10");
prop.setProperty("InitialLimit", "5"); 
dataSource.setConnectionCacheProperties(prop);
dataSource.setConnectionCachingEnabled(true);

even after closing the connection con.close() subsequent connection will see the rows inserted by the previous connections.

everything below applies to connection created with a pool, connection caching enabeld.

3. if GTT is on commit delete rows then better setAutoCommit(false) otherwise after insert no rows will be visible.

4. if GTT is on commit delete then committing connection will clear the table and others will not see any rows but will add IO

5. if GTT is on commit preserve then inserting rows within a PL/SQL function still makes rows visible for the subsequent connections

6. if GTT is on commit delete then inserting rows within a PL/SQL will only be visible within the PL/SQL block. The outside java connection will not see any rows and also any subsequent connections.


example codes

for on commit preserve

create global temporary table 
temp(a varchar2(10), b number) 
on commit preserve rows;

for on commit delete

create global temporary table 
temp(a varchar2(10), b number) 
on commit delete rows;

function

create or replace function proc return number as
rowscount number;
begin
   for i in 1 .. 100
   loop
     insert into temp values ('aaa '||i, i);
   end loop;
select count(*) into rowscount from temp;
return rowscount;
end;
/       

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;


public class RowSetTest {

    public static void main(String[] args) {

        new RowSetTest().test();
    }

    void test() {

        try {

            java.util.Properties prop = new java.util.Properties();
            prop.setProperty("MinLimit", "4"); // the cache size is 5 at least
            prop.setProperty("MaxLimit", "10");
            prop.setProperty("InitialLimit", "5"); // create


            OracleDataSource dataSource;
            dataSource = new OracleDataSource();
            dataSource.setURL("jdbc:oracle:thin:@192.168.0.110:1521:ent10");
            dataSource.setUser("asanga");
            dataSource.setPassword("asa");
            dataSource.setConnectionCacheProperties(prop);
            dataSource.setConnectionCachingEnabled(true);


            Connection conn = dataSource.getConnection();

            String fares = " BEGIN ? := proc() ;END; ";

            CallableStatement fares_call = conn.prepareCall(fares);

            fares_call.registerOutParameter(1, OracleTypes.INTEGER);
            fares_call.execute();


            System.out.println("count from plsql " + fares_call.getInt(1));

            PreparedStatement pr;

            System.out.println("befroe close ++++++++++++++++++++++++");
            pr = conn.prepareStatement("select count(*) from temp");
            ResultSet rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();
            conn.close();

            System.out.println("after close +++++++++++++++++++++");
            conn = dataSource.getConnection();
            pr = conn.prepareStatement("select count(*) from temp");
            rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();

            conn.close();

            System.out.println("after close +++++++++++++++++++++");
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            pr = conn.prepareStatement("select count(*) from temp");
            rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();
            conn.close();

            System.out.println("after close +++++++++++++++++++++");
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            pr = conn.prepareStatement("select count(*) from temp");
            rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();
            conn.close();


        } catch (Exception ex) {
            Logger.getLogger(RowSetTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

SQL Sinppet : Parent/Child tables

SQL to find out foreign key relationships between tables

select p.table_name as parent_table,
pc.column_name as parent_table_column,
C.Table_Name As Child_Table,
Cc.Column_Name As Child_Table_Column , 
C.R_Constraint_Name As Parent_Constraint_Name,
c.constraint_name as child_constraint_name
from user_constraints p, user_constraints c,
user_cons_columns pc, user_cons_columns cc
where p.constraint_name = c.r_constraint_name
and p.constraint_name = pc.constraint_name
and pc.position = cc.position
and c.constraint_name = cc.constraint_name
and c.table_name = 'table name';