ISTech Support Forum - query remote 2000i engine using php and odbc

visualkev

Browser

So much to learn, so
little brain

Posts: 1

query remote 2000i engine using php and odbc
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

Back to top

IP Logged

Danimal Member Posts: 59 Gender:	Re: query remote 2000i engine using php and odbc Reply #1 - 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
Back to top	IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: query remote 2000i engine using php and odbc Reply #2 - 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

visualkev-again Browser So much to learn... Posts: 5	Re: query remote 2000i engine using php and odbc Reply #3 - 11/21/05 at 07:32:24 is it true that solutions like this only work with pervasive server engines as opposed to workgroup?
Back to top	IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: query remote 2000i engine using php and odbc
Reply #4 - 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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

visualkev-again

Browser

So much to learn...

Posts: 5

Re: query remote 2000i engine using php and odbc
Reply #5 - 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?

Back to top

IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: query remote 2000i engine using php and odbc Reply #6 - 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

visualkev-again

Browser

So much to learn...

Posts: 5

Re: query remote 2000i engine using php and odbc
Reply #7 - 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.

Back to top

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: query remote 2000i engine using php and odbc
Reply #8 - 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 Tongue

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

Email me directly if you wish.

Regards,

Kelloggs

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

visualkev-again

Browser

So much to learn...

Posts: 5

Re: query remote 2000i engine using php and odbc
Reply #9 - 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?

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

-Kevin

Back to top

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: query remote 2000i engine using php and odbc
Reply #10 - 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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

julian

Browser

I love YaBB 1G -
SP1!

Posts: 26

Re: query remote 2000i engine using php and odbc
Reply #11 - 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.

Back to top

IP Logged

julian Browser I love YaBB 1G - SP1! Posts: 26	Re: query remote 2000i engine using php and odbc Reply #12 - 11/21/05 at 09:39:26 oh yeah, i'm pretty sure i never checked the bound box.
Back to top	IP Logged

Tim Goldstein

Full Member

'Cause sooner or
later everyone needs
a chief geek

Posts: 212
Gender: male

Re: query remote 2000i engine using php and odbc
Reply #13 - 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

Back to top

Tim

IP Logged