nilesh Katakkar

Ranch Hand
+ Follow
since Oct 27, 2004
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by nilesh Katakkar

Also

String funct = "{ ? = ci_publish_client_data(?) }";

Should be

String funct = "{ call ? = ci_publish_client_data(?) }";
Whats the Java version you are using ?

ojdbc14.jar is used with Java 1.4.x ? For older versions you will still need to go to classes12.jar
So lets say I have a correct JB specification adhering class, are you saying that I don't need mapping file ? If hibernate was smart enough to figure out what properties I "have" in mind to persist, it wouldn't ask me for a mapping file. No one can do that. Now if it's asking me for a mapping file, then it better be looking at it, and decide what to do exactly based on that NOT on what mathods I have in my class.

Whats the purpose of providing Mapping File ? Well.. if I understand it correctly, it's so that hibernate can manage persistance for your class and generate SQL statements at run time (i.e dynamic - insert/update = true) Now, hibernate docs say that your persistant class can be any Plain Old Java Class. You can write it as per JB specification , or just use existing normal java class AND map it through mapping file.If mapping file says string it better be looking for string. What should matter if I have isXXX method for same parameter or not.
Agreed JB specification go by naming conventions. And it says that if a property is boolean then method name has to be isXXX. I may be wrong, but does it also mean that if there's a method isXXX, it automatically means that there's a corresponding boolean property ? Does JB specs say that I cannot have any isXXX method in my class for some string parameter along with get / set for my convenience to avoid certain redundant operation? What if my property is derived from certain state variables ?
Hibernate never ever says in the doc that every method in you JB or Java class has to map to persistent parameter, does it ? Parameter map to a set/get method. So when I mention parameter as string why should it bother to it that I have isXXX method? ( please correct me if I am wrong. I am pretty new to hibernate. )If mapping file is required and it says String.. try explaning why hibernate still looks for isXXX method. If I am missing something, I will be really glad to find out that this is my own stupid mistake.

As far as feasibility goes.. well, tell your manager that we found such and such basic errors.. BUT there are "work arounds" (which for your
(d)amager mean no clean solution) So overall we think that though this product doesnt support SP for integrating with existing legacy system (where data can only be retrieved through SP call)..though this product has few basic errors with work arounds .. we should still use it !!

And if you read my first post correctly, I said I am hoping we don't find any more limitations / errors in what we are doing. You choose a product to suit your needs.... not a product to suit its need !

In all our application we do not allow deletes of master data. Soft deletes are available that is column "Active" maintains the state. So I can easily go to my team and tell them.. hey not a big deal.. just change method name.. and change corresponding dependent classes.. cause the product you chose need it.
Thanks mate.. you are basically asking me to look for another job

Peace.

Lack of support for store procs is a major issue integrating with legacy apps. But I hear its done in 3.0 )

[ November 15, 2004: Message edited by: nilesh Katakkar ]
[ November 15, 2004: Message edited by: nilesh Katakkar ]
Yeah it's easy to avoid by changing the method name.

However point is not that. As per Java Bean specifications, isXXX methods are for boolean variables. When a mapping file clearly says type is String, Hibernate should be looking for getXXX / setXXX . Why should isXXX method matter when there's no boolean property mention in mapping ? kinda looks silly...especially when you are evaluating feasibility of a product for your project. I am just hoping we don't run across any such basic errors.. or I would be hard pressed to sell it to my team to use Hibernate !
As part of evaluating feasiblity of hibernate to use in our new project ran across following error -

Adding isXXX method where XXX is a string field throws ClassCastException. Might have to with the way hibernate do reflection. However, seems quite basic error.

<hibernate-mapping>
<class name="AccountType" table="ACCOUNT_TYPE">
<id name="id" type="long" column="id">
<generator class="sequence">
<param name="sequence">
SQ_ACCT_TYPE_ID</param>
</generator>
</id>
<property name="description" type="string"
column="ACCOUNT_DESC"/>
<property name="active" type="string" column="ACTIVE"/>

</class>
</hibernater-mapping>

Active is a varchar2(1) column defined in DB (Oracle 9ir2) and has a check constraint on it to check the inserted / updated value is either Y or N. The persistent POJO class has correspoding get and set for field active. However, to avoid string comparision with Y or N in all other classes, it makes sense to provide utility method in this class to find if account type is active or not.

Class Definition ..

public class AccountType {

private static String EMPTY="", YES="Y", NO="N";
private long id;

private string description, active;

public long getId(){ ...}
public void setId(long id){.....}
public String getDescription(){.....}
public void setDecription(String desc){....}
public String getActive(){...}
public void setActive(String s){...}
/// utility method

public boolean isActive(){

if(active == null || active.equals(EMPTY)) return false;

if(active.equals(YES)) return true;

return false;

}

}

Adding above utility method isActive throws classcastexception. If I change the method name from isActive to isTypeActive or something else , it works fine. So it might have to with the way reflection works in hibernate.

However, if mapping file says active is of type String, and as long as there are right getActive and setActive methods defined correctly, why should it matter to hibernate if there's another isActive or whatever. Sounds like a pretty basic error to me.
oops sorry Rasheed..didn't check messages for a while. Have you figured your problem out yet ? If not then try following

stmt.registerOutParameter(3,Types.ARRAY,"T_MODEL_ID");

I think, the type needs to be in CAPSLOCK.

If this doesn't work then probably the DB user of your connection object may not have direct access to this type. So try -

stmt.registerOutParameter(3,Types.ARRAY,"<OracleUserName>.T_MODEL_ID");

Hope you have already figured this out. Just in case not then.. it should help.

nilesh
neilindallas@hotmail.com
May be SQLTimeOut set in weblogic is expiring before you retrieve the huge resultset on the middle tier. Just a wild guess

But yeah.. makes sense to reduce the # of recrods to meaningful value if it's web app.
Yeah agree with Bear Bibeault ! Second approach is the only correct approach !

If you have let's say 100,000 rows or 1 million rows and you are only showing lets say 25 rows at a time, you probably will end up overkilling your application by fetching all the data. Just caching all rows on middle tier wont make sense if your data is real time one. Once the data is send back to the client client may want to see next 25 records or provide another filter criteria to reduce the rows retrieved or whatever. Plus with web apps you will never know, how long it will take client to decide to click next. He may he may not. How long will you keep cached data on middle tier ? What happens when some other users modifies the data in the DB ? You will have stale copy of data. Now add 100 users simulteneously quering same data... # of copies on middle tier will increase linearly.

Bear in his message told the way you can do it in POSTgreSQL. In MS Sql server I think there's a TOP N clause. In oracle you can try using Analytical functions rank () etc. or.. other way is to use inline views..

SELECT * FROM
( SELECT rownum r, a.* r FROM
( <YOUR SQL Statement Here Including WHERE GROUP ORDER BY> A
WHERE rownum < :toRow)
WHERE r > :fromRow
[ November 08, 2004: Message edited by: nilesh Katakkar ]
Just a wild guess... Can you try setting context.setRollbackOnly as your last statement. In your code after setting rollback you are reading back from the message again. May be that causes a new transaction to start.

boolean isStop=true;
try{
String msgFileName = (StreamMessage)message.readString();
System.out.println("Get sourceFile:"+msgFileName );
if(isStop){
System.out.println("The Message will send back to the queue for redelivering");
context.setRollbackOnly();
}
Yes there's a better way -

String sql = "UPDATE ACTIVE SET STATUS = 'ACTIVE' WHERE where MobileNumber = ?";
PreparedStatement ps = conn.prepareStatement(sql);

try {
while ((line=bwin.readLine()) !=null){
StringTokenizer st = new StringTokenizer(line, "|");
String MSN = (st.nextToken());
System.out.println(MSN);
ps.setString(1, MSN)
ps.addBatch();
}
ps.executeBatch();
} catch(...)

1. SQL UPDATE statement use bind variable instead of string.

2. conn.prepareStatement - outside of while loop. By putting it inside of loop you are actually making it parse the sql every time.

3. Use batching API.. add all statements to the batch and executeAll at once reducing round trips.

4. Since # of records are gonna be 500 to 600 I guess you can also try setting the default batch size. Some of the jdbc drivers and in specific ORACLE's jdbc driver, has default batch size of 1. Call setDefaultExecuteBatch on the OracleConnection object to set the batch size.
Rashid,

Since you are gonna get Array as OUT parameter...you wont need ArrayDescriptor here.

Taking your example code it will look like

String query = null;
CallableStatement stmt = conn.prepareCall("{call EVDB.DCT_2_0.getModels(?,?,?,?)}");
stmt.setString(1,"K01439");
stmt.setString(2,"K00014");
stmt.registerOutParameter(3,OracleTypes.ARRAY,"T_MODEL_ID"); //
//stmt.registerOutParameter(3,2003);
stmt.registerOutParameter(4,OracleTypes.ARRAY,"T_MODEL_NAME");
stmt.execute();
// Assuming your T_MODEL_ID is int
Array sqlArray = stmt.getArray(2);

int[] ids = (int[])sqlArray.getArray();

sqlArray = stmt.getArray(3);

String[] names = (String[])sqlArray.getArray();
}
stmt.close();
CREATE or REPLACE TYPE myStringArray AS TABLE OF VARCHAR2(40);
/

CREATE OR REPLACE TYPE myNumberArray AS TABLE OF NUMBER;
/

CREATE or REPLACE PACKAGE UTILS
AS

PROCEDURE getEmpDept ( deptNo IN NUMBER, dNames OUT myStringArray, empNos OUT myNumberArray);
END;


/

CREATE or REPLACE PACKAGE BODY UTILS
AS
PROCEDURE getEmpDept ( deptNo IN NUMBER, dNames OUT myStringArray, empNos OUT myNumberArray)
IS
BEGIN
SELECT d.dname, e.empNo BULK COLLECT INTO dNames, empNos
FROM emp e, dept d
WHERE e.deptNo = d.deptNo;
END;
END;
/

show errors



ArrayDescriptor stringDesc = ArrayDescriptor.createDescriptor("SCOTT.MYSTRINGARRAY",connection);
ArrayDescriptor numberDesc = ArrayDescriptor.createDescriptor("SCOTT.MYNUMBERARRAY",connection);


CallableStatement procCall = null;

procCall = connection.prepareCall("{ call UTILS.getEmpDept(?,?,?)}");

procCall.setInt(1,10);


procCall.registerOutParameter(2,OracleTypes.ARRAY,"SCOTT.MYSTRINGARRAY");

procCall.registerOutParameter(3,OracleTypes.ARRAY,"SCOTT.MYNUMBERARRAY");

procCall.execute();

oracle.sql.ARRAY dNames = ((OracleCallableStatement)procCall).getArray(2);

oracle.sql.ARRAY empNos = ((OracleCallableStatement)procCall).getArray(3);

// OR
//oracle.sql.ARRAY dNames = (Oracle.sql.ARRAY)procCall.getObject(2);


Now you can use methods on class ARRAY to get specific arrays. Since oracle.sql.ARRAY implements java.sql.Array, you can use methods
in that interface too.

String[] deptNames = (String[])dNames.getArray();

int[] empNumbers = (int[])empNos.getArray();


However.. I havent really tested this code. Its of top my head. I am also working on trying to find something where I donot have to use
Oracle specific extensions. If I find something I will post it.

Let me know if this helps.
To get an array from ResultSet, you will need to use Oracle JDBC Extensions. You will need to create ArrayDescriptor first describing your array and use OracleTypes.ARRAY to get the object from resultset.

Havent tried using java.sql.Types.ARRAY. Could be interesting to check.

As a side note...instead of populating tables proecdurally in PL/SQL tables you can also try using SQL single BULK COLLECT syntax.to improve the performance.
I guess, now I am going to disagree to prove that Dave indeed is right !There will be as many opinions around as people.

I use stored procedures in most of the scenarios.Especially when working in the corporate and enterprise scenarios, your application is never gonna be the only application to access the data. Do you want to duplicate your data access logic in all those apps ? What if another application is .Net based ? You cannot use your same Java objects there. Now if you don't enforce your rules in the DB, the next application that talks to your data can very well compromise your data integrity.

Enterprise application are all about managing data. What changes is the delivery mechanism. It was client server first, now its web based. Tomorrow there will be some other paradigm. Java is not the last language. Paradigms will change.. data in the database won't. You can use Stored Procs from all your clients - Java based, .Net Based, PRO C or PHP based.

I have seen many Java programs using SQL / Jdbc directly in the java code. If I have to tune that query , then it would mean changing Java class. I can do so in Procedures directly where SQL is more readable, easily tunable and maintainable (as far as maintenance ask DBA's they will agree).

Other thing that people go JDBC way is to have DB independance. Well.. how much really can you acheive that ? Complying to SQL 92 std will only give you basic CRUD things. If you really want to exploit the power of SQL, you will be in need some point to use functionality like DECODE, TO_DATE , NVL, Analytical functions (in Oracle). If you have this SQL in Java JDBC client you are anyway depending on DB. Now add the fact that every db implement concurrency, locking differently. Only Oracle provides the multiversioning and non blocking reads right now. (SQL '05 will provide it with entirely new isolation level called snapshot) It's very difficult to acheive DB independence for truly scalable enterprise apps. And in my experience I have never seen any enterprise application yet changing the DB. MS apps were convereted to J2ee, some J2ee apps were converted to .Net .. some .net apps were converted to j2ee... however DB remained same.

PL/SQL, T-SQL all these languages are built for only one reason - tight integration with SQL and to exploit power of SQL. Use right tool to do right things .. we say that all the time dont we ?

Middle tier - all about application logic,presentation, content delivery mechanisms, integration with 3rd party system using webservices / Message Oriented Softwares.

DB - all about data access / business logic.

Do same things on the middle tier that you used to do in client tier env.
Java is good for computational intesive stuff. Stored Procs are good for SQL intensive stuff, reducing round trips to DB and having centrlized data access logic.

As far as difference between preparedstatement and SP goes, if you gonna do a SELECT / INSERT / UPDATE and your SP's gonna have just one SQL statement then, there's no difference between two. PreparedStatement have the advantage that, it always use bind variables protecting developer from misusing them, however all SQL is dynamic and Hard to read ! SP can use static SQL, explicit cursors / implicit cursors and proper use of bind variables in dynamic SQL.. giving better performance and allowing you to use features that clients paid for.