Re: escape string for pgsql (using jdbc/java)?

[Dave Cramer <pg(at)fastcrypt(dot)com>]

On 29-Jan-07, at 7:15 PM, Tobias Thierer wrote:

> Hi,
>
> Dave Cramer wrote:
>
> > > I'm not so worried about the performance. But if I have a column  
> > > of type SERIAL in my table, then I can retrieve the generated  
> > > value using:
> [...]
>
> > You could if that worked in postgresql but getGeneratedKeys does  
> > not currently work
>
> Yes, I was actually just about to post a follow-up to my previous  
> message because I just found out that pgsql doesn't support this.
>
> > > whereas it is not clear to me how this works with a prepared  
> > > statement. Strangely, PreparedStatement extends Statement, so  
> > > PreparedStatement still has the executeUpdate(String,int) method  
> > > - but it is not clear to me whether this method will throw the  
> > > previously prepared statement away or what.
> > >
> > > Is there any easy way to retrieve the generated value for the  
> > > SERIAL column when using a prepared statement?
> > Yes, if it worked it would work with prepared statements.
>
> Yes, I just found that out too.
>
> But given that pgsql doesn't allow me to immediately retrieve the  
> value of the autogenerated key, how can I (or can't I!?)? If I made a
>
>    SELECT MAX(id)
>
> statement right afterwards then I couldn't be sure that that is the  
> id just generated, because due to concurrency someone else may have  
> just submitted an entry too, right?
select currval('sequence name') is how it's done as long as you use  
the exact same connection.

alternatively you can increment the sequence before hand select  
nextval('sequence name') and insert the value

and if you are using 8.2 you can do 'insert into foo (cols) values  
(vals) returning id

Dave
> How are these things done in pgsql?
>
>   Tobias
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match