Pervasive SQL and Macromedia Coldfusion

[brettlaird]

My company has a Pervasive SQL database and I'm trying to setup a website that needs to access that data. I've used Coldfusion with Access and SQL before with no problems but can't seem to get it to connect to Pervasive SQL. I've setup my ODBC connection and it seems to work fine because I can link Microsoft Access XP tables to the Pervasive tables through this ODBC connection and view the data with no problem. Also I've connected Crystal reports with no problem. When I setup this connection in Coldfusion Administrator I get an error to check my username and password. If I go into Dreamweaver and try to use this connection I get prompted for a UserID and Password when I click on tables. The Pervasive database is on a remote server that I have administrative rights to. Any ideas?

 

[mirtheil]

There's a sample for ColdFusion at http://www.pervasive.com/developerzone/access_methods/ODBC.asp called "ODBC through Cold Fusion". Here's what it says about it:
The files included this download are provided "as is" and are not supported by Pervasive Software in any way. They are provided as sample code for development and learning pourposes only.

Code samples included with this release:

Code Snippet Description
------------ -----------
TEST.CFM Cold Fusion sample that shows how to access fields with spaces in them using ODBC.
ODBCTEST.CFM Cold Fusion sample that shows how to access regular tables and display them using ODBC.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[brettlaird]

Thanks for the quick response. I found that page while I was searching for a solution to my problem but I still wasn't able to get it working. In the code is has:
<cfquery NAME="GetClass" DATASOURCE="Demodata">
My problem is getting the DATASOURCE (Demodata) setup in Coldfusion Administrator. It prompts for a UserID and Password when I try to connect through Coldfusion and no matter what I use it doesn't work. But I can connect through this datasource with Microsoft Access and Crystal Reports with no problem and no prompts.

 

[mirtheil]

WHen it prompts, don't put anything in it. Does that work? If not, what error are you getting?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[brettlaird]

When I Verify the ODBC connection in Coldfusion Administrator I get the following error:

Connection verification failed for data source: DEMODATA
[]java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.
The root cause was that: java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.

When I click on Tables under this connection in Dreamweaver it prompts for LoginID and Password and if I leave it blank and click OK it just prompts again. Same thing if I put in a UserID and Password. Just keeps prompting. If I cancel it just says no tables.

 

[mirtheil]

Sounds like a Java issue in the JDBC to ODBC bridge.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[brettlaird]

Any idea how to fix it?

 

[mirtheil]

Unfortunately, no. I've only worked with Cold Fusion once and that was about 4 years ago..


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[brettlaird]

Using Dreamweaver I created a Java page to access the datasource and it works for DEMODATA on my pervasive server but I can't connect to my database. I get at Btrieve file directory is invalid(Btrieve Error 35) when I try to open a table. I also get this error when I go into the Pervasive control center and double click on one of my tables and try to open it with the SQL Agent. DEMODATA opens fine here but not my database. Do you know what could be causing this?

Thanks for all your help!

 

[mirtheil]

Sounds like you've got hard coded paths in your DDFs. Make sure the DDFs and Data file are in the proper locations. You might also, from the PCC, double click on the "X$FILE" table. It'll let you look at the table location (XF$LOC). If you see paths, then you'll need to either remove them (using the Table Designer, ALTER TABLE script, or my DDF Path Fixer tool (http://www.mirtheil.com/ddfpath.asp) or make sure you data files are in the proper path as specified.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com