# General Business Category > Accounting Forum >  automatic price & stock updates from Pastel Partner to MySQL website?

## Salad_Dressing

We are running Pastel Partner version 11 (build 11.2.1) and I am putting up a webpage for our business (HTML with PHP and MySQL backend).

Is there a way to automate updating of product prices and stock count from Pastel Partner to the MySQL database?

Any help would be greatly appreciated!

Thanks in advance.  :Smile:

----------


## Neville Bailey

Unfortunately my knowledge about MySQL databases is negligible, so I can't comment on that aspect of your query.

However, if you have Pastel's Business Intelligence Centre (BIC) module, you can extract the relevant data from the Pastel database into an Excel format. From there it may well be possible to transfer the data into a MySQL database, but you might be better qualified to answer that question.

----------


## Dave A

> However, if you have Pastel's Business Intelligence Centre (BIC) module, you can extract the relevant data from the Pastel database into an Excel format.


Can it be extracted as a csv or XML file by any chance?

----------


## Neville Bailey

> Can it be extracted as a csv or XML file by any chance?


Not directly from Pastel, but a macro button can be embedded into the BIC Excel report to generate a CSV format file.

----------


## Dave A

Surely Pastel has a connector app for third party plug-in developers?

----------


## Neville Bailey

> Surely Pastel has a connector app for third party plug-in developers?


Yes, it does, but one has to find the right Pastel-accredited 3rd Party Developer. Here is a list of them.

----------


## Dave A

:Hmmm: 

I hear you about the "authorised" bit, but ...

Salad_Dressing - do a Google search for _pastel sdk download_ and see if you can find the relevant version. If you tack on _free_ at the end of that the result does change a bit.

----------


## Salad_Dressing

Thanks all for your inputs into this matter.  Here's what I have found...

Pastel can output spreadsheets and I can easily script to rip and update my database with them.  However that does not fully automate the process.

a link to Pastel Partner 3rd Party Developers

a link to Pastel SDK download

I installed the SDK to see what sort of language I'll be getting myself into and I see examples in VB6 (Visual Basic?)

Anyway, I was really hoping for some sort of scripting language which can hook into Pastel Partner in order to perform my functions to our website's database.  I find it hard to believe that in the Internet age there's no way to automate stock counts and price changes in a fairly easy way???

Here's another approach, perhaps...

Can Pastel Partner be set up to create scheduled CSV exports, let's say each half hour?  I can then write code to pick up the latest CSV file, parse it, and update the online database.

----------


## Salad_Dressing

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?

----------


## Salad_Dressing

OK, I may have found a solution where the SDK is used through PHP... check out this link on the Pastel Development Zone.

----------


## 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.

----------

Salad_Dressing (11-Oct-11)

----------


## 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 (11-Oct-11)

----------


## 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!  :Thumbup: 

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.

----------

AndyD (08-Oct-11), Salad_Dressing (11-Oct-11)

----------


## 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...

----------


## Salad_Dressing

Right-clicking server properties when inside Pervasive Control Center brings up a list of properties of which "Communication Protocols" contains the port number.

However, when I click on Communication Protocols the panel with the settings is nearly blank because of some redraw problem.  After clicking repeatedly on the property list, the page showed properly and I was able to see the port number.

Now I need to set up the router's port forwarding to achieve remote access and then start testing the setup via PHP ODBC queries.

----------


## Dave A

Sounds like you're making good progress.

----------


## Salad_Dressing

After some interruption, this project is moving forward again, hoping to complete it soon.

I'm successfully accessing Pastel Partner remotely via PHP script.  I can query the tables, view their columns and rows, etc.

However, I'm not understanding how to access product price lists.  I'm not a Pastel expert.  All I understand is that Pastel installations have similar data structures.

Here's a list of tables which I can access, but none of them appear to be a price list when I query its contents:

AccountUser
ActivityMaster
AnalysisCodes
BankDetails
BICUsers
BillingParameters
BOMHeader
BOMLines
CashBookCurrency
Category1099
CompanyParameters
CostCodeBudgets
CostCodeLevelLinks
CostCodeLevelNames
CostCodeLevelSetup
CostCodes
CostCodeTransactions
CurrencyFile
CustomerCategories
CustomerDiscount
CustomerMaster
CustSuppParameters
DeliveryAddresses
EmployeeActivities
EmployeeMaster
Euro
FinancialCategories
Fonts
GAAP
HistoryHeader
HistoryLines
HistoryTax
HistoryUserDesc
HistoryUserSetup
InternetBank
InternetBenf
Inventory
InventoryCategory
InventoryGroups
InventoryLink
InventoryParameters
JumpUserAccess
LedgerBalances
LedgerMaster
LedgerParameters
LedgerTransactions
LinkHeader
LinkLines
MenuAccess
MultiStore
MultiStoreTrn
Notes
OpenItem
POSAudit
POSCashUp
POSPaidOuts
POSPayments
POSSetup
PreferredSupplier
ProcessOptions
ReceiptTransactions
ReportCategories
ReportExport
ReportWriter
SalesmanMaster
SerialMaster
SerialTransaction
SupplierCategories
SupplierMaster
SystemParameters
TaxBox
TaxDescription
TaxParameters
TimesheetHead
TimesheetLines
Unposted
UserGroups
UserStore

----------


## Neville Bailey

Selling prices are stored in the MultiStoreTrn data table, if I'm not mistaken.

----------

Salad_Dressing (25-Jun-12)

----------


## Salad_Dressing

> Selling prices are stored in the MultiStoreTrn data table, if I'm not mistaken.


Fantastic!  Found the prices.  Thanks Neville!

MultiStoreTrn turns out to be one of the few tables I had not yet searched in because the name just does not sound like it would hold item prices!

----------


## Salad_Dressing

I'm now also looking for the Pastel table where the stock on hand & stock available is stored.

Can anyone please help?

For those interested in this project: I've installed XAMPP onto the Pastel server, allowing me to remotely execute local PHP scripts which perform the queries and return the data to the web server via the HTML post feature, all via PHP.  This technique reduces the query time from 9 minutes to 6 seconds.  This makes it feasible to run fairly frequent price and stock update scripts.

----------

Dave A (25-Jun-12)

----------


## Neville Bailey

> I'm now also looking for the Pastel table where the stock on hand & stock available is stored.


You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

----------

Salad_Dressing (25-Jun-12)

----------


## Salad_Dressing

> You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.
> 
> Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.


Thanks again Neville!

Honestly, without your inputs I'd be in the dark since none of this is obvious.

I'll run this calculation and have the Pastel lady check it.

----------


## Salad_Dressing

> You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.
> 
> Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.


I have played with the formula in all the ways I can think of, but nothing I have done so far matches the actual Stock-on-Hand reported in Pastel Partner.

Do QtyBuyThis01 to QtyBuyThis12 represent activity over the 12 months of the financial year?

If so, would the formula for Stock-on-Hand for the 4th month be: OpeningQty + QtyBuyThis04 + QtyAdjustThis04 - QtySellThis04

OR

Should the formula be inclusive of all 12 columns, something like this:

$tB = $qB01+$qB02+$qB03+$qB04+$qB05+$qB06+$qB07+$qB08+$q  B09+$qB10+$qB11+$qB12; //QtyBuyThis
$tA = $qA01+$qA02+$qA03+$qA04+$qA05+$qA06+$qA07+$qA08+$q  A09+$qA10+$qA11+$qA12; //QtyAdjustThis
$tS = -$qS01-$qS02-$qS03-$qS04-$qS05-$qS06-$qS07-$qS08-$qS09-$qS10-$qS11-$qS12; //QtySellThis
$qOH = $qO + $tB + $tA - $tS; //qO = OpeningQty

----------


## Neville Bailey

> If so, would the formula for Stock-on-Hand for the 4th month be: OpeningQty + QtyBuyThis04 + QtyAdjustThis04 - QtySellThis04


No, it would be:

OpeningQty + QtyBuyLast + QtyBuyThis01 + QtyBuyThis02 + QtyBuyThis03 + QtyBuyThis04 + QtyAdjustLast + QtyAdjustThis01 + QtyAdjustThis02 + QtyAdjustThis03 + QtyAdjustThis04 - QtySellLast - QtySellThis01 - QtySellThis02 - QtySellThis03 - QtySellThis04

Sorry, in my previous post I forgot to mention that QtyBuyLast, QtyAdjustLast and QtySellLast must also be included. OpeningQty represents the quantity on hand at the beginning of the prior financial year.

----------

Salad_Dressing (16-Jul-12)

----------


## Salad_Dressing

Thanks again Neville!  The correction hit the mark!

I have run tests against what Pastel reports is the Stock on Hand and it all matches except for unposted orders.

The formula below calculates Stock on Hand which does not cater for unposted orders.  This means that if the order filling part of your operation does not run frequent updates, then the online stock may become incorrect and possibly cause frustration and anger with Internet customers (as well as with those having to deal with the customers).

One of the accounting ladies mentioned that Stock Available (instead of Stock on Hand) may take unposted orders into consideration and provide a more accurate stock count although (a) I'm not sure about this and (b) I don't know how to calculate Stock Available.  Any best practice insight / advice / info on this?

For those interested, here's the working formula for Stock on Hand as implemented in PHP:



```

$qO = (int)odbc_result($rs, "OpeningQty");
 $qB01 = (int)odbc_result($rs, "QtyBuyThis01");
$qB02 = (int)odbc_result($rs, "QtyBuyThis02");
$qB03 = (int)odbc_result($rs, "QtyBuyThis03");
$qB04 = (int)odbc_result($rs, "QtyBuyThis04");
$qB05 = (int)odbc_result($rs, "QtyBuyThis05");
$qB06 = (int)odbc_result($rs, "QtyBuyThis06");
$qB07 = (int)odbc_result($rs, "QtyBuyThis07");
$qB08 = (int)odbc_result($rs, "QtyBuyThis08");
$qB09 = (int)odbc_result($rs, "QtyBuyThis09");
$qB10 = (int)odbc_result($rs, "QtyBuyThis10");
$qB11 = (int)odbc_result($rs, "QtyBuyThis11");
$qB12 = (int)odbc_result($rs, "QtyBuyThis12");
$qBL = (int)odbc_result($rs, "QtyBuyLast");
 $qA01 = (int)odbc_result($rs, "QtyAdjustThis01");
$qA02 = (int)odbc_result($rs, "QtyAdjustThis02");
$qA03 = (int)odbc_result($rs, "QtyAdjustThis03");
$qA04 = (int)odbc_result($rs, "QtyAdjustThis04");
$qA05 = (int)odbc_result($rs, "QtyAdjustThis05");
$qA06 = (int)odbc_result($rs, "QtyAdjustThis06");
$qA07 = (int)odbc_result($rs, "QtyAdjustThis07");
$qA08 = (int)odbc_result($rs, "QtyAdjustThis08");
$qA09 = (int)odbc_result($rs, "QtyAdjustThis09");
$qA10 = (int)odbc_result($rs, "QtyAdjustThis10");
$qA11 = (int)odbc_result($rs, "QtyAdjustThis11");
$qA12 = (int)odbc_result($rs, "QtyAdjustThis12");
$qAL = (int)odbc_result($rs, "QtyAdjustLast");
 $qS01 = (int)odbc_result($rs, "QtySellThis01");
$qS02 = (int)odbc_result($rs, "QtySellThis02");
$qS03 = (int)odbc_result($rs, "QtySellThis03");
$qS04 = (int)odbc_result($rs, "QtySellThis04");
$qS05 = (int)odbc_result($rs, "QtySellThis05");
$qS06 = (int)odbc_result($rs, "QtySellThis06");
$qS07 = (int)odbc_result($rs, "QtySellThis07");
$qS08 = (int)odbc_result($rs, "QtySellThis08");
$qS09 = (int)odbc_result($rs, "QtySellThis09");
$qS10 = (int)odbc_result($rs, "QtySellThis10");
$qS11 = (int)odbc_result($rs, "QtySellThis11");
$qS12 = (int)odbc_result($rs, "QtySellThis12");
$qSL = (int)odbc_result($rs, "QtySellLast");
 $qOH = $qO + $qBL+$qB01+$qB02+$qB03+$qB04+$qB05+$qB06+$qB07+$qB08+$qB09+$qB10+$qB11+$qB12 + $qAL+$qA01+$qA02+$qA03+$qA04+$qA05+$qA06+$qA07+$qA08+$qA09+$qA10+$qA11+$qA12 - $qSL-$qS01-$qS02-$qS03-$qS04-$qS05-$qS06-$qS07-$qS08-$qS09-$qS10-$qS11-$qS12; 


```

----------

Dave A (12-Jul-12)

----------


## Neville Bailey

> One of the accounting ladies mentioned that Stock Available (instead of Stock on Hand) may take unposted orders into consideration and provide a more accurate stock count although (a) I'm not sure about this and (b) I don't know how to calculate Stock Available.  Any best practice insight / advice / info on this?


Quantities of Purchase Orders and Sales Orders outstanding can be accessed in the HistoryLines data table. You can filter for Purchase Orders and Sales Orders on the DocumentType field using the criteria of 106 and 102 respectively.

Perhaps you can then join the MultiStoreTrn and HistoryLines tables using the ItemCode field as the link, and then modify your formula accordingly? The only complication is that, in the HistoryLines table, a particular ItemCode could appear in multiple records.

----------

Salad_Dressing (16-Jul-12)

----------


## Salad_Dressing

> Quantities of Purchase Orders and Sales Orders outstanding can be accessed in the HistoryLines data table. You can filter for Purchase Orders and Sales Orders on the DocumentType field using the criteria of 106 and 102 respectively.
> 
> Perhaps you can then join the MultiStoreTrn and HistoryLines tables using the ItemCode field as the link, and then modify your formula accordingly? The only complication is that, in the HistoryLines table, a particular ItemCode could appear in multiple records.


Thank you, Neville!

I'm honestly not brave enough for this right now.  I'll keep it in mind should it be required in the future.

I'm pleased to have a working solution for Stock on Hand.  The unposted orders issue forces operational efficiency in the warehouse - a far greater value than more accurate PHP code.

Now to incorporate the live price and stock code into the website front-end...

----------


## bjsteyn

Hi Salad_Dressing, how do you find the IP address that you are remote connecting to, or do you need to use something like LogMeIn?

----------


## Salad_Dressing

> Hi Salad_Dressing, how do you find the IP address that you are remote connecting to, or do you need to use something like LogMeIn?


Hi bjsteyn, I'm assuming you are wanting to connect via the Internet and not on a LAN.

Let's also call your Pastel machine the "server".

You need to know if your ISP (Internet Service Provider) provides you with a fixed IP address.  Your server needs a fixed IP address for your scripts to correctly and consistently access your server.

From the server, using a browser, you can use this free Open Port Check Tool  to not only see your external facing IP address but also to check the open ports required for remote access.  There are also other methods you can use to determine router outward facing IP address by Googling "find ip address router."

Note that if your server is connected to the Internet via a Router, then accessing the outward facing IP of the router still does not grant access to your server.

Let me know what your setup is (network, hardware, OS, ) and I'll see if I can be of assistance.

----------


## bjsteyn

Hi @Salad_Dressing, my problem is the following. I have written a sales system and am wanting to integrate it with Pastel and add Web features to it. For Pastel i am looking at the moment to just do an export. I want to my system to send sales data and target data to a mysql database that is on a web server. The sales manager can then use it for setting targets and checking progress on sales vs budget, as-well as drilling down on data. Reps can use it to check there progress and daily sales figures as-well. Latest customer pricing / login information will also be sent to web server and customers can place there orders online, check on order delivery status, as-well as check on outstanding invoices, promotions etc.

I am thinking of running a timer on my ms access system that runs a php script to send and fetch data from the web server. Alternatively, I would also like it if I could run a cron task on the web server to remote access the ms access database on LAN Server via ODBC and then fetch and send data to the ms access database.

Any thoughts on this will be appreciated.

Thanx

----------


## Salad_Dressing

> Hi @Salad_Dressing, my problem is the following. I have written a sales system and am wanting to integrate it with Pastel and add Web features to it. For Pastel i am looking at the moment to just do an export. I want to my system to send sales data and target data to a mysql database that is on a web server. The sales manager can then use it for setting targets and checking progress on sales vs budget, as-well as drilling down on data. Reps can use it to check there progress and daily sales figures as-well. Latest customer pricing / login information will also be sent to web server and customers can place there orders online, check on order delivery status, as-well as check on outstanding invoices, promotions etc.
> 
> I am thinking of running a timer on my ms access system that runs a php script to send and fetch data from the web server. Alternatively, I would also like it if I could run a cron task on the web server to remote access the ms access database on LAN Server via ODBC and then fetch and send data to the ms access database.
> 
> Any thoughts on this will be appreciated.
> 
> Thanx


A fairly easy setup is to install XAMPP on your Pastel server, thereby turning it into a web server with PHP.  These PHP scripts are then used to locally access your Pastel (or other) database using the installed ODBC drivers.

In the meanwhile your remote web server also runs PHP but does not need to have ODBC drivers installed since it sends a request to the Pastel server (which does a local database query = very fast) and then receives the data again, parsing it and updating the web server database.

All that is required is a cron job on the remote web server to trigger the PHP script and for the Pastel server to be online.

----------


## bjsteyn

Hi Salad_Dressing, I use WAMP (same thing) . I have used WAMP before to run PHP Scripts locally on my ms access database via ODBC. It is just the remote connection to the ODBC database that I nead a clear picture on. 

If it is as fast as you say, i believe you :-) , then I could just make my website write and query data directly, and no need for an MySQL database on the Web Server. Wamp/Xampp has then no influence of the functioning of the remote connection or is it needed to make odbc available to remote web connections. I still need an IP then to remote connect. How do i go about setting up the IP. And if you can just explain to me what XAMPP has to do with the remote connection.

Thanx
BJ

----------


## Salad_Dressing

> Hi Salad_Dressing, I use WAMP (same thing) . I have used WAMP before to run PHP Scripts locally on my ms access database via ODBC. It is just the remote connection to the ODBC database that I nead a clear picture on. 
> 
> If it is as fast as you say, i believe you :-) , then I could just make my website write and query data directly, and no need for an MySQL database on the Web Server. Wamp/Xampp has then no influence of the functioning of the remote connection or is it needed to make odbc available to remote web connections. I still need an IP then to remote connect. How do i go about setting up the IP. And if you can just explain to me what XAMPP has to do with the remote connection.
> 
> Thanx
> BJ


Yes, you can query your Pastel server without having a MySQL database on a remote web server.  I use both because the Pastel server only runs 08h00 to 17h00 weekdays while the web server is 24/7.  So I duplicate the essential data onto the web server's database and then benefit from excellent up time.  The other benefit is that if the Pastel server is down for whatever reason, then the web server is still representing the company to the customers out there.  A further reason is that the partially duplicated data is actually an offsite backup of some of the most essential business data.

I personally have zero interest in trying to physically keep a server running, maintained and redundant, not in South Africa, not in any first-world country for that matter.  Web and cloud servers provide an essential service in this regard.

WAMP / XAMPP has got nothing to do with the ODBC driver set up.  PHP does have ODBC commands built in which work if you already have the ODBC drivers installed and the database properly exposed.  Once set up, it is a simple matter of doing an SQL query and then whatever else in PHP to parse and get the data out.

Yes, you need a static IP for remote connections.  You need to contact your ISP for this.

What OS is your Pastel machine running?

----------


## bjsteyn

Use Windows 7. I ( But should work on Win Xp as-well )

So you use wamp/xamp on the local server to send data from your odbc database to your MySQL database on the Web Server and also to fetch data
from the Web Server and insert it on the local server odbc database.

Do you use a specific program to simulate cron tasks, as windows task scheduler can't be configured to run eg. every few minutes? 

I agree an online duplicate database, looks like is the better option.

Thanx BJ

----------


## Salad_Dressing

> Use Windows 7. I ( But should work on Win Xp as-well )
> 
> So you use wamp/xamp on the local server to send data from your odbc database to your MySQL database on the Web Server and also to fetch data
> from the Web Server and insert it on the local server odbc database.
> 
> Do you use a specific program to simulate cron tasks, as windows task scheduler can't be configured to run eg. every few minutes? 
> 
> I agree an online duplicate database, looks like is the better option.
> 
> Thanx BJ


I call a PHP script on the Pastel server from out of a PHP script running on the web server.

Before this call, the web server initiates a session and provides a token which gets sent to the Pastel server which later forwards the same token back to the web server along with the query results.

Before the web server writes the results into the MySQL database, it first verifies the token to ensure the results were requested by itself earlier.

Because I initiate the request from the web server, I can use a cron job to repeat the request and not worry about doing that on Windows.

----------

bjsteyn (17-Jul-12)

----------


## bjsteyn

> I call a PHP script on the Pastel server from out of a PHP script running on the web server.
> 
> Before this call, the web server initiates a session and provides a token which gets sent to the Pastel server which later forwards the same token back to the web server along with the query results.
> 
> Before the web server writes the results into the MySQL database, it first verifies the token to ensure the results were requested by itself earlier.
> 
> Because I initiate the request from the web server, I can use a cron job to repeat the request and not worry about doing that on Windows.


Cool, can you mabe, PRETTY PLEASE, show me some sample code of the php script on the web server and the one the local server that gets this done. Will save me time if i can see some sample code. Thanx again, Salad_Dressing. 

Thanx
BJ

----------


## kleva

bjsteyn - If your ISP can't provide a permanent IP or want to charge too much per month for IP address, then most ADSL routers support a feature called dynamic DNS, which your webserver can use (eg: bjsteynpastel.dyndns.org). There would need to be a few tweaks depending on your router and your network (firewalls) to allow access for the query. I must however point out that security is a concern here as while performing the query your data/information may be being transmitted insecurely (fixed IP or no Fixed IP). I highly suggest you investigate some form of VPN connectivity between the servers (internal & external instead). I have some clients who run their complete operations off of dynamic DNS and can then host internally to avoid the security exposure (even if you only host the pages that need to show the data).

----------

bjsteyn (19-Jul-12)

----------


## bjsteyn

> bjsteyn - If your ISP can't provide a permanent IP or want to charge too much per month for IP address, then most ADSL routers support a feature called dynamic DNS, which your webserver can use (eg: bjsteynpastel.dyndns.org). There would need to be a few tweaks depending on your router and your network (firewalls) to allow access for the query. I must however point out that security is a concern here as while performing the query your data/information may be being transmitted insecurely (fixed IP or no Fixed IP). I highly suggest you investigate some form of VPN connectivity between the servers (internal & external instead). I have some clients who run their complete operations off of dynamic DNS and can then host internally to avoid the security exposure (even if you only host the pages that need to show the data).


Hi Kleva, Am working on a second version of sales system for distributors that I wrote and want to make it downloadable online. I want the web features to be easy to setup with the installation of the system. The user is not going to bother to setup a fixed IP, configuring a router etc. 

So maby i should try and automate cron task from local server and then send and request data from the website server. I could maby run a vbscript at startup that runs a php script every few minutes via a timer. Then use a token method for keeping track of what has been sent and received. Every time there is changes to the database there is token created on either side (local or web)  and once it has been updated on the other side, the token is marked as completed. The vbscript will use an ODBC connection to check for new tokens on the MS Access system and run a php script to check for new tokens on the web server.

VPN is probably i good idee if the system is specifically developed for a company, but as I want to license the software out it is not an option. But my knowledge on VPN's is to great, so if I am missing how I can use it in my scenario then please correct me.

Thanx
BJ

----------


## kleva

Ok, misundertood the purpose there then.

This could be a lot trickier to deal with as if you wanted to work "live" then all the VPN & connectivity tools have to built into your application - Support nightmare. From what it sounds like you are also already trying to deal with disparate systems (Windows, Linux, VB, php, ODBC, MS Access, MySQL) adding further complexity, maybe a rethink of process to try simplify?

I have configured in many similar scenarios and none of them is easy. No matter what there will always be a customisation required if there are so many systems involved or at least a very complicated installation process. You might be solving an immediate problem but creating a nightmare for yourself later.

Eg: If your clients are garenteed to all be windows based, then you should firstly reconsider your website hosting to be Windows based (IIS) and the database on the webserver can also be MSAccess to start and migrated through to MSSQL later? I don't recommend a MS Access based database (size and speed). Alternately, maybe changing the client side operation to a MS SQL base (even express) - Then your link can be direct and scheduled within SQL?

Maybe I am still misreading the intention and objective here. If you want, give me a call, I will provide some "free for first hour" :Big Grin:  info/advice (0835579832).

www.kleva.co.za

----------


## Salad_Dressing

> Cool, can you mabe, PRETTY PLEASE, show me some sample code of the php script on the web server and the one the local server that gets this done. Will save me time if i can see some sample code. Thanx again, Salad_Dressing. 
> 
> Thanx
> BJ


Sorry for the delay in answering. My recent schedule has been pressured by me coaching softball and also studying for softball umpire exam.

Here's the PHP code snippet triggered on the remote server by Chron job (Linux machine).  The token is a session token for security, and the prodCODES is a delimited text string of product codes I want prices and stock counts for.  The PHP code on the Pastel server will explode the string into an array and then traverse the codes while querying the ODBC database.  Note the function do_post_request() which allows large text strings to be posted without problems (I did modify the do_post_request() function very slightly, so follow Wez Furlong's link in the code to reference the original version).



```

$paramsA = array(
    'p1' => $token,
    'p2' => $prodCODES
);
 $paramsA = http_build_query($paramsA);
 $responseA = do_post_request("http://00.000.000.000/ODBC_get_Pastel_prices.php", $paramsA); //use server's fixed IP address here (you must take all security precautions necessary)
 function do_post_request($url, $data, $optional_headers = null)
{
    //=== function by Wez Furlong ============================================
    //=== http://wezfurlong.org/blog/2006/nov/http-post-from-php-without-curl/
    
    if (!isset($php_errormsg))
    {
        $php_errormsg = '';
    }
    
    $params = array('http' => array(
              'method' => 'POST',
              'content' => $data
            ));
            
    if ($optional_headers !== null)
    {
        $params['http']['header'] = $optional_headers;
    }
    
    $ctx = stream_context_create($params);
    $fp = @fopen($url, 'rb', false, $ctx);
    
    if (!$fp)
    {
        echo "<BR>could not open file >>> $url";
    }
    
    $response = @stream_get_contents($fp);
    
    if ($response === false)
    {
        echo "<BR>no content response for file >>> $url";
    }
    
    return $response;
} 


```

Here's the PHP code on the Pastel server (Windows machine) which does the actual query (I'm assuming you have a fairly recent PHP version and that ODBC functions are included).  The results are then put into a delimited string and returned with an echo.  This code does assume that an ODBC driver connected to a database is already setup on the Pastel server (the same machine executing this PHP code) and that you know that DSN (set up via Pervasive's Data Source Administrator found under Tools in the Pervasive Control Center).  Also note that this code accesses Pastel price and stock codes.  You may need to access something else.



```

<?php
 ini_set('display_errors', 'on');
ini_set('max_execution_time',1800); //1800 = 30 minutes
error_reporting(E_ALL);
 if ((isset($_POST['p1'])) && (isset($_POST['p2'])))
{
    $token = $_POST['p1'];
    $prodCODES = $_POST['p2'];
     $x = 0;
    $priceSTR = "";
     $prodCODE_array = explode("^", $prodCODES); //explode list into array
    $x = count($prodCODE_array);
    
    if ($x > 0) //more than 0 products code
    {
        //=== connect to the Pastel database ===
        $connect_string = "DRIVER={Pervasive ODBC Client Interface};".
        "SERVERNAME=127.0.0.1;".
        "SERVERDSN=Your_DSN;"; //Your_DSN is set up via Pervasive's Data Source Administrator found under Tools in the Pervasive Control Center
         $tbl = "MultiStoreTrn";
        $fld = "StoreCode,ItemCode,SellIncl01,SellIncl02,SellIncl04,SellIncl05,SellExcl01,SellExcl02,SellExcl04,SellExcl05,OpeningQty,QtyBuyThis01,QtyBuyThis02,QtyBuyThis03,QtyBuyThis04,QtyBuyThis05,QtyBuyThis06,QtyBuyThis07,QtyBuyThis08,QtyBuyThis09,QtyBuyThis10,QtyBuyThis11,QtyBuyThis12,QtyBuyThis13,QtyAdjustThis01,QtyAdjustThis02,QtyAdjustThis03,QtyAdjustThis04,QtyAdjustThis05,QtyAdjustThis06,QtyAdjustThis07,QtyAdjustThis08,QtyAdjustThis09,QtyAdjustThis10,QtyAdjustThis11,QtyAdjustThis12,QtyAdjustThis13,QtySellThis01,QtySellThis02,QtySellThis03,QtySellThis04,QtySellThis05,QtySellThis06,QtySellThis07,QtySellThis08,QtySellThis09,QtySellThis10,QtySellThis11,QtySellThis12,QtySellThis13,QtyBuyLast,QtyAdjustLast,QtySellLast";
        
        $conn = @odbc_connect($connect_string, '', ''); //odbc_connect() Returns an ODBC connection id or 0 (FALSE) on error.
        
        if ($conn) //connection OK
        {
            //echo "<BR>connection ID = $conn"; //DEBUG!
            
            //=== traverse product array and look for corresponding matches on Pastel database ===
            //$x = 20; //force small dataset for testing
                        
            for ($a = 0; $a <= $x - 1; $a++)
            {
                $pCode = $prodCODE_array[$a];
                 $sql = "SELECT $fld FROM $tbl WHERE StoreCode = 'XYZ' AND ItemCode = '$pCode'"; //MPW = Miracle Park Warehouse
                
                $rs = odbc_exec($conn,$sql);
                //$numfields = odbc_num_fields($rs);
                //$numrows = odbc_num_rows($rs);
                
                if (!$rs) exit("Error in SQL");
                
                $counter = 0;
                
                while (odbc_fetch_row($rs))
                {
                    $counter += 1;
                     $pA1 = odbc_result($rs, "SellIncl01");
                    $pB1 = odbc_result($rs, "SellIncl02");
                    $pC1 = odbc_result($rs, "SellIncl04");
                    $pD1 = odbc_result($rs, "SellIncl05");
                    $pA2 = odbc_result($rs, "SellExcl01");
                    $pB2 = odbc_result($rs, "SellExcl02");
                    $pCode2 = odbc_result($rs, "SellExcl04");
                    $pD2 = odbc_result($rs, "SellExcl05");
                    
                    $qO = (int)odbc_result($rs, "OpeningQty");
                    $storeC = odbc_result($rs, "StoreCode");
                    
                    $qB01 = (int)odbc_result($rs, "QtyBuyThis01");
                    $qB02 = (int)odbc_result($rs, "QtyBuyThis02");
                    $qB03 = (int)odbc_result($rs, "QtyBuyThis03");
                    $qB04 = (int)odbc_result($rs, "QtyBuyThis04");
                    $qB05 = (int)odbc_result($rs, "QtyBuyThis05");
                    $qB06 = (int)odbc_result($rs, "QtyBuyThis06");
                    $qB07 = (int)odbc_result($rs, "QtyBuyThis07");
                    $qB08 = (int)odbc_result($rs, "QtyBuyThis08");
                    $qB09 = (int)odbc_result($rs, "QtyBuyThis09");
                    $qB10 = (int)odbc_result($rs, "QtyBuyThis10");
                    $qB11 = (int)odbc_result($rs, "QtyBuyThis11");
                    $qB12 = (int)odbc_result($rs, "QtyBuyThis12");
                    $qB13 = (int)odbc_result($rs, "QtyBuyThis13");
                    $qBL = (int)odbc_result($rs, "QtyBuyLast");
                    
                    $qA01 = (int)odbc_result($rs, "QtyAdjustThis01");
                    $qA02 = (int)odbc_result($rs, "QtyAdjustThis02");
                    $qA03 = (int)odbc_result($rs, "QtyAdjustThis03");
                    $qA04 = (int)odbc_result($rs, "QtyAdjustThis04");
                    $qA05 = (int)odbc_result($rs, "QtyAdjustThis05");
                    $qA06 = (int)odbc_result($rs, "QtyAdjustThis06");
                    $qA07 = (int)odbc_result($rs, "QtyAdjustThis07");
                    $qA08 = (int)odbc_result($rs, "QtyAdjustThis08");
                    $qA09 = (int)odbc_result($rs, "QtyAdjustThis09");
                    $qA10 = (int)odbc_result($rs, "QtyAdjustThis10");
                    $qA11 = (int)odbc_result($rs, "QtyAdjustThis11");
                    $qA12 = (int)odbc_result($rs, "QtyAdjustThis12");
                    $qA13 = (int)odbc_result($rs, "QtyAdjustThis13");
                    $qAL = (int)odbc_result($rs, "QtyAdjustLast");
                    
                    $qS01 = (int)odbc_result($rs, "QtySellThis01");
                    $qS02 = (int)odbc_result($rs, "QtySellThis02");
                    $qS03 = (int)odbc_result($rs, "QtySellThis03");
                    $qS04 = (int)odbc_result($rs, "QtySellThis04");
                    $qS05 = (int)odbc_result($rs, "QtySellThis05");
                    $qS06 = (int)odbc_result($rs, "QtySellThis06");
                    $qS07 = (int)odbc_result($rs, "QtySellThis07");
                    $qS08 = (int)odbc_result($rs, "QtySellThis08");
                    $qS09 = (int)odbc_result($rs, "QtySellThis09");
                    $qS10 = (int)odbc_result($rs, "QtySellThis10");
                    $qS11 = (int)odbc_result($rs, "QtySellThis11");
                    $qS12 = (int)odbc_result($rs, "QtySellThis12");
                    $qS13 = (int)odbc_result($rs, "QtySellThis13");
                    $qSL = (int)odbc_result($rs, "QtySellLast");
                    
                    if ($counter == 0)
                    {
                        $priceSTR .= "$pCode;error^";
                    }
                    else
                    {
                        $qOH = $qO + $qBL+$qB01+$qB02+$qB03+$qB04+$qB05+$qB06+$qB07+$qB08+$qB09+$qB10+$qB11+$qB12 + $qAL+$qA01+$qA02+$qA03+$qA04+$qA05+$qA06+$qA07+$qA08+$qA09+$qA10+$qA11+$qA12 - $qSL-$qS01-$qS02-$qS03-$qS04-$qS05-$qS06-$qS07-$qS08-$qS09-$qS10-$qS11-$qS12;
                        
                        //formula = OpeningQty + QtyBuyLast + QtyBuyThis01 + QtyBuyThis02 + QtyBuyThis03 + QtyBuyThis04 + QtyAdjustLast + QtyAdjustThis01 + QtyAdjustThis02 + QtyAdjustThis03 + QtyAdjustThis04 - QtySellLast - QtySellThis01 - QtySellThis02 - QtySellThis03 - QtySellThis04
                         $priceSTR .= "$pCode;$pA1;$pB1;$pC1;$pD1;$pA2;$pB2;$pCode2;$pD2;$qOH^"; //build price string for this product
                    }
                }
             } //end for
            
            odbc_close($conn);
        }
        else //connection FAIL
        {
            exit("<BR>Connection Failed: " . $conn."!");
        }
        
        $priceSTR = substr($priceSTR, 0, -1); //strip last (excessive) "^" character
        
        echo "$priceSTR"; //this sends query results back to remote web server
    }
    else
    {
        echo "<BR>Zero product codes received to look up.";
    }
}
?>


```

Lastly (I'm not showing PHP code for this) the data string is received by the remote web server, exploded using the relevant delimiters, and then the database is updated with the price & stock data using the MySQL UPDATE statement.

Please note that my examples above are simplified and stripped of any identifying information.  You have to develop your own code with adequate Internet security measures.

Let me know if you need any help in setting up the Pervasive drivers on the Pastel server.

----------


## bjsteyn

Hey Salad Dressing, awesome. Will try out the code a bit later and let you know how it goes. (THANX ALOT) 

Just working on my new website www.pcdeals.co.za . Should launch within a week.

----------

