Received: from mailhost.onramp.net (mailhost.onramp.net [199.1.11.3])
	by hub.org (8.9.2/8.9.1) with ESMTP id PAA59704
	for <pgsql-interfaces@postgreSQL.org>;
	Thu, 11 Feb 1999 15:31:29 -0500 (EST)
	(envelope-from jbarrett@onramp.net)
Received: from devsys (ppp21-26.dllstx.onramp.net [206.50.202.26])
	by mailhost.onramp.net (8.8.7/8.8.7) with SMTP id OAA20676
	for <pgsql-interfaces@postgreSQL.org>;
	Thu, 11 Feb 1999 14:31:24 -0600 (CST)
Message-ID: <005501be55fd$7b240e80$1aca32ce@devsys.iplay.net>
From: "John Barrett" <jbarrett@onramp.net>
To: <pgsql-interfaces@postgreSQL.org>
Subject: Re: [INTERFACES] JDBC: DatabaseMetaData and getPrimaryKeys
Date: Thu, 11 Feb 1999 14:31:15 -0600
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_0052_01BE55CB.2E68E160"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.3110.1
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3

This is a multi-part message in MIME format.

------=_NextPart_000_0052_01BE55CB.2E68E160
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I've attached a quick and dirty little application in Java I use to transfer
data from one server to another
It uses the getPrimaryKeys call and works nicely... please excuse the lack
of documentation/comments :)))))

Might help ya to see a working app that uses the method.....

-----Original Message-----
From: Alejandro Alberola Arias <alberola@bio.es>
To: pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgreSQL.org>
Date: Thursday, February 11, 1999 1:39 PM
Subject: [INTERFACES] JDBC: DatabaseMetaData and getPrimaryKeys


>Hi,
>
>I am using PostgreSQL 6.4 on a Linux box and its JDBC driver.
>
>The problem is that the getPrimaryKeys() method of the
>DatabaseMetaData class doesn't work for me.
>
>Any ideas ?
>
>Thanks.
>
>Alejandro Alberola
>alberola@bio.es
>
>
>
>

------=_NextPart_000_0052_01BE55CB.2E68E160
Content-Type: application/octet-stream;
	name="DBXfer.java"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="DBXfer.java"

import java.sql.*;
import java.util.Vector;

class DBLogin
{
	public	String	username;
	public	String	password;
	public	String	connurl;
=09
	public DBLogin(String u, String p, String c)
	{
		username =3D u;
		password =3D p;
		connurl =3D c;
	}
}

class DBField
{
	public	int	fnum;
	public	int	ftyp;
	public	int	fsiz;
	public	int	fdig;
	public	String	fnam;
	public	String	fdef;
=09
	public DBField(ResultSet res) throws SQLException
	{
		fnum =3D res.getInt(17);
		ftyp =3D res.getInt(5);
		fsiz =3D res.getInt(7);
		fdig =3D res.getInt(9);
		ftyp =3D res.getInt(5);
		fnam =3D res.getString(4).toLowerCase();
		fdef =3D res.getString(6);
=09
		if (fdef.equals("VARCHAR2")) {
			fdef =3D "VARCHAR";
		}
	=09
		if (fdef.equals("DATE")) {
			fdef =3D "DATETIME";
		}
	=09
		if (fdef.equals("VARCHAR")) {
			fdef =3D fdef + "(" + fsiz + ")";
		}
		if (fdef.equals("NUMBER")) {
			if (fdig =3D=3D 0) {
				// integer
				fdef =3D "int4";
			} else {
				if (fdig =3D=3D 2) {
					fdef =3D "money";
				} else {
					fdef =3D "decimal(" + fsiz + "," + fdig + ")";
				}
			}
		}
		fdef =3D fdef.toLowerCase();
	}
=09
	public String toString()
	{
		return fnam + " " + fdef;
	}
}
		=09
class DBXfer
{
	Connection	srcConn;
	Connection	dstConn;
	DBLogin		srcLogin;
	DBLogin		dstLogin;

	public static void main(String argv[])
	{
		try {
			Class.forName ("oracle.jdbc.driver.OracleDriver");
			DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
=09
			Class.forName ("postgresql.Driver");
			DriverManager.registerDriver (new postgresql.Driver());
		} catch (Exception e) {
			System.out.println("Cound Not Register Drivers");
			return;
		}

		DBLogin	srcLogin =3D new =
DBLogin("user","pass","jdbc:oracle:thin:@<host>:1521:ORCL");
		DBLogin	dstLogin =3D new =
DBLogin("user","pass","jdbc:postgresql://<host>/<db>");
		DBXfer dbx =3D new DBXfer(srcLogin,dstLogin);
	=09
		dbx.transfer(null,"schema","table");
	}
=09
	public DBXfer(DBLogin src, DBLogin dst)
	{
		srcLogin =3D src;
		dstLogin =3D dst;
	=09
		try {
			srcConn =3D =
DriverManager.getConnection(src.connurl,src.username,src.password);
		} catch (Exception e) {
			System.out.println("Could not open source connection: " + e);
			return;
		}
		try {
			dstConn =3D =
DriverManager.getConnection(dst.connurl,dst.username,dst.password);
		} catch (Exception e) {
			System.out.println("Could not open destination connection: " + e);
			srcConn =3D null;
			return;
		}
	}

	public void transfer(String catalog, String schema, String table)
	{
		ResultSet	res;
		String		srcsql;
		String		dstsql;
		String		pkey[];
		int		pmax;
		DBField		fdat[];
		int		fmax;
		int		i;
		String		com;

		srcsql =3D "create table " + table.toLowerCase() + " ( ";
		dstsql =3D "insert into " + table.toLowerCase() + " ( ";
		com =3D "";
		pmax =3D 0;
		pkey =3D new String[16];
		fmax =3D 0;
		fdat =3D new DBField[100];

		try {
			DatabaseMetaData dmd =3D srcConn.getMetaData();
		=09
			res =3D dmd.getColumns(catalog,schema,table,"%");
			while (res.next()) {
				DBField	tmp =3D new DBField(res);
				fdat[tmp.fnum] =3D tmp;
				if (tmp.fnum > fmax) {
					fmax =3D tmp.fnum;
				}
			}
		=09
			for (i=3D1;i<fmax+1;i++) {
				srcsql =3D srcsql + com + fdat[i].toString();
				dstsql =3D dstsql + com + fdat[i].fnam;
				com =3D ", ";
			}

			res =3D dmd.getPrimaryKeys(catalog,schema,table);
			while (res.next()) {
				//System.out.println("Primary("+res.getString(5)+"): " + =
res.getString(4));
				pkey[res.getInt(5)] =3D res.getString(4);
				if (res.getInt(5) > pmax) {
					pmax =3D res.getInt(5);
				}
			}
			if (pmax > 0) { // got a primary key
				srcsql =3D srcsql + com + "primary key ( ";
				com =3D "";
				for (i=3D1; i<pmax+1;i++) {
					srcsql =3D srcsql + com + pkey[i].toLowerCase();;
					com =3D ", ";
				}
				srcsql =3D srcsql + " )";
			}

			srcsql =3D srcsql + " )";
			System.out.println(srcsql);
		=09
			// finish building destination insert statement
			dstsql =3D dstsql + " ) values ( ";
			com =3D "";
			for (i=3D1;i<fmax+1;i++) {
				dstsql =3D dstsql + com + "?";
				com =3D ", ";
			}
			dstsql =3D dstsql + " )";
			System.out.println(dstsql);
					=09
			// we got everything we need.... lets do something with it !!
			Statement dstcmd =3D dstConn.createStatement();
		=09
			// drop the table if it exists
			try {
				dstcmd.executeUpdate("drop table " + table);
			} catch (Exception e) {}
		=09
			// create table using the definition we just assembled
			System.out.println("creating table");
			System.out.flush();
			dstcmd.executeUpdate(srcsql);
		=09
			// we got a table.... get the data
			Statement srccmd =3D srcConn.createStatement();
			System.out.println("accessing source data");
			System.out.flush();
			res =3D srccmd.executeQuery("select * from " + table);
		=09
			// setup to store data to new table
			System.out.println("building insert statement");
			System.out.flush();
			PreparedStatement dstins =3D dstConn.prepareStatement(dstsql);
		=09
			// move that data !!
			int cnt =3D 0;
			String fld;
			while (res.next()) {
				System.out.println("=3D=3D=3D=3D=3D=3D=3D=3D Record: " + (++cnt));
				System.out.flush();
				for (i=3D1;i<fmax+1;i++) {
					fld =3D res.getString(i);
					if (fld =3D=3D null) {
						System.out.println(i + "\t" + fdat[i].ftyp + "\t" + fdat[i].fdig + =
"\t" + null);
					} else {
						System.out.println(i + "\t" + fdat[i].ftyp + "\t" + fdat[i].fdig + =
"\t" + fld.toString());
					}
					System.out.flush();
					if (fld =3D=3D null) {
						dstins.setNull(i,fdat[i].ftyp);
					} else {
						dstins.setString(i,fld);
					}
				}
				dstins.executeUpdate();
			}
		=09
		} catch (Exception e) {
			System.out.println("Exception: " + e);
		}
	}
}
------=_NextPart_000_0052_01BE55CB.2E68E160--