App. Server:WebLogic 6.1 DB racle 9i Ent. Driver: Oracle JDriver (Level-2 OCI) OS:Windows 2000 *Everythig is running local Hi everybody, I am trying to go through a book called "WebLogic Server 6.1 Workbook for Enterprise JavaBeans, 3rd Edition" I am having a problem with one of the exercises: The book suggests to make a table: CREATE TABLE CUSTOMER ( ID INT PRIMARY KEY, LAST_NAME CHAR(20), FIRST_NAME CHAR(30), HAS_GOOD_CREDIT BIT  ) SQLPlus does not like the statement. Well, I don't remember any BIT datatype in Oracle(I am also an Oracle beginner.), so I created a table like this: CREATE TABLE CUSTOMER ( ID NUMBER(10), LAST_NAME VARCHAR2(20), FIRST_NAME VARCHAR2(10), HAS_GOOD_CREDIT NUMBER(1) ) And set ID as PRIMARY KEY and NOT NULL. I received an error: Parsing: SELECT ID, LAST_NAME, FIRST_NAME, HAS_GOOD_CREDIT FROM CUSTOMER WHERE 1 = 0 Executing: SELECT ID, LAST_NAME, FIRST_NAME, HAS_GOOD_CREDIT FROM CUSTOMER WHERE 1 = 0 Parsing: insert into CUSTOMER (ID, LAST_NAME, FIRST_NAME, HAS_GOOD_CREDIT) values (:1, :2, :3, :4) Executing: insert into CUSTOMER (ID, LAST_NAME, FIRST_NAME, HAS_GOOD_CREDIT) values (:1, :2, :3, :4) SQLException: SQLState() vendor code(1461) java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column) at weblogic.db.oci.OciCursor.getCDAException(OciCursor.java:240) at weblogic.jdbc.oci.Statement.executeUpdate(Statement.java:990) at weblogic.jdbc.pool.Statement.executeUpdate(Statement.java:293) at weblogic.jdbc.rmi.internal.PreparedStatementImpl.executeUpdate(PreparedStatementImpl.java:66) at weblogic.jdbc.rmi.SerialPreparedStatement.executeUpdate(SerialPreparedStatement.java:57) at com.titan.customer.CustomerEJB_1q1rpd__WebLogic_CMP_RDBMS.__WL_create(CustomerEJB_1q1rpd__WebLogic_CMP_RDBMS.java:931) at com.titan.customer.CustomerEJB_1q1rpd__WebLogic_CMP_RDBMS.ejbPostCreate(CustomerEJB_1q1rpd__WebLogic_CMP_RDBMS.java:849) at java.lang.reflect.Method.invoke(Native Method) at weblogic.ejb20.manager.DBManager.create(DBManager.java:583) at weblogic.ejb20.manager.DBManager.remoteCreate(DBManager.java:462) at weblogic.ejb20.internal.EntityEJBHome.create(EntityEJBHome.java:190) at com.titan.customer.CustomerBean_1q1rpd_HomeImpl.create(CustomerBean_1q1rpd_HomeImpl.java:78) at com.titan.customer.CustomerBean_1q1rpd_HomeImpl_WLSkel.invoke(Unknown Source) at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:298) at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:93) at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:267) at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:22) at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139) at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120) Parsing: select * from CUSTOMER where ID = :1 Executing: select * from CUSTOMER where ID = :1 Well, looks like a type mismatch. I went through some info, and noticed that LONG is just for backward compatible, now BLOB is better. I did exactly just that: CREATE TABLE CUSTOMER ( ID NUMBER(10), LAST_NAME VARCHAR2(20), FIRST_NAME VARCHAR2(10), HAS_GOOD_CREDIT BLOB() ) *Sorry I used the wizard, and don't remember the exact syntax. Now I got java.sql.SQLException: ORA-00932: Data mismatch.
What exactly the way to map BIT datatype to Oracle? Or is there any DD in WebLogic that I can change? Or any entries in weblogic-cmp-rdbms-jar that I can add to specify datatype? Please let me know if you have some idea. Thanks in advance.
This would probably be the table you want: CREATE TABLE CUSTOMER ( ID NUMBER(10), LAST_NAME VARCHAR2(20), FIRST_NAME VARCHAR2(10), HAS_GOOD_CREDIT NUMBER(1), CONSTRAINT customer_credit_ck CHECK(HAS_GOOD_CREDIT BETWEEN 0 AND 1) ) When you insert into the table, use the setInt(int,int) and getInt(int) methods to access the column. BLOB's, CLOB's and LONG's are not what you are looking for. CLOB's and LONG's are used to store large fixed-width character data where as BLOB's are used to store large unstructured data(binary images).
by the way, the constraint is in there only to allow a 1 or a 0 to be entered into the database. I only included it as an suggestion Jamie
Joined: Dec 04, 2001
Dear Jamie, Thank you for your posting. Yes, now I can store data in DB. I appreciate it.
By the way, now I have some beginner question. I hope someone can give me an answer. <<Java primitive type mapping>> When a particular DB is said to be "JDBC2.0 complient", which means all Java primitive types can be mapped to some other types in that DB schema? For example, myEJB.setCredential(true); If I want to set boolean value to EJB, is there a way for me to create some column that match "boolean" value in Oracle DB? If I use the method Jamie explained to me, I can change client code to use integer, but I cannot use boolean value. In a way, I have to modify EJB design in order to accomodate DB schema design. If I change underlaying DB(for example, SQL Server), I have to change EJB design again to let EJB use boolean primitive value. I find this a kind of weird. I'll appreciate any input. Thank you.
Shin: There is no support for Boolean datatypes up to and including Oracle 8.1.7. Other databases use other ways like Access uses Yes/No column. So you are probably better off, for portability sakes, using an integer of either 0 or 1 or using VARCHAR2 with values like 'T' and 'F'. You will find that the term "JDBC2.0 compliant" is used pretty loosely and does not mean too much. Jamie
Joined: Dec 04, 2001
Dear Jamie, Thank you for the input. I still feel a little weird to think that myEJB.setToolVal(true) is innapropriate. Probably because I've spent a lot of time only on Access, and very new to enterprise DBs like Oracle or DB2. There are a lot to learn. Also I heard DB2 doesn't support boolean value, either. Like you said, I guess it is safe to stay away from boolean values alltogether. "JDBC complaint" sounded very "official" to me, and I made an assumption on the boolean value case, ended up costing me a lot of time. Anyway, I appreciate your postings. I can finally go back to my project. Thank you. Sincerely,
"JDBC complaint" sounded very "official" to me... me too, but after hanging out at the ranch for a while, you'll know only to believe it as far as you can throw it.( That means don't believe it! ) Although some are better than others. Jamie