Обсуждение: Cursor in JDBC
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,notefirstpmt,");
select.append("noteclosed,noteDLP,lastname,firstname,suffix,birthdate,homephone,metro2cii,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.
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,notefirstpmt,");
select.append("noteclosed,noteDLP,lastname,firstname,suffix,birthdate,homephone,metro2cii,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.
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.