connecting to SQL database

[windandwaves]

Hi Folk

I have been asked to create an e-commerce website, where we need to
connect to an SQL database. The database is used to run their POS in
several branches. As this database is on the company server and we are
hosting the website with a webhost we have to solve an communication
issue.

Here are the options that I can see:

a. create a branch like environment on the webserver and then directly
access the branch database (OBDC??)

b. run a script that sucks data from the SQL environment and then ftps
it as a text file to a LAMP server. PHP and Mysql (my preferred
platform), will then notice the new file and do something with it?

c. have a direct OBDC connection between the website and the company's
server

What option do you recommend? What difficulties do you see?

I would love to get some ideas from you and more ideas on the typical
problems.

TIA

Nicolaas

[Jerry Stuckle]

Re: connecting to SQL database

windandwaves wrote:[color=blue]
> Hi Folk
>
> I have been asked to create an e-commerce website, where we need to
> connect to an SQL database. The database is used to run their POS in
> several branches. As this database is on the company server and we are
> hosting the website with a webhost we have to solve an communication
> issue.
>
> Here are the options that I can see:
>
> a. create a branch like environment on the webserver and then directly
> access the branch database (OBDC??)
>
> b. run a script that sucks data from the SQL environment and then ftps
> it as a text file to a LAMP server. PHP and Mysql (my preferred
> platform), will then notice the new file and do something with it?
>
> c. have a direct OBDC connection between the website and the company's
> server
>
> What option do you recommend? What difficulties do you see?
>
> I would love to get some ideas from you and more ideas on the typical
> problems.
>
> TIA
>
> Nicolaas
>[/color]

Nicolaas,

Too little information for what you're asking.

What database are they going to have on their company server? Do they want you
to have direct access to this server (possible security issues)? How often is
the database updated? Do they need live data? If not, how old can the data be
and still be valid?

There are any number of possibilities beyond what you're asking. But there's
way too many variables to even have a reasonable list of possibilities.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[windandwaves]

Re: connecting to SQL database

Hi Jerry

Thank you for your reply.

It is a SQL server on their company server. We dont want to talk to it
directly because of security issues. The database is updated daily most
of the time, with some specials going live almost instantly.

Your questions are actually really useful, because they give me more
confidence that I am on the right track.

What else do you like to know?

Thank you

Nicolaas

[Jerry Stuckle]

Re: connecting to SQL database

windandwaves wrote:[color=blue]
> Hi Jerry
>
> Thank you for your reply.
>
> It is a SQL server on their company server. We dont want to talk to it
> directly because of security issues. The database is updated daily most
> of the time, with some specials going live almost instantly.
>
> Your questions are actually really useful, because they give me more
> confidence that I am on the right track.
>
> What else do you like to know?
>
> Thank you
>
> Nicolaas
>[/color]

Nicolaas,

Sorry for the delay; I've been musing on your questions, thinking of the various
options. And I wouldn't necessarily say you're asking the right questions based
on what I asked! :-)

You indicate it's a SQL server. But there are several of them around there,
such as Microsoft's SQL Server, Oracle, DB2, MySQL, Postgres and many others.
What you need to do is going to be based the options available to you - and that
depends on the server.

I can understand the security issues - but many SQL database servers can handle
that. For instance, you can limit external connections to originating from one
IP address only (your web server), and further isolate that to only allow SELECT
requests on specific tables and/or views.

Or if the database supports replication, you can have all or part of it
replicated to another server.

But if they are limiting access to the company database due to security issues,
then you won't be able to suck the data from the database. The transfer will
need to be initiated by the server. Same with an ODBC connection to the server.

IOW, you're going to need to work with the DBA on the server end to discuss your
options and how he wants to allow you to handle it.

And I would suggest you then ask in a newsgroup specific to that database;
you'll get better answers on questions like this, because it really isn't a PHP
question - it's database-related.

And BTW - I typically do not use ODBC for something like this, if there is a
database-specific interface available. ODBC is great for when you don't have a
specific database in mind or it's one PHP can't talk to directly. But being
general-purpose, it will have higher overhead because it has to first translate
your calls to database-specific ones, then call the database. And you won't be
able to make use of features specific to your database.

In your case, you know what database you'll be working with, and if the
interface does exist you can make better use of your database.

Hope this helps.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===