DBD::ODBC as "middleware" between Crystal Reports and MySQL

[Dave Hammond]

For those who are thinking "just install the MySQL ODBC driver and
point Crystal to it", bear with me...

Most users connect to our MySQL databases via web applications. The
web apps and databases live on hosts which are in a "DMZ" segment of
our network, thus protecting our LAN from any direct Internet access.
However, in addition to the web apps, some power users use Crystal
Reports to directly access the databases, in exactly the manner you
might have assumed -- via the MySQL ODBC driver.

We have been tasked with moving the MySQL databases off the DMZ hosts
and into the LAN. This won't cause problems for the web apps, however
the Crystal users will be S.O.L. unless we can install some kind of
middleware app which lives in the DMZ and shuttles data between
Crystal and the MySQL databases.

I was thinking that we might be able to use DBD::ODBC for such an
app. Ideally, Crystal would connect to the middleware app via
DBD::ODBC, the app would validate and forward the SQL query to the
MySQL server, and then shuttle the results back to Crystal.

Does anyone have any comments on if something like this would be do-
able with DBD::ODBC? Or, maybe I'm trying to reinvent the wheel and
there is something already existing which does what we need?

Thanks in advance!

-Dave H.

 

[Ben Morrow]

For those who are thinking "just install the MySQL ODBC driver and
point Crystal to it", bear with me...

Most users connect to our MySQL databases via web applications. The
web apps and databases live on hosts which are in a "DMZ" segment of
our network, thus protecting our LAN from any direct Internet access.
However, in addition to the web apps, some power users use Crystal
Reports to directly access the databases, in exactly the manner you
might have assumed -- via the MySQL ODBC driver.

We have been tasked with moving the MySQL databases off the DMZ hosts
and into the LAN. This won't cause problems for the web apps, however
the Crystal users will be S.O.L. unless we can install some kind of
middleware app which lives in the DMZ and shuttles data between
Crystal and the MySQL databases.

I was thinking that we might be able to use DBD::ODBC for such an
app. Ideally, Crystal would connect to the middleware app via
DBD::ODBC, the app would validate and forward the SQL query to the
MySQL server, and then shuttle the results back to Crystal.

Does anyone have any comments on if something like this would be do-
able with DBD::ODBC? Or, maybe I'm trying to reinvent the wheel and
there is something already existing which does what we need?

No, you've got your thinking backwards. DBD::ODBC is an ODBC *client*:
that is, it runs at the application end of the connection. What you want
is an ODBC-capable *server*: something that runs at the database end, so
Crystal can talk to it. DBIx::MyServer allows you to create a fake MySQL
server that forwards all requests to another database; in combination
with the MySQL ODBC driver at the Crystal end, this may do what you
need.

Ben

 

[Dave Hammond]

Quoth Dave Hammond <[email protected]>:








No, you've got your thinking backwards. DBD::ODBC is an ODBC *client*:
that is, it runs at the application end of the connection. What you want
is an ODBC-capable *server*: something that runs at the database end, so
Crystal can talk to it. DBIx::MyServer allows you to create a fake MySQL
server that forwards all requests to another database; in combination
with the MySQL ODBC driver at the Crystal end, this may do what you
need.

Ben

Well, only as regards using DBD::ODBC ... the project concept itself
seems reasonable enough That aside, your pointer to
DBIx::MyServer looks to be just what I need. Thanks very much.

-Dave H.