need help extracting data from database to .xls file

[Atli]

I can't see anything wrong with the PHP code, and according to the output you posted, MySQL is passing PHP the 0 value.

So, you need to figure our why that is happening.
Can you execute queries on your database via a command line, or something similar?

Try seeing exactly how your table structure is from MySQL's point of view, by doing:
[code=mysql]SHOW CREATE TABLE alarmnotificati ondetail[/code]
Post that here. Maybe there is some problem with the types you use for your fields.

And select a few of the rows in that table that contain the values that are not displaying correctly. See if MySQL actually returns the correctly.
That way we can know for sure whether the data is getting lost in transition or whether it is simply not stored correctly.

[ndedhia1]

I wasn't able to do a SHOW CREATE TABLE but I am going to paste the table creation definitions from the LOG files that were created after the tables were created.
[code=oracle]
CREATE TEXT TABLE ALARMNOTIFICATI ON(LOGGINGVERSI ON DECIMAL(4,2),LO GGINGSTYLE CHAR(1),NOTIFIC ATIONID INTEGER,MILLISE CONDS BIGINT,TIMESTAM P VARCHAR,ACTIVAT IONID INTEGER,DEFINIT IONID INTEGER,DEFINIT IONSEVERITY INTEGER,CONSTRA INT PK_ALARMNOTIFIC ATION PRIMARY KEY(NOTIFICATIO NID))
CREATE TEXT TABLE ALARMNOTIFICATI ONDETAIL(CONDIT IONINDEX INTEGER,NOTIFIC ATIONID INTEGER,CONDITI ONID VARCHAR,TRIPVALUE VARCHAR,TRIPSUBJECTNAM E VARCHAR,TRIPCON TEXTNAME VARCHAR,CONSTRA INT PK_ALARMNOTIFIC ATIONDETAIL PRIMARY KEY(CONDITIONIN DEX))
SET TABLE ALARMNOTIFICATI ON SOURCE "alarmNotificat ion.txt;fs=,"
SET TABLE ALARMNOTIFICATI ONDETAIL SOURCE "alarmNotificat ionDetail.txt;f s=|"
[/code]
The log file shows that the TRIPVALUE is of type VARCHAR

Here is the data that is being stored in the tables:
The 4TH field is TRIPVALUE.
[code=oracle]
('3062'| '3383'| '10615'| '6049'| 'Prodcas01v2cas 0310'| 'CIT/lx-choptqgw2:8104@ 13613729/PST/ConsumerProxy@8 64697602');
('3063'| '3384'| '23601'| '56'| 'Prodcas01v2cas 0310'| 'POATP/CASQuote');
('3064'| '3386'| '23902'| '<FINE> <org.apache.act ivemq.broker.re gion.TopicSubsc ription> < Wed 2009/02/04 08:30:06:351 > <org.apache.act ivemq.broker.re gion.TopicSubsc ription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707 ,4,main]> Discarding state cleared, delta-discarded(6422) . TopicSubscripti on: consumer=ID:mdg c01a-55672-1233729340206-0:130:1:2, destination=top ic:///ProdRecap/IDL:consumers/RecapConsumer:1 .0---RecapLocalMD01---local, destinations=1, dispatchedQueue =0, delivered=63789 1, matched=0, discarded=6422'| 'ProdAMQBrokerm dgc01a.out'| 'ProdLogWatcher mdgc01a');
('3065'| '3387'| '23902'| '<FINE> <org.apache.act ivemq.broker.re gion.TopicSubsc ription> < Wed 2009/02/04 08:30:07:627 > <org.apache.act ivemq.broker.re gion.TopicSubsc ription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707 ,4,main]> Discarding state cleared, delta-discarded(419). TopicSubscripti on: consumer=ID:mdg c01a-55672-1233729340206-0:130:1:2, destination=top ic:///ProdRecap/IDL:consumers/RecapConsumer:1 .0---RecapLocalMD01---local, destinations=1, dispatchedQueue =0, delivered=64820 8, matched=0, discarded=6841'| 'ProdAMQBrokerm dgc01a.out'| 'ProdLogWatcher mdgc01a');
('3066'| '3389'| '23902'| '<FINE> <org.apache.act ivemq.broker.re gion.TopicSubsc ription> < Wed 2009/02/04 08:30:08:85 > <org.apache.act ivemq.broker.re gion.TopicSubsc ription.add> <Thread[ActiveMQ Transport: tcp:///172.16.93.10:62 333,4,main]> Discarding state cleared, delta-discarded(400). TopicSubscripti on: consumer=ID:cas 0034-59736-1233735671876-0:9:1:1, destination=top ic:///ProdCurrentMark et/IDL:consumers/CurrentMarketCo nsumer:1.0---CurrentMarketLo calExtentcas003 4---local, destinations=1, dispatchedQueue =1868, delivered=3325, matched=0, discarded=400'| 'ProdAMQBrokerp rdfe02.out'| 'ProdLogWatcher prdfe02');
('3067'| '3390'| '10615'| '10128'| 'Prodcas01v2cas 0170'| 'CD422/FTCBOEMD1:8104@ 10827017/PST/ConsumerProxy@4 7964553');
('3068'| '3391'| '1809'| '151'| 'ProdBC30x1Hybr idTradeServer1p rdbc30b'| 'MarketDataQueu e_14');
[/code]

The data seems to show that the values are being stored correctly because the 4th field is TRIPVALUE, and when it is a numeric value, the value is displayed correctly in the excel file, but when the TRIPVALUE is a string, the excel file displays a 0.
I think that the data is getting lost in transition.

thanks for the help

[Atli]

Ok, now I'm getting confused.

Based on your PHP code, I was assuming your were using MySQL, but now that I look over the thread again, I see you saying that you use Oracle.

If that is true, why do you use mysql functions in your PHP code?
Shouldn't you be using Oracle functions?

(I know very little about Oracle, so I might be missing something obvious to Oracle users.)

[ndedhia1]

We are connecting to a hypersonic Database and from what I have learned, the MySQL functions are very similar to the ones to connect and get data from a hypersonic Database

[ndedhia1]

Here are the properties for the file
ICS_DB.properti es
I believe that these properties are correct and have the sql.compare_in_ locale set to false, which should store the exact string that is inserted

[code=oracle]
#HSQL database
#Tue Feb 17 12:16:01 CST 2009
hsqldb.cache_fi le_scale=1
runtime.gc_inte rval=0
hsqldb.first_id entity=0
version=1.7.3
modified=yes
hsqldb.script_f ormat=0
sql.enforce_siz e=false
hsqldb.cache_si ze_scale=10
hsqldb.cache_sc ale=14
hsqldb.version= 1.7.3
hsqldb.log_size =200
sql.enforce_str ict_size=false
readonly=false
hsqldb.compatib le_version=1.7. 2
hsqldb.original _version=1.7.1
sql.compare_in_ locale=false
hsqldb.nio_data _file=true
hsqldb.cache_ve rsion=1.7.0
[/code]

[ndedhia1]

I have determined that the error is that the data is getting lost in transition. I have a script that is sent from the hypersonic database that shows what the table definitions are and they are as I thought they were. The TRIPVALUE type is VARCHAR. You dont see a problem with having TRIPVALUE as a VARCHAR do you?

[code=oracle]
CREATE TEXT TABLE ALARMNOTIFICATI ONDETAIL(CONDIT IONINDEX INTEGER,NOTIFIC ATIONID INTEGER,CONDITI ONID VARCHAR,TRIPVALUE VARCHAR,TRIPSUBJECTNAM E VARCHAR,TRIPCON TEXTNAME VARCHAR,CONSTRA INT PK_ALARMNOTIFIC ATIONDETAIL PRIMARY KEY(CONDITIONIN DEX))
SET TABLE ALARMNOTIFICATI ONDETAIL SOURCE "alarmNotificat ionDetail.txt;f s=|"
[/code]

[Atli]

I see.

I don't see a problem with using the VARCHAR type, not that I know nearly enough about HSQLDB to say that with any certainty. But in theory, it should not be a problem.

I am guessing the problem here is the way you are interacting with the database.
Could you post the code that connects to and sends the actual query to the database?

I searched, but I could only find a handful of sites that even mention HSQLDB and PHP on the same page, and none of them explained how they should be used together. (Am I missing something here?)

[ndedhia1]

I am sending all of the php code that connects to the database, does the select statement, writes to the html/xls files.
I dont believe you are missing anything. To try and clarify what I am trying to do..
Each day, a file is copied into a HSQLDB folder and transferred into a unix box that we use. The file is parsed into two seperate files and then these files are then imported back into the hypersonic database. The hypersonic database is then imported into and Oracle database. The data that is passed to the unix box is correct.
The data passed back to the hypersonic database after being manuplated is correct.
They data passed to the Oracle Database is also correct.
I believe that the problem is that the data is getting lost in transition when we try to display it in the html/xls files.

thank you for all the help you are giving me

[code=php]
<?php

// Gets today's date
function getTodaysDate() {

try {
if (!$today = mktime(0, 0, 0, date("m"), date("d"), date("Y")))
throw new Exception('Coul d not create date!');
}
catch (Exception $e) {
echo '<h3>Exception </h3>';
echo 'Error message: ' . $e->getMessage() . '<br />';
echo 'File and line: ' . $e->getFile() . '(' . $e->getLine() . ')<br />';
echo 'Trace: ' . $e->getTraceAsStri ng() . '<br />';
}
return $today;
}

//This function takes a string '$table' and parses the file 'mysql_$table.t xt' in order for it to enter a MySQL database without error
function insertMySQLTabl es($table) {

include 'config.php';
$filename = "mysql_".$table .".txt";
// The reason there is a 21 there is because originally, this file is taken from genLogs.
// genLogs returns results in the format:
// INSERT VALUES INTO TABLE <some_table> (".....
// This parse variable tells the function how much it has to delete until it reaches that first parenthesis.
$parse = strlen($table)+ 21;

try {
if(!$data = file($projectDi r.$filename))
throw new Exception ("File can't be opened: $projectDir$fil ename");

foreach($data as $line) {
// Again, the minus 4 this time is how much to parse from the end of the line.
$line = substr($line, $parse, -4) . "\r\n";
$line = str_replace("'" , "", $line);

// *** NOTE ***
// This next part is superdy-duper static and specific only to the entries for the table alarmdefinition .
// Long story short, the insert alarmdefinition txt has columns within field names and I am using commas
// to distinguish between fields.
if ((substr_count( $line, ',')==3) && ($table=="alarm definition")) {
$pos = strrpos($line, ",", "-6");
$line = substr_replace( $line, ' ', $pos, '1');
}

$parsed_mysql .= $line;

}
if(!$handle = fopen($projectD ir.$filename, 'w'))
throw new Exception ("File can't be updated: $projectDir.$fi lename");

if(fwrite($hand le, $parsed_mysql) === FALSE)
throw new Exception ("File can't be written to: $projectDir.$fi lename");

if(!fclose($han dle))
throw new Exception ("File can't be saved: $projectDir.$fi lename");

$query = "LOAD DATA INFILE '$projectDir$fi lename' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\r\\n'";
if(!$result = mysql_query($qu ery))
throw new Exception("Cann ot insert tables using the MySQL statement: $query");

unlink($filenam e);

}
catch (Exception $e) {
print '<h3>Exception </h3>';
print 'Error message: ' . $e->getMessage() . '<br />';
print 'File and line: ' . $e->getFile() . '(' . $e->getLine() . ')<br />';
print 'Trace: ' . $e->getTraceAsStri ng() . '<br />';
}
}

// Resets the database using credentials given from the config file
function resetTables() {

include 'config.php';
$query = $mysqlDir . ' -u ' . $dbuser . ' --password=' . $dbpass . ' -f ' . $dbname .' < ' . $projectDir.$re setTables;
$result = exec($query);

}

// This is the main function.
// Here is the order of events:
// 1) connect to db using credentials
// 2) extract archive
// 3) reset/create mysql tables
// 4) insertmysqldata to form tables
// 5) search/join tables
// 6) create html or excel/debug files
// 7) ftp files to devstor
function createHTMLTable ($currentDate) {

include 'config.php';
echo "<br/>" . date("h:i.s") . ": create $fileType Table started for date '$currentDate'< br/>";

try {

if (!$conn = mysql_connect($ dbhost, $dbuser, $dbpass))
throw new Exception("Cann ot connect to MySQL using host: '$dbhost', username: '$dbuser', password: '$dbpass'");

// If the database which is requested doesn't exist, make it
if (!mysql_select_ db($dbname)) {
$query = "CREATE DATABASE $dbname";
if(!$result = mysql_query($qu ery))
throw new Exception("Cann ot connect to database: '$dbname'");
}

if(!$shout=extr actArchive($cur rentDate)) {
echo "Timed Out. Skipping making $fileType table for $currentDate <br/><br/>";
return;
//throw new Exception ("Timed Out. Skipping making $fileType table for $currentDate");

}
echo date("h:i.s") . ": extractArchive Complete <br/>";

resetTables();
echo date("h:i.s") . ": resetTables Complete <br/>";

foreach ($tables as $singleTable) {
insertMySQLTabl es($singleTable );
}
echo date("h:i.s") . ": insertMySQLTabl es Complete <br/>";

if ($sort=="time")
$query = "SELECT a2.conditionind ex, a1.timestamp, a3.alarmdefinit ionname, a3.alarmdefinit ionseverity, a2.tripsubjectn ame, a2.tripcontextn ame, a2.tripvalue, a5.conditionnam e, a5.conditiontyp e FROM alarmnotificati on AS a1, alarmnotificati ondetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notification id=a2.notificat ionid AND a1.definitionid =a3.databaseide ntifier AND a2.conditionid= a5.databaseiden tifier ORDER BY a1.timestamp, a3.alarmdefinit ionseverity";
else if ($sort=="severi ty")
$query = "SELECT a2.conditionind ex, a1.timestamp, a3.alarmdefinit ionname, a3.alarmdefinit ionseverity, a2.tripsubjectn ame, a2.tripcontextn ame, a2.tripvalue, a5.conditionnam e, a5.conditiontyp e FROM alarmnotificati on AS a1, alarmnotificati ondetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notification id=a2.notificat ionid AND a1.definitionid =a3.databaseide ntifier AND a2.conditionid= a5.databaseiden tifier ORDER BY a3.alarmdefinit ionseverity, a1.timestamp";

while (true) {
if (!$result = mysql_query($qu ery))
throw new Exception("Cann ot search tables using the MySQL statement: $query");
// If the query returns correct results, quit
// otherwise wait until MySQL indexes and generates the results
else if ($row=mysql_fet ch_array($resul t))
break;
else if ($j > 20)
throw new Exception("TIME OUT ERROR.");
sleep(1);
$j.=1;
}
echo date("h:i.s") . ": MySQL Search Statement Complete <br/>";

// function createFile creates either an excel or html file depending on the variable $fileType
createFile($res ult, $currentDate);

// release memory and exit connection to the database
mysql_free_resu lt($result);
mysql_close($co nn);

// ftp file to an external location
ftpTables($curr entDate, $server_System, $user_name_Syst em, $user_pass_Syst em);
echo date("h:i.s") . ": Sent FTP Complete <br/>";

echo "Created $fileType tables for date '$currentDate' successfully! <br/>";

// Check to see if an html or excel file should be created
if ($fileType == "Excel")
{
if ($TableFile == "")
$TableFile = "$currentDate.x ls";
} else {

if ($TableFile == "")
$TableFile = "$currentDate.h tml";
}


echo "Location of $fileType file: $dstRootDir$Tab leFile<br/><br/>";

}
catch (Exception $e) {
echo '<h3>Exception </h3>';
echo 'Error message: ' . $e->getMessage() . '<br />';
echo 'File and line: ' . $e->getFile() . '(' . $e->getLine() . ')<br />';
echo 'Trace: ' . $e->getTraceAsStri ng() . '<br />';
}
}

// This function creates a debug file and either an excel or html(depending on variable $fileType file using two variables
// 1) $results: this is a variable which contains the MySQL search results
// 2) $currentDate: sets the active date of the archive being extracted
function createFile($res ult, $currentDate) {

include 'config.php';
$debugFile = "debug.txt" ;

// *** NOTE ***
// Because each TableFile will be FTP'ed to its own directory in the format /2008/06/20/....html/xls
// It is possible to change this next variable to whatever you want it to be without making things messy.
// Check to see if an html or excel file should be created
if ($fileType == "Excel")
{
if ($TableFile == "")
$TableFile = "$currentDate.x ls";
} else {

if ($TableFile == "")
$TableFile = "$currentDate.h tml";
}

if(!$debug_hand le = fopen($debugFil e, 'w'))
throw new Exception("Cann ot open file '$debug_handle' for writing");

if(!$handle = fopen($TableFil e, 'w'))
throw new Exception("Cann ot open file '$TableFile' for writing");

//write data to a file in table-form
fwrite($handle, "<table border='1'>");
fwrite($handle, "<tr>");

for($i = 0; $i < (mysql_num_fiel ds($result)-1); $i++) {
fwrite($handle, '<th>' . mysql_field_nam e($result, $i+1) . '</th>');
}
fwrite($handle, "</tr>");
while($row=mysq l_fetch_array($ result)){

fwrite($debug_h andle, $row['conditionindex '] . ",");
fwrite($debug_h andle, date("H:i:s", strtotime($row['timestamp'])) . ",");
fwrite($debug_h andle, $row['alarmdefinitio nname'] . ",");
fwrite($debug_h andle, $row['alarmdefinitio nseverity'] . ",");
fwrite($debug_h andle, $row['tripsubjectnam e'] . ",");
fwrite($debug_h andle, $row['tripcontextnam e'] . ",");
fwrite($debug_h andle, $row['tripvalue'] . ",");
fwrite($debug_h andle, $row['conditionname'] . ",");
fwrite($debug_h andle, $row['conditiontype'] . ",");
fwrite($debug_h andle, "\r\n");

if ((substr($row['timestamp'],11,12)>=$timeF rame_min) && (substr($row['timestamp'],11,12)<$timeFr ame_max)) {
fwrite($handle, "<tr>");
fwrite($handle, "<td>" . date("H:i:s:u", strtotime($row['timestamp'])) . "</td>");
fwrite($handle, "<td>" . $row['alarmdefinitio nname'] . "</td>");
if ($row['alarmdefinitio nseverity'] == 1)
fwrite($handle, "<td> High </td>");
elseif ($row['alarmdefinitio nseverity'] == 2)
fwrite($handle, "<td> Medium </td>");
elseif ($row['alarmdefinitio nseverity'] == 3)
fwrite($handle, "<td> Low </td>");
fwrite($handle, "<td>" . $row['tripsubjectnam e'] . "</td>");
fwrite($handle, "<td>" . $row['tripcontextnam e'] . "</td>");
if ($row['tripvalue'] == 1)
fwrite($handle, "<td> DOWN </td>");
else
fwrite($handle, "<td>" . $row['tripvalue'] . "</td>");
fwrite($handle, "<td>" . $row['conditionname'] . "</td>");
if ($row['conditiontype'] == 1)
fwrite($handle, "<td> Instrumentor </td>");
elseif ($row['conditiontype'] == 2)
fwrite($handle, "<td> Process Watcher </td>");
else
fwrite($handle, "<td> ERROR#NOTENOUGH INFO# </td>");
fwrite($handle, "</tr>");
}
}

fwrite($handle, "</table>");

// save and close either the excel or HTML file
if(!fclose($han dle))
throw new Exception("Cann ot close file '$TableFile'");

if(!fclose($deb ug_handle))
throw new Exception("Cann ot close file '$debug'");
}

// This function gets the previous workday
// This function effectively overlooks all weekends.
function getPreviousWork Day($presentDay ) {

$year = date("Y", $presentDay);
$month = date("m", $presentDay);
$day = date("d", $presentDay);

$yesterday = mktime(0, 0, 0, $month, $day-1, $year);

// These two 'if' statements overlook weekends.
if (date("N",$yest erday)==7)
$yesterday = mktime(0,0,0, $month, $day-3, $year);
else if (date("N",$yest erday)==6)
$yesterday = mktime(0,0,0, $month, $day-2, $year);

return $yesterday;

}

function extractArchive( $archiveDate) {

include 'config.php';
// This script works anywhere bash is supported.
// Change this only if the bash statement does not work.
$script = "bash -l $deepfrzdDir$ge nLogs $archiveDate";

$mysqlTextFiles = array(
"mysql_$tab les[0].txt",
"mysql_$tab les[1].txt",
"mysql_$tab les[2].txt",
"mysql_$tab les[3].txt");

try {

if(!$sshconn = openSSH($server _Local, $user_name_Loca l, $user_pass_Loca l))
throw new Exception("Coul d not open SSH connection using server: '$server_Local' , username: '$user_name_Loc al', password: '$password_Loca l'");

if (!$sftp = ssh2_sftp($sshc onn))
throw new Exception ("Cannot create an sftp connection using $sshconn");

// This 'foreach' gets the latest update time of the file on the server where genLogs created the mysql_insert files
// This value WILL get the previous file. That's OK!
// We will use that value to determine if the script has changed yet.
foreach($mysqlT extFiles as $singleMysqlTex tFile) {
// If no files are found, create a makebelive time of '0'
if(!$statinfo = ssh2_sftp_stat( $sftp, $genLogResultsD ir.$singleMysql TextFile))
$originalTime = 0;
else {
$originalTime = $statinfo['mtime'];
}
}

if (!ssh2_exec($ss hconn, $script))
throw new Exception("Coul d not run script: '$script_Local' ");

foreach($mysqlT extFiles as $singleMysqlTex tFile) {
if(!$write = fopen($singleMy sqlTextFile, "w"))
throw new Exception("Coul d not open destination file: $singleMysqlTex tFile");
}

// *** Tricky Part ***
// This is my clever way of determining if genLogs has completed running or not.
// The foreach loop runs through all 4 tables.
foreach($mysqlT extFiles as $singleMysqlTex tFile) {
// This while loop runs indefinately until genLog completes running.
// This statement has no otherwise break/timeout value because
// the automatic/manual form should prevent any wrong values from entering here.
while (true) {
// This while loop runs indefinately until genLog creates the files.
// This while loop only runs if for instance, we have to set $orignalTime to 0.
while (true) {
if ($statinfo = ssh2_sftp_stat( $sftp, $genLogResultsD ir.$singleMysql TextFile))
break;
else if ($time_out < $d) {
return false;
//throw new Exception ("Timed out getting file data from $server_Local on $archiveDate");
//$no_errors=fals e;
//die();
}
sleep(1);
$d+=1;
}
if ((($originalTim e < $statinfo['mtime'])) && (($statinfo['size'] > 0))) {
break;
}
else if ($time_out < $i) {
return false;
}
sleep(1);
$i+=1;
}
if(!ssh2_scp_re cv($sshconn, "$genLogResults Dir$singleMysql TextFile", $singleMysqlTex tFile))
throw new Exception('Tran sfer Incomplete');
}
}

catch (Exception $e) {
echo "Time out reached => $time_out";
echo '<h3>Exception </h3>';
echo 'Error message: ' . $e->getMessage() . '<br />';
echo 'File and line: ' . $e->getFile() . '(' . $e->getLine() . ')<br />';
echo 'Trace: ' . $e->getTraceAsStri ng() . '<br />';
}
return true;
}

// This function takes a variable $fileDate which is effectively the day of the archive which needs to be made.
// This function creates a directory equivelant to the archive's date.
// ** NOTE ** The directory which gets created, is composed of two parts.
// 1) $dstRootDir: this is where the root directory is to be /root/directory/ ....
// 2)$fileDate: this will put the directory into the format .../year/month/day/ .... html/xls
function ftpTables($file Date) {

include 'config.php';

// These 3 variables are used for the new directory
$year = substr($fileDat e, 0,4);
$month = substr($fileDat e, 5,2);
$day = substr($fileDat e, 8,2);

// *** NOTE ***
// If you want to change the root directory of the location where this file is going to be saved
// goto the config file and change $dstRootDir
$activeDir = "$year/$month/$day/SHMAlarms";

if ($fileType == "Excel")
{
if ($TableFile == "")
$TableFile = "$fileDate.xls" ;
} else {

if ($TableFile == "")
$TableFile = "$fileDate.html ";
}


$srcFile = $TableFile;
// COMMENT out the next line along with the try and catch clauses to work on program locally on your pc
// $finalDstDir = $dstRootDir.$ac tiveDir;
//
// try {
// if (!$sshconn = openSSH($server _System, $user_name_Syst em, $user_pass_Syst em))
// throw new Exception ("Cannot create connection using server: '$server_System ', username: '$user_name_Sys tem', password: '$user_pass_Sys tem'");
//
// if (!$sftp = ssh2_sftp($sshc onn))
// throw new Exception ("Cannot create an sftp connection using $sshconn");
//
// //If the directory isn't present make one
// if(!is_dir('ssh 2.sftp://' .$sftp. $finalDstDir))
// if (!ssh2_sftp_mkd ir($sftp, $finalDstDir, 0755, true))
// throw new Exception ("cannot make directory $finalDstDir");
//
// if (!$sftpStream = @fopen('ssh2.sf tp://'.$sftp.$finalD stDir. "/" . $srcFile, 'w'))
// throw new Exception ("Cannot open an sftpStream with '$finalDstDir/$srcFile'");
//
// if (!$data_to_send = @file_get_conte nts($srcFile))
// throw new Exception ("Could not get file contents from '$scrFile'");
//
// if (!@fwrite($sftp Stream, $data_to_send))
// throw new Exception ("Could not send data from file: $srcFile.");
//
// fclose($sftpStr eam);
// }
// catch (Exception $e) {
// echo '<h3>Exception </h3>';
// echo 'Error message: ' . $e->getMessage() . '<br />';
// echo 'File and line: ' . $e->getFile() . '(' . $e->getLine() . ')<br />';
// echo 'Trace: ' . $e->getTraceAsStri ng() . '<br />';
// }

}

// This function gets used a couple of times. It opens up and authenticates an SSH connection with another server
// Why did I use SSH? No clue... Just happened to be what worked first.
function openSSH($server , $user_name, $user_pass) {

try {
if(!$sshconn = ssh2_connect($s erver, 22))
throw new Exception ("Cannot create connection using server: '$server'");

// authenticates connection or die
if(!ssh2_auth_p assword($sshcon n, $user_name, $user_pass))
throw new Exception ("Cannot authenticate connection using username: '$user_name', password: '$user_pass'");
}
catch (Exception $e) {
echo '<h3>Exception </h3>';
echo 'Error message: ' . $e->getMessage() . '<br />';
echo 'File and line: ' . $e->getFile() . '(' . $e->getLine() . ')<br />';
echo 'Trace: ' . $e->getTraceAsStri ng() . '<br />';
}

return $sshconn;
}

// Used only when, in manual-mode, the user requests a span of dates instead of just one
function createMultipleH TMLTables($requ estedDate, $finalDate) {
createHTMLTable (date("Y-m-d", $requestedDate) );
while ($requestedDate >$finalDate) {
$requestedDate = getPreviousWork Day($requestedD ate);
createHTMLTable (date("Y-m-d", $requestedDate) );
}

}

?>
[/code]

[code=php]

[Atli]

Ok, so this is how I understood that code.

1. First, you fetch a bunch of text files from a remote server.
2. You parse these text files and place them into a empty MySQL database.
3. You compile your Excel or HTML files based on the new data in the MySQL database.
4. And finally, you send the new Excel or HTML files to a remote server.

Is that correct?

Try something for me.
In line #144, paste the following code:
[code=php]/* FOR DEBUGGIN ONLY! */
$debug_query = "SELECT * FROM alarmnotificati ondetail";
$debug_result = mysql_query(deb ug_query) or die(mysql_error ());

echo "<data>\n";
while($debug_ro w = mysql_fetch_ass oc($debug_resul t)) {
echo "\t<row>\n" ;
foreach($debug_ row as $_i => $_col) {
echo "\t\t<". $_i .">";
echo $_col;
echo "</". $_i .">\n";
}
echo "\t</row>\n";
}
echo "</data>\n";[/code]
Execute the PHP page in a browser (this is how you usually do it right?), open the source of the resulting page and copy the contents of the <data>...</data> tags here.

This should show us exactly what is inside the MySQL database, so we can determine whether or not your data is being added correctly.

If this prints incorrect values, there is a problem with how your MySQL tables are being created and populated.

[ndedhia1]

You are correct on the understanding of the code.

I added the code you asked me to but for line 3 in your code you asked me to add, I had to add an '$' in front of debug_query in the line:
$debug_result = mysql_query(deb ug_query) or die(mysql_error ())
You are also correct that I use a browser to execute the PHP page.
I had to comment out the ftp part so that I can do all the work locally.

<data>
1 443 10601 1 Prodcas01v2cas0 135 ALL 2 442 11326 1 Prodfixcas14v2f ix2a ALL 3 445 15401 1 Prodfixcas14v2f ix2a ALL 4 444 15401 1 Prodfixcas14v2f ix2a ALL 5 446 10601 1 Prodcas01v2cas0 075 ALL 6 447 10306 1 Prodmdx01prdmdx 99b ALL 7 449 15401 1 Prodcas01v2cas0 135 ALL 8 448 10306 1 Prodmdx01prdmdx 90c ALL 9 450 15401 1 Prodcas01v2cas0 135 ALL 10 452 15401 1 Prodcas01v2cas0 075 ALL 11 451 15401 1 Prodcas01v2cas0 075 ALL 12 453 15401 1 Prodcfix98v2prd cfix2 ALL 13 454 15401 1 Prodcfix98v2prd cfix2 ALL 14 456 15401 1 Prodmdx01prdmdx 99b ALL 15 455 15401 1 Prodmdx01prdmdx 99b ALL 16 457 10601 1 Prodcas01v2cas0 091 ALL 17 459 15401 1 Prodmdx01prdmdx 90c ALL 18 458 11326 1 Prodfixcas13v2f ix1a ALL 19 460 15401 1 Prodmdx01prdmdx 90c ALL 20 461 10701 1 Prodfixcas07v2f ix3b ALL 21 462 15401 1 Prodcas01v2cas0 091 ALL 22 463 15401 1 Prodcas01v2cas0 091 ALL 23 464 15401 1 Prodfixcas13v2f ix1a ALL 24 465 15401 1 Prodfixcas13v2f ix1a ALL 25 466 15401 1 Prodfixcas07v2f ix3b ALL 26 467 15401 1 Prodfixcas07v2f ix3b ALL 27 468 10701 1 Prodfixcas15v2f ix20b ALL 28 469 15401 1 Prodfixcas15v2f ix20b ALL 29 470 15401 1 Prodfixcas15v2f ix20b ALL 30 471 10601 1 Prodcas01v2cas0 240 ALL 31 472 10701 1 Prodfixcas05v2f ix6 ALL 32 473 10601 1 Prodcas01v2cas3 003 ALL 33 475 15401 1 Prodcas01v2cas0 240 ALL 34 474 15401 1 Prodcas01v2cas0 240 ALL 35 477 15401 1 Prodfixcas05v2f ix6 ALL 36 478 10601 1 Prodcas01v2cas0 071 ALL 37 476 15401 1 Prodfixcas05v2f ix6 ALL 38 479 10601 1 Prodcas01v2cas0 170 ALL 39 480 15401 1 Prodcas01v2cas3 003 ALL 40 481 15401 1 Prodcas01v2cas3 003 ALL 41 482 10601 1 Prodcas01v2cas0 068 ALL 42 483 15401 1 Prodcas01v2cas0 071 ALL 43 484 15401 1 Prodcas01v2cas0 071 ALL 44 485 15401 1 Prodcas01v2cas0 170 ALL 45 486 15401 1 Prodcas01v2cas0 170 ALL 46 487 15401 1 Prodcas01v2cas0 068 ALL 47 488 15401 1 Prodcas01v2cas0 068 ALL 48 489 10601 1 Prodcas01v2cas0 305 ALL 49 491 15401 1 Prodcas01v2cas0 305 ALL 50 492 15401 1 Prodcas01v2cas0 305 ALL 51 490 10601 1 Prodcas01v2cas0 234 ALL 52 493 15401 1 Prodcas01v2cas0 234 ALL 53 494 15401 1 Prodcas01v2cas0 234 ALL 54 496 15401 1 Prodmdx01prdmdx 91a ALL 55 495 10306 1 Prodmdx01prdmdx 91a ALL 56 497 15401 1 Prodmdx01prdmdx 91a ALL 57 499 15401 1 Prodcas01v2cas0 023 ALL 58 500 10601 1 Prodcas01v2cas0 023 ALL 59 498 15401 1 Prodcas01v2cas0 023 ALL 60 502 15401 1 Prodfixcas02v2f ix3a ALL 61 501 15401 1 Prodfixcas02v2f ix3a ALL 62 503 10701 1 Prodfixcas02v2f ix3a ALL 63 506 15401 1 Prodcas01v2cas0 209 ALL 64 505 15401 1 Prodcas01v2cas0 209 ALL 65 504 10601 1 Prodcas01v2cas0 209 ALL 66 508 15401 1 Prodcas01v2cas2 006 ALL 67 507 15401 1 Prodcas01v2cas2 006 ALL 68 511 10601 1 Prodcas01v2cas2 006 ALL 69 510 15401 1 Prodcas01v2cas0 026 ALL 70 512 10601 1 Prodcas01v2cas0 026 ALL 71 509 15401 1 Prodcas01v2cas0 026 ALL 72 513 10701 1 Prodfixcas02v2f ix7 ALL 73 516 10601 1 Prodcas01v2cas0 187 ALL 74 515 15401 1 ProdPRDCONS6Ins trumentationMon itor ALL 75 514 15401 1 ProdPRDCONS6Ins trumentationMon itor ALL 76 518 15401 1 Prodfixcas02v2f ix7 ALL 77 517 15401 1 Prodfixcas02v2f ix7 ALL 78 519 15401 1 Prodcas01v2cas0 187 ALL 79 520 15401 1 Prodcas01v2cas0 187 ALL 80 521 10701 1 Prodfixcas01v2f ix9 ALL 81 523 15401 1 Prodfixcas01v2f ix9 ALL 82 522 15401 1 Prodfixcas01v2f ix9 ALL 83 525 15401 1 Prodmdcas02v2md cas200 ALL 84 524 15401 1 Prodmdcas02v2md cas200 ALL 85 526 10601 1 Prodcas01v2cas0 158 ALL 86 529 10601 1 Prodcas01v2cas3 022 ALL 87 528 15401 1 Prodcas01v2cas0 158 ALL 88 527 15401 1 Prodcas01v2cas0 158 ALL 89 530 10701 1 Prodfixcas04v2f ix8 ALL 90 531 10701 1 Prodfixcas10v2f ix3b ALL 91 534 10601 1 Prodcas01v2cas0 244 ALL 92 533 15401 1 Prodcas01v2cas3 022 ALL 93 532 15401 1 Prodcas01v2cas3 022 ALL 94 536 15401 1 Prodfixcas04v2f ix8 ALL 95 535 15401 1 Prodfixcas04v2f ix8 ALL 96 538 15401 1 Prodfixcas10v2f ix3b ALL 97 537 15401 1 Prodfixcas10v2f ix3b ALL 98 539 15401 1 Prodcas01v2cas0 244 ALL 99 540 15401 1 Prodcas01v2cas0 244 ALL 100 541 10601 1 Prodcas01v2cas0 056 ALL 101 542 10601 1 Prodcas01v2cas0 182 ALL 102 543 15401 1 Prodcas01v2cas0 056 ALL 103 544 15401 1 Prodcas01v2cas0 056 ALL 104 545 10701 1
[Snipped]
</data>

I was looking at the output and I realized by looking at these few lines from the output, that the data is incorrect. There should be a string then the 0 that I have bolded.
I hope that I have put the correct tags on the above output and did not confuse too much with the output.

4815 4514 23902 0 ProdAMQBrokermd gc01a.out ProdLogWatcherm dgc01a
4816 4515 23902 0 ProdAMQBrokermd gc01a.out ProdLogWatcherm dgc01a
4817 4516 23902 0 ProdAMQBrokermd gc01a.out ProdLogWatcherm dgc01a
4818 4517 23902 0 ProdAMQBrokermd gc01a.out ProdLogWatcherm dgc01a

[Atli]

So the output also had 0 where there should have been text?
That would indicate that your code is parsing the files incorrectly.

On line #60, you use the LOAD DATA INFILE command to load the data into MySQL.
Could we see an example of that file? If you could locate a line that causes the 0 error to be inserted, that would be very helpful.

A sample of the file you parse into the file used on line #60 would also be helpful.

P.S.
What happened to the XML-like output my code would have generated?
Did you perhaps copy the output from the browser window, rather than the source view?

P.P.S.
I cut a bit of the output you posted away. It was like 240KB of data, so it was making the post a bit to large :)
I still have it tho, so I can go through it later if it's needed.

[ndedhia1]

Here is a sample of the file '$projectDir$fi lename' from the line:

$query = "LOAD DATA INFILE '$projectDir$fi lename' REPLACE INTO
TABLE $table FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\r\\n'";

I use to use this line for the query:
$query = "LOAD DATA INFILE '$projectDir$fi lename' REPLACE INTO TABLE $table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'' LINES TERMINATED BY '\\r\\n'";

But because of the ',' I had to change the line to a '|' because when it parsed the fields for output, it would parse only part of TRIPVALUE because that field has commas in the middle, separating numeric value. I changed some JAVA code to have each value appended with a PIPE rather then COMMA.
Initially, when this code was first written, the fields were all separated by COMMAS and the output to the html file was sufficient. Later on thru the project, I had the output displayed to an excel file and there was one field, TRIPVALUE that seemed to have 0s in it when it should have had some more data. That is when I discovered that TRIPVALUE, when it was not a numeric value, was outputting something incorrect. When I looked at the data it was supposed to output, and saw that the field itselt had COMMAS in it, I had to have the output separated by PIPES so that I could use PIPES to start and stop the search of each field.




I have bolded the TRIPVALUE field:
[code=oracle]
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3271'| '3522'| '12512'| '4892'| 'Prodcas01v2cas 1003'| 'CME 13/10.5.108.184:43 672@1765346/BD/ConsumerProxy@8 03111635');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3273'| '3523'| '12512'| '1148'| 'Prodcas01v2cas 1004'| 'CME16/10.5.108.185:34 528@8936339/BD/ConsumerProxy@-789513853');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3275'| '3524'| '10615'| '18623'| 'Prodcas01v2cas 0171'| 'CD449/dbcashbr3:8102@ 30276098/PST/ConsumerProxy@-686960907');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3276'| '3525'| '23902'| '<FINE> <org.apache.act ivemq.broker.re gion.TopicSubsc ription> < Wed 2009/02/04 08:40:17:600 > <org.apache.act ivemq.broker.re gion.TopicSubsc ription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707 ,4,main]> Discarding state cleared, delta-discarded(559). TopicSubscripti on: consumer=ID:mdg c01a-55672-1233729340206-0:130:1:2, destination=top ic:///ProdRecap/IDL:consumers/RecapConsumer:1 .0---RecapLocalMD01---local, destinations=1, dispatchedQueue =1992, delivered=38388 77, matched=0, discarded=24317 '| 'ProdAMQBrokerm dgc01a.out'| 'ProdLogWatcher mdgc01a');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3277'| '3526'| '23902'| '<FINE> <org.apache.act ivemq.broker.re gion.TopicSubsc ription> < Wed 2009/02/04 08:40:19:516 > <org.apache.act ivemq.broker.re gion.TopicSubsc ription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707 ,4,main]> Discarding state cleared, delta-discarded(592). TopicSubscripti on: consumer=ID:mdg c01a-55672-1233729340206-0:130:1:2, destination=top ic:///ProdRecap/IDL:consumers/RecapConsumer:1 .0---RecapLocalMD01---local, destinations=1, dispatchedQueue =1986, delivered=38538 91, matched=0, discarded=24909'| 'ProdAMQBrokerm dgc01a.out'| 'ProdLogWatcher mdgc01a');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3278'| '3527'| '23902'| '<FINE> <org.apache.act ivemq.broker.re gion.TopicSubsc ription> < Wed 2009/02/04 08:40:25:723 > <org.apache.act ivemq.broker.re gion.TopicSubsc ription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707 ,4,main]> Discarding state cleared, delta-discarded(2172) . TopicSubscripti on: consumer=ID:mdg c01a-55672-1233729340206-0:130:1:2, destination=top ic:///ProdRecap/IDL:consumers/RecapConsumer:1 .0---RecapLocalMD01---local, destinations=1, dispatchedQueue =1962, delivered=39030 86, matched=0, discarded=27081'| 'ProdAMQBrokerm dgc01a.out'| 'ProdLogWatcher mdgc01a');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3279'| '3528'| '25930'| '41'| 'ProdBC90x1OHSe rverHybridprdbc 90a'| 'ORDER_HISTORY_ QUEUE_6');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3280'| '3529'| '12512'| '2232'| 'Prodcas01v2cas 1004'| 'CME16/10.5.108.185:34 528@8936339/BD/ConsumerProxy@-789513853');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3282'| '3530'| '10615'| '17487'| 'Prodcas01v2cas 0171'| 'CD449/dbcashbr3:8102@ 30276098/PST/ConsumerProxy@-686960907');
INSERT INTO ALARMNOTIFICATI ONDETAIL VALUES ('3283'| '3531'| '10605'| '45'| 'Prodcas01v2cas 0241'| 'POATP/CASQuote');

[/code]

when you ask me "A sample of the file you parse into the file used on line #60 would also be helpful"
are you talking about the excel file?

I did copy the output from the browser window rather then the source view, which by the way, I am not sure where I can get that from:(..sorry

thanks for the help

[ndedhia1]

Here is part of the JAVA code I had to change so that I could separate the fields using a PIPE rather then a COMMA.

[code=java]
/** dump this particular table to the string buffer */
private static void dumpTable(Conne ction dbConn, StringBuffer result, String tableName) {
try {
// First we output the create table stuff
PreparedStateme nt stmt = dbConn.prepareS tatement("SELEC T * FROM "+tableName );
ResultSet rs = stmt.executeQue ry();
ResultSetMetaDa ta metaData = rs.getMetaData( );
int columnCount = metaData.getCol umnCount();

// Now we can output the actual data
result.append(" \n\n-- Data for "+tableName+"\n ");
System.out.prin tln(result.toSt ring());
result = null;
while (rs.next()) {
result = new StringBuffer();
result.append(" INSERT INTO "+tableName +" VALUES (");
for (int i=0; i<columnCount; i++) {
if (i > 0) {
result.append(" | ");
}
Object value = rs.getObject(i+ 1);
if (value == null) {
result.append(" NULL");
} else {
String outputValue = value.toString( );
outputValue = outputValue.rep laceAll("'","\\ '");
result.append(" '"+outputValue+ "'");
// result.append(o utputValue);
}
}
result.append(" );\n");
System.out.prin tln(result.toSt ring());
result = null;
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.prin tln("Unable to dump table "+tableName +" because: "+e);
}
}
[/code]

And here is some AWK I had to comment out because before, if the TRIPVALUE field was some long string, I had it display a 1, but I had to change that because I needed the actual value for TRIPVALUE no matter what the value.
[code=awk]
BEGIN{FS=sprint f("%c",1); x=1;}
{print $1, $2, $3, $4, $5, $6, $7 > (dbDir "/alarmNotificati onTmp.txt");
for(n=0; n<NF-8; n=n+4){
pos=index($(n+8 ),"=");
# if ($(n+9) ~ /[A-Za-z]+/ )
# value = "1";
# else
value = $(n+9);
printf "%s|%s|%s|%s|%s |%s\n",x,$3,sub str($(n+8),pos+ 1),value,$(n+10 ),$(n+11) > (dbDir "/alarmNotificati onDetail.txt");
x=x+1;}}
[/code]

[ndedhia1]

Hi...any word on the last few posts I sent.

Thanks for the help

[devsusen]

After going through the posts in this thread I am also little bit confused about the original problem.

Original problem is to extract data from a database(or a table?) and dump it in a excell file. Right?

If this is the case why don't you use Spreadsheet_Exc el_Writer from pear class?