I believe the problem is that you are calling rs.first() on a default
prepared statement. The API states that the method throws an
"SQLException - if a database access error occurs or the result set type
is TYPE_FORWARD_ONLY." Try setting your result set type to a scrollable
one.
JB.
-----Original Message-----
From: Helmut Leininger [mailto:h.leininger@stripped]
Sent: Wednesday, May 21, 2003 8:36 AM
To: java@stripped
Subject: PreparedStatement gives always empty Resultset
Hi,
I am using MySQL 4.1.0 alpha
Connector/J 3.1, nightly 20030501
Problem description:
I prepare a Statement
String sql = "select art from FahrtArten where FahrtArt = ?";
PreparedStatement pstmt = ccon.getPreparedStatement(sql);
Later on I use it:
String key = "dienstlich";
pstmt.setString(1, key);
ResultSet rs = pstmt.executeQuery();
if (rs.first()) {
retString = rs.getString(1);
}
rs.close();
The ResultSet appears to be empty (the rs.first path is not taken,
neither an eventual while (rs.next()) loop.
Note: In the bindings I can see the value "dienstlich"
If I use a normal Statement like
stmt.executeQuery("select art from FahrtArten where FahrtArt =
'dienstlich'";
instead of the PreparedStatement, everything is ok.
Thanks for your help.
Regards
Helmut
h.leininger@stripped
Contents of table FahrtArten:
mysql> select art, FahrtArt from FahrtArten;
+-----+------------+
| art | FahrtArt |
+-----+------------+
| d | dienstlich |
| w | Whg / Fa |
| p | privat |
+-----+------------+
3 rows in set (0.00 sec)
Extract from logfile:
9 Query describe FahrtenBuch Kommentar
9 Prepare select art from FahrtArten where
FahrtArt = ?
9 Prepare select art from AusgabenArten where
AusgArt = ?
>>> here is the execution of the prepared statement
(select, not shown
??) <<<<
030521 11:32:20 9 Query insert into FahrtenBuch set DatumVon
=
'2003-05-23', DatumBis = NULL, Weg = 'sdfcv', kmStand = 55558, Art =
'null', Kommentar = NULL
Extract from tracefile (not exactly the same try, but equivalent):
T@6 : | <vio_read
T@6 : | info: Command on TCP/IP (17616) = 23 (Prepare Execute)
T@6 : <do_command
T@6 : >dispatch_command
T@6 : | >mysql_stmt_execute
T@6 : | | >find_prepared_statement
T@6 : | | | enter: stmt_id: 1
T@6 : | | <find_prepared_statement
T@6 : | | >setup_params_data
T@6 : | | <setup_params_data
T@6 : | | >mysql_execute_command
T@6 : | | | >mysql_reset_errors
T@6 : | | | | >free_root
T@6 : | | | | | enter: root: 1162830 flags: 0
T@6 : | | | | <free_root
T@6 : | | | <mysql_reset_errors
T@6 : | | | >open_tables
T@6 : | | | | >open_table
T@6 : | | | | | >hash_search
T@6 : | | | | | | exit: found key at 2
T@6 : | | | | | <hash_search
T@6 : | | | | <open_table
T@6 : | | | <open_tables
T@6 : | | | >mysql_lock_tables
T@6 : | | | | >_mymalloc
T@6 : | | | | | enter: Size: 24
T@6 : | | | | | exit: ptr: 1163fd8
T@6 : | | | | <_mymalloc
T@6 : | | | | >lock_external
T@6 : | | | | | >mi_lock_database
T@6 : | | | | | | >my_lock
T@6 : | | | | | | | my: Fd: 6 Op: 2 start: 0 Length: 1073741823
MyFlags: 32
T@6 : | | | | | | <my_lock
T@6 : | | | | | <mi_lock_database
T@6 : | | | | <lock_external
T@6 : | | | | >thr_multi_lock
T@6 : | | | | | lock: data: 1163fe8 count: 1
T@6 : | | | | | >thr_lock
T@6 : | | | | | | lock: data: 1130654 thread: 6 lock: 116167c
type: 1
T@6 : | | | | | | >mi_get_status
T@6 : | | | | | | | info: key_file: 2048 data_file: 104
T@6 : | | | | | | <mi_get_status
T@6 : | | | | | <thr_lock
T@6 : | | | | <thr_multi_lock
T@6 : | | | <mysql_lock_tables
T@6 : | | | >Query_cache::store_query
T@6 : | | | <Query_cache::store_query
T@6 : | | | >mysql_select
T@6 : | | | | >JOIN::prepare
T@6 : | | | | | >setup_tables
T@6 : | | | | | <setup_tables
T@6 : | | | | | >setup_fields
T@6 : | | | | | <setup_fields
T@6 : | | | | | >setup_conds
T@6 : | | | | | <setup_conds
T@6 : | | | | | >setup_procedure
T@6 : | | | | | <setup_procedure
T@6 : | | | | <JOIN::prepare
T@6 : | | | | >JOIN::optimize
WHERE:(original) (FahrtArten.FahrtArt = ?)
WHERE:(after const change) (FahrtArten.FahrtArt = ?)
WHERE:(after remove) (FahrtArten.FahrtArt = ?)
T@6 : | | | | | >get_sort_by_table
T@6 : | | | | | <get_sort_by_table
T@6 : | | | | | >make_join_statistics
T@6 : | | | | | | >mi_status
T@6 : | | | | | | <mi_status
T@6 : | | | | | | >init_dynamic_array
T@6 : | | | | | | | >_mymalloc
T@6 : | | | | | | | | enter: Size: 400
T@6 : | | | | | | | | exit: ptr: 11966d0
T@6 : | | | | | | | <_mymalloc
T@6 : | | | | | | <init_dynamic_array
T@6 : | | | | | | >find_best_combination
T@6 : | | | | | | | best: read_time: 1 record_count: 3
T@6 : | | | | | | <find_best_combination
T@6 : | | | | | <make_join_statistics
T@6 : | | | | | >make_select
T@6 : | | | | | <make_select
T@6 : | | | | | >make_join_select
WHERE:(FahrtArten) (FahrtArten.FahrtArt = ?)
T@6 : | | | | | <make_join_select
T@6 : | | | | | >remove_const
T@6 : | | | | | | exit: simple_order: 1
T@6 : | | | | | <remove_const
T@6 : | | | | | >remove_const
T@6 : | | | | | | exit: simple_order: 1
T@6 : | | | | | <remove_const
T@6 : | | | | | >remove_const
T@6 : | | | | | | exit: simple_order: 1
T@6 : | | | | | <remove_const
T@6 : | | | | | >make_join_readinfo
T@6 : | | | | | <make_join_readinfo
T@6 : | | | | | >TEST_join
Info about JOIN
FahrtArten type: ALL q_keys: 0 refs: 0 key: -1 len: 0
select used
T@6 : | | | | | <TEST_join
T@6 : | | | | <JOIN::optimize
T@6 : | | | | >JOIN::exec
T@6 : | | | | | >do_select
T@6 : | | | | | | >send_fields
T@6 : | | | | | | | packet_header: Memory: 378ee6c Bytes: (4)
01 00 00 01
T@6 : | | | | | | | >_mymalloc
T@6 : | | | | | | | | enter: Size: 16
T@6 : | | | | | | | | exit: ptr: 117b568
T@6 : | | | | | | | <_mymalloc
T@6 : | | | | | | | >Protocol::write
T@6 : | | | | | | | <Protocol::write
T@6 : | | | | | | | packet_header: Memory: 378ee04 Bytes: (4)
35 00 00 02
T@6 : | | | | | | | >send_eof
T@6 : | | | | | | | | packet_header: Memory: 378edec Bytes: (4)
05 00 00 03
T@6 : | | | | | | | | >net_flush
T@6 : | | | | | | | | | >vio_is_blocking
T@6 : | | | | | | | | | | exit: 1
T@6 : | | | | | | | | | <vio_is_blocking
T@6 : | | | | | | | | | >net_real_write
T@6 : | | | | | | | | | | >vio_write
T@6 : | | | | | | | | | | | enter: sd=17616, buf=01183710, size=71
T@6 : | | | | | | | | | | | exit: 71
T@6 : | | | | | | | | | | <vio_write
T@6 : | | | | | | | | | <net_real_write
T@6 : | | | | | | | | <net_flush
T@6 : | | | | | | | <send_eof
T@6 : | | | | | | <send_fields
T@6 : | | | | | | >_myfree
T@6 : | | | | | | | enter: ptr: 117b568
T@6 : | | | | | | <_myfree
T@6 : | | | | | | >init_read_record
T@6 : | | | | | | | info: using rr_sequential
T@6 : | | | | | | | >mi_scan_init
T@6 : | | | | | | | <mi_scan_init
T@6 : | | | | | | | >mi_extra
T@6 : | | | | | | | | enter: function: 3
T@6 : | | | | | | | | >init_io_cache
T@6 : | | | | | | | | | enter: cache: 11305b0 type: 0 pos: 0
T@6 : | | | | | | | | | >my_seek
T@6 : | | | | | | | | | | my: Fd: 7 Hpos: 0 Pos: 0 Whence: 2
MyFlags: 0
T@6 : | | | | | | | | | | exit: pos: 104
T@6 : | | | | | | | | | <my_seek
T@6 : | | | | | | | | | >_mymalloc
T@6 : | | | | | | | | | | enter: Size: 16384
T@6 : | | | | | | | | | | exit: ptr: 11968a8
T@6 : | | | | | | | | | <_mymalloc
T@6 : | | | | | | | | | info: init_io_cache: cachesize = 16384
T@6 : | | | | | | | | <init_io_cache
T@6 : | | | | | | | <mi_extra
T@6 : | | | | | | <init_read_record
T@6 : | | | | | | >mi_scan
T@6 : | | | | | | <mi_scan
T@6 : | | | | | | >_mi_read_rnd_dynamic_record
T@6 : | | | | | | | >_mi_read_cache
T@6 : | | | | | | | | >_my_b_read
T@6 : | | | | | | | | | >my_seek
T@6 : | | | | | | | | | | my: Fd: 7 Hpos: 0 Pos: 0 Whence: 0
MyFlags: 0
T@6 : | | | | | | | | | <my_seek
T@6 : | | | | | | | | | >my_read
T@6 : | | | | | | | | | | my: Fd: 7 Buffer: 11968a8 Count: 104
MyFlags: 32
T@6 : | | | | | | | | | <my_read
T@6 : | | | | | | | | <_my_b_read
T@6 : | | | | | | | <_mi_read_cache
T@6 : | | | | | | | header: Memory: 378ee10 Bytes: (20)
03 00 23 01 03 FE 64 0A 64 69 65 6E 73 74 6C 69 63 68 14 44 <<<--
these
are values in table FahrtArten
T@6 : | | | | | | | >_mi_read_cache
T@6 : | | | | | | | <_mi_read_cache
T@6 : | | | | | | | >_mi_rec_unpack
T@6 : | | | | | | | <_mi_rec_unpack
T@6 : | | | | | | <_mi_read_rnd_dynamic_record
T@6 : | | | | | | >mi_scan
T@6 : | | | | | | <mi_scan
T@6 : | | | | | | >_mi_read_rnd_dynamic_record
T@6 : | | | | | | | >_mi_read_cache
T@6 : | | | | | | | <_mi_read_cache
T@6 : | | | | | | | header: Memory: 378ee68 Bytes: (20)
03 00 20 00 03 FE 77 08 57 68 67 20 2F 20 46 61 13 57 65 67
T@6 : | | | | | | | >_mi_read_cache
T@6 : | | | | | | | <_mi_read_cache
T@6 : | | | | | | | >_mi_rec_unpack
T@6 : | | | | | | | <_mi_rec_unpack
T@6 : | | | | | | <_mi_read_rnd_dynamic_record
T@6 : | | | | | | >mi_scan
T@6 : | | | | | | <mi_scan
T@6 : | | | | | | >_mi_read_rnd_dynamic_record
T@6 : | | | | | | | >_mi_read_cache
T@6 : | | | | | | | <_mi_read_cache
T@6 : | | | | | | | header: Memory: 378ee68 Bytes: (20)
03 00 16 02 03 FE 70 06 70 72 69 76 61 74 0B 50 72 69 76 61
T@6 : | | | | | | | >_mi_read_cache
T@6 : | | | | | | | <_mi_read_cache
T@6 : | | | | | | | >_mi_rec_unpack
T@6 : | | | | | | | <_mi_rec_unpack
T@6 : | | | | | | <_mi_read_rnd_dynamic_record
T@6 : | | | | | | >mi_scan
T@6 : | | | | | | <mi_scan
T@6 : | | | | | | >_mi_read_rnd_dynamic_record
T@6 : | | | | | | | >_mi_writeinfo
T@6 : | | | | | | | <_mi_writeinfo
T@6 : | | | | | | <_mi_read_rnd_dynamic_record
T@6 : | | | | | | >end_send
T@6 : | | | | | | <end_send
T@6 : | | | | | | >join_free
T@6 : | | | | | | | >free_io_cache
T@6 : | | | | | | | <free_io_cache
T@6 : | | | | | | | >close_cached_file
T@6 : | | | | | | | <close_cached_file
T@6 : | | | | | | | >_myfree
T@6 : | | | | | | | | enter: ptr: 0
T@6 : | | | | | | | <_myfree
T@6 : | | | | | | | >mi_extra
T@6 : | | | | | | | | enter: function: 4
T@6 : | | | | | | | | >end_io_cache
T@6 : | | | | | | | | | >_flush_io_cache
T@6 : | | | | | | | | | <_flush_io_cache
T@6 : | | | | | | | | | >_myfree
T@6 : | | | | | | | | | | enter: ptr: 11968a8
T@6 : | | | | | | | | | <_myfree
T@6 : | | | | | | | | <end_io_cache
T@6 : | | | | | | | <mi_extra
T@6 : | | | | | | | >mysql_unlock_read_tables
T@6 : | | | | | | | | >thr_multi_unlock
T@6 : | | | | | | | | | lock: data: 1163fe8 count: 1
T@6 : | | | | | | | | | >thr_unlock
T@6 : | | | | | | | | | | lock: data: 1130654 thread: 6 lock:
116167c
T@6 : | | | | | | | | | | lock: No waiting read locks to free
T@6 : | | | | | | | | | <thr_unlock
T@6 : | | | | | | | | <thr_multi_unlock
T@6 : | | | | | | | | >unlock_external
T@6 : | | | | | | | | | >mi_lock_database
T@6 : | | | | | | | | | | >my_lock
T@6 : | | | | | | | | | | | my: Fd: 6 Op: 0 start: 0 Length:
1073741823 MyFlags: 48
T@6 : | | | | | | | | | | <my_lock
T@6 : | | | | | | | | | <mi_lock_database
T@6 : | | | | | | | | <unlock_external
T@6 : | | | | | | | <mysql_unlock_read_tables
T@6 : | | | | | | <join_free
T@6 : | | | | | | >mysql_unlock_tables
T@6 : | | | | | | | >_myfree
T@6 : | | | | | | | | enter: ptr: 1163fd8
T@6 : | | | | | | | <_myfree
T@6 : | | | | | | <mysql_unlock_tables
T@6 : | | | | | | >send_eof
T@6 : | | | | | | | packet_header: Memory: 378ef58 Bytes: (4)
05 00 00 04
T@6 : | | | | | | | >net_flush
T@6 : | | | | | | | | >vio_is_blocking
T@6 : | | | | | | | | | exit: 1
T@6 : | | | | | | | | <vio_is_blocking
T@6 : | | | | | | | | >net_real_write
T@6 : | | | | | | | | | >vio_write
T@6 : | | | | | | | | | | enter: sd=17616, buf=01183710, size=9
T@6 : | | | | | | | | | | exit: 9
T@6 : | | | | | | | | | <vio_write
T@6 : | | | | | | | | <net_real_write
T@6 : | | | | | | | <net_flush
T@6 : | | | | | | <send_eof
T@6 : | | | | | | info: 0 records output
T@6 : | | | | | <do_select
T@6 : | | | | <JOIN::exec
T@6 : | | | | >JOIN::cleanup
T@6 : | | | | | >join_free
T@6 : | | | | | <join_free
T@6 : | | | | | >close_cached_file
T@6 : | | | | | <close_cached_file
T@6 : | | | | | >_myfree
T@6 : | | | | | | enter: ptr: 11966d0
T@6 : | | | | | <_myfree
T@6 : | | | | <JOIN::cleanup
T@6 : | | | <mysql_select
T@6 : | | <mysql_execute_command
T@6 : | <mysql_stmt_execute
T@6 : | >close_thread_tables
T@6 : | | info: thd->open_tables=0116E968
T@6 : | | >close_thread_table
T@6 : | | | >mi_extra
T@6 : | | | | enter: function: 2
T@6 : | | | <mi_extra
T@6 : | | <close_thread_table
T@6 : | <close_thread_tables
T@6 : | >free_root
T@6 : | | enter: root: 11627f0 flags: 1
T@6 : | <free_root
T@6 : <dispatch_command
T@6 : >do_command
T@6 : | >vio_is_blocking
T@6 : | | exit: 1
T@6 : | <vio_is_blocking
T@6 : | >vio_read
T@6 : | | enter: sd=17616, buf=01183710, size=4
T@5 : | | exit: 4
T@5 : | <vio_read
T@5 : | >vio_read
T@5 : | | enter: sd=17596, buf=01159EE0, size=1
T@5 : | | exit: 1
T@5 : | <vio_read
T@5 : | info: Command on TCP/IP (17596) = 10 (Processlist)
T@5 : <do_command
T@5 : >dispatch_command
T@5 : | >net_printf
T@5 : | | enter: message: 1227
T@5 : | | >query_cache_abort
T@5 : | | <query_cache_abort
T@5 : | | >vio_is_blocking
T@5 : | | | exit: 1
T@5 : | | <vio_is_blocking
T@5 : | | >net_real_write
T@5 : | | | >vio_write
T@5 : | | | | enter: sd=17596, buf=01159EE0, size=80
T@5 : | | | | exit: 80
T@5 : | | | <vio_write
T@5 : | | <net_real_write
T@5 : | <net_printf
T@5 : | >free_root
T@5 : | | enter: root: 1167f20 flags: 1
T@5 : | <free_root
T@5 : <dispatch_command
T@5 : >do_command
T@5 : | >vio_is_blocking
T@5 : | | exit: 1
T@5 : | <vio_is_blocking
T@5 : | >vio_read
T@5 : | | enter: sd=17596, buf=01159EE0, size=4
T@6 : | | exit: 4
T@6 : | <vio_read
T@6 : | >vio_read
T@6 : | | enter: sd=17616, buf=01183710, size=135
T@6 : | | exit: 135
T@6 : | <vio_read
T@6 : | info: Command on TCP/IP (17616) = 3 (Query)
T@6 : <do_command
T@6 : >dispatch_command
T@6 : | query: insert into FahrtenBuch set DatumVon = '2003-05-23',
DatumBis = NULL, Weg = 'asdcsdc', kmStand = 66666, Art = 'null',
Kommentar
= NULL
T@6 : | >mysql_parse
--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:
http://lists.mysql.com/java?unsub=1
