Обсуждение: Help:updateRow() with CIDR types
Hi,
I'm having some trouble updating CIDR types using jdbc, I use
updateObject() to change it, which goes OK, but when I do an
updateRow(), I get this error
java.sql.SQLException: ERROR: invalid cidr value: "1.1.1.1/4"
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2Resul
tSet.java:1117)
in other circumstances I get an SQLException with no getMessage()
value, I can't quite put my finger on why the error sometimes changes.
I can change them command-line style no problem.
I am using version 7.5 build 301, the earlier 7.4 driver was worse, it
just returned null for all the values.
Any ideas?
Thanks very much for any help.
Moray
Moray Taylor wrote: > Hi, > > I'm having some trouble updating CIDR types using jdbc, I use > updateObject() to change it, which goes OK, but when I do an > updateRow(), I get this error > > java.sql.SQLException: ERROR: invalid cidr value: "1.1.1.1/4" > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2Resul > tSet.java:1117) What object type and SQL typecode are you passing to updateObject()? Can we see that bit of code? Turn on log_statement on the backend; what is the entire query that errors? From memory the driver doesn't really have support for CIDR types other than as raw strings, but that might have changed.. -O
On 23 Apr 2004, at 01:29, Oliver Jowett wrote:
> Moray Taylor wrote:
>> Hi,
>> I'm having some trouble updating CIDR types using jdbc, I use
>> updateObject() to change it, which goes OK, but when I do an
>> updateRow(), I get this error
>> java.sql.SQLException: ERROR: invalid cidr value: "1.1.1.1/4"
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2Res
>> ul tSet.java:1117)
>
> What object type and SQL typecode are you passing to updateObject()?
> Can we see that bit of code?
>
> Turn on log_statement on the backend; what is the entire query that
> errors?
>
> From memory the driver doesn't really have support for CIDR types
> other than as raw strings, but that might have changed..
I was using updateObject(), I changed it to updateString, I still get
an SQLException, with no getMessage() data, however, the data IS
entered into the database, but the ResultSet isn't updated, i.e. I have
to requery to see the results. Any ideas?
My code is pretty much this
String CIDR = "1.1.1.1/6";// just an example one
ResultSet RS = <code that gets resultset>
RS.absolute(<the row>);
RS.updateString("IP",CIDR);
RS.updateRow();// errors at this point, data is entered into the DB
though.
My code works perfectly for all the other types I've used, so it's
pretty much a CIDR thing I think.
Thanks for the reply.
Moray
Moray Taylor wrote: > I was using updateObject(), I changed it to updateString, I still get > an SQLException, with no getMessage() data, Can you post the exception you get, including stacktrace? What is the query and error (if any) logged on the backend? (it looks like updateRow's exception rethrow behaviour is pretty lousy -- it should probably be catching and rethrowing SQLExceptions unchanged) > however, the data IS > entered into the database, but the ResultSet isn't updated, i.e. I have > to requery to see the results. Any ideas? I'd guess that the exception is causing the row update logic not to update the resultset's version of the data. -O
On Sat, 24 Apr 2004, Moray Taylor wrote: > > On 23 Apr 2004, at 01:29, Oliver Jowett wrote: > > > Moray Taylor wrote: > >> Hi, > >> I'm having some trouble updating CIDR types using jdbc, I use > >> updateObject() to change it, which goes OK, but when I do an > >> updateRow(), I get this error > >> java.sql.SQLException: ERROR: invalid cidr value: "1.1.1.1/4" > >> at > >> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2Res > >> ul tSet.java:1117) > > Well this first error is backend error with detail of "Value has bits set to right of mask." Using a valid cidr value such as 192.168.1.0/24 produces the driver error I imagine you are complaining about. > I was using updateObject(), I changed it to updateString, I still get > an SQLException, with no getMessage() data, however, the data IS > entered into the database, but the ResultSet isn't updated, i.e. I have > to requery to see the results. Any ideas? > I have applied the attached patch to fix this to the cvs and stable branches. Binary versions are available here: http://www.ejurka.com/pgsql/jars/ Kris Jurka
Вложения
On 24 Apr 2004, at 02:57, Kris Jurka wrote: > > > On Sat, 24 Apr 2004, Moray Taylor wrote: > >> >> On 23 Apr 2004, at 01:29, Oliver Jowett wrote: >> >>> Moray Taylor wrote: >>>> Hi, >>>> I'm having some trouble updating CIDR types using jdbc, I use >>>> updateObject() to change it, which goes OK, but when I do an >>>> updateRow(), I get this error >>>> java.sql.SQLException: ERROR: invalid cidr value: "1.1.1.1/4" >>>> at >>>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2R >>>> es >>>> ul tSet.java:1117) >>> > > Well this first error is backend error with detail of "Value has bits > set > to right of mask." Using a valid cidr value such as 192.168.1.0/24 > produces the driver error I imagine you are complaining about. > >> I was using updateObject(), I changed it to updateString, I still get >> an SQLException, with no getMessage() data, however, the data IS >> entered into the database, but the ResultSet isn't updated, i.e. I >> have >> to requery to see the results. Any ideas? >> > > I have applied the attached patch to fix this to the cvs and stable > branches. Binary versions are available here: > http://www.ejurka.com/pgsql/jars/ > > Kris Jurka<updatecidr.patch> > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly Hi, Thanks very much for the patch, but I'm having a problem with the updated jars, I am getting a lot of these errors... ERROR:Operation requires a scrollable resultset, but this resultset is FORWARD_ONLY. org.postgresql.util.PSQLException: Operation requires a scrollable resultset, but this resultset is FORWARD_ONLY. at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkScrollable(AbstractJdbc 2ResultSet.java:179) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.first(AbstractJdbc2ResultSet .java:258) There errors occur when I do a getFirst() on the resultsets that get create by methods like getCatalogs(), and getColumns() Has something else changed in the CVS that would cause this, I never had these errors with the 7.5 (build 301) resultsets. Thanks very much for your assistance. Moray
On Sat, 24 Apr 2004, Moray Taylor wrote: > Thanks very much for the patch, but I'm having a problem with the > updated jars, I am getting a lot of these errors... > > ERROR:Operation requires a scrollable resultset, but this resultset is > FORWARD_ONLY. > org.postgresql.util.PSQLException: Operation requires a scrollable > resultset, but this resultset is FORWARD_ONLY. > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkScrollable(AbstractJdbc > 2ResultSet.java:179) > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.first(AbstractJdbc2ResultSet > .java:258) > > There errors occur when I do a getFirst() on the resultsets that get > create by methods like getCatalogs(), and getColumns() > > Has something else changed in the CVS that would cause this, I never > had these errors with the 7.5 (build 301) resultsets. > The cvs driver has been tightened up to default to TYPE_FORWARD_ONLY ResultSets per spec to try and increase the number of queries we can use cursor based fetches on. Since it is a TYPE_FORWARD_ONLY ResultSet your getFirst() call fails because that only works on scrollable ResultSets. I don't think the TYPE_FORWARD_ONLY default should apply to the MetaData where the results are usually small and we want to allow the most flexibility in navigating them. Please try the attached patch or the newly uploaded jar files at http://www.ejurka.com/pgsql/jars/ Kris Jurka
Вложения
On 24 Apr 2004, at 22:02, Kris Jurka wrote: > > > On Sat, 24 Apr 2004, Moray Taylor wrote: > >> Thanks very much for the patch, but I'm having a problem with the >> updated jars, I am getting a lot of these errors... >> >> ERROR:Operation requires a scrollable resultset, but this resultset is >> FORWARD_ONLY. >> org.postgresql.util.PSQLException: Operation requires a scrollable >> resultset, but this resultset is FORWARD_ONLY. >> at >> org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkScrollable(AbstractJd >> bc >> 2ResultSet.java:179) >> at >> org.postgresql.jdbc2.AbstractJdbc2ResultSet.first(AbstractJdbc2ResultS >> et >> .java:258) >> >> There errors occur when I do a getFirst() on the resultsets that get >> create by methods like getCatalogs(), and getColumns() >> >> Has something else changed in the CVS that would cause this, I never >> had these errors with the 7.5 (build 301) resultsets. >> > > The cvs driver has been tightened up to default to TYPE_FORWARD_ONLY > ResultSets per spec to try and increase the number of queries we can > use > cursor based fetches on. Since it is a TYPE_FORWARD_ONLY ResultSet > your > getFirst() call fails because that only works on scrollable ResultSets. > I don't think the TYPE_FORWARD_ONLY default should apply to the > MetaData > where the results are usually small and we want to allow the most > flexibility in navigating them. > > Please try the attached patch or the newly uploaded jar files at > http://www.ejurka.com/pgsql/jars/ > > Kris Jurka > <dbmdrstype.patch> Does this mean the absolute() method will not work on any ResultSet that I get from a query? absolute() row access is totally critical to my application, will I need to stick with 7.5 build 301? Best regards Moray
Moray Taylor wrote: > Does this mean the absolute() method will not work on any ResultSet > that I get from a query? > > absolute() row access is totally critical to my application, will I > need to stick with 7.5 build 301? If you want to use absolute() (or relative(), first(), last(), prev()), you must specify TYPE_SCROLLABLE_xxx when creating the statement, per the spec. The default is TYPE_FORWARD_ONLY, which as the name implies creates resultsets that can only be iterated through forwards using next(). See the ResultSet javadoc for details: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html -O
On 25 Apr 2004, at 00:43, Oliver Jowett wrote: <excerpt>Moray Taylor wrote: <excerpt>Does this mean the absolute() method will not work on any ResultSet that I get from a query? absolute() row access is totally critical to my application, will I need to stick with 7.5 build 301? </excerpt> If you want to use absolute() (or relative(), first(), last(), prev()), you must specify TYPE_SCROLLABLE_xxx when creating the statement, per the spec. The default is TYPE_FORWARD_ONLY, which as the name implies creates resultsets that can only be iterated through forwards using next(). See the ResultSet javadoc for details: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html -O </excerpt> I use this code, <fixed>stmt = DBConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);</fixed> but still get this error ERROR:Operation requires a scrollable resultset, but this resultset is FORWARD_ONLY. org.postgresql.util.PSQLException: Operation requires a scrollable resultset, but this resultset is FORWARD_ONLY. do I need to specify anything else in the statment? Thanks v much Moray On 25 Apr 2004, at 00:43, Oliver Jowett wrote: > Moray Taylor wrote: >> Does this mean the absolute() method will not work on any ResultSet >> that I get from a query? >> absolute() row access is totally critical to my application, will I >> need to stick with 7.5 build 301? > > If you want to use absolute() (or relative(), first(), last(), > prev()), you must specify TYPE_SCROLLABLE_xxx when creating the > statement, per the spec. The default is TYPE_FORWARD_ONLY, which as > the name implies creates resultsets that can only be iterated through > forwards using next(). See the ResultSet javadoc for details: > > http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html > > -O > I use this code, stmt = DBConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet .CONCUR_UPDATABLE); but still get this error ERROR:Operation requires a scrollable resultset, but this resultset is FORWARD_ONLY. org.postgresql.util.PSQLException: Operation requires a scrollable resultset, but this resultset is FORWARD_ONLY. do I need to specify anything else in the statment? Thanks v much Moray
Hi, I'd like to say a big thanks to Kris and Oliver for all their help in resolving my CIDR problems, my app works perfectly now, CIDR types included! Thanks again Moray