automatic price & stock updates from Pastel Partner to MySQL website?

[Neville Bailey]

The other thing I want to ask is this: Can Pastel Partner be queried from outside via an online connection to obtain relevant stock and pricing information, much like one would query a database?

It depends on what you mean by a query.

If you want to access Pastel remotely via the user interface, then I would simply create a VPN connection from the outside to the local machine and then run Pastel remotely. You can create user profiles that limit the remote user from accessing certain sensitive info. You can also use systems such as LogMeIn or TeamViewer (my personal favourite).

Is that what you had in mind?

[Salad_Dressing]

It depends on what you mean by a query.

If you want to access Pastel remotely via the user interface, then I would simply create a VPN connection from the outside to the local machine and then run Pastel remotely. You can create user profiles that limit the remote user from accessing certain sensitive info. You can also use systems such as LogMeIn or TeamViewer (my personal favourite).

Is that what you had in mind?

I want to do what you are saying without using Pastel remotely via a user interface, but rather using a PHP script running from a server to sign-in to a Pastel host using the correct protocols and authentication credentials.

From such a connection I can imagine very easily doing a query for stock levels and latest prices and then updating the website database accordingly. It would also mean that task scheduling (e.g. doing a stock update every 15 minutes) would be handled by the web server running PHP with no burden on the Pastel Partner sys admin.

[Neville Bailey]

I want to do what you are saying without using Pastel remotely via a user interface, but rather using a PHP script running from a server to sign-in to a Pastel host using the correct protocols and authentication credentials.

From such a connection I can imagine very easily doing a query for stock levels and latest prices and then updating the website database accordingly. It would also mean that task scheduling (e.g. doing a stock update every 15 minutes) would be handled by the web server running PHP with no burden on the Pastel Partner sys admin.

Hmmm, I'm afraid you have surpassed my level of expertise in this case! I have no idea whether it is possible to do what you want and, if so, how to do it.

[Dave A]

I've kept pretty quiet up to now because we are doing something similar - but there are differences which may be significant.

First, we're on Quickbooks - which has to run on a Windoze machine.
Then we're connecting to Sugar CRM, which we've got running on a Kubuntu server.
Third, the two hosting machines are within a LAN, not a WAN setup like you're facing.
Fourth, the SDK for Quickbooks pumps out its query returns in XML.
Lastly, we're using a javascript rather than a PHP script.

However, the basic principle should be pretty similar.

1. The SDK query tool resides on the same machine as the financial package (the Windoze server) and is all set up to return data when queried. I expect this will be much the same in Pastel.

2. The query script is set up on the Kubuntu server and is triggered by a CRON job. However, because it's javascript it could just as easily reside on the Windoze machine which I suspect is probably the way you should think about going.

3. The script queries the SDK script, which returns the data, which is then parsed by the script to match the Sugar MySQL fields it needs to fill, and then appends or updates the records in the Sugar db using MySQL queries and commands. This needs a coder!

Essentially you're constructing a bridge between Pastel and your MySQL database. The SDK just helps you extract data out of the Pastel database via script rather than a user interface. But from there you're pretty much on your own.

[AndyD]

I've also been watching this one from a distance. It's been a few years since I was involved in pastel database systems but a few things strike me immediately.

Firstly you're dealing with Pastel Partner which as far as I know is pervasive database system which evolved from the old btrieve system. Does Partner come with a sql frontend nowadays? Evolution used sql databases so it might be more suitable for what you're trying to do.

Secondly with either system you can set up simple transactions without the sdk kit. You obviously need to know which tables you're dealing with, the fieldsand their relationships but lifting customer lists for example would be straight forward. Complex transactions can also be performed directly into the tables but for safety purposes it might be easier to use sdk scripts for this.

[Salad_Dressing]

Thanks guys for the encouragement through your replies!

I'm currently looking at setting up an ODBC driver on the Pastel server, linking that to the database, and then performing ODBC queries from PHP.

Any input here regarding this?

I spoke to a Pastel reseller who recommended the Pastel E-Business module. But I'm guessing this is not essential.

[Salad_Dressing]

It appears that Pastel runs the Pervasive database, formerly Btrieve, as mentioned by AndyD :-)

Part of a Pastel database installation should therefore include Pervasive's ODBC driver (as referenced from this source).

Once the ODBC driver is configured on the Pastel server, a remote PHP script should be able to connect to it using standard ODBC functions and the proper credentials (server address, database name, username, password).

Does anyone have experience with setting up ODBC on a Pastel server?

[Neville Bailey]

Does anyone have experience with setting up ODBC on a Pastel server?

Aha! Now this is where I can help you at last!

At the server, you need to open the Pervasive Control Centre - type pcc in the Run box, or go to Start...All Programs...Pervasive...PSQL 10...Control Centre.

Click on New Database (see screenshot below).



Click Next.

Type in your database name (I used DEMO for illustrative purposes below), then click on the browse icon.


Browse to the location of your Pastel database folder.


Click on OK.

Click on Finish.


Your ODBC database has been created.

[Salad_Dressing]

Thanks Neville!

I've now set up a demo OCDB database via the Pervasive Control Center called "DEMOFORODBC".

Now I need to connect the database to an ODBC driver and obtain a port for remote access.

What I understand I should do is: Control Panel --> Administrative Tools --> Data Sources (ODBC) --> System DSN --> Add New --> but here all I have on the list is "SQL Server/6.01.7600.16385/Microsoft Corporation/SQLSRV32.DLL"

As I understand from this PDF tutorial on select.co.za I need to be able to select "Pervasive ODBC Engine Interface" and this is what I do not know how to install / setup / access.

Can anyone please help me with this?

[Salad_Dressing]

I have it solved!

The Pervasive manual mentioned the following:

ODBC Administrator on 64-bit Platforms
Windows 64-bit operating systems contain two different executable files for ODBC Administrator. If you want to add a system data source name (DSN) for a Pervasive Engine or Client driver, invoke the ODBC Administrator from Pervasive PSQL Control Center.
If you start ODBC Administrator from the Control Panel, the Pervasive drivers are not listed.

The above is exactly what was happening. SOLUTION: I opened the Pervasive Control Center and under the Tools menu opened "ODBC Administrator" and suddenly it was all there!

Now I need to know which port to use when remotely connecting to ODBC on the server...