Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...
Database Forum / DB2 Topics / January 2007

Tip: Looking for answers? Try searching our database.

JDBC type 4 and OpenOffice.org

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LorneSunley - 30 Jan 2007 11:27 GMT
I am trying to access an 8.2 database through openoffice.org version 2.0 or
2.1. I am using the type 4 JDBC driver. Tables work OK, but when I attempt
to access a view I always get an sqlcode error value of -243 that seems to
indicate that the cursor sensitivity is incorrect. Has anyone else done
this and actually managed to display view in an openoffice.org database?

Thanks for any help

--
Lorne Sunley
Reply to this Message
Knut Stolze - 30 Jan 2007 18:35 GMT
> I am trying to access an 8.2 database through openoffice.org version 2.0
> or 2.1. I am using the type 4 JDBC driver. Tables work OK, but when I
> attempt to access a view I always get an sqlcode error value of -243 that
> seems to indicate that the cursor sensitivity is incorrect. Has anyone
> else done this and actually managed to display view in an openoffice.org
> database?

Could you provide more information on the failing query?  A JDBC trace may
help you with figuring out what's going on.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message
LorneSunley - 30 Jan 2007 14:31 GMT
>> I am trying to access an 8.2 database through openoffice.org version 2.0
>> or 2.1. I am using the type 4 JDBC driver. Tables work OK, but when I
[quoted text clipped - 5 lines]
> Could you provide more information on the failing query?  A JDBC trace may
> help you with figuring out what's going on.

Sure

This query works (this is a view defined in the database and openoffice is
issuing a select * from ... type of query)

create view rawbankdata.RBC_TX_RESOLVED as
select cola as bank_ID,
colb as transit_ID,
colc as account_ID,
cast( substr(cold,1,4) || '-' || substr(cold,5,2)|| '-' || substr(cold,7,2)
as date) as TX_Date1,
cast( substr(cole,1,4) || '-' || substr(cole,5,2) || '-' || substr(cole,7,2)
as date) as TX_Date2,
colf as ID4,
colg as ID5,
colh as Currency_type,
coli as ID6, colj as TX_type,
colk as TX_ID,
coll as TX_merchant,
colm as amex_merchant,
cast(coln as
decimal(18,2)) as Amount,
colo as ID7,
colp as ID8,
colq as ID9 from rawbankdata.rbc_transactions

In the above view "rawbankdata.rbc_transactions" is a table

This query does not work ( this is another view in the database and is based
on another table in the database) AFAIK openoffice is issuing another
"select * from ..." type of query

All I am doing with open office is defining a database  that is a connection
to the DB2 server with the JDBC driver, after OO.org fetches the database
information I just double-click on a table or view.

error is

DB2 SQL error: SQLCODE: -243, SQLSTATE 36001, SQLERRMC:: SQL_CURSH200C1

create view rawbankdata.rbc_transasction_union as select
cast('NDDS' as char(4)) as tx_types,
TX_DATE1,
TX_DATE2,
tx_merchant,
tx_id,
amex_merchant,
substr(tx_id, 11,3) as DepSlip,
substr(tx_id, 8,3) as StoreNumber,
case when id6 = '0' then amount * -1 else amount end as amount
from rawbankdata.rbc_archive a
where tx_merchant like 'NDDS%'
union all
select cast('AMEX' as char(4)) as tx_types,
TX_DATE1,
TX_DATE2,
tx_merchant,
tx_id,
amex_merchant,
substr(tx_id, 11,3) as DepSlip,
substr(tx_id, 8,3) as StoreNumber,
case when id6 = '0' then amount * -1 else amount end as amount
from rawbankdata.rbc_archive a
where tx_merchant = 'MISC PAYMENT' and substr(amex_merchant, 1, 4) = 'AMEX'
union all
select
cast('DEPS' as char(4)) as tx_types,
TX_DATE1,
TX_DATE2,
tx_merchant,
tx_id,
amex_merchant,
substr(tx_id, 11,3) as DepSlip,
substr(tx_id, 8,3) as StoreNumber,
case when id6 = '0' then amount * -1 else amount end as amount
from rawbankdata.rbc_archive a
where tx_merchant like 'DEPOSIT%'
union all
select
cast('OTHS' as char(4)) as tx_types,
TX_DATE1,
TX_DATE2,
tx_merchant,
tx_id,
amex_merchant,
substr(tx_id, 11,3) as DepSlip,
substr(tx_id, 8,3) as StoreNumber,
case when id6 = '0' then amount * -1 else amount end as amount
from rawbankdata.rbc_archive a
where substr(tx_merchant, 1, 2) not in ('MC', 'VI', 'EF', 'MI', 'DE', 'ND')

union all
select
cast('EFTA' as char(4)) as tx_types,
TX_DATE1,
TX_DATE2,
tx_merchant,
tx_id,
amex_merchant,
substr(tx_id, 11,3) as DepSlip,
storeno as StoreNumber,
case when id6 = '0' then amount * -1 else amount end as amount
from rawbankdata.rbc_archive a
join rawbankdata.merchant_number on substr(tx_merchant, 8, 8) =
rb_merch_short where substr(tx_merchant, 1, 6) = 'EFT 60'

Signature

Lorne Sunley

Reply to this Message
LorneSunley - 30 Jan 2007 15:37 GMT
>>> I am trying to access an 8.2 database through openoffice.org version 2.0
>>> or 2.1. I am using the type 4 JDBC driver. Tables work OK, but when I
[quoted text clipped - 112 lines]
> join rawbankdata.merchant_number on substr(tx_merchant, 8, 8) =
> rb_merch_short where substr(tx_merchant, 1, 6) = 'EFT 60'

This is the JDBC trace data for the failing query

[ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419][Connection@789144]
prepareStatement (SELECT * FROM "RAWBANKDATA"."RBC_TRANSASCTION_UNION",
1005, 1008) called
[ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419][Connection@789144]
prepareStatement () returned PreparedStatement@1551f60
[ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419
[PreparedStatement@1551f60] executeQuery () called
[ibm][db2][jcc] [t4][time:1170171024401][thread:Thread-9419][tracepoint:1
[Request.flush]
[ibm][db2][jcc][t4]        SEND BUFFER: PRPSQLSTT              (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F  
0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   0058D05100010052
200D0044211387A2  .X.Q...R ..D!...  ..}...........gs
[ibm][db2][jcc][t4] 0010   8986858440404040
4040404040404040  ....@@@@@@@@@@@@  ifed            
[ibm][db2][jcc][t4] 0020   D5E4D3D3C9C44040
4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][db2][jcc][t4] 0030   4040E2E8E2E2C8F2  F0F0404040404040
@@........@@@@@@    SYSSH200      
[ibm][db2][jcc][t4] 0040   404040405359534C  564C303100010005
@@@@SYSLVL01....      ...<.<......
[ibm][db2][jcc][t4] 0050  
2116F10005214604                    !....!F.          ..1.....        
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        SEND BUFFER: SQLATTR                (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0033D0530001002D
2450000000002353  .3.S...-$P....#S  ..}......&......
[ibm][db2][jcc][t4] 0010   454E534954495645  2053544154494320  ENSITIVE
STATIC   .+..............
[ibm][db2][jcc][t4] 0020   5343524F4C4C2046  4F52205550444154  SCROLL FOR
UPDAT  ...|<<..|...&...
[ibm][db2][jcc][t4] 0030   4520FF                            
E .               ...            
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        SEND BUFFER: SQLSTT                 (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0044D0430001003E
2414000000003453  .D.C...>$.....4S  ..}.............
[ibm][db2][jcc][t4] 0010   454C454354202A20  46524F4D20225241  ELECT * FROM
"RA  .<........|(....
[ibm][db2][jcc][t4] 0020   5742414E4B444154  41222E225242435F
WBANKDATA"."RBC_  ...+............
[ibm][db2][jcc][t4] 0030   5452414E53415343  54494F4E5F554E49
TRANSASCTION_UNI  ...+......|+..+.
[ibm][db2][jcc][t4] 0040   4F4E22FF                            ON".            
|+..            
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        SEND BUFFER: DSCSQLSTT              (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0053D0410002004D
20080044211387A2  .S.A...M ..D!...  ..}....(......gs
[ibm][db2][jcc][t4] 0010   8986858440404040
4040404040404040  ....@@@@@@@@@@@@  ifed            
[ibm][db2][jcc][t4] 0020   D5E4D3D3C9C44040
4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][db2][jcc][t4] 0030   4040E2E8E2E2C8F2  F0F0404040404040
@@........@@@@@@    SYSSH200      
[ibm][db2][jcc][t4] 0040   404040405359534C  564C303100010005
@@@@SYSLVL01....      ...<.<......
[ibm][db2][jcc][t4] 0050  
214605                              !F.               ...            
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        SEND BUFFER: OPNQRY                 (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0069D00100030063
200C0044211387A2  .i.....c ..D!...  ..}...........gs
[ibm][db2][jcc][t4] 0010   8986858440404040
4040404040404040  ....@@@@@@@@@@@@  ifed            
[ibm][db2][jcc][t4] 0020   D5E4D3D3C9C44040
4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][db2][jcc][t4] 0030   4040E2E8E2E2C8F2  F0F0404040404040
@@........@@@@@@    SYSSH200      
[ibm][db2][jcc][t4] 0040   404040405359534C  564C303100010008
@@@@SYSLVL01....      ...<.<......
[ibm][db2][jcc][t4] 0050   211400007FFF0006
2141FFFF00082156  !.......!A....!V  ...."...........
[ibm][db2][jcc][t4] 0060   000000400005215D
01                ...@..!].         ... ...).      
[ibm][db2][jcc][t4]
[ibm][db2][jcc] [t4][time:1170171024408][thread:Thread-9419][tracepoint:2
[Reply.fill]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: SQLCARD             (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F  
0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   0067D04300010061
2408000DFFFFFF33  .g.C...a$......3  ..}..../........
[ibm][db2][jcc][t4] 0010   3630303153514C4E  51373532006D001A
6001SQLNQ752.m..  ......<+....._..
[ibm][db2][jcc][t4] 0020   8000000000000000
000000000070FEFF  .............p..  ................
[ibm][db2][jcc][t4] 0030   FF00000000202020
2020202020202020  .....             ................
[ibm][db2][jcc][t4] 0040   0012475349464544
2020202020202020  ..GSIFED          ................
[ibm][db2][jcc][t4] 0050   202020200000000E
53514C5F43555253      ....SQL_CURS  ..........<.....
[ibm][db2][jcc][t4] 0060   483230304331FF                    
H200C1.           .......        
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: SQLCARD             (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0074D0430002006E
24080029FDFFFF35  .t.C...n$..)...5  ..}....>........
[ibm][db2][jcc][t4] 0010   3630393853514C4E  51373532006D0012
6098SQLNQ752.m..  ......<+....._..
[ibm][db2][jcc][t4] 0020   8000000000000000
0000000000000000  ................  ................
[ibm][db2][jcc][t4] 0030   0000000000202020
2020202020202020  .....             ................
[ibm][db2][jcc][t4] 0040   0012475349464544
2020202020202020  ..GSIFED          ................
[ibm][db2][jcc][t4] 0050   202020200000001B
32FF2D323433FF33      ....2.-243.3  ................
[ibm][db2][jcc][t4] 0060   36303031FF53514C  5F43555253483230
6001.SQL_CURSH20  .......<........
[ibm][db2][jcc][t4] 0070   304331FF                          
0C1.              ....            
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: OPNQFLRM            (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0026D05200030020  2212000611490008  .&.R...
"....I..  ..}.............
[ibm][db2][jcc][t4] 0010   00162110C7E2C9C6
C5C4404040404040  ..!.......@@@@@@  ....GSIFED      
[ibm][db2][jcc][t4] 0020   404040404040                        @@@@@@                            
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: SQLCARD             (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   0074D0030003006E
24080029FDFFFF35  .t.....n$..)...5  ..}....>........
[ibm][db2][jcc][t4] 0010   3630393853514C4E  51373532006D0012
6098SQLNQ752.m..  ......<+....._..
[ibm][db2][jcc][t4] 0020   8000000000000000
0000000000000000  ................  ................
[ibm][db2][jcc][t4] 0030   0000000000202020
2020202020202020  .....             ................
[ibm][db2][jcc][t4] 0040   0012475349464544
2020202020202020  ..GSIFED          ................
[ibm][db2][jcc][t4] 0050   202020200000001B
32FF2D323433FF33      ....2.-243.3  ................
[ibm][db2][jcc][t4] 0060   36303031FF53514C  5F43555253483230
6001.SQL_CURSH20  .......<........
[ibm][db2][jcc][t4] 0070   304331FF                          
0C1.              ....            
[ibm][db2][jcc][t4]
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d]
java.sql.SQLException
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80] DB2
SQLCA from server
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlCode        = -243
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlErrd        = { -2145779603, 0, 0, 0, -400, 0 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlErrmc       = SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlErrmcTokens = { SQL_CURSH200C1 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlErrp        = SQLNQ752
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlState       = 36001
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d][Sqlca@1995d80]
SqlWarn        =            
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d] SQL state  = 36001
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d] Error code = -243
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d] Tokens     =
SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException@10bc49d] Stack trace
follows
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -243, SQLSTATE:
36001, SQLERRMC: SQL_CURSH200C1
       at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
       at com.ibm.db2.jcc.a.hd.a(hd.java:1235)
       at com.ibm.db2.jcc.c.jb.h(jb.java:149)
       at com.ibm.db2.jcc.c.jb.a(jb.java:43)
       at com.ibm.db2.jcc.c.w.a(w.java:30)
       at com.ibm.db2.jcc.c.cc.g(cc.java:160)
       at com.ibm.db2.jcc.a.hd.n(hd.java:1215)
       at com.ibm.db2.jcc.a.id.gb(id.java:1780)
       at com.ibm.db2.jcc.a.id.d(id.java:2255)
       at com.ibm.db2.jcc.a.id.X(id.java:505)
       at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3]
java.sql.SQLException
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe] DB2
SQLCA from server
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlCode        = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlErrd        = { -2146303891, 0, 0, 0, 0, 0 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlErrmc       = 2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlErrmcTokens = { 2, -243, 36001, SQL_CURSH200C1 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlErrp        = SQLNQ752
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlState       = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3][Sqlca@1ab28fe]
SqlWarn        =            
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3] SQL state  = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3] Error code = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3] Tokens     =
2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException@c4aad3] Stack trace follows
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -727, SQLSTATE:
56098, SQLERRMC: 2;-243;36001;SQL_CURSH200C1
       at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
       at com.ibm.db2.jcc.a.id.a(id.java:1717)
       at com.ibm.db2.jcc.c.jb.a(jb.java:200)
       at com.ibm.db2.jcc.c.jb.b(jb.java:88)
       at com.ibm.db2.jcc.c.w.b(w.java:60)
       at com.ibm.db2.jcc.c.dc.d(dc.java:339)
       at com.ibm.db2.jcc.a.id.db(id.java:1710)
       at com.ibm.db2.jcc.a.id.gb(id.java:1781)
       at com.ibm.db2.jcc.a.id.d(id.java:2255)
       at com.ibm.db2.jcc.a.id.X(id.java:505)
       at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738]
java.sql.SQLException
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c] DB2
SQLCA from server
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlCode        = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlErrd        = { -2146303891, 0, 0, 0, 0, 0 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlErrmc       = 2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlErrmcTokens = { 2, -243, 36001, SQL_CURSH200C1 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlErrp        = SQLNQ752
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlState       = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738][Sqlca@ce5b1c]
SqlWarn        =            
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738] SQL state  = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738] Error code = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738] Tokens     =
2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException@105738] Stack trace follows
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -727, SQLSTATE:
56098, SQLERRMC: 2;-243;36001;SQL_CURSH200C1
       at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
       at com.ibm.db2.jcc.a.hd.a(hd.java:1256)
       at com.ibm.db2.jcc.c.jb.o(jb.java:655)
       at com.ibm.db2.jcc.c.jb.j(jb.java:270)
       at com.ibm.db2.jcc.c.jb.c(jb.java:57)
       at com.ibm.db2.jcc.c.w.c(w.java:42)
       at com.ibm.db2.jcc.c.cc.h(cc.java:177)
       at com.ibm.db2.jcc.a.hd.p(hd.java:1252)
       at com.ibm.db2.jcc.a.id.d(id.java:2298)
       at com.ibm.db2.jcc.a.id.X(id.java:505)
       at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc] [t4][time:1170171024416][thread:Thread-9419][tracepoint:1
[Request.flush]
[ibm][db2][jcc][t4]        SEND BUFFER: RDBCMM                 (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F  
0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   000AD00100010004
200E              ........ .        ..}.......      
[ibm][db2][jcc][t4]
[ibm][db2][jcc] [t4][time:1170171024416][thread:Thread-9419][tracepoint:2
[Reply.fill]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: ENDUOWRM            (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F  
0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   002BD05200010025  220C000611490004  .+.R..
%"....I..  ..}.............
[ibm][db2][jcc][t4] 0010   0005211501001621
10C7E2C9C6C5C440  ..!....!.......@  .........GSIFED
[ibm][db2][jcc][t4] 0020   4040404040404040  404040            @@@@@@@@@@@                      
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: SQLCARD             (ASCII)          
(EBCDIC)
[ibm][db2][jcc][t4] 0000   000BD00300010005  2408FF            .......
$..       ..}........    
[ibm][db2][jcc][t4]

Signature

Lorne Sunley

Reply to this Message
Knut Stolze - 31 Jan 2007 17:09 GMT
>>>> I am trying to access an 8.2 database through openoffice.org version
>>>> 2.0 or 2.1. I am using the type 4 JDBC driver. Tables work OK, but when
[quoted text clipped - 33 lines]
>>
>> In the above view "rawbankdata.rbc_transactions" is a table

A view is also just a table.  In fact, everything that holds data in an
RDBMS is a table.

>> DB2 SQL error: SQLCODE: -243, SQLSTATE 36001, SQLERRMC:: SQL_CURSH200C1
>>
[quoted text clipped - 64 lines]
>> join rawbankdata.merchant_number on substr(tx_merchant, 8, 8) =
>> rb_merch_short where substr(tx_merchant, 1, 6) = 'EFT 60'

You have a JOIN here.  The explanation for SQL0243 states:

   For example, if the query includes a join, the result table is
   read-only.

DB2 won't be able to apply a reverse mapping for the join, i.e. it can't
figure out how to apply changes from insert/update/delete to the correct
underlying row(s).

So you may want to consider rephrasing your view definition in such a way
that no join occurs, for example by using subselects.  Also, have a look at
the access plan for "SELECT * FROM <view>"?  You shouldn't see temp tables
there.

> [ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419][Connection@789144]
> prepareStatement (SELECT * FROM "RAWBANKDATA"."RBC_TRANSASCTION_UNION",
> 1005, 1008) called

I guess the interesting part comes before that, namely where the statement
attributes are set.  You could change this to make it an
INSENSITIVE/ASENSITIVE cursor.  In any case, this looks to me like an
application error and you should report it to the OpenOffice developers.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message
LorneSunley - 31 Jan 2007 14:09 GMT
>>>>> I am trying to access an 8.2 database through openoffice.org version
>>>>> 2.0 or 2.1. I am using the type 4 JDBC driver. Tables work OK, but
[quoted text clipped - 122 lines]
>
>> [ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419
[Connection@789144]
>> prepareStatement (SELECT * FROM "RAWBANKDATA"."RBC_TRANSASCTION_UNION",
>> 1005, 1008) called
[quoted text clipped - 3 lines]
> INSENSITIVE/ASENSITIVE cursor.  In any case, this looks to me like an
> application error and you should report it to the OpenOffice developers.

Yes, I can see that that is probably the route to take. I had set up a
couple of simple little tables and views to test this out with and noticed
that as  soon as a join is in there the -243 error comes up. It is too bad
there is no way to tell the JDBC driver to use INSENSITIVE cursors through
a settable property (like cursorSensitivity) as that would make life much
easier.

The -243 error seems to be unique to the Type 4 JDBC driver.... If I define
an openoffice.org database that uses the Windows CLI driver through the
ODBC interface I can access the views with joins without an error of any
kind.

Signature

Lorne Sunley

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.