ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Pervasive SQL >> >> query remote 2000i engine using php and odbc
http://www.istechforum.com/YaBB.pl?num=1132432715

Message started by visualkev on 11/19/05 at 12:38:35

Title: query remote 2000i engine using php and odbc
Post by visualkev on 11/19/05 at 12:38:35

I am a Pervasive newb and i am looking for instruction on using my IIS web server on our local intranet, running php to connect to, and query a 2000i workgroup engine on a separate win 2k server box on the same network. we use the most recent evoerp software. i'd like to know how to connect to the company data (read only) using php. i am sure i need to know more about btrieve also.
if there is already a source like this on the web, please post it. i haven't been able to find a proper intro that covers this specifically.

thanks,
kev

Title: Re: query remote 2000i engine using php and odbc
Post by Danimal on 11/21/05 at 06:45:02

Here is a demonstration that I created using ASP to run with IIS on a Windows 2000 server... www.istechsupport.com/dl/dba_demo.zip

Title: Re: query remote 2000i engine using php and odbc
Post by Kelloggs on 11/21/05 at 07:17:06

The only way to connect a webserver to pervasive is using a ODBC Connection.

PHP has extensives ODBC Functions.

http://ca.php.net/manual/en/function.odbc-fetch-row.php

Is your PHP running already on win2k? iis?

http://ca3.php.net/install.windows

Regards,

Kelloggs

Title: Re: query remote 2000i engine using php and odbc
Post by DrKev on 11/21/05 at 07:32:24

is it true that solutions like this only work with pervasive server engines as opposed to workgroup?

Title: Re: query remote 2000i engine using php and odbc
Post by Kelloggs on 11/21/05 at 08:01:22

I think it will work on both systems.

A web server and PHP Engine are independent.

What is important is that you have a working ODBC connection on the computer that runs the web server.

Here is an example:

<html>
<body><?php
$conn=odbc_connect('LOCALDBA','','');
if (!$conn)
{exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM BKAPPO";
$rs=odbc_exec($conn,$sql);
if (!$rs)
{exit("Error in SQL");}
echo "<table><tr>";
echo "<th>PO_Number</th>";
echo "<th>Vendor</th></tr>";
while (odbc_fetch_row($rs))
{
$PO=odbc_result($rs,"BKAP_PO_NUM");
$VENDOR=odbc_result($rs,"BKAP_PO_VNDNME");
echo "<tr><td>$PO</td>";
echo "<td>$VENDOR</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>
</body>
</html>

OS: Windows 2000 Profesional
Web Server: Apache 2.1
PHP: 4.1

http://www.w3schools.com/php/php_db_odbc.asp

You need to create a ODBC called LOCALDBA or change the one on the script.

Regards,

Kelloggs

Title: Re: query remote 2000i engine using php and odbc
Post by visualkev-again on 11/21/05 at 08:14:15

currently my developement web server is currently running on a win xp pro box. PHP is up and running there.

i have created a DSN called 'dba' on the win 2k box named 'liz', where the 2000i workgroup engine is running.

using this php code:
$dsn="Driver={Pervasive ODBC Client Interface};ServerName=liz;ServerDSN=dba;TransportHint=TCP:SPX";

$u="";
$pw="";
$DBAconn = odbc_connect("$dsn", "$u", "$pw") or die("error");

i get this error:
HTTP/1.1 500 Server Error Server: Microsoft-IIS/5.1 Date: Mon, 21 Nov 2005 16:57:01 GMT Connection: close Content-Type: text/html Content-Length: 93 The remote procedure call failed.

What am i missing?

Title: Re: query remote 2000i engine using php and odbc
Post by Kelloggs on 11/21/05 at 08:26:49

Open your php.ini

change display_errors to ON

display_errors = On

restart your IIS and let me know what's the error.

I think is your $dsn=

by the way dont use IIS use apache!!!

anyways, let me know what's the error that php shows.

Regards,

Kelloggs

Title: Re: query remote 2000i engine using php and odbc
Post by visualkev-again on 11/21/05 at 08:59:25

Kellogs-
i turned errors=on. I get the same error output as before:
HTTP/1.1 500 Server Error Server: Microsoft-IIS/5.1 Date: Mon, 21 Nov 2005 17:48:45 GMT Connection: close Content-Type: text/html Content-Length: 93 The remote procedure call failed.

I think you are right, it must be a DSN problem. i also tried connecting to 'DEMODATA' with the same result as above.
any tips on DSNs?

thanks for all your help in any case. learning works best with lots of trial and error.
I will look into Apache also.

Title: Re: query remote 2000i engine using php and odbc
Post by Kelloggs on 11/21/05 at 09:13:49

Use my example. I works very well for us, and it should work on your system.

Tips??

http://www.carlprothman.net/Default.aspx?tabid=81

The remote procedure call failed?

php has an extensive error reporting functions. too bad that you are using IIS :P

I like working on php, apache an all GLP software.

Email me directly if you wish.

Regards,

Kelloggs

Title: Re: query remote 2000i engine using php and odbc
Post by visualkev-again on 11/21/05 at 09:14:54

I read a bit about creating a DSN.

Quote:

...enter the path to your DDF files under Dictionary Location and Data File Locations.

from
http://www.mwaccounting.com/dba/white_paper.htm#NT%20Server/Workstation%20Engine

where do these DDF files come from? do i need to create them? if so, how?

http://www.mwaccounting.com/database.gif

also, in creation of my DSN, i checked Bound (DDFs Created). is that correct?

-Kevin

Title: Re: query remote 2000i engine using php and odbc
Post by Kelloggs on 11/21/05 at 09:37:52

I dont know much about Pervasive as a Workgrop, but
I dont think you need to create a DNS on you server.

It must be there already, isnt it?

What you need to do is to create a ODBC connection on the computer where your web server is running.

That computer has to have a Pervasive Client Software installed, by the way.

where do these DDF files come from?

C:\DBAMFG\*.DDF

Regards,

Kelloggs

Title: Re: query remote 2000i engine using php and odbc
Post by julian on 11/21/05 at 09:38:19

I think these steps are right, though I don't entirely remember what I did. I run the webserver and DBA on the same machine, however I was able to connect from another machine as I'll describe in a moment.

use system manager UT-J to create the DDF files.

then when I created the DSN on the DBA server, I'm pretty sure I pointed the DSN to my base DBAMFG folder. I then have to specify which company I want with my queries, rather than have a separate DSN for each company.

I am also able to run the webserver on another machine if I create a system dsn with the pervasive client driver on the separate webserver. I then just connect to the local DSN on that computer.

As a side note... I remember when I upgraded to v8.5 from 2000i that I had to recreate the DSN on the server, but it was conflicting with the previous DSN. I had trouble deleting the old DSN, but was able to fix it by going into control center and clicking on configuration. then along the top tool bar there's a button named Maintain Named Databases. I removed the old entry for DBA there and then went through the normal steps of creating the system DSN. You may or may not find that screen useful.

Title: Re: query remote 2000i engine using php and odbc
Post by julian on 11/21/05 at 09:39:26

oh yeah, i'm pretty sure i never checked the bound box.

Title: Re: query remote 2000i engine using php and odbc
Post by timgolds on 02/22/06 at 09:37:23

Three points.

1: you must create a DSN on the server for your database that is a System method not a user method otherwise the only one available is DEMODATA created by the pervasive install for testing.

2: If you are running your server and DBA on an XP box, You will have to modify the firewall to allow ODBC pipes to pass through WEB is automatic and you are prompted for DBA but not for external ODBC calls.

3: If you are running on Windows 2003 AS with Teamshare/Sharepoint, you MUST create a new non-managed space. default will only use MSSQL and not let you get to the Pervasive ODBC side unless you build an external script and use XML.

Hope it helps,

Tim