Re: 8.1.11 PREPARE problem? - Mailing list pgsql-general

moving on:
After digging through the JDBC source. It seems that the server is
returning a 'C':  // Command Status (end of Execute) before returning
any data rows. So it doesn't look like JDBC is causing any problems.

I've tried not running the first, and only running the second, and I get
still no results. But as usual when running by hand, I do get results.


the SQL that i'm running ends up looking like these when run by hand:

1) works as a prepared statement
select aggregationvalue.value as aggregationvalue
$value,aggregationvalue.aggregatetype as aggregationvalue
$aggregatetype,aggregationvalue.count as aggregationvalue
$count,aggregationvalue.stoptime as aggregationvalue
$stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
aggregationvalue where  date_trunc('month', aggregationvalue.stoptime)
between '2007-11-01' and '2008-12-01'  and
aggregationvalue.aggregatetype = 'MONTHLY' and
split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE'  and
split_part(aggregationvalue.value,':',2) like '%'  and
split_part(aggregationvalue.value,':',3) like '%'  ;

2) does not work as prepared statement
select aggregationvalue.value as aggregationvalue
$value,aggregationvalue.aggregatetype as aggregationvalue
$aggregatetype,aggregationvalue.count as aggregationvalue
$count,aggregationvalue.stoptime as aggregationvalue
$stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
aggregationvalue where  date_trunc('day', aggregationvalue.stoptime)
between '2008-12-18' and '2008-12-18'  and
aggregationvalue.aggregatetype = 'HOURLY' and
split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE'  and
split_part(aggregationvalue.value,':',2) like '%'  and
split_part(aggregationvalue.value,':',3) like '%'  ;





On Thu, 2008-12-18 at 11:21 -0600, Jeremiah Jahn wrote:
> This is with jdbc3-415. None of the (jdbc, or pg) change logs since then
> have mentioned this problem. When run as a prepared statement the first
> statement will execute and return results, while the next two seem to
> execute, but return no results. When run by hand, not prepared , each
> statement runs just fine. when using an older version of the jdbc driver
> (7.3?), that does not call PREPARE, things work just fine. Looking at
> the logs below, it sure looks like the statements are running, given the
> different durations, which are correct for the differences between the
> statements. help please.
>
>
>
>
> Dec 18 10:59:15 devel postgres[6889]: [2-1] LOG:  duration: 358.005 ms  statement: EXECUTE <unnamed>  [PREPARE:
selectaggregationvalue.value as 
> Dec 18 10:59:15 devel postgres[6889]: [2-2]  aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas 
> Dec 18 10:59:15 devel postgres[6889]: [2-3]  aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas 
> Dec 18 10:59:15 devel postgres[6889]: [2-4]  aggregationvalue$starttime from aggregationvalue where  date_trunc($1,
aggregationvalue.stoptime)between $2 and $3  and 
> Dec 18 10:59:15 devel postgres[6889]: [2-5]  aggregationvalue.aggregatetype = $4 and
split_part(aggregationvalue.value,':',1)= $5  and  
> Dec 18 10:59:15 devel postgres[6889]: [2-6]  split_part(aggregationvalue.value,':',2) like $6  and
split_part(aggregationvalue.value,':',3)like $7  ] 
>
> Dec 18 10:59:25 devel postgres[6889]: [4-1] LOG:  duration: 25.001 ms  statement: EXECUTE <unnamed>  [PREPARE:
selectaggregationvalue.value as 
> Dec 18 10:59:25 devel postgres[6889]: [4-2]  aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas 
> Dec 18 10:59:25 devel postgres[6889]: [4-3]  aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas 
> Dec 18 10:59:25 devel postgres[6889]: [4-4]  aggregationvalue$starttime from aggregationvalue where  date_trunc($1,
aggregationvalue.stoptime)between $2 and $3  and 
> Dec 18 10:59:25 devel postgres[6889]: [4-5]  aggregationvalue.aggregatetype = $4 and
split_part(aggregationvalue.value,':',1)= $5  and  
> Dec 18 10:59:25 devel postgres[6889]: [4-6]  split_part(aggregationvalue.value,':',2) like $6  and
split_part(aggregationvalue.value,':',3)like $7  ] 
>
> Dec 18 10:59:25 devel postgres[6889]: [6-1] LOG:  duration: 2.000 ms  statement: EXECUTE <unnamed>  [PREPARE:
selectaggregationvalue.value as 
> Dec 18 10:59:25 devel postgres[6889]: [6-2]  aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas 
> Dec 18 10:59:25 devel postgres[6889]: [6-3]  aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas 
> Dec 18 10:59:25 devel postgres[6889]: [6-4]  aggregationvalue$starttime from aggregationvalue where  date_trunc($1,
aggregationvalue.stoptime)between $2 and $3  and 
> Dec 18 10:59:25 devel postgres[6889]: [6-5]  aggregationvalue.aggregatetype = $4 and
split_part(aggregationvalue.value,':',1)= $5  and  
> Dec 18 10:59:25 devel postgres[6889]: [6-6]  split_part(aggregationvalue.value,':',2) like $6  and
split_part(aggregationvalue.value,':',3)like $7  ] 
>