From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | yazmin morales <yazmin(dot)morales(at)gmail(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Cursor in JDBC |
Date: | 2006-08-24 20:33:39 |
Message-ID: | FDF3EB49-7701-4BA2-BB2B-FB116C73C856@fastcrypt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Please contact the pljava folks, this is the jdbc list.
Dave
On 24-Aug-06, at 4:10 PM, yazmin morales wrote:
> Hello
>
> I have been trying to implement a cursor in a pljava using the
> JDBC, but I get an error when I run de function, which is the
> following:
> SPI function SPI_prepare failed with error code -5
> How can I solve this error? Here is the code that I am using:
>
> StringBuffer select = new StringBuffer();
> select.append("select distinct on (n.companyid,n.custid)
> n.companyid,n.custid,n.noteid,ssn,");
> select.append
> ("dateadded,notetotal,interest,fee1,notepayments,notetype,notepmt,");
> select.append
> ("metro2asc,n.notecage,notestatus,metro2sc,metro2cc,n.notebalance,note
> firstpmt,");
> select.append
> ("noteclosed,noteDLP,lastname,firstname,suffix,birthdate,homephone,met
> ro2cii,country,");
> select.append("address,city,state,zip,getDueAmount
> (n.noteid,"+ConstantsEngine.LEDGER+"),");
> select.append("getPmtHist(c.custid,''"+iDate+"'',''"+eDate
> +"''),f.amount,f.trantype ,a.postdate,f2.amount as
> woff,equifaxId,experianId,transunionId from ");
> select.append(viewCusts+" as c JOIN ");
> select.append(viewNotes+" as n USING (custid,companyid) LEFT OUTER
> JOIN ");
> select.append(viewFintrans+" as f ON (n.noteid =f.noteid and
> (credit="+ConstantsEngine.LEDGER+" or debit="+ConstantsEngine.LEDGER
> +") ");
> select.append("and (trantype=1 or trantype=-1) and date( f.postdate)
> >=date(''"+initDate.toSqlFull(company.getTZ())+"'') ");
> select.append("and date(f.postdate)<=date(''"+limitDate.toSqlFull
> (company.getTZ())+"'')) LEFT OUTER JOIN "+viewAgehist+" as a ");
> select.append("ON (n.noteid=a.noteid and a.notecage=''30 days'')");
> select.append(" LEFT OUTER JOIN "+viewFintrans+" as f2 ON (n.noteid
> =f2.noteid and f2.credit=10610 and f2.trantype=20)");
> select.append(" LEFT OUTER JOIN "+viewCompany+" as cp ON
> (cp.companyid =n.companyid)");
> select.append(" WHERE n.custid=c.custid ");
> select.append(" and ((notestatus=4 or notestatus=1 or date
> (noteclosed)>=date(''"+endDate.toSqlFull( company.getTZ())+"'')))");
> select.append(" and date(noteclosed)<=date(''"+limitDate.toSqlFull
> (company.getTZ())+"'') and ");
> select.append("date(notemade)<=date(''"+limitDate.toSqlFull
> ( company.getTZ())+"'') order by n.companyid,n.custid,postdate desc");
> Statement m_statement = DriverManager.getConnection
> ("jdbc:default:connection").createStatement();
> int result = m_statement.executeUpdate("DECLARE metro2cursor CURSOR
> FOR "+select.toString());
> ResultSet rs = m_statement.executeQuery("fetch all in metro2cursor");
> if(rs!=null){
> while(rs.next()){
> //get data
> String companyId = rs.getString("companyId");
> }
> }
> m_statement.executeUpdate("CLOSE metro2cursor");
> m_statement.close();
>
> Thanks for any help.
From | Date | Subject | |
---|---|---|---|
Next Message | Roland Walter | 2006-08-25 07:42:17 | Re: Exception in thread "main" java.lang.OutOfMemoryError: |
Previous Message | yazmin morales | 2006-08-24 20:10:59 | Cursor in JDBC |