Can Pervasive Handle this?

[demoman]

Howdy

I am trying to create a VB program, which directly links to a Timberline/Pervasive SQL2000 (SP2) DSN. I can make the connection without any problem.

I looked at the Pervasive SQL documentation prior to setting out on this project. It appeared to support a very workable SQL toolbox, so I began the application coding. It was much faster downloading the TL data into Access for the development stage. My plan was to simply change the Access syntax to Pervasive SQL syntax at the end, and point to the pervasive DSN.

I reached that point last week, and simply cannot get the first serious SQL statement to execute. I am using an ADODC connection object to load a DataGrid object. The ADODC RecordSource property is:

SELECT If(MJJ.project_manager is NOT null,MJJ.project_manager,ACC.project_manager) "ProjectManager",MAC.Name,If(BBI.Job_Address_1 is NOT null,BBI.Job_Address_1,MJJ.description) "JobName",If(AAS.contract is NOT null,AAS.contract,AAS.job) "JobNumber",(AAS.Due_Date+30) "Due_Date",AAS.Invoice,AAS.Status,AAS.Customer, If("textday"='Saturday',((AAS.Actual_Complete_Date + "DaysToAdd&quot-1),If("textday"='Sunday',((AAS.Actual_Complete_Date +"DaysToAdd&quot-2),'')) "LienDate",AAS.Outstanding_Amount,If(AAS.Retainage>0,0,AAS.Outstanding_Amount) "InvAmount",AAS.Retainage_Billed,If(AAS.Retainage_Billed>0,AAS.Outstanding_Amount,0) "Retainage",AAS.Status_Notes,MJJ.Actual_Complete_Date,MJJ.Work_State,If(MJJ.Work_State='WA',85,If(MJJ.Work_State='OR',70,0)) "DaysToAdd",If(MJJ.Actual_Complete_Date Is Not Null,(MJJ.Actual_Complete_Date+DaysToAdd),MJJ.Actual_Complete_Date) "NewDate",Weekday("NewDate&quot "TextDay FROM ACTIVITY_ARA_ACTIVITY AAA outer join ACTIVITY_ARA_ACTIVITY AAS ON (AAA.Invoice = AAS.Invoice) outer join MASTER_ARM_CUSTOMER MAC ON (AAS.Customer = MAC.Customer) left outer join MASTER_JCM_JOB_1 MJJ ON (AAS.Job = MJJ.Job) left outer join ACTIVE_CNC_CONTRACT ACC on (AAS.Contract = ACC.Contract) left outer join BILLED_BLI_INVOICE BBI ON (AAS.Invoice = BBI.Invoice) WHERE AAS.Status='unpaid'

Now, I am not asking anyone to troubleshoot this statement. I have been doing it, piece by piece. I can get some parts to work, but not others.

I went to Pervasive's web-site, and read; "With ....SP2a, and earlier, the provider only supported the iRowset interface and as such did not support SQL syntax."

Is this my problem, even though Pervasive documents the full SQL toolset?

I would appreciate any insight you may have.

Thanks
Steve

 

[Vancouverite]

Yes, this is the primary problem. An upgrade to SP3 or later will give you the full Command interface.

HOWEVER, the ADO provider for Pervasive may still be problematic if you are using client-server coding. The problem is this: Unlike the ODBC interface, the ADO interface processes complex queries on the client side. So, when doing joins, unless the data can be filtered on an indexed field, the Pervasive DB transfers all of the data that may be needed to the client over the network, and does the filters and joins locally. This can result in significantly slower query returns than the ODBC interface provides.

Using the ODBC driver with MS' ADO-ODBC interface alleviates this problem. And, Pervasive is developing an improved ADO provider without this method of processing, which should be released with the next major build.

 

[demoman]

Thanks for the response! So, you recommend:

1) Upgrade to SP3
2) Drop the ADODC
3) Create a connection object with my ODBC DSN
4) Create a recordset object, set the cursorLocation to 'server' and pass my SQL statement to it's RecordSource property?

Thanks in advance
Steve (seattleite)

 

[Vancouverite]

Well now, having covered the basic issues, let's look at the SQL. A couple of things come up that I see:

(1) You probably want to use IFNULL in some of the conditions. The basic definition of IFNULL is:

IFNULL(exp, value)

If Exp is Null then return Value, otherwise return Exp

So, your first condition is more efficient as:

IFNULL(MJJ.Project_Manager, ACC.project_manager)

Repeat as needed. I notice one location where IFNULL will not work, e.g.:

If(MJJ.Actual_Complete_Date Is Not Null,(MJJ.Actual_Complete_Date + DaysToAdd), MJJ.Actual_Complete_Date)

Second, you want to get rid of the "textday" value and Weekday function and use the DAYNAME function to return the name of the day.

Third, you should probably place in the explicit "AS" before each column name. In some versions of the SQL parser, the AS has been needed.

Other than that, this should work as you laid it out.

 

[demoman]

I DO appreciate the help! I have hit a snag...Timberline only supports 2000 (SP2) right now. They have a limited release of (SP4), but I am not sure how much complaining will be requied to obtain it. But, that is another battle to be fought. Thanks again, and if you ever need help with ERP system design (my speciality), let me know.

 

[Vancouverite]

Note: SP2 supports ODBC out of the box ... it's just the PervasiveOLEDB driver that is limited. Use the Pervasive ODBC driver, and SP2 should be a go.

 

[jbeecher]

Please feel free to contact Tom Moore at Timberline software as he is the Product Manager for Timberline Gold and he can get you a copy of the Timberline LRP (based on SP4 of Pervasive.SQL 2000).

Regards,

Jim Beecher
Pervasive Software