Java.SQL.Exception The Network Adapter could not establish the connection

1. The port number can be 1526 or 1521. Confirm with tnsnames.ora on the Oracle Server.
2. If that is all right, then use the IP address of the Oracle host, rather than the name.

Oracle Triggers

CREATE OR REPLACE TRIGGER EBP.INSERT_EBP_TRADING_PARTNERS
BEFORE INSERT
ON EBP.EBP_TRADING_PARTNERS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
v_fax_subject_line VARCHAR2(60);
BEGIN
tmpVar := 0;

SELECT SUBJECT_LINE INTO v_fax_subject_line FROM EBP_BILLERS WHERE BILLER_ID = :NEW.BILLER_ID;

IF :NEW.FAX_SUBJECT IS NULL THEN
:NEW.FAX_SUBJECT := v_fax_subject_line;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Warning: The Biller_Id does not exist in EBP_BILLERS table.');

END ;
/

Select between dates in Oracle SQL

SQL> with t as 
(select to_date('01/01/2007',
'dd/mm/yyyy') as update_date from dual union all
2    select to_date('01/01/2007',
'dd/mm/yyyy') from dual union all
3    select to_date('03/01/2007',
'dd/mm/yyyy') from dual union all
4    select to_date('03/01/2007',
'dd/mm/yyyy') from dual union all
5    select to_date('03/01/2007', 
'dd/mm/yyyy') from dual union all
6    select to_date('04/01/2007', 
'dd/mm/yyyy') from dual union all
7    select to_date('05/01/2007', 
'dd/mm/yyyy') from dual union all
8    select to_date('07/01/2007', 
'dd/mm/yyyy') from dual union all
9    select to_date('07/01/2007', 
'dd/mm/yyyy') from dual)
10  -- end of test data
11  select to_char(x.UPDATE_DATE,  
'YYYY-MM-DD') as update_date, 
DECODE(t.update_date, NULL, 0, count (*)) 
as count
12  from (select to_date('2007-01-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')+
(rownum-1) as update_date
13        from   dual
14        connect by rownum <= 
(to_date('2007-02-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')-
15             to_date('2007-01-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS'))) x
16        LEFT OUTER JOIN t 
ON (x.update_date = t.update_date)
17  where x.UPDATE_DATE >= 
to_date('2007-01-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')
18  and x.UPDATE_DATE < 
color="navy">'2007-02-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')
19  group by to_char(x.UPDATE_DATE, 'YYYY-MM-DD'), 
t.update_date
20  order by 1
21  /

UPDATE_DAT      COUNT
---------- ----------
2007-01-01          2
2007-01-02          0
2007-01-03          3
2007-01-04          1
2007-01-05          1

CONCAT Strings in Oracle v.s. MySQL

Oracle uses the CONCAT(string1, string2) function or the || operator. The Oracle CONCAT function can only take two strings so the above example would not be possible as there are three strings to be joined (FirstName, ' ' and LastName). To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server / Access.

-- Oracle
SELECT FirstName || ' ' || LastName As FullName FROM Customers

MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Customers

Backup MySQL Database

Full backup:
echo "============================================================"
echo "Backup $dbName database to $dbFile"

# The following commands will be put to the beginning of the database dump file.
echo " 1. Creating header.tmp temporary file"
echo "SET AUTOCOMMIT = 0;SET FOREIGN_KEY_CHECKS=0;" > $dbDir/header.tmp

# Dumping the database.
echo " 2. Creating $dbName.tmp temporary file"
mysqldump --opt --user=$dbUSER --password=$dbPASSWORD $dbName > $dbDir/$dbName.tmp

# The following commands will be put to the end of the database dump file.
echo " 3. Creating tailer.tmp temporary file"
echo "SET FOREIGN_KEY_CHECKS = 1;COMMIT;SET AUTOCOMMIT = 1;" > $dbDir/tailer.tmp

echo " 4. Create today's backup file : $dbFile"
cat $dbDir/header.tmp $dbDir/$dbName.tmp $dbDir/tailer.tmp > $dbDir/$dbFile

Backup Schema Only:
$ mysqldump --no-data --user=$dbUSER --password=$dbPASSWORD $dbName > Schema20081003.sql

Get Create Table statement from MySQL

$ sql "show create table my_table\G" | sed -e '1,2d;s/Create Table: //'
Note: sed -e '1,2d' removes the first 2 lines.

Java NullPointerException from a NULL field in MySQL database

Problem

A java.lang.NullPointerException error occurs when the Entity Bean trying to retrieve rows from MySQL database.

2007-09-11 09:05:20,025 DEBUG
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SMS_SymbolTrade]
Executing SQL: SELECT tradeid, symbolid, boughtdate, boughtshare, boughtprice,
solddate, soldshare, soldprice FROM sms_symboltrade WHERE (tradeid=?) OR (tradeid=?)
OR (tradeid=?) OR (tradeid)
2007-09-11 09:05:20,035 ERROR
[org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException in
method: public abstract smsj.ejb.interfaces.SMS_SymbolTradeValue
smsj.ejb.interfaces.SMS_SymbolTradeLocal.getSMS_SymbolTradeValue(),
causedBy:java.lang.NullPointerException

at smsj.ejb.bean.SMS_SymbolTradeCMP$Proxy.getSoldshare(<generated>)
at smsj.ejb.bean.SMS_SymbolTradeCMP.getSMS_SymbolTradeValue(SMS_SymbolTradeCMP.java:76)
at sun.reflect.GeneratedMethodAccessor114.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
at $Proxy220.selectTradeBySymbolIdBoughtDate(Unknown Source)
at smsj.server.SMSSocketProtocol.generateXML(SMSSocketProtocol.java:423)

Environment

MySQL Table: SMS_SymbolTrade

+-------------+-------------+------+-----+---------+-------+
| Field |Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| TradeId | varchar(64) | NO | PRI | | |
| SoldDate | date | YES | | NULL | |
| SoldShare | int(7) | YES | | 0| |
| SoldPrice | float(7,2) | YES | | 0.00| |
+-------------+-------------+------+-----+---------+-------+

Data Sample:
+--------------------+----------+-----------+-----------+
| TradeId | SoldDate | SoldShare | SoldPrice |
+--------------------+----------+-----------+-----------+
| 101189456691548214 | NULL | NULL | NULL |
| 101189456691550974 | NULL | NULL | NULL |
| 101189456691559293 | NULL |0 | NULL |
| 101189456691563329 | NULL |0 | NULL |
+--------------------+----------+-----------+-----------+

Entity Bean: SMS_SymbolTradeBean.java
public abstract class SMS_SymbolTradeBean{
private String tradeid; //pk
private String solddate;
private int soldshare;
private Float soldprice;
. . . . . . . .

Solution

The root cause of the NullPointerException is from the NULL value of the SoldShare field in SMS_SymbolTrade table. The data type of the SoldShare field is set to int(7), which means the SoldShare field is a primitive, not an Object. Although you can set a NULL value to the SoldShare field in MySQL database, when the Entity bean trying to read it from database, because it’s a primitive field that can’t be set to NULL in EJB, it throws the famous NullPointerException to the log.

Just simply updated all NULL value to 0 and it fixed myproblem.

Update SMS_SymbolTrade SET SoldShare = 0 WHERE SoldShare IS NULL;