Unable to connect to SA using Win2003 + iiS 6.0 + FastCGI + PHP 5.3.2 (nts)

[Gordon]

Hi everyone,
I am a newbie to SQL Anywhere, but I have run my own webserver for a
while, and I consider myself to be a reasonably proficient PHP
programmer.

The server specs are: Win2003 + IIS 6.0 + FastCGI + PHP 5.3.2 (nts). I
use the server for testing stuff so it has several database servers on
it: MySQL, PostgreSQL, MSSQL and Oracle (XE).

I have been able to install SQL Anywhere and get a database called
"hits" up and running following the white paper at:
http://www.sybase.com/files/White_Papers/PHP_SQL_Anywhere.pdf

I can connect to the "hits" database (created following the
instructions in the white paper) with "Sybase Central" on my machine.
However, the problem I have it that I cannot connect to the "hits"
database using PHP using the sample PHP script given in the white
paper.

The phpinfo() output for my IIS server shows that the sqlmyadmin
extension is present, but it has an error regarding the SA client:

SQLAnywhere support:
- enabled
Allow Persistent Connections:
- Yes
Persistent Connections:
- 0/unlimited
Total Connections:
- 0/unlimited
PHP SQLAnywhere driver version:
- 2.0.10.0
SQLAnywhere client version:
- The SQLAnywhere client libraries could not be loaded.
- Please ensure that dbcapi.dll can be found in your PATH environment
variable.

The path listed in the "Environment" section of the phpinfo() page is:
C:\Program Files\SQL Anywhere 12\Bin32;
C:\WINDOWS\system32;
C:\WINDOWS;
C:\WINDOWS\System32\Wbem;
C:\PHP;
C:\MySQL\bin;
C:\Perl\bin;
C:\XEClient\bin;
C:\Oracle\app\oracle\product\10.2.0\server\bin;
C:\Program Files\TortoiseSVN\bin;
C:\Program Files (x86)\CVSNT;
C:\Program Files (x86)\GnuWin32\bin;
C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn;
C:\Program Files (x86)\OpenSSH\bin

Because the machine is running 64-bit Windows, I tried adding SQL
Anywhere's "Bin64" folder to the path too, but that didn't help.

I have confirmed that "dbcapi.dll" exists in both the Bin32 and Bin64
folders.

I found another post on this group regarding this problem, but it had
no solution posted :-(
http://groups.google.com/group/sql-anywhere-web-development/browse_thread/thread/cc3885c6a7368f58

Does anyone have any ideas on how I might be able to connect to the
SQL Anywhere database on my server from PHP?

many thanks in advance
Gordon

[Mohammed Abouzour]

Hello Gordon,

I am in the process of setting up an environment similar to yours (Windows 2003-x64+IIS6.0 + FastCGI+PHP 5.3.2(nts)).
I will see if I can replicate your problem.

--
Mohammed

--
You received this message because you are subscribed to the Google Groups "SQL Anywhere Web Development" group.
To post to this group, send email to sql-anywhere-w...@googlegroups.com.
To unsubscribe from this group, send email to sql-anywhere-web-dev...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sql-anywhere-web-development?hl=en.

[Mohammed Abouzour]

Gordon:

I was able to successfully setup a machine with Windows 2003-x64+IIS6.0+FastCGI+PHP 5.3.2 and connect
to a SQLAnywhere database through IIS. Your description look like you have covered the basics, so
we need to take a closer look at why dbcapi is not being loaded successfully.

In my installation, I tested my PHP installation by running PHP from the command line directly. Just
open up cmd.exe and run the following:
c:\> php-cgi.exe -i  > out.html

The contents of out.html can be view by any web browser. The -i option is the equivalent of running phpinfo().

There are a couple of thing to check:
1) If the output shows that dbcapi.dll could not be loaded then we need to look at the SQL Anywhere bin32
directory. Make sure your bin32 directory contains the following files:
dbcapi.dll, dblib12.dll, dblgen12.dll

If any of these files is missing, then you will need to re-run the SQL Anywhere 12 install
and make sure to select the 32-bit option.

2) Make sure that both the 64-bit and 32-bit directories are in the path. This is how my PATH environment
variable looks like as reported by phpinfo:
C:\php-5.3.2\;c:\windows\system32;c:\windows\system32\wbem;c:\program files\SQL Anywhere 12\bin64;
c:\program files\SQL Anywhere 12\bin32;

Can you confirm that your command line php-cgi.exe is working as expected above?

--
Mohammed

[Gordon]

Wow - thanks for doing all that investigative work Mohammed. I really
appreciate it !!

> ... re-run the SQL Anywhere 12 install

> and make sure to select the 32-bit option.

This was the key to the solution. I had used only the defaults for the
install, so the 32-bit option was *not* checked and *not* installed.
When I re-ran the installer to "modify" the install, I checked the 32-
bit option, and now the phpinfo() indicates that the SQLAnywhere
client is available.

Here is the relevant snippet from my phpinfo() output:

PHP SQLAnywhere driver version
- 2.0.10.0
SQLAnywhere client version

- 12.0.0.2483

I feel much more confident that I can continue now by myself and have
my PHP scripts connect to SQLAnywhere databases.

I would like to suggest that a note be added to the whitepaper I
referred to earlier, saying that even if you are installing on a
Windows 64-bit machine, you will need to install *both* the 32-bit and
the 64-bit versions of SQL Anywhere, i.e. override the default
"disabled" setting for 32-bit, if you wish to access the database
from PHP.

Finally, since my primary objective is to to test out SQL Anywhere
with IIS+PHP, I would like to know what is the minimum install I need
to achieve my goal. Do you know of any documentation on this point, or
could you tell me directly which of following installed options marked
"??" I can safely remove?

many thanks for all your fantastic help!
Gordon

========================
Currently installed:
========================

Databases:
?? SQL Anywhere (64-bit)
-- SQL Anywhere (32-bit)
?? SQL Anywhere (Windows Mobile)
?? Ultralite
Synchronization and Messaging
?? Mobilink (64-bit)
?? QAnywhere
?? SQL Remote (64-bit)
Other
-- Administration Tools (64-bit)
?? SQL Anywhere Monitor (64-bit)
?? Relay Server (64-bit)
?? Samples
?? High Availability
?? In-memory mode
?? Read-Only Scale-Out

========================
Currently NOT installed:
========================

Synchronization and Messaging
xx Mobilink (32-bit)
xx SQL Remote (32-bit)
Other
xx Administration Tools (32-bit)
xx SQL Anywhere Monitor (32-bit)
xx Relay Server (32-bit)

> On Mon, Aug 16, 2010 at 12:03 PM, Mohammed Abouzour <abouz...@gmail.com>wrote:
>
> > Hello Gordon,
>
> > I am in the process of setting up an environment similar to yours (Windows
> > 2003-x64+IIS6.0 + FastCGI+PHP 5.3.2(nts)).
> > I will see if I can replicate your problem.
>
> > --
> > Mohammed
>

> >>http://groups.google.com/group/sql-anywhere-web-development/browse_th...

>
> >> Does anyone have any ideas on how I might be able to connect to the
> >> SQL Anywhere database on my server from PHP?
>
> >> many thanks in advance
> >> Gordon
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "SQL Anywhere Web Development" group.
> >> To post to this group, send email to
> >> sql-anywhere-w...@googlegroups.com.
> >> To unsubscribe from this group, send email to

> >> sql-anywhere-web-dev...@googlegroups.com<sql-anywhere-web-development%2Bunsu...@googlegroups.com>
> >> .

[Gordon]

Hi Mohammed,
unfortunately I still cannot connect to my "hits" database via PHP :-(

I created the database and started it running like this:
- dbinit hits.db
- dbsrv12 hits.db

I was then able to connect to the database using the "Sybase Central"
program on my computer.

However, when I tried running the "hits.php" script which simply tries
to connect as the DBA user (to any database that is running ?) , the
sasql_connect() always fails.

I will send you a private email with the url of the site we are
talking about, so you can see the full phpinfo() for yourself.

thanks in advance for ideas and pointers for what I could try next.
Gordon

[Mohammed Abouzour]

On Tue, Aug 17, 2010 at 12:59 AM, Gordon <gordon...@gmail.com> wrote:

> ... re-run the SQL Anywhere 12 install

> and make sure to select the 32-bit option.

This was the key to the solution. I had used only the defaults for the
install, so the 32-bit option was *not* checked and *not* installed.
When I re-ran the installer to "modify" the install, I checked the 32-
bit option, and now the phpinfo() indicates that the SQLAnywhere
client is available.

I am glad that you were able tod get the driver loaded successfully.
 

I would like to suggest that a note be added to the whitepaper I
referred to earlier, saying that even if you are installing on a
Windows 64-bit machine, you will need to install *both* the 32-bit and
the 64-bit versions of SQL Anywhere, i.e. override the default
"disabled" setting for 32-bit,  if you wish to access the database
from PHP.

I will see that a note along those lines be added to the PDF document.
 

Finally, since my primary objective is to to test out SQL Anywhere
with IIS+PHP, I would like to know what is the minimum install I need
to achieve my goal. Do you know of any documentation on this point, or
could you tell me directly which of following installed options marked
"??" I can safely remove?

To get IIS+PHP working you only need the 32-bit component. To create databases
and administer the server you need the 64-bit components and the "Administration Tools" component.
The only other component that you might want to install is the "SQL Anywhere Monitor"  which allows
you to monitor the health of the SQL Anywhere server.

--
Mohammed
 

[Mohammed Abouzour]

On Tue, Aug 17, 2010 at 7:29 AM, Gordon <gordon...@gmail.com> wrote:

Hi Mohammed,
unfortunately I still cannot connect to my "hits" database via PHP :-(

I created the database and started it running like this:
 - dbinit hits.db
 - dbsrv12 hits.db

I was then able to connect to the database using the "Sybase Central"
program on my computer.

However, when I tried running the "hits.php" script which simply tries
to connect as the DBA user (to any database that is running ?) , the
sasql_connect() always fails.

Is the database server running on the same machine where IIS is running? What's the
connection string that you are using to connect?

If you have not changed the password of DBA then you should be able to connect
with the following PHP code:

<?php
    $conn = sasql_connect( "uid=dba;pwd=sql" );
    if( $conn ) {
        echo "Hello world! <br>";
        sasql_close( $conn );
    } else {
        echo "Failed to connect! <br>";
    }
?>

The above code will attempt to establish a connection to the SQL Anywhere server
through shared memory. If that does not work you might want to try connecting
over TCP/IP as follows:
    $conn = sasql_connect( "uid=dba;pwd=sql;links=tcpip;eng=hits" );


Hope that helps.

--
Mohammed

[Gordon]

Thanks Mohammed - you're a genius!

To summarize, a minimal install for PHP web developer's wishing to use
SQL Anywhere on a Windows 64-bit machine, would be something like the
following:

1. Install with all options *disabled* except the following:
Databases:
-- SQL Anywhere (32-bit)

Other
-- Administration Tools (64-bit)

-- In-memory mode

2. To create a database called "hits", as per the white paper, use:
-- dbinit hits.db
-- dbsrv12 hits.db

3. You can then connect to the above database from your PHP scripts
using the following:
-- sasql_connect("uid=dba;pwd=sql;links=tcpip;eng=hits")

Thereafter, you can experiment with Sybase Central to create new
databases and users.

Two further quick questions, if I may. Despite a lot of Googling I
cannot find out how to do the following:

(a) how do I stop a database that has been started with "dbsrv12"? At
the moment, the only way I have discovered is to open the Windows Task
Manager and kill the dbsrv12 process.

(b) How do I run the a database as a Windows service, so it starts
automatically when the server reboots?

many thanks
Gordon

On Aug 18, 1:28 am, Mohammed Abouzour <abouz...@gmail.com> wrote:

[Mohammed Abouzour]

On Tue, Aug 17, 2010 at 7:42 PM, Gordon <gordon...@gmail.com> wrote:

(a) how do I stop a database that has been started with "dbsrv12"? At
the moment, the only way I have discovered is to open the Windows Task
Manager and kill the dbsrv12 process.

You can use the dbstop command line utility. The documentation for dbstop
can be found here:
http://dcx.sybase.com/index.html#1200en/dbadmin/dbstop.html*d5e56508
 

(b) How do I run the a database as a Windows service, so it starts
automatically when the server reboots?

To start the database server as a service you can use the dbsvc command
line utility:
http://dcx.sybase.com/index.html#1200en/dbadmin/dbsvc-service-dbutilities.html*d5e55770

Also, you can find more documentation about the SQL Anywhere PHP driver
here:
http://dcx.sybase.com/index.html#1200en/dbprogramming/pg-php.html

[Gordon]

Dear Mohammed,
thank you so much for all your kind help. Thanks to you, I am making
good progress now with my SQL Anywhere project.

best regards
Gordon

On Aug 18, 12:00 pm, Mohammed Abouzour <abouz...@gmail.com> wrote:

> On Tue, Aug 17, 2010 at 7:42 PM, Gordon <gordonbate...@gmail.com> wrote:
>
> > (a) how do I stop a database that has been started with "dbsrv12"? At
> > the moment, the only way I have discovered is to open the Windows Task
> > Manager and kill the dbsrv12 process.
>
> > You can use the dbstop command line utility. The documentation for dbstop
>
> can be found here:http://dcx.sybase.com/index.html#1200en/dbadmin/dbstop.html*d5e56508
>
> > (b) How do I run the a database as a Windows service, so it starts
> > automatically when the server reboots?
>
> > To start the database server as a service you can use the dbsvc command
>

> line utility:http://dcx.sybase.com/index.html#1200en/dbadmin/dbsvc-service-dbutili...

[Gordon]

As the original poster of this thread, I would like to conclude by
adding notes on the answers to the questions I posed earlier.

(a) you can stop the "hits" server (created according to the
whitepaper link earlier in this thread) using the following command:

dbstop -c "uid=dba;pwd=sql;server=hits"

(b) you can create a Windows service to run the "hits" database either
using the following command (all on one line):

dbsvc -as -s auto "C:\Program Files\SQL Anywhere 12\Bin32\dbeng12.exe"
-n myservername -c 8M "C:\path\to\hits.db"

Alternatively, you can create a service using Sybase Central:
- login to Sybase Central
- in the right pane, select "Services" tab
- from "File" menu, select New -> Service
- follow instructions from wizard

Apologies to the veterans if this seems too trivial to post, but I
publish it here in the hope it may help other newbies get up and
running - and also in case I forget it ;-)

enjoy!
Gordon