ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> Linux Gurus: write output query to cvs file
http://www.istechforum.com/YaBB.pl?num=1216311015

Message started by Kelloggs on 07/17/08 at 09:10:15

Title: Linux Gurus: write output query to cvs file
Post by Kelloggs on 07/17/08 at 09:10:15

I would like to output a odbc query to a text file. I have the following php code but it isnt working.

<?

//conneccion

$conn=odbc_connect('DBA','','');
if (!$conn)
{exit("Connection Failed: " . $conn);}
$sql="SELECT BKAR_INV_SONUM FROM BKARINV WHERE BKAR_INV_ORDDTE >= '2008-6-01' and BKAR_INV_ORDDTE <= '2008-6-30' AND BKAR_INV_LOC = 'UNI' AND BKAR_INV_INVDTE IS NULL";
$rs=odbc_exec($conn,$sql);
if (!$rs)
{exit("Error in SQL");}

while (odbc_fetch_array ($rs))
{
$so=odbc_result($rs,"BKAR_INV_SONUM");
$f = fopen("tmp_temp.csv", "w");
fwrite($f, $so);
}
fclose($f);
odbc_close($conn);
?>

I was reading on the net that I need to send the query output to an array, but not a clue how to do it.

Thanks,

Kelloggs

Title: Re: Linux Gurus: write output query to cvs file
Post by GasGiant on 07/17/08 at 09:17:10

What are you getting for errors?

Title: Re: Linux Gurus: write output query to cvs file
Post by Kelloggs on 07/17/08 at 09:33:21

There is not errors. It output only one record (either the first or last)

Thnks

Title: Re: Linux Gurus: write output query to cvs file
Post by GasGiant on 07/17/08 at 09:46:31

Ah, okay. That is because you are creating the file inside of the while, so you make a new file every time through. Try this...

Code:

$sql = "SELECT bkar_inv_sonum
FROM BKARINV
WHERE bkar_inv_orddte BETWEEN('2008-06-01' and '2008-06-30')
AND bkar_inv_loc = 'UNI'
AND bkar_inv_invdte IS NULL";
if(!$rs = odbc_exec($conn, $sql)) {exit(odbc_errormsg());}
$f = fopen("tmp_temp.csv", "w");
while (odbc_fetch_array ($rs))
{
$so = odbc_result($rs,"bkar_inv_sonum")."\n";
fwrite($f, $so);
}
fclose($f);
odbc_close($conn);

Notice that the fopen happens before the while loop. It does not write to the file until you use fclose, so it is building $f as an array.

I don't know what you are trying to find, but using the null or zero date will also pick up canceled orders. To get open orders you'd want to use something like

AND (bkar_invl_pqty <> '0' OR bkar_invl_ubo <> '0')

(instead of the NULL line) to grab open orders with quantities awaiting shipment.

I also fiddled with your SQL to use a BETWEEN instead of the other two cases. Much faster. :-)

Title: Re: Linux Gurus: write output query to cvs file
Post by Kelloggs on 07/17/08 at 10:30:30

The fwrite works very well.

I modified my sql and include "AND BKAR_INV_INVCD <> 'X"

Now, I can transfer data from PSQL to MySQL:

<?php
$connection = mysql_connect("localhost", "root", "") or die ("Unable to connect to server");
$db = mysql_select_db("intranet", $connection) or die ("Unable to select database");
$fcontents = file ('./tmp_temp.csv');
for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode("\"", $line);
//$sql = "insert into uni_uninvoiceso (". implode("'", $arr).")";
$sql = "insert into uni_uninvoiceso values ('". $arr ."')";
mysql_query($sql);
if(mysql_error()) {
echo mysql_error() ."<br>\n";
}
}
mysql_close($connection);
?>

Thanks,

Kelloggs

Title: Re: Linux Gurus: write output query to cvs file
Post by kkmfg on 07/17/08 at 10:54:52

Couldn't you have used ODBC to retrieve the data from PSQL and then directly save it in MySQL without the intervening CSV file step? I guess if the two parts are on different machines then maybe not... Otherwise why not do it directly?

Title: Re: Linux Gurus: write output query to cvs file
Post by GasGiant on 07/17/08 at 10:55:42

What is the reason for moving data from PSQL to MySQL? Building a reporting app or something for Internet use?

Title: Re: Linux Gurus: write output query to cvs file
Post by Kelloggs on 07/17/08 at 11:39:01

We receive/ship SOs with an external app (ms access/myql)

I was ask to setup an automatic email (cron job) to be send to the sales dept with all Last month's uninvoice SOs that are fully receive and fully ship :P

Open SO = Pervasive
Rec/Ship = Mysql

;)

Kelloggs

Title: Re: Linux Gurus: write output query to cvs file
Post by GasGiant on 07/17/08 at 11:53:33

Nifty. We invoice same day in most cases, but our production boss felt that he needed to see how things are progressing, so I gave him a report of "shipped, not invoiced" SOs that shows SO #, customer and the $ value of the released lines, both individually and as a total. Now it only takes him two seconds to find a reason to get on the invoicing clerk's case :D

Title: Re: Linux Gurus: write output query to cvs file
Post by kkmfg on 07/17/08 at 12:16:35

Again, is there a reason why the same machine cannot see both the PSQL and MySQL DBs? Because, PHP can connect to both DBs at the same time. Depending on how your queries work you may be able to query against both one at a time. This might be less efficient processing wise but it could be less coding and screwing around. Heck, it might even end up faster anyway since you wouldn't need the dump/load cycle anymore.

Kelloggs wrote:

Title: Re: Linux Gurus: write output query to cvs file
Post by Kelloggs on 07/17/08 at 12:38:02

Mysql and PSQL are on diferent servers, diferent OS. Heck even diferent locations.

:-?

Title: Re: Linux Gurus: write output query to cvs file
Post by GasGiant on 07/17/08 at 16:00:08

You can certainly read from one and write to the other in one PHP file. I do it in a several sections of my order entry code. All of our orders are entered in a web interface and the user has the option of saving the incomplete order as a Quote in a MySQL database on the web server or into the EDI tables. Saved quotes can be retrieved from MySQL and submitted to DBA/Evo in one, fluid motion.

Title: Re: Linux Gurus: write output query to cvs file
Post by kkmfg on 07/18/08 at 04:43:04

Kelloggs wrote:

Mysql and PSQL are on diferent servers, diferent OS. Heck even diferent locations.

:-?

It doesn't matter if they are on different planets so long as the machine running the PHP script can SEE both of them. In other words, does it have a valid network route to both machines and are the firewalls setup such that it can make connections to both DB's on the two machines? If so then you can connect to both from the script.