Coldfusion 10 and Actian PSQL 12 Server...CF ODBC Server crashing

[wannab0133]

Our Coldfusion 10 environment(Update 23) has been running for several years without any major issues.  Lately, at about every 60 hours or so, we are this database error:

"Error Executing Database Query.[Macromedia][SequeLink JDBC Driver]TCP/IP error, connection refused. The specific sequence of files included or processed is: C:\inetpub\wwwroot\appname\index.cfm, line: 42 "

coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query.

We are connection to an Actian PSQL 12 Server via ODBC.  It turns out that while stopping the Coldfusion services to investigate, the 'ColdFusion 10 ODBC Server' hung on the stopping command.  I had to go to task manager and end the following tasks: 'DataDirect Sequelink Server'.  Then the ODBC service would stop.  I then delete all of the .trc files in folder C:\ColdFusion10\db\slserver54\tracing.  I could then restart the Coldfusion services and everything works again.  This is great, but after a couple days or so, this same thing happens again and I have to perform the above steps to get the app running again.  This iteration has occured 4 times over the past 10 days or so.

A look into the event viewer shows:

"The description for Event ID 0 from source ColdFusion 10 ODBC Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

ColdFusion 10 ODBC Server@LOCALHOST,ErrorCode=3005,ErrorMessage=socsrv.swsoc.9856.Internal error, fatal server error detected."

Any ideas on how to get this stabilized utilizing the current ODBC config?

Another solution is to use JDBC instead.  Can I connect to Actian PSQL with JDBC vs ODBC?  It seems JDBC is preferred, but I cannot find drivers for Actian PSQL.

[Wilgeno1]

Is Actian PSQL 12 Server A PostgreSQL server?  If so try using the PostgreSQL JDBC connectors.

[Wilgeno1 · Accepted solution]

A little searching via Duck Duck Go and I found this in the docs. 

JDBC

The PSQL JDBC driver now supports portions of the JDBC4 API. In particular, the PreparedStatement methods setNString and setNCharacterStream are now implemented. To get the best Unicode support, set the pvtranslate=auto option in the connection string.

The PSQL JDBC driver now implements the PreparedStatement method getParameterMetaData. The name of the .jar file for the JDBC driver is unchanged.

I found other docs for other software that connects to this DB type and they say the jar files are included in the PSQL install.   Look for them in Program Files\Pervasive Software\PSQL\BIN\ folder:  JPSCS.JAR, PVJDBC2.JAR, PVJDBC2X.JAR 

Copy those to the ColdFusion10/cfusion/lib folder. or for ColdFusion Enterprise, copy them into ColdFusion10/[instance name]/lib/   Then restart ColdFusion.  To add the JDBC datasource you'll need to build the connection string manually. 

• Java Driver Class: com.pervasive.jdbc.v2.Driver
• URL: jdbc:pervasive://{HOST}:{PORT}/{DB}

If you cannot find the PSQL JDBC jar files you might be able to use the openJDK JDBC jar files to make a JDBC connection to PSQL.

Regards,

Wil

[wannab0133]

Will,

I have located the jar files and will try your solution and report back.

[wannab0133]

Wil,

Your suggestion worked.  I have not done all the testing I need to do, but it seems to be working great.  Thank you for the help.

[wannab0133]

I honestly don't know if PSQL 12 Server is PostgreSQL.  I thought so at first, but now sure on this.  I've done some research on it and it seems Pervasive at one point was PostgreSQL.