ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> MySQL
http://www.istechforum.com/YaBB.pl?num=1214254226

Message started by dashdown on 06/23/08 at 13:50:26

Title: MySQL
Post by dashdown on 06/23/08 at 13:50:26

What do I need to do to connect to the Pervasive database using MySQL? I can connect using PHP
$conn=odbc_connect('newevo08','','');

but not directly from MySQL.

Title: Re: MySQL
Post by GasGiant on 06/23/08 at 15:40:16

what would be the purpose? connecting one DB to another seems rather... odd.

Title: Re: MySQL
Post by kkmfg on 06/23/08 at 16:47:00

It's not necessarily that odd. Sometimes you've got data from one application in one DB and data from another application in a different DB system and you want to query across them. Granted, you probably should import the relevant data from one DB system to the other but what if you want realtime results? It's not an every day sort of thing but sometimes it can be something you'd want to try to do. It's too complicate to do on a whim...

Title: Re: MySQL
Post by dashdown on 06/23/08 at 17:42:14

My boss wants our customers to be able to log on to our web site and view their inventory on hand and outstanding Sales Orders. Our web server supports MySQL and PHP. I've been told that the best way to accomplish this is with these two. Is there a better way?

Title: Re: MySQL
Post by kkmfg on 06/24/08 at 04:12:20

dashdown wrote:

PHP can also use ODBC databases. Your best bet is probably to setup an ODBC link to the Evo tables and then use PHP's ODBC module to access the data.

GasGiant has done a lot of work with Evo and PHP. You can see his code at http://www.evoerpwiki.com/index.php?title=PHP_Hacks

Title: Re: MySQL
Post by GasGiant on 06/24/08 at 07:05:11

Sorry, I read that and jumped to the conclusion that it was to be a 100% exchange of data, keeping a complete copy in MySQL.

It is possible to duplicate the SO data into MySQL (something we considered.) A chron job could update it as desired. I have never created a working version of that. We opted to connect directly to PSQL. I have tested customer account access, but I do not yet have a secure web server on which to run it. Probably this fall.

Security is the concern when accessing PSQL directly. Both protecting the PSQL from attack/injection and also limiting user access to only their own data. A good firewall and a correct internal IP to IP ODBC connection are the key to the first part, while careful PHP security is the answer to the second part.

Title: Re: MySQL
Post by Kelloggs on 06/24/08 at 07:29:19

Here is a good example: this code will show all employees

<?

$mydpt = "SELECT
BKPRMSTR.BKPR_EMP_NUM,
BKPRMSTR.BKPR_EMP_DEPT,
BKPRMSTR.BKPR_EMP_FNMI,
BKPRMSTR.BKPR_EMP_LNME,
RTRIM(LEFT(BKPRINFO.BKPR_INFO_REVNT_12, 8)) AS EXT,
RTRIM(SUBSTRING (BKPRINFO.BKPR_INFO_REVNT_12, 9, 16)) AS CELL,
RTRIM(SUBSTRING (BKPRINFO.BKPR_INFO_REVNT_12, 31, 100)) AS POSIT
FROM BKPRINFO INNER JOIN BKPRMSTR ON BKPRINFO.BKPR_INFO_NUM = BKPRMSTR.BKPR_EMP_NUM
WHERE BKPR_EMP_TERM = 'N'
ORDER BY BKPR_EMP_FNMI";

//conneccion

$conn=odbc_connect('DBA','','');
if (!$conn)
{exit("Connection Failed: " . $conn);}
$sql=$mydpt;
$rs=odbc_exec($conn,$sql);
if (!$rs)
{exit("Error in SQL");}
?>

<p><a href="javascript:history.back()"><font face="Tahoma" size="2">Back</font></a></p>

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#999999" id="AutoNumber1">
<tr>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> Dpt </font></td>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> F Name </font></td>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> L Name </font></td>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> Ext  </font></td>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> Cell  </font></td>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> Position </font></td>
<td bgcolor="#336699"><p align="center"><font face="Tahoma" size="2" color="#FFFFFF"> View </font></td>
</tr>
<?
//Color Code
$i = 0;
$max = 0;

while (odbc_fetch_row($rs))
{
//Color Code
$max++;
$bgcolor = $i ? "#EFEFEF" : "#FFFFFF";
$i ^= 1; // This is the bitwise XOR
//

$DEPT=odbc_result($rs,"BKPR_EMP_DEPT");
$FNMI=odbc_result($rs,"BKPR_EMP_FNMI");
$LNME=odbc_result($rs,"BKPR_EMP_LNME").",";
$EXT=odbc_result($rs,"EXT");
$CELL=odbc_result($rs,"CELL");
$POSIT=odbc_result($rs,"POSIT");
$NUM=odbc_result($rs,"BKPR_EMP_NUM");
?>
<tr bgcolor="<? echo $bgcolor ?>">
<td> <font face="Tahoma" size="2"><? echo $DEPT; ?></font> </td>
<td> <font face="Tahoma" size="2"><? echo $FNMI; ?></font> </td>
<td> <font face="Tahoma" size="2"><? echo $LNME; ?></font> </td>
<td> <font face="Tahoma" size="2"><? echo $EXT; ?></font> </td>
<td> <font face="Tahoma" size="2"><? echo $CELL; ?></font> </td>
<td> <font face="Tahoma" size="2"><? echo $POSIT; ?></font> </td>
<td> <font face="Tahoma" size="2"><a href="emp_info.php?key=<? echo $NUM; ?>">View</a></font> </td>
</tr>
<?
}
odbc_close($conn);
?>
</table>

Title: Re: MySQL
Post by kkmfg on 06/24/08 at 09:44:02

GasGiant wrote:

Security is the concern when accessing PSQL directly. Both protecting the PSQL from attack/injection and also limiting user access to only their own data. A good firewall and a correct internal IP to IP ODBC connection are the key to the first part, while careful PHP security is the answer to the second part.

Careful PHP security is the answer to SQL injection not firewalling. No amount of IP security is going to prevent a customer from saying that their name just happens to be:

');delete * from users;insert into users (username, password) values ("evilhacker","mypassword");

To me this looks like an odd name for someone's mother to give them.

Title: Re: MySQL
Post by GasGiant on 06/24/08 at 09:51:59

Yes. I mixed my pieces together a little too much. The firewall to refuse connections from anywhere but the app on the web server, and secure code for the rest. Verify user, filter/validate all input, change session ids often, block XSS... lots to do, lots to do. Keeps us coders employed :-)

And how about spanking whomever mixed style in with their markup in that code up above. Not to mention excessive referencing. ::)

Title: Re: MySQL
Post by Kelloggs on 06/24/08 at 10:36:15

On your router limit the access to your web server

Allow CUST_JOHNDOE WAN,XXX.XXX.XXX.XXX LAN,WEBSERVER TCP,80

Security is important but dont let it stop you from improving your services.

:P

Kelloggs

Title: Re: MySQL
Post by kkmfg on 06/24/08 at 10:36:56

GasGiant wrote:

And how about spanking whomever mixed style in with their markup in that code up above. Not to mention excessive referencing. ::)

;-) yes, there are more efficient ways that the code could have been laid out but I'll bet it gets the job done.

Just for reference for anyone else reading this who might want to do PHP work with databases:

Switching from php to html and back again is NAUGHTY. Keep the mode switches to a minimum. This means that lines like:

<td> <font face="Tahoma" size="2"><? echo $POSIT; ?></font> </td>

Aren't so kosher. It's actually more efficient to stay in the PHP interpreter and then use:

print('<td> <font face="Tahoma" size="2"' . $POSIT . '</font> </td>\n');

The only real pain there is that you've got to be exceedingly careful with your use of ' and ". You'll note that I used ' for the quote character in PHP since the original line included "'s in it.

Title: Re: MySQL
Post by GasGiant on 06/24/08 at 11:19:33

Once you learn how to use "heredocs" e.g. ---

echo <<<HTML

<h3>HTML goes in here</h3>

HTML;

--- to contain your cleaner HTML, like table headings, etc. it gets a lot easier to follow. This method has a few quirks, like the fact that you can use variables directly, but need curly braces around array variables, or that you can't use function calls, etc. inside it, but it is wicked handy for most HTML. Try to only use the print() block inside your while() block where it is really needed. For example...

<?php
/** Creates a table of sales orders that are open and unshipped **/

include($inc.'odbc_connect.inc');
$query = "SELECT bkar_inv_sonum, bkar_inv_orddte, bkar_inv_cuscod, bkar_inv_cusnme
FROM BKARINV
WHERE bkar_inv_loc = 'BW'
AND NOT(bkar_inv_invcd = 'Y')";
if(!$result = odbc_exec($db, $query)) {exit(odbc_errormsg());}
if(odbc_fetch_row($result)) {
@odbc_fetch_row($result, 0); // reset cursor
echo <<<HTML
<br/><h3>All Open Sales Orders</h3>
<p class="indiv alc hly nodisp">This listing does not include shipped orders, loaners, or trials.</p>
<table class="tr" id="liveSort">
<thead>
<tr>
<th>SO #    </th>
<th>Date</th>
<th>Customer #   </th>
<th>Customer</th>
</tr>
</thead>
<tbody>
HTML;

while(odbc_fetch_row($result)) {
print("<tr>
<td>".odbc_result($result,"bkar_inv_sonum")."</td>\n
<td>".odbc_result($result,"bkar_inv_orddte")."</td>\n
<td>".odbc_result($result,"bkar_inv_cuscod")."</td>\n
<td>".odbc_result($result,"bkar_inv_cusnme")."</td>\n
</tr>");
}
echo "</tbody></table><br/>";
}

?>

Title: Re: MySQL
Post by GasGiant on 06/24/08 at 11:36:08

Um... looks like we may have strayed a bit from the original beef, but.. I think the consensus is that connecting to PSQL is funner than messing with MySQL :-)

Title: Re: MySQL
Post by Kelloggs on 06/24/08 at 11:49:58

I suppose your odbc_connect.inc looks like:

$database = "DSN";
if (!$db = @odbc_connect($database,"user","pass")) {
echo "Could not connect to $database!\n";
exit;
}

right?

Title: Re: MySQL
Post by GasGiant on 06/24/08 at 11:51:06

<?php
/** Generic start for any work with live DBA PSQL database **/

if (!$db = odbc_connect('DBA-090','','')) {exit("Connection Failed: " . $db);}
$start_trans = odbc_autocommit($db, 0);
?>

Title: Re: MySQL
Post by dashdown on 06/25/08 at 06:15:05

Thanks for your replies. Being new to PHP and not having much experience with web sites either you all have been very helpful. Admittedly, sometimes I can be a little slow to catch on. I am still not clear on how to connect a PHP application on my remote web server to my local psql. Apparently there are ways within PHP to make the connection secure and I'll learn how to do that. But first I need to know if it's possible to make the connection and if it is, how to do it. My wed server is running Apache, PHP 5.0, and MySQL.

Thanks again for all your help.

Title: Re: MySQL
Post by GasGiant on 06/25/08 at 06:32:38

Remote? How remote? Is it your server or a commercial web host? Is it co-location or shared?

I'm not sure I'd want to give a shared web server access to my PSQL server.

Sounds like you got yourself into a BHA project. If you get stuck, you can ask me for a quote. My consulting rates are reasonable ;)