I recently set up PostgreSQL (version 7.4) on a linux box. I successfully set up the jdbc connection for Servoy which is running on a Mac. I then built a form in servoy using columns from a table I had already setup. All was going well until I tried to create a new record and save it by creating a second record. This is unscripted, I’m just using the new record button in Servey. I get this exception:
java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported
After reading the “directions”, I learned that Postgres version 7.3 and earlier allowed ‘SET AUTOCOMMIT TO OFF’ , but 7.4 requires: ‘\set AUTOCOMMIT off’ (at least with the psql). I assume the same SQL sent over jdbc would work.
Is there some way I can modify this so tyhat servoy doesn’t send the incorrect SQL statement? It appears to be either a bug in Servoy or that Servoy doesn’t yet support the current release of PostgreSQL. I really don’t want to downgrade as the compile, install, and configure takes a lot of time. Suggestions? Workarounds?
I just reread the section “migration issues” in the PostgreSQL 7.4 release notes made on Nov 17 (okay, Servoy, we’ll give you up to 2 weeks to support every new RDBMS out there so you still have a couple of days to fix this).
"The server-side autocommit setting was removed and reimplemented in client applications and languages. Server-side autocommit was causing too many problems with languages and applications that wanted to control their own autocommit behavior, so autocommit was removed from the server and added to individual client APIs as appropriate.
we we use a jdbc call to set autocommit off, if you download the latest postgress driver you will see it all works again. (the driver talks the dialect of db, we are using general method and do not fire any of specific SQL to dbs)
Thanks, Jan. I’m glad I didn’t post this in the bugs section! However, I assume I have the most recent release of the driver installed, i.e. the version that I installed was done some by compiling postgres 7.4 with the ‘–with-jdbc’ option. This should include 7.3.4 build 113 release in August, right? I can’t find the actual installed version number of the driver. Anyway, are you suggesting that I use the JDBC 7.4beta version or should I make sure I have 7.3.4 build 113?
Bummer. I just spent the past 3 hours rebuilding and configuring postgresql 7.4 with the newest jdbc driver, i.e the 7.4beta driver. I know a lot more about Postgres now, but I don’t have this time to spend on this. The new driver also causes servoy to fail with the same autocommit exception. I have now tried the newest stable release of the jdbc driver and the newest development version. I get the same error.
After thinking more about this, I have a hard time seeing how the jdbc driver version is the issue. This is not a bug. PG no longer allows supports the “set autocommit off” SQL statement. Are you saying their is a driver that somehow works around the very thing the server is trying to prevent?
Do you know of anyone else using Servoy successfuly with PostgreSQL 7.4? It was only released 12 days ago.
Can anyone tell me which of these is the answer:
Bob you screwed up your build of jdbc somehow. Servoy has been tested with PostgresSQL 7.4. Try again.
Servoy probably does not work with PostgresSQL 7.4’s change in the autocommit setting. Wait for a fix from Servoy, or drop back to Postgres 7.3.
If the answer is #2, when do you think you would have a fix - even if it is in the beta version? Postgres 7.4 is the first new release in over a year and it offers significant performance boosts in many areas over 7.3. I hate to miss out on that power.
As said before option 2 is not possible for us, the postgress people have to update there jdbc driver as well, we do not fire any db specific SQL to any db.
Jan Blok:
As said before option 2 is not possible for us, the postgress people have to update there jdbc driver as well, we do not fire any db specific SQL to any db.
Thank’s Jan. I think I’ve got it. Servoy is sending this to jdbc:
Connection.setAutoCommit(false)
My jdbc driver then translates that to:
SET AUTOCOMMIT TO OFF
My database returns this to stderr:
ERROR: SET AUTOCOMMIT TO OFF is no longer supported
My jdbc driver doesn’tr know that it can’t send that SQL to postgres anymore. It is clearly not a Servoy issue.
Has anyone successfully used postgresql7.4 with servoy? If so, it’s my install. If not, I’ll revert to an earlier version of Postgres.
I can answer the question I earlier asked. Most likeley, no one is using PostgreSQL 7.4 with servoy. I joined the postgres jdbc list and asked the question about this and was told that this is likely a bug. The developer wants some test code, so I’m suggesting he install the demo of Servoy. I’ll keep you posted.
Jan, I’ve been posting on the postgreSQL list and arguing that the issue has to be a bug in the jdbc driver. I’m not making any friends over there. They want a stack trace showing the problem as some believe Servoy may have a “naively implemented transaction model”. I shudder at the thought. How can I generate a stack trace for them? I can provide you with a connection to me postgres 7.4 server if that would help.
java.sql.SQLException: no transaction in progress
java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported
ERROR: SET AUTOCOMMIT TO OFF is no longer supported
It looks like Servoy is acting as if there is a transaction already started, but the database isn’t in agreement.
The postgresql driver that ships with Servoy does not work with PostgreSQL 7.4. This driver is is outdated but works on earlier versions.
If you wish to use the current PostgreSQL 7.4 release, replace the file …/Servoy/drivers/postgresql.jar with the current stable release from jdbc.postgresql.org. I’ve tried it and it works. Servoy please update future packages.
Note. Although installing a fresh JDBC driver on your new Postgres 7.4 server won’t get you very far. The driver that matters is the one in the Servoy installation - a lesson learned the hard way.
FYI: the jdbc driver that ships with Servoy R2 1.2 does not work with PostgreSQL 7.4. However, the current stable release (version 7.3x or higher) of the driver does. See jdbc.postgresql.org.