org.lucee.postgresql JDBC driver dated - how would we update it?

[Markus Wollny]

Hi,

The JDBC driver that comes with Lucee 5 is at 8.3.0.jdbc4. We'd like to use a more current version 9.4.1209 JDBC 4. There is a Maven Repo (see http://repo1.maven.org/maven2/org/postgresql/postgresql/9.4.1209.jre7/), but I couldn't find any documentation whatsoever on how to exchange the 8.3 driver for the more current one. I did find a similar discussion for the MySQL driver here http://lang.lucee.org/t/lucee-5-osgi-and-changing-a-bundle/270 - but I couldn't really make much of this. In Lucee 4.5 I would simply change the .jar file for the driver, now that there's the OSGi magic in Lucee 5 it seems to be somewhat more complicated to customize the innards of Lucee.

Am I missing something here?

Kind regards

  Markus

[Joseph Gooch]

9.4.1206 is in the JDBC extension factory repository... I'm not sure why it's unavailable via the update provider or the website (http://preview.lucee.org/download/?type=extensions)

If you wanted to deploy it yourself:

git clone https://github.com/lucee/JDBC-extension-factory

cd JDBC-extension-factory

ant

In your dist folder, there should be a postgresql-9.4.1206.jdbc41.lex - copy that to your lucee-server/deploy/ folder.

In a minute or so it should disappear and your Lucee administrator should show the new version.

If you want 1209 specifically, it appears the jar from the maven repository does not include the OSGi items needed in the manifest. To fix:

1) copy the build/postgresql-9.4.126  [sic] directory to a new one i.e. postgresql-9.4.1209

2) Extract META-INF/MANIFEST.MF from the existing postgresql-9-4-1206-jdbc41.jar.  Delete the jar.

3) Change the Bundle-Version header in the MANIFEST.MF to 1209 (or 1209.jre7)

4) Download the new v1209 jar

5) Extract the jar to a temp folder

6) Remove the existing META-INF/MANIFEST.MF from the temp folder, and any signatures if present (.RSA or .SF files in META-INF)

7) Create a new jar from the temp folder, using the MANIFEST you created (i.e. cd tempfolder; jar cvmf ../MANIFEST.MF ../postgresql-9-4-1209-jre7.jar .)

8) Run ant at the root of the JDBC-extension-factory dir

9) Copy your postgresql-9-4-1209 lex file from the dist folder into the deploy folder

10) Fork the repository, commit your new directory and issue a Pull Request.  Create a JIRA ticket here https://luceeserver.atlassian.net/secure/Dashboard.jspa  to have that PR processed.  (Ideally, so others can benefit from your work!)

-G

[mark]

Hey Joseph,

Micha replied to this on May 27th,

completely forget to answer the question about postgres.
We had problems with newer version of the postgres driver, because it contains some OSGi functionality that is specific to Karaf OSGi and breaks with Felix OSGi. so we decided to go back to an older version (8.3.0.jdbc4) and no longer provide the newer versions.
We have testcases in place that runs with every commit that are testing postgres:
https://github.com/lucee/Lucee/blob/master/test/datasource/PostgreSQL.cfc
https://github.com/lucee/Lucee/blob/master/test/jira/Jira2903.cfc
So postgres is working, otherwise we never could do a build.

Did you get it to work somehow?

[Markus Wollny]

Thank you - followed the first part and it's just what I needed for now, so Lucee now shows 9.4.1206.jdbc41 as loaded. I'll check out the second part (building 1209) tomorrow.

Thanks again for the help!

Kind regards

  Markus

--
Win a ticket to dev.objective from Lucee via Twitter, see http://bit.ly/1UbTMWj for details, good luck and see you there...
---
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/2KhURdjHtDU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/fc57a24b-d6d2-4901-a74f-7873ae92d928%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Markus Wollny]

Hi,

Seems to me that Karaf OSGi specifics are not the only thing that's breaking with the driver update. I just now stumbled over this discussion https://github.com/pgjdbc/pgjdbc/issues/488 - and yes, after upgrading the driver it tacked a "RETURNING *" clause blindly to each and every query, which breaks all of the SELECTs and a couple of other queries with explicit RETURNING clauses as well. Seems to me to be a questionable design decision on behalf of the PostgreSQL JDBC team, but OTOH as far as I understand the gist of the discussion, there's currently just no chance to provide a cleaner solution.

I remember hacking a Postgresql-9.3 JDBC 4 build for ACF a long time ago, though it was an ugly hack as I simply removed the whole "RETURNING *" magic altogether, which may or may not break other stuff that I am not aware of. It certainly is not the canoncial way to do things.Just for good measure, here's the patch to the driver, I assume that the general implementation of this hasn't changed much, so it might still work with more recent versions:

diff -br postgresql-jdbc-9.3-1102.src/org/postgresql/jdbc3/AbstractJdbc3Connection.java postgresql-jdbc-9.3-1102.src.patched/org/postgresql/jdbc3/AbstractJdbc3Connection.java
363,371c363
<         if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
<             sql = AbstractJdbc3Statement.addReturning(this, sql, new String[]{"*"}, false);
<
<         PreparedStatement ps = prepareStatement(sql);
<
<         if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
<             ((AbstractJdbc3Statement)ps).wantsGeneratedKeysAlways = true;
<
<         return ps;
---
>         return prepareStatement(sql);
diff -br postgresql-jdbc-9.3-1102.src/org/postgresql/jdbc3/AbstractJdbc3Statement.java postgresql-jdbc-9.3-1102.src.patched/org/postgresql/jdbc3/AbstractJdbc3Statement.java
139,144d138
<         if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
<             return executeUpdate(sql);
<
<         sql = addReturning(connection, sql, new String[]{"*"}, false);
<         wantsGeneratedKeysOnce = true;
<
268,273d261
<         if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
<             return execute(sql);
<
<         sql = addReturning(connection, sql, new String[]{"*"}, false);
<         wantsGeneratedKeysOnce = true;
<

So far the issue remains unresolved and open, but given the amount of time that this is already the default behaviour of the PostgreSQL JDBC driver, I have my doubts if this would actually be resolved in a timely fashion. For what it's worth, I'd very much prefer some sort of server side configuration that would allow me too choose if I actually want RETURN_GENERATED_KEYS set for my queries to a certain datasource or not, default could be true so as not to break anything else.

For now I'll revert to the 8.3 driver until I can find the time to experiment with rolling my own again, likely incorporating the dirty hack above.

Kind regards

 Markus

[Joseph Gooch]

I can't speak to the specifics of the driver, just that it's in the JDBC extension factory, and available as a module, and how to get the module from the repo into Lucee.

Specifically this procedure as it applies to JDBC drivers is something that could stand to be documented. (hence my verbosity)

If the driver isn't ready for prime time, wouldn't it be better in a dev branch or a modules.broken folder?

Is there some way to know this module isn't ready for prime time?  Is there a test case illustrating the problem with it?

I went a step further and installed the module, and it passes. (debugging added by me, run against the current docker postgresql image)  See attached screenshots.

Also note that most developers doing a local build would automatically skip these tests - the way they're constructed, if you don't have a DB server setup, and don't provide the credentials in the environment, they're silently skipped.  Perhaps the build process could be extended to automatically set up appropriate docker containers (or pull appropriate docker containers) for consistent and comprehensive testing.  If docker isn't available, well... we're no worse off than now.  Being able to run subsets of the tests would be good too.

The only JIRA ticket I can find is LDEV-760: but this error doesn't seem to apply, because the osgi classes aren't in the pgjdbc driver anymore

https://luceeserver.atlassian.net/browse/LDEV-760

And Micha indicates in this ticket that even when they were, that the Lucee package doesn't include them. (Ostensibly to resolve the issue run into in the past)  It doesn't indicate that a module has been deprecated or isn't working.

Is there any source that indicates

  1) the package in the JDBC-extension-factory shouldn't be used

  2) there was a problem with this PG driver - what it was, a testcase to reproduce it, etc.

  3) how, in general to add, use, and package custom jdbc drivers

  4) the changes made in Lucee to package this jdbc driver (vs the stock jar in maven from the PG team)

  5) Best practices for module naming (note the bundle for 8.3 is org.lucee.postgresql, the 9.4 driver is just "postgresql")

(other than talking to Micha directly)

-G

[Joseph Gooch]

They didn't resolve this in https://github.com/pgjdbc/pgjdbc/pull/491?

If you do decide to go with a local fix - you now have a recipe to do so.  Just compile your own classes/jar, copy the manifest over etc.  I'd change the UUID in the build.properties and assign it a different bundle name. (i.e. markus.wollny.postgresql or something)

As for the logic/decisions of the pgjdbc team... well.. You're on your own. :)
-G

[Harry Klein]

Unfortunately I get an ant error:

dist:

     [echo] F81ADA62-BB10-552D-9ACEE5D43F3FFC46

     [echo] Apache Derby

     [echo] Apache Derby, an Apache DB subproject, is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0.

     [echo] D:\projekte_cf\lucee\JDBC-extension-factory\build\derby\derby.jar

     [echo] D:\projekte_cf\lucee\JDBC-extension-factory\build\derby\Derby.cfc

     [echo] Derby.cfc

     [echo] D:\projekte_cf\lucee\JDBC-extension-factory\build\derby

     [echo] D:\projekte_cf\lucee\JDBC-extension-factory\build\derby\build.properties

    [unzip] Expanding: D:\projekte_cf\lucee\JDBC-extension-factory\build\derby\derby.jar into D:\projekte_cf\lucee\JDBC-extension-factory\temp

     [echo] Driver class name:org.apache.derby.jdbc.AutoloadedDriver

    [mkdir] Created dir: D:\projekte_cf\lucee\JDBC-extension-factory\temp\extension\derby-10.12.1000001.1704137

BUILD FAILED

D:\projekte_cf\lucee\JDBC-extension-factory\build.xml:260: The following error occurred while executing this line:

D:\projekte_cf\lucee\JDBC-extension-factory\build.xml:223: java.io.FileNotFoundException: D:\projekte_cf\lucee\JDBC-extension-factory\temp\extension\derby-10.12.1000001.1704137\META-INF\MANIFEST.MF (Das System kann den angegebenen Pfad nicht finden)

[Harry Klein]

My colleague has another problem:

D:\contens\javaworkspace\JDBC-extension-factory\build.xml:260: Problem: failed to create task or type foreach
Cause: The name is undefined.
Action: Check the spelling.
Action: Check that any custom tasks/types have been declared.
Action: Check that any <presetdef>/<macrodef> declarations have taken place.

[Joseph Gooch]

Interesting.

I've uploaded a github release:

https://github.com/goochjj/JDBC-extension-factory/releases/tag/MSSQL-6.0.7507

That should get you the lex file you need.

-G

--
Get 10% off of the regular price for this years CFCamp in Munich, Germany (Oct. 20th & 21st) with the Lucee discount code Lucee@cfcamp. 189€ instead of 210€. Visit https://ti.to/cfcamp/cfcamp-2016/discount/Lucee@cfcamp
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/dba067d4-9596-4e2f-b5b0-be981c2f5dda%40googlegroups.com.

[Igal @ Lucee.org]

Markus,

Please comment on the pgjdbc comment so that it doesn't fall off the radar.  I've been arguing with their team for a some time, but maybe if they see more users that are affected by this issue they will give it some more thought.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

--
Get 10% off of the regular price for this years CFCamp in Munich, Germany (Oct. 20th & 21st) with the Lucee discount code Lucee@cfcamp. 189€ instead of 210€. Visit https://ti.to/cfcamp/cfcamp-2016/discount/Lucee@cfcamp

---

You received this message because you are subscribed to the Google Groups "Lucee" group.

To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/dada9545-a2bc-49dd-ab62-eb6d8ceba92c%40googlegroups.com.

[Harry Klein]

Thanks for the lexfile.

Unfortunately we have to update more drivers, and also build a new one for MariaDB.

This is not possible with the JDBC-extension-factory right now. Maybe it works only on Linux/Mac?

Should I create issues for the problems mentioned below?

 

-Harry

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAKY58c5MZNjwEhNyR4zvBoqLUKDve3ziQQaMKCxh8oQJqCL4PQ%40mail.gmail.com.

[Markus Wollny]

Hello Igal,

I have just done so. On the other hand: As far as I understand, the issue doesn't pose a problem as long as the old 8.3 driver is being used, which doesn't implement the autoGeneratedKeys flag and just silently ignores it (same as with my driver patch). So wouldn't it be possible to always set the flag int to NO_GENERATED_KEYS in case of a PostgreSQL datasource being used in Lucee? As the outcome is exactly the same as when the currently deployed 8.3 driver is used, this shouldn't break anything at all. If at all in doubt, there could or should be an option to explicitly override this flag with a separate CFQUERY argument.

From my point of view, using this "feature" at all is simply sloppy programming style in any case, but that may be due to the fact that I don't know nearly enough about other RDBMSs and their method of retrieving autoincremented values (serials, sequences, autoincrement or whatever that is called in a specific database). That a RETURNING * magically appears in my statements without my having asked for it in the first place is just plain wrong.

Kind regards

  Markus

[Joseph Gooch]

I've never had any issues with JDBC-extension-factory.  I've used it on several macs and linux machines.

The foreach error just means your environment doesn't have ant-contrib installed.

I have no idea on the derby error - as I said, I've never encountered it.

You can roll lex files yourself, (they're just ZIP files with a manifest)

Or you can look into using lucee - cfbundle comes to mind.  I've never used it.

-G

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/5834990a4c5247d6a40d97f4b8b31599%40srv-dc3.contens.local.

[Harry Klein]

Thanks Joe,

after communicating with Michael I am using another approach:

I uninstalled all driver extensions and just copied the matching driver jars to my lib Folder. (made a backup of my lucee-server/context/context/admin/dbdriver folder first)

Works like in Lucee 4.5 and is much easier to manage.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAKY58c5Uh3VvVOVOb3%2B-4qGCm1uXn7de6imYHPuDfSAdOGLWqg%40mail.gmail.com.

[Markus Wollny]

Hi,

I have created a Github repo with my changes to the original JDBC driver here: https://github.com/Ratcreamsoup/pgjdbc/tree/REL9.4.1209-NO_GENERATED_KEYS

I have repackaged the jar with the manifest from the version that was included in the JDBC-extension-factory, forked that and created a new extension folder there - https://github.com/Ratcreamsoup/JDBC-extension-factory/tree/PGJDBC4.2_REL9.4.1209-patched

I haven't touched the UUID however, as I assumed that this would prevent the extension from overriding the older driver. After deploying I just had to change the <driver>-element for the org.postgresql.Driver in lucee-server.xml to point to the new bundle-version like so:

<driver bundle-name="postgresql" bundle-version="9.4.1209.jdbc41" class="org.postgresql.Driver" label="PostgreSQL"/>

Now everything is running fairly smoothly on the new driver. I have issued a pull request, for what it's worth, but as I am not fully aquainted with the conventions of these extensions, especially with regards to the UUID, I'm not sure if that's what people want. Anybody can clone from my repo though, if it's needed.

Kind regards

  Markus