Timberline ODBC Connection String in ASP Page

[kwikstix]

I am developing an Extranet website solution for a construction company, using Timberline as a source for some of the data. I am trying to write a connection string using the Timberline ODBC driver, but have not had any success. I have successfully connected to the database using the following connection string:
"Driver={Pervasive ODBC Client Interface};DBQ=D:\Timberline Office\TSDATA"

So far everything is working fine EXCEPT I can't query based on job number (MASTER_JCM_R1.Job). For instance, if I run the following query:
SELECT * FROM MASTER_JCM_R1

I get results. The Job column is returned as a value such as 01101. (If I run this same query in MS Access, that Job column value is formatted as 01-101). I then try the following queries:
select * from MASTER_JCM_R1 where Job = '01101'
select * from MASTER_JCM_R1 where Job = '01-101'
select * from MASTER_JCM_R1 where Job = '1101'
select * from MASTER_JCM_R1 where Job = '1-101'

None of these queries return any results, nor do they return errors. So, for some reason, I can't query using the Job column in the WHERE clause.

I spoke with Timberline support, and they said it was because I'm using the Pervasive driver, rather than the Timberline ODBC driver. However, I haven't been able to get the Timberline ODBC driver to work in a connection string at all. If I use the following connection string:
"Driver={Timberline Data};dns=tl;uid=(user);pwd=(password);DBQ=D:\Timberline Office\TSDATA"

And execute the following SQL statement:
SELECT * FROM MASTER_JCM_JOB

I always receive the following error:
[TimberlineODBC][TimberlineODBCEngine ODBC Driver][DRM File Library]No such table or object.

And if I use the following connection string:
"Provider=MSDASQL.1;DRIVER={Timberline Data};UID=(user);PWD=(password);DBQ=D:\Timberline Office\TSDATA;"

I always receive the following error:
Provider error '8000ffff'
Catastrophic failure
/test.asp, line 6
(Line 6 is where I try to open the connection).

(I understand that if I use the Timberline driver, the table name is MASTER_JCM_JOB, and if I use the Pervasive driver, the table name is MASTER_JCM_R1.)

Any ideas?

 

[mirtheil]

First off, you need to pick a driver. Then you need to solve the problem(s) associated with that driver.
For example, if you pick the Pervasive driver, then you'll want to look at the database and find out what data type the "Job" field is defined as. If it's a string, then you'll need to pass it in as a string as it's formatted in the table. What happens if you try to use the Pervasive Control Center? Can you restrict on that field?
If however you pick the Timberline driver, what happens if you use a Data Source Name (DSN) rather than the connection string? You might want to turn on ODBC tracing to see what the real error is.
DOn't forget that permissions are probably one of the biggest problems I see with ODBC, IIS, and ASP. Make sure that the IUSR_machinename user has rights to the directory where the data is located.

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

http://www.mirtheil.com

 

[lbarrow]

I use the following string and it works:
"DSN=TSProduction;Driver=Timberline Data;DBQ=E:\Timberline Office\Accounting\TSDATA;UID=user;PWD=password;CODEPAGE=1252"
However, if USE MAXIMUM TABLE SEGMENT SIZE in your ODBC connection is NOT check, then you must use the table name MASTER_JCM_JOB_1 in your SQL statement. If it is checked, then use MASTER_JCM_JOB.

Hope that helps.
- L