Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Ingres 10.1, JDBC parameters issue

24 views
Skip to first unread message

zarafiq

unread,
Aug 15, 2014, 3:43:32 PM8/15/14
to

Hi,

I am encountering a strange problem when accessing Ingres 10.1
using jdbc. Old db (2.6) everything fine.
New DB (10.1) the following code runs ok:

String sql = "SELECT * FROM table_x WHERE column_x = 'A1234' ";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();

After changing to:

String sql = "SELECT * FROM table_x WHERE column_x = ? ";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, "A1234");
ResultSet rs = pstmt.executeQuery();

Super slow (I am guessing full table scan), and the following
error in errlog.log.

E_PS0C05_BAD_ADF_STATUS Unexpected status from ADF

Tried 3 different iijdbc.jar files, (client v. 3, 9 and 10)
with no success.

Any help would be appreciated.

Regards,
zarafiq

dominik.j...@gmail.com

unread,
Aug 18, 2014, 5:36:32 AM8/18/14
to
On Friday, 15 August 2014 20:43:32 UTC+1, zarafiq wrote:

Managed to find a workaround:

String sql = "SELECT * FROM table_x WHERE column_x = 'A1234' "; // fast

String sql = "SELECT * FROM table_x WHERE column_x = ? "; // slow
pstmt.setString(1, "A1234");

String sql = "SELECT * FROM table_x WHERE column_x = text(?) "; // fast
pstmt.setString(1, "A1234");

The trace files generated by slow and fast versions:

---------------------------------------------
-------------- SLOW -------------------------
---------------------------------------------
LOCK: MVCC PHYS Mode: S Timeout: 0
Key: (livedb,table_x)
LOCK: TABLE PHYS Mode: S Timeout: 0
Key: (livedb,table_x)
Fetch Cursor <1525.,1.,jdbc_crsr_0_1> Statement
pre-fetch 10 row(s)
Close Cursor <1525.,1.,jdbc_crsr_0_1> Statement

----------
| 0|
ORIG
at 1
et 368744
ad 3863
ed 8428
ac 39
ec 368744
et 5

---------------------------------------------
-------------- FAST -------------------------
---------------------------------------------
LOCK: MVCC PHYS Mode: S Timeout: 0
Key: (livedb,table_x)
LOCK: TABLE PHYS Mode: IS Timeout: 0
Key: (livedb,table_x)
Fetch Cursor <1526.,1.,jdbc_crsr_0_1> Statement
pre-fetch 10 row(s)
LOCK: PAGE PHYS,LOCL,NOIN,QUTM Mode: S Timeout: 0
Key: (livedb,table_x,0.0)
LOCK: PAGE PHYS,LOCL,NOIN,QUTM Mode: S Timeout: 0
Key: (livedb,table_x,0.18162)
UNLOCK: PAGE Key: (livedb,table_x,0.0)
UNLOCK: PAGE Key: (livedb,table_x,0.18162)
LOCK: PAGE STAT,LOCL,QUTM Mode: S Timeout: 0
Key: (livedb,table_x,0.22275)
LOCK: PAGE STAT,LOCL,QUTM Mode: S Timeout: 0
Key: (livedb,table_x,0.22285)
Close Cursor <1526.,1.,jdbc_crsr_0_1> Statement
----------
| 0|
ORIG
at 1
et 1
ad 0
ed 4
ac 9
ec 1
et 0

Regards,
zarafiq
0 new messages