aspose file tools*
The moose likes JDBC and the fly likes SQL statement to java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL statement to java" Watch "SQL statement to java" New topic
Author

SQL statement to java

Kingsley Mullers
Ranch Hand

Joined: Jan 14, 2005
Posts: 48
i have the following SQL statement and have tested it on SQL plusand SQL navigator but i want to use the same statement on my JSP or Java, how do i convert this statement to a java code or do i save it as a procedure and call it on my code (how do i do that)
DECLARE
v_first_date varchar2(10);
v_last_date varchar2(10);
v_original_date varchar2(10) := '200401';
BEGIN
select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07',
substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06'
into v_first_date,v_last_date
from dual ;
dbms_output.put_line('v_first_date '||v_first_date);
dbms_output.put_line('v_last_date '||v_last_date);
END;
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Your procedure is anonomously defined - you need to create the procedure properly (as I think you are aware via "create or replace procedure foo ( ... )") and call it from Java via a CallableStatement.

That being said, your procedure seems to only manipulate a date format. You don't need to use any DB code to do this - particularaly since you don't retrieve any data from the DB. So why not just do this in Java?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Yes procedure is the answer
but creating procedure will not help because code here is using

"dbms_output.put_line" that is mean for sql console

I think this can be done in very simple way



and after executing this query value can be retrieve from resultset

like rs.getString("v_last_date")
[ March 04, 2005: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Kingsley Mullers
Ranch Hand

Joined: Jan 14, 2005
Posts: 48
this is the full code that i have written but i have a feeling there's a syntax error here since this is not executing at all. Can you please check where i've gone wrong...Many thanks

StringBuffer strBuff= new StringBuffer();
strBuff.append(" select CCP07_MSISDN, ");
strBuff.append(" CCP07_in_calls, CCP07_out_calls, ");
strBuff.append(" CCP07_in_seconds, CCP07_out_seconds, ");
strBuff.append(" CCP07_in_Cost, CCP07_out_Cost, ");
strBuff.append(" to_char(CCP07_date_modified,'yyyy-mm-dd'), ccp07_user_id ");
strBuff.append(" from so_cpp.tcp07_payphone_calls,so_cpp.tcp06_payphones ");
strBuff.append(" where CCP07_bill_period = ? ");
strBuff.append(" and ccp06_type_code = ? ");


String BillDate =("select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07' v_first_date,substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06' v_last_date from dual ");
rs.getString("v_first_date");
rs.getString ("v_last_date ");


if (billtype.equals(" AB ")) {
strBuff.append (" select ccp08_msisdn");
strBuff.append (" from so_cpp.tcp08_payphone_histories");
strBuff.append (" where ccp08_date_actioned <= to_date('v_first_date','yyyy-mm-dd'");
strBuff.append (" and nvl(ccp08_date_ceased,sysdate) < to_date('v_last_date','yyyy-mm-dd') ");
}
else if (billtype.equals(" ANB ")){
strBuff.append (" select ccp08_msisdn");
strBuff.append ("from so_cpp.tcp08_payphone_histories");
strBuff.append (" where ccp08_date_actioned <= to_date('v_first_date','yyyy-mm-dd')");
strBuff.append (" and nvl(ccp08_date_ceased,sysdate+1) > to_date('v_last_date','yyyy-mm-dd') ");
}

else if(billtype.equals("AA")){
strBuff.append ("select distinct ccp08_msisdn ");
strBuff.append (" from so_cpp.tcp06_payphones, so_cpp.tcp08_payphone_histories ");
strBuff.append ("where ccp06_msisdn = ccp08_msisdn");
strBuff.append (" and nvl (ccp08_date_ceased,sysdate+1)>sysdate ");

}

else if(billtype.equals("IB")){
strBuff.append (" select distinct ccp07_msisdn ");
strBuff.append (" from so_cpp.tcp07_payphone_calls, so_cpp.tcp06_payphones ");
strBuff.append (" where ccp06_msisdn=ccp07_msisdn ");
strBuff.append (" and nvl (ccp06_date_ceased,sysdate-1)< sysdate ");
}

String strSQL = strBuff.toString();

try
{
pstmt1 = conn.prepareStatement(strSQL);
pstmt1.setString(1,billperiod);
rs = pstmt1.executeQuery();
}
catch (Exception e)
{
logger.debug("Error on database unreachable statements"+ e);
System.out.println("Error on database unreachable statements"+ e);
}
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Mandla,

I have given some comments in your code, can you tell what is final query you want to make, because it seems you are making wrong query.

Thanks



[ March 14, 2005: Message edited by: Shailesh Chandra ]
Kingsley Mullers
Ranch Hand

Joined: Jan 14, 2005
Posts: 48
thanks for the response, i'm new in devepment hope you dont mind me throwing stupid question..

After executing this the statement strBuff, where i pass a value (200303) then i get back the two values (v_first_date and v_last_date) which i'm supposed to be retreiving from the BillDate, retreiving the 2 i need to pass them to the if else SQL statement.(is the code below correct...thanks

String BillDate =("select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07' v_first_date,substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06' v_last_date from dual ");
pstmt2 = conn.prepareStatement(BillDate);
rs = pstmt2.executeQuery();
rs.getString("v_first_date");
rs.getString ("v_last_date ");
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Mandla,

you need not to execute saperate query for getting date you can simply use same in your query.

1. you have a date 200303 ie yyyymm format
2. you are formating this date as 2003-02-07 and 2003-03-06 with help of dual
3. you are using same as v_first_date and v_last_date

instead of executing query on dual you can use directly to_date function of oracle.

try to execute query on sql plus then put them in code

make query like



and write your code as


[ March 14, 2005: Message edited by: Shailesh Chandra ]
Kingsley Mullers
Ranch Hand

Joined: Jan 14, 2005
Posts: 48
thanks for the response,
the v_original_date is equal to Bill_Period
strBuff.append("WHERE CCP07_BILL_PERIOD = ?"); which is then formatted.
how do i pass this original_date to the if else statement
(strBuff.append (v_original_date) do i also put a question mark here i.e " ?"



StringBuffer strBuff= new StringBuffer();
strBuff.append(" SELECT CCP07_MSISDN, ");
strBuff.append(" CCP07_IN_CALLS, CCP07_OUT_CALLS, ");
strBuff.append(" CCP07_IN_SECONDS, CCP07_OUT_SECONDS, ");
strBuff.append(" CCP07_IN_COST, CCP07_OUT_COST, ");
strBuff.append(" TO_CHAR(CCP07_DATE_MODIFIED,'YYYY-MM-DD'), CCP07_USER_ID ");
strBuff.append(" FROM SO_CPP.TCP07_PAYPHONE_CALLS,SO_CPP.TCP06_PAYPHONES ");
strBuff.append(" WHERE CCP07_BILL_PERIOD = ? ");
strBuff.append(" AND CCP06_TYPE_CODE = ? ");


if(billtype.equals(" AB ")) {
strBuff.append (" SELECT CCP08_MSISDN");
strBuff.append (" FROM SO_CPP.TCP08_PAYPHONE_HISTORIES, SO_CPP.TCP_06PAYPHONES");
strBuff.append (" WHERE CCP08_MSISDN =CCP06_MSISDN ");
strBuff.append (" AND CCP08_DATE_ACTIONED <= ADD_MONTHS(TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("07");
strBuff.append ("','YYYYMMDD')-1)");
strBuff.append (" AND NVL(CPP08_DATE_CEASED,SYSDATE) < TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("06");
strBuff.append ("','YYYYMMDD')");


}
else if (billtype.equals(" ANB ")){
strBuff.append (" SELECT CCP08_MSISDN");
strBuff.append (" FROM SO_CPP.TCP08_payphone_HISTORIES");
strBuff.append (" WHERE CCP08_DATE_ACTIONED <= ADD_MONTHS(TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("07");
strBuff.append ("','YYYYMMDD')-1)");
strBuff.append (" AND NVL(CPP08_DATE_CEASED,SYSDATE+1) > TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("06");
strBuff.append (" ','YYYYMMDD')");


}

else if(billtype.equals("AA")){
strBuff.append ("SELECT DISTINCT CCP08_MSISDN ");
strBuff.append (" FROM SO_CPP.TCP06_PAYPHONES, SO_CPP.TCP08_PAYPHONE_HISTORIES ");
strBuff.append ("WHERE CCP06_MSISDN = ccp08_msisdn");
strBuff.append (" AND NVL (CCP08_DATE_CEASED,SYSDATE+1)>SYSDATE ");

}

else if(billtype.equals("IB")){
strBuff.append (" SELECT DISTINCT CCP07_MSISDN ");
strBuff.append (" FROM SO_CPP.TCP07_PAYPHONE_CALLS, SO_CPP.TCP06_PAYPHONES ");
strBuff.append (" WHERE ccp06_MSISDN=CCP07_MSISDN ");
strBuff.append (" AND NVL (CCP06_DATE_CEASED,SYSDATE-1)< SYSDATE ");
}

String strSQL = strBuff.toString();

try
{
pstmt1 = conn.prepareStatement(strSQL);
pstmt1.setString(1,billperiod);
rs = pstmt1.executeQuery();
}
catch (Exception e)
{
logger.debug("Error on database unreachable statements"+ e);
System.out.println("Error on database unreachable statements"+ e);
}
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Mandla,

hope this will help you



thanks
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Mandla Mwelase:


String strSQL = strBuff.toString();



Mandla,

Still you would face hard time to execute query in java. because you are adding to select sql in you StringBuffer. it will work fine IFF no if condition is satisifed in your code.

thanks
 
Don't get me started about those stupid light bulbs.
 
subject: SQL statement to java