Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: Postgres DB

Re: Postgres DB

From
Kris Jurka
Date:

On Mon, 26 Apr 2004, Sumita Biswas wrote:

> Thanks for the response. You are right, CAR is our application. We have
> included the .jar file(pg73jdbc3.jar) that was bundled with the Postgres
> 7.3.4 database. But the issue here is that it does not support a
> functionality that is supported by Postgres 7.3.4 database. Is it
> possible that the jar file would not support some functionality that the
> database supports?

Sure, it takes a while for JDBC developers to catch up with new backend
functionality.

> IN that case can we procure the .jar file which
> supports the functionality without licensing formalities?
>

The JDBC driver is distributed under the same license as postgresql and
downloads from http://jdbc.postgresql.org/download.html are encumbered
with no more license restrictions than the jar file distributed with the
7.3.4 server.

Kris Jurka

Re: Postgres DB

From
"Sumita Biswas"
Date:
Kris,

Does that mean I can download and use the pg74jdbc3.jar that comes with
Postgres 7.4 without any licensing formality.
Is there a contact number where I can call you up?

That would be really appreciated.

Thanks,
Sumita

-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Monday, April 26, 2004 9:26 PM
To: Sumita Biswas
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Postgres DB




On Mon, 26 Apr 2004, Sumita Biswas wrote:

> Thanks for the response. You are right, CAR is our application. We
> have included the .jar file() that was bundled with the
> Postgres 7.3.4 database. But the issue here is that it does not
> support a functionality that is supported by Postgres 7.3.4 database.
> Is it possible that the jar file would not support some functionality
> that the database supports?

Sure, it takes a while for JDBC developers to catch up with new backend
functionality.

> IN that case can we procure the .jar file which
> supports the functionality without licensing formalities?
>

The JDBC driver is distributed under the same license as postgresql and
downloads from http://jdbc.postgresql.org/download.html are encumbered
with no more license restrictions than the jar file distributed with the

7.3.4 server.

Kris Jurka

Date addition using Interval

From
"Sumita Biswas"
Date:
Hi Experts,

I try to write the following in my Function and it works:

ld_FromDateTemp := ld_FromDate + ''5 DAYS''::interval;

But when I have to pass a parameter in the NUMBER of days(instead of 5)
like 'li_NoOfDays'

ld_FromDateTemp := ld_FromDate +  ''li_NoOfDays DAYS''::interval;

Does not work. Error as follows:
------------------------------------------------------------------------
--------
WARNING:  Error occurred while executing PL/pgSQL function
proc_dailyutilization
WARNING:  line 41 at assignment
ERROR:  Bad interval external representation 'li_NoOfDays DAYS'
------------------------------------------------------------------------
--------
If I write this way:
ld_FromDateTemp := ld_FromDate +  li_NoOfDays ''DAYS''::interval;

Following Error:
------------------------------------------------------------------------
--------
WARNING:  Error occurred while executing PL/pgSQL function
proc_dailyutilization
WARNING:  line 41 at assignment
ERROR:  parser: parse error at or near "'DAYS'" at character 21
------------------------------------------------------------------------
--------


I am sure there is a way of doing it. Can anyone point out where I am
going wrong?

Thanks in Advance.

Regards,
Sumita

Re: Date addition using Interval

From
Stephan Szabo
Date:
On Mon, 3 May 2004, Sumita Biswas wrote:

> Hi Experts,
>
> I try to write the following in my Function and it works:
>
> ld_FromDateTemp := ld_FromDate + ''5 DAYS''::interval;
>
> But when I have to pass a parameter in the NUMBER of days(instead of 5)
> like 'li_NoOfDays'
>
> ld_FromDateTemp := ld_FromDate +  ''li_NoOfDays DAYS''::interval;

This is a string containing 'li_NoOfDays DAYS' not
'<the value of li_NoOfDays> DAYS' so that's not going to work.

> If I write this way:
> ld_FromDateTemp := ld_FromDate +  li_NoOfDays ''DAYS''::interval;

I think you probably want:
li_NoOfDays * interval '1 day'

Re: Date addition using Interval

From
Richard Huxton
Date:
Sumita Biswas wrote:
> Hi Experts,
>
> I try to write the following in my Function and it works:
>
> ld_FromDateTemp := ld_FromDate + ''5 DAYS''::interval;
>
> But when I have to pass a parameter in the NUMBER of days(instead of 5)
> like 'li_NoOfDays'
>
> ld_FromDateTemp := ld_FromDate +  ''li_NoOfDays DAYS''::interval;

You want ... + (li_NoOfDays || '' DAYS'')::interval;

plpgsql doesn't interpolate variables like perl/php do.

--
   Richard Huxton
   Archonet Ltd

Re: Date addition using Interval

From
"Sumita Biswas"
Date:
Thanks Richard,

It works fine now.

Regards,
Sumita

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, May 05, 2004 9:18 AM
To: Sumita Biswas
Cc: 'Kris Jurka'; 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Date addition using Interval


Sumita Biswas wrote:
> Hi Experts,
>
> I try to write the following in my Function and it works:
>
> ld_FromDateTemp := ld_FromDate + ''5 DAYS''::interval;
>
> But when I have to pass a parameter in the NUMBER of days(instead of
> 5) like 'li_NoOfDays'
>
> ld_FromDateTemp := ld_FromDate +  ''li_NoOfDays DAYS''::interval;

You want ... + (li_NoOfDays || '' DAYS'')::interval;

plpgsql doesn't interpolate variables like perl/php do.

--
   Richard Huxton
   Archonet Ltd

Function with RETURN TYPE RECORD Called From JAVA

From
"Sumita Biswas"
Date:
Hi All,

I have a Function that returns a variable of Type RECORD.
PFA the .sql file in which the Function is written.

When I execute this Function from JAVA and try to get the Return
Variable in a ResultSet object I get the following Error:

Exception in thread "main" java.lang.ClassCastException
        at com.cisco.ccm.car.general.Test.testStoredProc(Test.java:119)

Here is the JAVA Code that I am using:

---------------------------------------------------------------
    CARConnector objCARConn = new CARConnector();
    CallableStatement objCallStmt = null;

    objCallStmt = objCARConn.prepareCall("{ ? = call
Proc_ConferenceSummary(?,?,?,?,?) }");
    objCallStmt.registerOutParameter(1, 12);
    // '12/1/2003','1/23/2004',1,1,0,5001
    //'3/5/2004','3/5/2004',1,1,5001

    objCallStmt.setString(2,"3/5/2004");
    objCallStmt.setString(3,"5/5/2004");
    //objCallStmt.setString(3,"3/5/2004");
    objCallStmt.setInt(4,1);
    objCallStmt.setInt(5,1);
    objCallStmt.setInt(6,5001);
    objCallStmt.execute();
    ResultSet rs = (ResultSet)objCallStmt.getObject(1);//THIS IS
WHERE I GET THE CLASSCASTEXCEPTION
---------------------------------------------------------------

Please let me know in case I am doing something wrong, or there is a
different way of doing things.

Regards,
Sumita

Re: Function with RETURN TYPE RECORD Called From JAVA

From
"Carl E. McMillin"
Date:
I'm fairly sure that postgres does not support "out" parameters:

    objCallStmt.registerOutParameter(1, 12);

Isn't allowed.

Since you only have one value coming out of the function, you could use
regular Statement or PreparedStatement client-side and rewrite your
server-side function so it returns a value thru RETURNS clause.  I'm making
assumption that you are using PL/pgsql (only language I'm familiar with).

The second is that you probably want to call the executeQuery method on the
Statement/PreparedStatement and walk through the result-set (per jdocs) it
returns.

Carl <|};-)>



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sumita Biswas
Sent: Sunday, May 16, 2004 7:10 PM
To: pgsql-general@postgresql.org
Cc: 'Kris Jurka'; 'Tom Lane'; 'Richard Huxton'
Subject: [GENERAL] Function with RETURN TYPE RECORD Called From JAVA


Hi All,

I have a Function that returns a variable of Type RECORD.
PFA the .sql file in which the Function is written.

When I execute this Function from JAVA and try to get the Return Variable in
a ResultSet object I get the following Error:

Exception in thread "main" java.lang.ClassCastException
        at com.cisco.ccm.car.general.Test.testStoredProc(Test.java:119)

Here is the JAVA Code that I am using:

---------------------------------------------------------------
    CARConnector objCARConn = new CARConnector();
    CallableStatement objCallStmt = null;

    objCallStmt = objCARConn.prepareCall("{ ? = call
Proc_ConferenceSummary(?,?,?,?,?) }");
    objCallStmt.registerOutParameter(1, 12);
    // '12/1/2003','1/23/2004',1,1,0,5001
    //'3/5/2004','3/5/2004',1,1,5001

    objCallStmt.setString(2,"3/5/2004");
    objCallStmt.setString(3,"5/5/2004");
    //objCallStmt.setString(3,"3/5/2004");
    objCallStmt.setInt(4,1);
    objCallStmt.setInt(5,1);
    objCallStmt.setInt(6,5001);
    objCallStmt.execute();
    ResultSet rs = (ResultSet)objCallStmt.getObject(1);//THIS IS WHERE I
GET THE CLASSCASTEXCEPTION
---------------------------------------------------------------

Please let me know in case I am doing something wrong, or there is a
different way of doing things.

Regards,
Sumita


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

pg_dump when used with chroot

From
"Sumita Biswas"
Date:
Hi All,

I am facing an issue with pg_dump, please let me know if there is a
solution to this.

I have a database called 'car' on one partition , on which I have the
Postgres Server running.
When I run a script from another partition on the same machine, which
does a pg_dump, the pg_dump is unable to find the car database.
The reason here might be the pg_dump command is run as chroot and chroot
is /partB.
The car database is on /.
So the pg_dump command tries to find a car database on /partB, which
does not exist.

Is there a way by which I can run the pg_dump from a different root, and
make it backup a database on a different root?
Regards,
Sumita