need help extracting data from database to .xls file

[ndedhia1]

I have data in a database that looks like this:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1865', '2295', '15401', '1', 'Prodprdcons11InstrumentationMonitor', 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1866', '2296', '15401', '1', 'ProdPRDCONS5InstrumentationMonitor', 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1867', '2297', '15401', '1', 'ProdPRDCONS5InstrumentationMonitor', 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1868', '2298', '21401', '[IIOPSocketProxy(prdfe05,32846,0,0)] WorkflowHome: Reached the end still unable to route Order, location is marked as primary destination Order=(CBOE:411:AAD:2684:DES:20090120) Order Location ((9CITIFR1))', 'ProdBC22x1OHServerHybridprdbc22b', 'OHServerHybrid');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1869', '2299', '25915', '41', 'ProdBC99x1OHServerHybridprdbc99b', 'POAQ/ProdUserSessionEvent');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1870', '2300', '25909', '43', 'ProdBC95x1OHServerHybridprdbc95b', 'POAQ/ProdUserSessionEvent');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1871', '2301', '25926', '45', 'ProdBC22x1OHServerHybridprdbc22a', 'POAQ/ProdUserSessionEvent');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1872', '2302', '25910', '48', 'ProdBC97x1OHServerHybridprdbc97a', 'POAQ/ProdUserSessionEvent');

I need to extract that data to an .xls file and I was trying to deliminate each field with a comma(,) but that stopped working when I added fields that are long and have commas in the fields. Now the results are distorted for those lines that have commas some of the values of individual field.

I tried to use this line:

$query = "LOAD DATA INFILE '$projectDir$filename' REPLACE INTO TABLE $table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n'";

please help.

thanks

[Atli]

Hi.

Check out the SELECT ... INTO OUTFILE syntax.
That should create a CSV file that Excel should be able to read.

Note that this file should not be named .xls, but rather .csv.
The .xls file type is a proprietary M$ format that is not used much outside the M$ bubble, and it is not supported by many Open-Source applications.

But Excel should be able to read CSV files.

[ndedhia1]

I am defining my table as this:

CREATE TEXT TABLE ALARMNOTIFICATIONDETAIL(
CONDITIONINDEX INTEGER,
NOTIFICATIONID INTEGER,
CONDITIONID VARCHAR,
TRIPVALUE VARCHAR,
TRIPSUBJECTNAME VARCHAR,
TRIPCONTEXTNAME VARCHAR,
CONSTRAINT PK_ALARMNOTIFICATIONDETAIL PRIMARY KEY(CONDITIONINDEX)
);

The value for TRIPVALUE is either a few numbers or a very long string. In the table, the output for the TRIPVALUE is correct:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1986'| '2401'| '12601'| '626'| 'ProdGlobalGlobalServerprdgc01b'| 'POAQ/GlobalServerPOA');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1987'| '2402'| '4402'| '626'| 'ProdGlobalGlobalServerprdgc01b'| 'POAQ/GlobalServerPOA');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1988'| '2406'| '23701'| '<WARNING> <LWL> < Mon 2009/02/02 07:48:26:982 > <Thread[Thread Name [==>#17 POA={FrontendPOA} <==],5,POA ThreadAdministrator]> PendingRequestManager.waitForCompletion: Request timed out after 30001399912 ns, falseNotifies(0).  RequestId(11), typeId(IDL:businessServices/OrderHandlingService:2.0), operation(acceptOrder), orbName(ProdBC30x1OHServerHybridprdbc30b), iiopHost(prdbc30b), iiopPort(18202), tiopHost(prdbc30b), tiopPort(18701)'| 'v20Frontend.err'| 'ProdLogWatcherprdfe05');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1989'| '2407'| '12401'| '201'| 'ProdFE21v20Frontendprdfe21'| 'POATP/FrontendPOA');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('1991'| '2408'| '12601'| '353'| 'ProdGlobalGlobalServerprdgc01b'| 'POAQ/GlobalServerPOA');

TRIPVALUE is the 4th field. When it is a numeric value, it comes out in my .xls file correctly, but when it is a long string, the value outputs to my excel file as 0.

Here is the php code:

        $query = "LOAD DATA INFILE '$projectDir$filename' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\r\\n'";
$query = "SELECT a2.conditionindex, a1.timestamp, a3.alarmdefinitionname, a3.alarmdefinitionseverity, a2.tripsubjectname, a2.tripcontextname, a2.tripvalue, a5.conditionname, a5.conditiontype FROM alarmnotification AS a1, alarmnotificationdetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notificationid=a2.notificationid AND a1.definitionid=a3.databaseidentifier AND a2.conditionid=a5.databaseidentifier ORDER BY a1.timestamp, a3.alarmdefinitionseverity";
 
fwrite($handle, "<tr>");
//            fwrite($handle, "<td>" . strtotime($row['milliseconds']) . "</td>");
            fwrite($handle, "<td>" . date("H:i:s:u", strtotime($row['timestamp'])) . "</td>");
            fwrite($handle, "<td>" . $row['alarmdefinitionname'] . "</td>");
            if ($row['alarmdefinitionseverity'] == 1)
                fwrite($handle, "<td> High </td>");
            elseif ($row['alarmdefinitionseverity'] == 2)
                fwrite($handle, "<td> Medium </td>");
            elseif ($row['alarmdefinitionseverity'] == 3)
                fwrite($handle, "<td> Low </td>");
            fwrite($handle, "<td>" . $row['tripsubjectname'] . "</td>");
            fwrite($handle, "<td>" . $row['tripcontextname'] . "</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#NOTENOUGHINFO# </td>");
            fwrite($handle, "</tr>");

can you please help me to get the true value of TRIPVALUE whether its a long string or a few numbers.

thanks

[ndedhia1]

Also,
When I created the file to be .csv rather then .xls, my excel file was completely messed up and put all the values in one cell.

thanks

[ndedhia1]

here are move values for the TRIPVALUE that cause the value being sent to the excel file to be 0.

 
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('2263'| '3324'| '21402'| '[TIOPReceiverThread - /ProdOHSA00ParAdapter3pprdaps02aA] ManualOrderMaintenanceServiceHome: Print Request received From Par Location=PAR3:XPK:W275 LocationType=4(PAR) CboeDirect showing Order at Location=9CITIFR1 LocationType=5(BOOTH) CBOE High/Low = 1063462828/87364 Order Id =CBOE:226:EZO:175::20090202 ORS ID =5YJT00, with quantities :leg3leg2leg1leg0quantity = 550000, OHS BC = ProdBC90x1'| 'ProdBC90x1OHServerHybridprdbc90a'| 'OHServerHybrid');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('2264'| '3325'| '21402'| '[TIOPReceiverThread - /ProdOHSA00ParAdapter3pprdaps02aA] ManualOrderMaintenanceServiceHome: ManualOrder Auction received From Par Location=PAR3:XPK:W275 LocationType=4(PAR) CboeDirect showing Order at Location=9CITIFR1 LocationType=5(BOOTH) CBOE High/Low = 1063462828/87364 Order Id =CBOE:226:EZO:175::20090202 ORS ID =5YJT00, with quantities :leg3leg2leg1leg0quantity = 550000, OHS BC = ProdBC90x1'| 'ProdBC90x1OHServerHybridprdbc90a'| 'OHServerHybrid');
 

[Atli]

Ok, I see.

Could we see an example of what the file your PHP is creating looks like?
It looks to me like you are simply creating a HTML table, and I don't really know how Excel handles that.

As to the SELECT ... INTO OUTFILE command, if you use that, MySQL would create the CSV file. All you would have to have PHP do is execute the command.

I'm not sure what the differences between each of the Excel versions are, but a while back I was able to simply execute a SELECT ... INTO OUTFILE command and load the resulting file into Excel without any problems.

[ndedhia1]

Here is a sample of one of the files that it creates...
I put the name of the columns on top..
Where there is a value of 0 for TRIPVALUE is where there should be some sort of
long string.

If the value of TRIPVALUE is DOWN, the value of TRIPVALUE is 1. It is okay for that value to be DOWN.

I can also attach my excel file if you would like. I wasnt sure if I was allowed to do that because I read that only source code should be attached.

Thank you for your help

 
timestamp    alarmdefinitionname    alarmdefinitionseverity    tripsubjectname    tripcontextname    tripvalue    conditionname    conditiontype
07:00:07:000000    BC95B OHS Q > 40    Low    ProdBC95x1OHServerHybridprdbc95b    POAQ/ProdUserSessionEvent    43    BC95B OHS Q > 40    Instrumentor
07:00:07:000000    BC99B OHS Q > 40    Low    ProdBC99x1OHServerHybridprdbc99b    POAQ/ProdUserSessionEvent    41    BC99B OHS Q > 40    Instrumentor
07:00:08:000000    BC22A OHS Q > 40    Low    ProdBC22x1OHServerHybridprdbc22a    POAQ/ProdUserSessionEvent    45    BC22A OHS Q > 40    Instrumentor
07:00:08:000000    BC97A OHS Q > 40    Low    ProdBC97x1OHServerHybridprdbc97a    POAQ/ProdUserSessionEvent    48    BC97A OHS Q > 40    Instrumentor
07:00:08:000000    BC93A OHS Q > 40    Low    ProdBC93x1OHServerHybridprdbc93a    POAQ/ProdUserSessionEvent    57    BC93A OHS Q > 40    Instrumentor
07:00:09:000000    BC92B OHS Q > 40    Low    ProdBC92x1OHServerHybridprdbc92b    POAQ/ProdUserSessionEvent    60    BC92B OHS Q > 40    Instrumentor
07:00:09:000000    BC06B OHS Q > 40    Low    ProdBC06x1OHServerHybridprdbc06b    POAQ/ProdUserSessionEvent    61    BC06B OHS Q > 40    Instrumentor
07:00:09:000000    BC90A OHS Q > 40    Low    ProdBC90x1OHServerHybridprdbc90a    POAQ/ProdUserSessionEvent    63    BC90A OHS Q > 40    Instrumentor
07:00:10:000000    BC06A OHS Q > 40    Low    ProdBC06x1OHServerHybridprdbc06a    POAQ/ProdUserSessionEvent    71    BC06A OHS Q > 40    Instrumentor
07:00:10:000000    BC90B OHS Q > 40    Low    ProdBC90x1OHServerHybridprdbc90b    POAQ/ProdUserSessionEvent    80    BC90B OHS Q > 40    Instrumentor
07:00:10:000000    BC04A OHS Q > 40    Low    ProdBC04x1OHServerHybridprdbc04a    POAQ/ProdUserSessionEvent    76    BC04A OHS Q > 40    Instrumentor
07:00:10:000000    BC91B OHS Q > 40    Low    ProdBC91x1OHServerHybridprdbc91b    POAQ/ProdUserSessionEvent    77    BC91B OHS Q > 40    Instrumentor
07:00:10:000000    BC95A OHS Q > 40    Low    ProdBC95x1OHServerHybridprdbc95a    POAQ/ProdUserSessionEvent    78    BC95A OHS Q > 40    Instrumentor
07:00:11:000000    BC98A OHS Q > 40    Low    ProdBC98x1OHServerHybridprdbc98a    POAQ/ProdUserSessionEvent    93    BC98A OHS Q > 40    Instrumentor
07:00:11:000000    BC94B OHS Q > 40    Low    ProdBC94x1OHServerHybridprdbc94b    POAQ/ProdUserSessionEvent    83    BC94B OHS Q > 40    Instrumentor
07:00:11:000000    BC21B OHS Q > 40    Low    ProdBC21x1OHServerHybridprdbc21b    POAQ/ProdUserSessionEvent    84    BC21B OHS Q > 40    Instrumentor
07:00:11:000000    BC93B OHS Q > 40    Low    ProdBC93x1OHServerHybridprdbc93b    POAQ/ProdUserSessionEvent    89    BC93B OHS Q > 40    Instrumentor
07:00:12:000000    BC92A OHS Q > 40    Low    ProdBC92x1OHServerHybridprdbc92a    POAQ/ProdUserSessionEvent    108    BC92A OHS Q > 40    Instrumentor
07:00:13:000000    BC09B OHS Q > 40    Low    ProdBC09x1OHServerHybridprdbc09b    POAQ/ProdUserSessionEvent    120    BC09B OHS Q > 40    Instrumentor
07:00:13:000000    BC21A OHS Q > 40    Low    ProdBC21x1OHServerHybridprdbc21a    POAQ/ProdUserSessionEvent    124    BC21A OHS Q > 40    Instrumentor
07:00:13:000000    BC99A OHS Q > 40    Low    ProdBC99x1OHServerHybridprdbc99a    POAQ/ProdUserSessionEvent    111    BC99A OHS Q > 40    Instrumentor
07:00:13:000000    BC10B OHS Q > 40    Low    ProdBC10x1OHServerHybridprdbc10b    POAQ/ProdUserSessionEvent    124    BC10B OHS Q > 40    Instrumentor
07:00:13:000000    BC30A OHS Q > 40    Low    ProdBC30x1OHServerHybridprdbc30a    POAQ/ProdUserSessionEvent    116    BC30A OHS Q > 40    Instrumentor
07:00:14:000000    ParAdapter SMS slowdown    Medium    ProdOHSA00ParAdapter5pprdaps03aA    ParAdapter    0    ParAdapter SMS slowdown    ERROR#NOTENOUGHINFO#
07:00:14:000000    BC10A OHS Q > 40    Low    ProdBC10x1OHServerHybridprdbc10a    POAQ/ProdUserSessionEvent    132    BC10A OHS Q > 40    Instrumentor
07:00:14:000000    BC30B OHS Q > 40    Low    ProdBC30x1OHServerHybridprdbc30b    POAQ/ProdUserSessionEvent    126    BC30B OHS Q > 40    Instrumentor
07:00:15:000000    BC04B OHS Q > 40    Low    ProdBC04x1OHServerHybridprdbc04b    POAQ/ProdUserSessionEvent    125    BC04B OHS Q > 40    Instrumentor
07:00:16:000000    BC94A OHS Q > 40    Medium    ProdBC94x1OHServerHybridprdbc94a    POAQ/ProdUserSessionEvent    123    BC94A OHS Q > 40    Instrumentor
07:00:16:000000    BC91A OHS Q > 40    Low    ProdBC91x1OHServerHybridprdbc91a    POAQ/ProdUserSessionEvent    110    BC91A OHS Q > 40    Instrumentor
07:00:16:000000    BC97B OHS Q > 40    Low    ProdBC97x1OHServerHybridprdbc97b    POAQ/ProdUserSessionEvent    111    BC97B OHS Q > 40    Instrumentor
07:00:17:000000    BC22B OHS Q > 40    Low    ProdBC22x1OHServerHybridprdbc22b    ORDER_HISTORY_QUEUE_3    55    BC22B OHS Q > 40    Instrumentor
07:00:17:000000    BC22B OHS Q > 40    Low    ProdBC22x1OHServerHybridprdbc22b    POAQ/ProdUserSessionEvent    73    BC22B OHS Q > 40    Instrumentor
07:00:17:000000    BC98B OHS Q > 40    Low    ProdBC98x1OHServerHybridprdbc98b    POAQ/ProdUserSessionEvent    88    BC98B OHS Q > 40    Instrumentor
07:00:17:000000    BC09A OHS Q > 40    Low    ProdBC09x1OHServerHybridprdbc09a    POAQ/ProdUserSessionEvent    102    BC09A OHS Q > 40    Instrumentor
07:01:12:000000    OHS: Reached the end still unable to route    High    ProdBC93x1OHServerHybridprdbc93a    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:01:38:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    603    BC02a Status Server Que <=1000    Instrumentor
07:01:48:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    696    BC02a Status Server Que <=1000    Instrumentor
07:01:58:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    773    BC02a Status Server Que <=1000    Instrumentor
07:02:08:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    836    BC02a Status Server Que <=1000    Instrumentor
07:02:18:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    905    BC02a Status Server Que <=1000    Instrumentor
07:02:28:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    938    BC02a Status Server Que <=1000    Instrumentor
07:02:39:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    966    BC02a Status Server Que <=1000    Instrumentor
07:02:48:000000    BC02A Status Server Que > 500 <= 1000    Low    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    987    BC02a Status Server Que <=1000    Instrumentor
07:02:54:000000    OHS: Reached the end still unable to route    High    ProdBC94x1OHServerHybridprdbc94a    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:02:58:000000    BC02A Status Server Que > 1000 <= 2500    Medium    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    1001    BC02a Status Server Que <=2500    Instrumentor
07:03:08:000000    BC02A Status Server Que > 1000 <= 2500    Medium    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    1027    BC02a Status Server Que <=2500    Instrumentor
07:03:12:000000    OHS: Reached the end still unable to route    High    ProdBC93x1OHServerHybridprdbc93a    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:03:18:000000    BC02A Status Server Que > 1000 <= 2500    Medium    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    1048    BC02a Status Server Que <=2500    Instrumentor
07:03:28:000000    BC02A Status Server Que > 1000 <= 2500    Medium    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    1078    BC02a Status Server Que <=2500    Instrumentor
07:03:39:000000    BC02A Status Server Que > 1000 <= 2500    Medium    ProdBC02StatusServerprdbc02a    UserReportManager/subscriptionQueue_1    1074    BC02a Status Server Que <=2500    Instrumentor
07:05:34:000000    BC10A OHS Q > 40    Low    ProdBC10x1OHServerHybridprdbc10a    ORDER_HISTORY_QUEUE_0    60    BC10A OHS Q > 40    Instrumentor
07:07:56:000000    BC97B OHS Q > 40    Low    ProdBC97x1OHServerHybridprdbc97b    ORDER_HISTORY_QUEUE_3    50    BC97B OHS Q > 40    Instrumentor
07:07:56:000000    BC97B OHS Q > 40    Low    ProdBC97x1OHServerHybridprdbc97b    ORDER_HISTORY_QUEUE_4    45    BC97B OHS Q > 40    Instrumentor
07:07:56:000000    BC97B OHS Q > 40    Low    ProdBC97x1OHServerHybridprdbc97b    DROP_COPY_QUEUE    68    BC97B OHS Q > 40    Instrumentor
07:10:44:000000    Prcess down    High    ProdPRDCONS8InstrumentationMonitor    ALL    DOWN    Prcess down    Process Watcher
07:10:44:000000    Prcess down    High    ProdPRDCONS8InstrumentationMonitor    ALL    DOWN    Prcess down    Process Watcher
07:15:17:000000    OHS: Reached the end still unable to route    High    ProdBC22x1OHServerHybridprdbc22b    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:18:55:000000    OHS: Reached the end still unable to route    High    ProdBC95x1OHServerHybridprdbc95a    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:18:55:000000    OHS: Reached the end still unable to route    High    ProdBC95x1OHServerHybridprdbc95a    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:18:55:000000    OHS: Reached the end still unable to route    High    ProdBC95x1OHServerHybridprdbc95a    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
07:19:15:000000    OHS: Reached the end still unable to route    High    ProdBC97x1OHServerHybridprdbc97b    OHServerHybrid    0    OHS: Reached the end still unable to route    ERROR#NOTENOUGHINFO#
 
 

[Atli]

Where is that data coming from?
It can't be what the PHP code is generating. The PHP code is outputting <tr> and <td> tags. Where are they?

@ndedhia1
Any data that can help us figure out the problem is welcome.
But we usually don't need the entire file. Just posting a sample usually works fine.

[ndedhia1]

The data is coming from a database. Different tables have different values and in the select statement that I gave the code for earlier, selects the output from 1 of 4 different tables. All the tables are located on an Oracle database.
The php code is not generating the data. The php code is used mainly to figure out which day/days data to display in an excel file.

thanks

[ndedhia1]

All the data is stored in 4 different tables in an Oracle Database. The data in the tables are all correct and are displayed correctly when you search the database for the values. When I do that SELECT statement, so that I can have the data displayed in an excel file, that I have provided in a earlier post, the value for TRIPVALUE is correct if it is a numeric value, but when the value is a string of some length, the value is being brought over and displayed as 0, which is incorrect.
Can this maybe be a length issue or something where the length of TRIPVALUE, when it isnt a numeric value, is very long and can not be displayed in the excel file, or isnt bring brought over correctly because of its length?

thanks

[ndedhia1]

Hi,
I was wondering if someone was still trying to help me with this problem I am having?

Thanks

[Atli]

Can we see an example of what the PHP code is outputting?

An by that, I do not mean how Excel is displaying it, but how it is actually being stored. (Try opening the file in WordPad).

It's possible that the data is there, but Excel isn't reading it properly.

[ndedhia1]

This is how the output is being displayed in wordpad:
This is just a sample from different time periods:
I have bolded the times and underlined the values that have a 0, but should have a longer string value for TRIPVALUE

 
1919,07:02:48, BC02A Status Server Que > 500 <= 1000,3, ProdBC02StatusServerprdbc02a, UserReportManager/subscriptionQueue_1,987, BC02a Status Server Que <=1000,1,
1921,07:02:54, OHS: Reached the end still unable to route,1, ProdBC94x1OHServerHybridprdbc94a, OHServerHybrid,0,OHS: Reached the end still unable to route,3,
1922,07:02:58, BC02A Status Server Que > 1000 <= 2500,2, ProdBC02StatusServerprdbc02a, UserReportManager/subscriptionQueue_1,1001, BC02a Status Server Que <=2500,1,
1924,07:03:08, BC02A Status Server Que > 1000 <= 2500,2, ProdBC02StatusServerprdbc02a, UserReportManager/subscriptionQueue_1,1027, BC02a Status Server Que <=2500,1,
1926,07:03:12, OHS: Reached the end still unable to route,1, ProdBC93x1OHServerHybridprdbc93a, OHServerHybrid,0, OHS: Reached the end still unable to route,3,
1927,07:03:18, BC02A Status Server Que > 1000 <= 2500,2, ProdBC02StatusServerprdbc02a, UserReportManager/subscriptionQueue_1,1048, BC02a Status Server Que <=2500,1,
1929,07:03:28, BC02A Status Server Que > 1000 <= 2500,2, ProdBC02StatusServerprdbc02a, UserReportManager/subscriptionQueue_1,1078, BC02a Status Server Que <=2500,1,
1931,07:03:39, BC02A Status Server Que > 1000 <= 2500,2, ProdBC02StatusServerprdbc02a, UserReportManager/subscriptionQueue_1,1074, BC02a Status Server Que <=2500,1,
 

Here is the PHP code that displays the wordpad output that is shown above:

 
$debugFile = "debug.txt";
if(!$debug_handle = fopen($debugFile, 'w'))
        throw new Exception("Cannot open file '$debug_handle' for writing"); 
 
    if(!$handle = fopen($TableFile, 'w'))
        throw new Exception("Cannot 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_fields($result)-1); $i++) {
        fwrite($handle, '<th>' . mysql_field_name($result, $i+1) . '</th>');
    }
    fwrite($handle, "</tr>");
    while($row=mysql_fetch_array($result)){
 
        fwrite($debug_handle, $row['conditionindex'] . ",");
        fwrite($debug_handle, date("H:i:s", strtotime($row['timestamp'])) . ",");
        fwrite($debug_handle, $row['alarmdefinitionname'] . ",");
        fwrite($debug_handle, $row['alarmdefinitionseverity'] . ",");
        fwrite($debug_handle, $row['tripsubjectname'] . ",");
        fwrite($debug_handle, $row['tripcontextname'] . ",");
        fwrite($debug_handle, $row['tripvalue'] . ",");
        fwrite($debug_handle, $row['conditionname'] . ",");
        fwrite($debug_handle, $row['conditiontype'] . ",");
        fwrite($debug_handle, "\r\n");
 

Please let me know if this is how you wanted me to show you what the PHP code is outputting.

thanks

[ndedhia1]

Please help me with my above prob ASAP.

Thank you

[ndedhia1]

Can someone please help me with this project.
The time table in which this needs to be done is getting URGENT because I need to finish the project in the next few days.

thank you

[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:

SHOW CREATE TABLE alarmnotificationdetail

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.

CREATE TEXT TABLE ALARMNOTIFICATION(LOGGINGVERSION DECIMAL(4,2),LOGGINGSTYLE CHAR(1),NOTIFICATIONID INTEGER,MILLISECONDS BIGINT,TIMESTAMP VARCHAR,ACTIVATIONID INTEGER,DEFINITIONID INTEGER,DEFINITIONSEVERITY INTEGER,CONSTRAINT PK_ALARMNOTIFICATION PRIMARY KEY(NOTIFICATIONID))
CREATE TEXT TABLE ALARMNOTIFICATIONDETAIL(CONDITIONINDEX INTEGER,NOTIFICATIONID INTEGER,CONDITIONID VARCHAR,TRIPVALUE VARCHAR,TRIPSUBJECTNAME VARCHAR,TRIPCONTEXTNAME VARCHAR,CONSTRAINT PK_ALARMNOTIFICATIONDETAIL PRIMARY KEY(CONDITIONINDEX))
SET TABLE ALARMNOTIFICATION SOURCE "alarmNotification.txt;fs=,"
SET TABLE ALARMNOTIFICATIONDETAIL SOURCE "alarmNotificationDetail.txt;fs=|"
 

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.

('3062'| '3383'| '10615'| '6049'| 'Prodcas01v2cas0310'| 'CIT/lx-choptqgw2:8104@13613729/PST/ConsumerProxy@864697602');
('3063'| '3384'| '23601'| '56'| 'Prodcas01v2cas0310'| 'POATP/CASQuote');
('3064'| '3386'| '23902'| '<FINE> <org.apache.activemq.broker.region.TopicSubscription> < Wed 2009/02/04 08:30:06:351 > <org.apache.activemq.broker.region.TopicSubscription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707,4,main]> Discarding state cleared, delta-discarded(6422). TopicSubscription: consumer=ID:mdgc01a-55672-1233729340206-0:130:1:2, destination=topic:///ProdRecap/IDL:consumers/RecapConsumer:1.0---RecapLocalMD01---local, destinations=1, dispatchedQueue=0, delivered=637891, matched=0, discarded=6422'| 'ProdAMQBrokermdgc01a.out'| 'ProdLogWatchermdgc01a');
('3065'| '3387'| '23902'| '<FINE> <org.apache.activemq.broker.region.TopicSubscription> < Wed 2009/02/04 08:30:07:627 > <org.apache.activemq.broker.region.TopicSubscription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707,4,main]> Discarding state cleared, delta-discarded(419). TopicSubscription: consumer=ID:mdgc01a-55672-1233729340206-0:130:1:2, destination=topic:///ProdRecap/IDL:consumers/RecapConsumer:1.0---RecapLocalMD01---local, destinations=1, dispatchedQueue=0, delivered=648208, matched=0, discarded=6841'| 'ProdAMQBrokermdgc01a.out'| 'ProdLogWatchermdgc01a');
('3066'| '3389'| '23902'| '<FINE> <org.apache.activemq.broker.region.TopicSubscription> < Wed 2009/02/04 08:30:08:85 > <org.apache.activemq.broker.region.TopicSubscription.add> <Thread[ActiveMQ Transport: tcp:///172.16.93.10:62333,4,main]> Discarding state cleared, delta-discarded(400). TopicSubscription: consumer=ID:cas0034-59736-1233735671876-0:9:1:1, destination=topic:///ProdCurrentMarket/IDL:consumers/CurrentMarketConsumer:1.0---CurrentMarketLocalExtentcas0034---local, destinations=1, dispatchedQueue=1868, delivered=3325, matched=0, discarded=400'| 'ProdAMQBrokerprdfe02.out'| 'ProdLogWatcherprdfe02');
('3067'| '3390'| '10615'| '10128'| 'Prodcas01v2cas0170'| 'CD422/FTCBOEMD1:8104@10827017/PST/ConsumerProxy@47964553');
('3068'| '3391'| '1809'| '151'| 'ProdBC30x1HybridTradeServer1prdbc30b'| 'MarketDataQueue_14');
 

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.properties
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

#HSQL database
#Tue Feb 17 12:16:01 CST 2009
hsqldb.cache_file_scale=1
runtime.gc_interval=0
hsqldb.first_identity=0
version=1.7.3
modified=yes
hsqldb.script_format=0
sql.enforce_size=false
hsqldb.cache_size_scale=10
hsqldb.cache_scale=14
hsqldb.version=1.7.3
hsqldb.log_size=200
sql.enforce_strict_size=false
readonly=false
hsqldb.compatible_version=1.7.2
hsqldb.original_version=1.7.1
 sql.compare_in_locale=false
hsqldb.nio_data_file=true
hsqldb.cache_version=1.7.0
 

[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?

CREATE TEXT TABLE ALARMNOTIFICATIONDETAIL(CONDITIONINDEX INTEGER,NOTIFICATIONID INTEGER,CONDITIONID VARCHAR,TRIPVALUE VARCHAR,TRIPSUBJECTNAME VARCHAR,TRIPCONTEXTNAME VARCHAR,CONSTRAINT PK_ALARMNOTIFICATIONDETAIL PRIMARY KEY(CONDITIONINDEX))
SET TABLE ALARMNOTIFICATIONDETAIL SOURCE "alarmNotificationDetail.txt;fs=|"
 

[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

<?php
 
// Gets today's date
function getTodaysDate() {
 
    try {
        if (!$today  = mktime(0, 0, 0, date("m"), date("d"), date("Y")))
            throw new Exception('Could 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->getTraceAsString() . '<br />';
    } 
    return $today;
}
 
//This function takes a string '$table' and parses the file 'mysql_$table.txt' in order for it to enter a MySQL database without error
function insertMySQLTables($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($projectDir.$filename))
            throw new Exception ("File can't be opened: $projectDir$filename");
 
        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=="alarmdefinition")) {
                $pos = strrpos($line, ",", "-6");
                $line = substr_replace($line, ' ', $pos, '1');
            }
 
            $parsed_mysql .= $line;
 
        }
        if(!$handle = fopen($projectDir.$filename, 'w'))
            throw new Exception ("File can't be updated: $projectDir.$filename");
 
        if(fwrite($handle, $parsed_mysql) === FALSE)
            throw new Exception ("File can't be written to: $projectDir.$filename");
 
        if(!fclose($handle))
            throw new Exception ("File can't be saved: $projectDir.$filename");
 
        $query = "LOAD DATA INFILE '$projectDir$filename' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\r\\n'";
        if(!$result = mysql_query($query))
            throw new Exception("Cannot insert tables using the MySQL statement: $query");
 
        unlink($filename);
 
    }
    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->getTraceAsString() . '<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.$resetTables;
    $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("Cannot 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($query))
                throw new Exception("Cannot connect to database: '$dbname'"); 
        }
 
        if(!$shout=extractArchive($currentDate)) {
            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) {
            insertMySQLTables($singleTable);
        }
        echo date("h:i.s") . ": insertMySQLTables Complete <br/>";
 
        if ($sort=="time")
            $query = "SELECT a2.conditionindex, a1.timestamp, a3.alarmdefinitionname, a3.alarmdefinitionseverity, a2.tripsubjectname, a2.tripcontextname, a2.tripvalue, a5.conditionname, a5.conditiontype FROM alarmnotification AS a1, alarmnotificationdetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notificationid=a2.notificationid AND a1.definitionid=a3.databaseidentifier AND a2.conditionid=a5.databaseidentifier ORDER BY a1.timestamp, a3.alarmdefinitionseverity";
        else if ($sort=="severity")
            $query = "SELECT a2.conditionindex, a1.timestamp, a3.alarmdefinitionname, a3.alarmdefinitionseverity, a2.tripsubjectname, a2.tripcontextname, a2.tripvalue, a5.conditionname, a5.conditiontype FROM alarmnotification AS a1, alarmnotificationdetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notificationid=a2.notificationid AND a1.definitionid=a3.databaseidentifier AND a2.conditionid=a5.databaseidentifier ORDER BY a3.alarmdefinitionseverity, a1.timestamp";
 
        while (true) {
            if (!$result = mysql_query($query))
                throw new Exception("Cannot 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_fetch_array($result))
                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($result, $currentDate);
 
        // release memory and exit connection to the database
        mysql_free_result($result);
        mysql_close($conn);
 
        // ftp file to an external location
        ftpTables($currentDate, $server_System, $user_name_System, $user_pass_System);
        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.xls";
        } else {
 
            if ($TableFile == "")
                $TableFile = "$currentDate.html";
        }
 
 
        echo "Location of $fileType file: $dstRootDir$TableFile<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->getTraceAsString() . '<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($result, $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.xls";
    } else {
 
            if ($TableFile == "")
                $TableFile = "$currentDate.html";
        }
 
    if(!$debug_handle = fopen($debugFile, 'w'))
        throw new Exception("Cannot open file '$debug_handle' for writing"); 
 
    if(!$handle = fopen($TableFile, 'w'))
        throw new Exception("Cannot 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_fields($result)-1); $i++) {
        fwrite($handle, '<th>' . mysql_field_name($result, $i+1) . '</th>');
    }
    fwrite($handle, "</tr>");
    while($row=mysql_fetch_array($result)){
 
        fwrite($debug_handle, $row['conditionindex'] . ",");
        fwrite($debug_handle, date("H:i:s", strtotime($row['timestamp'])) . ",");
        fwrite($debug_handle, $row['alarmdefinitionname'] . ",");
        fwrite($debug_handle, $row['alarmdefinitionseverity'] . ",");
        fwrite($debug_handle, $row['tripsubjectname'] . ",");
        fwrite($debug_handle, $row['tripcontextname'] . ",");
        fwrite($debug_handle, $row['tripvalue'] . ",");
        fwrite($debug_handle, $row['conditionname'] . ",");
        fwrite($debug_handle, $row['conditiontype'] . ",");
        fwrite($debug_handle, "\r\n");
 
        if ((substr($row['timestamp'],11,12)>=$timeFrame_min) && (substr($row['timestamp'],11,12)<$timeFrame_max)) {
            fwrite($handle, "<tr>");
            fwrite($handle, "<td>" . date("H:i:s:u", strtotime($row['timestamp'])) . "</td>");
            fwrite($handle, "<td>" . $row['alarmdefinitionname'] . "</td>");
            if ($row['alarmdefinitionseverity'] == 1)
                fwrite($handle, "<td> High </td>");
            elseif ($row['alarmdefinitionseverity'] == 2)
                fwrite($handle, "<td> Medium </td>");
            elseif ($row['alarmdefinitionseverity'] == 3)
                fwrite($handle, "<td> Low </td>");
            fwrite($handle, "<td>" . $row['tripsubjectname'] . "</td>");
            fwrite($handle, "<td>" . $row['tripcontextname'] . "</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#NOTENOUGHINFO# </td>");
            fwrite($handle, "</tr>");
        }
    }
 
    fwrite($handle, "</table>");
 
    // save and close either the excel or HTML file
    if(!fclose($handle))
        throw new Exception("Cannot close file '$TableFile'"); 
 
    if(!fclose($debug_handle))
        throw new Exception("Cannot close file '$debug'"); 
}
 
// This function gets the previous workday
// This function effectively overlooks all weekends.
function getPreviousWorkDay($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",$yesterday)==7)
        $yesterday = mktime(0,0,0, $month, $day-3, $year);    
    else if (date("N",$yesterday)==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$genLogs $archiveDate";
 
    $mysqlTextFiles = array(
        "mysql_$tables[0].txt",
        "mysql_$tables[1].txt", 
        "mysql_$tables[2].txt", 
        "mysql_$tables[3].txt");
 
    try {
 
        if(!$sshconn = openSSH($server_Local, $user_name_Local, $user_pass_Local))
            throw new Exception("Could not open SSH connection using server: '$server_Local', username: '$user_name_Local', password: '$password_Local'");
 
        if (!$sftp = ssh2_sftp($sshconn))
            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($mysqlTextFiles as $singleMysqlTextFile) {
            // If no files are found, create a makebelive time of '0'
            if(!$statinfo = ssh2_sftp_stat($sftp, $genLogResultsDir.$singleMysqlTextFile))
                $originalTime = 0;
            else {
                $originalTime = $statinfo['mtime']; 
            }
        }
 
        if (!ssh2_exec($sshconn, $script))
            throw new Exception("Could not run script: '$script_Local'");
 
        foreach($mysqlTextFiles as $singleMysqlTextFile) {
            if(!$write = fopen($singleMysqlTextFile, "w"))
                throw new Exception("Could not open destination file: $singleMysqlTextFile");
        }
 
        // *** 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($mysqlTextFiles as $singleMysqlTextFile) {
            // 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, $genLogResultsDir.$singleMysqlTextFile))
                        break;
                    else if ($time_out < $d) {
                        return false;
                        //throw new Exception ("Timed out getting file data from $server_Local on $archiveDate");
                        //$no_errors=false;
                        //die();
                    }
                    sleep(1);
                    $d+=1;
                }
                if ((($originalTime < $statinfo['mtime'])) && (($statinfo['size'] > 0))) {
                    break;
                }
                else if ($time_out < $i) {
                    return false;
                }
                sleep(1);
                $i+=1;
            }
            if(!ssh2_scp_recv($sshconn, "$genLogResultsDir$singleMysqlTextFile", $singleMysqlTextFile))
                throw new Exception('Transfer 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->getTraceAsString() . '<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($fileDate) {
 
    include 'config.php';
 
    // These 3 variables are used for the new directory
    $year = substr($fileDate, 0,4);
    $month = substr($fileDate, 5,2);
    $day = substr($fileDate, 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.$activeDir;
//    
//    try {
//        if (!$sshconn = openSSH($server_System, $user_name_System, $user_pass_System))
//            throw new Exception ("Cannot create connection using server: '$server_System', username: '$user_name_System', password: '$user_pass_System'");
//
//        if (!$sftp = ssh2_sftp($sshconn))
//            throw new Exception ("Cannot create an sftp connection using $sshconn");
//        
//        //If the directory isn't present make one
//        if(!is_dir('ssh2.sftp://' .$sftp. $finalDstDir))
//            if (!ssh2_sftp_mkdir($sftp, $finalDstDir, 0755, true))
//                throw new Exception ("cannot make directory $finalDstDir");
//            
//        if (!$sftpStream = @fopen('ssh2.sftp://'.$sftp.$finalDstDir. "/" . $srcFile, 'w'))
//            throw new Exception ("Cannot open an sftpStream with '$finalDstDir/$srcFile'");
//
//        if (!$data_to_send = @file_get_contents($srcFile))
//            throw new Exception ("Could not get file contents from '$scrFile'");
//
//        if (!@fwrite($sftpStream, $data_to_send)) 
//            throw new Exception ("Could not send data from file: $srcFile.");
//
//        fclose($sftpStream);
//    }
//    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->getTraceAsString() . '<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($server, 22))
            throw new Exception ("Cannot create connection using server: '$server'");
 
        // authenticates connection or die
        if(!ssh2_auth_password($sshconn, $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->getTraceAsString() . '<br />';
    }
 
return $sshconn;
}
 
// Used only when, in manual-mode, the user requests a span of dates instead of just one
function createMultipleHTMLTables($requestedDate, $finalDate) {
    createHTMLTable(date("Y-m-d", $requestedDate));
    while ($requestedDate>$finalDate) {
        $requestedDate = getPreviousWorkDay($requestedDate);
        createHTMLTable(date("Y-m-d", $requestedDate));
    }
 
}
 
?>
 

[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:

/* FOR DEBUGGIN ONLY! */
$debug_query = "SELECT * FROM alarmnotificationdetail";
$debug_result = mysql_query(debug_query) or die(mysql_error());
 
echo "<data>\n";
while($debug_row = mysql_fetch_assoc($debug_result)) {
    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";

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(debug_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 Prodcas01v2cas0135 ALL 2 442 11326 1 Prodfixcas14v2fix2a ALL 3 445 15401 1 Prodfixcas14v2fix2a ALL 4 444 15401 1 Prodfixcas14v2fix2a ALL 5 446 10601 1 Prodcas01v2cas0075 ALL 6 447 10306 1 Prodmdx01prdmdx99b ALL 7 449 15401 1 Prodcas01v2cas0135 ALL 8 448 10306 1 Prodmdx01prdmdx90c ALL 9 450 15401 1 Prodcas01v2cas0135 ALL 10 452 15401 1 Prodcas01v2cas0075 ALL 11 451 15401 1 Prodcas01v2cas0075 ALL 12 453 15401 1 Prodcfix98v2prdcfix2 ALL 13 454 15401 1 Prodcfix98v2prdcfix2 ALL 14 456 15401 1 Prodmdx01prdmdx99b ALL 15 455 15401 1 Prodmdx01prdmdx99b ALL 16 457 10601 1 Prodcas01v2cas0091 ALL 17 459 15401 1 Prodmdx01prdmdx90c ALL 18 458 11326 1 Prodfixcas13v2fix1a ALL 19 460 15401 1 Prodmdx01prdmdx90c ALL 20 461 10701 1 Prodfixcas07v2fix3b ALL 21 462 15401 1 Prodcas01v2cas0091 ALL 22 463 15401 1 Prodcas01v2cas0091 ALL 23 464 15401 1 Prodfixcas13v2fix1a ALL 24 465 15401 1 Prodfixcas13v2fix1a ALL 25 466 15401 1 Prodfixcas07v2fix3b ALL 26 467 15401 1 Prodfixcas07v2fix3b ALL 27 468 10701 1 Prodfixcas15v2fix20b ALL 28 469 15401 1 Prodfixcas15v2fix20b ALL 29 470 15401 1 Prodfixcas15v2fix20b ALL 30 471 10601 1 Prodcas01v2cas0240 ALL 31 472 10701 1 Prodfixcas05v2fix6 ALL 32 473 10601 1 Prodcas01v2cas3003 ALL 33 475 15401 1 Prodcas01v2cas0240 ALL 34 474 15401 1 Prodcas01v2cas0240 ALL 35 477 15401 1 Prodfixcas05v2fix6 ALL 36 478 10601 1 Prodcas01v2cas0071 ALL 37 476 15401 1 Prodfixcas05v2fix6 ALL 38 479 10601 1 Prodcas01v2cas0170 ALL 39 480 15401 1 Prodcas01v2cas3003 ALL 40 481 15401 1 Prodcas01v2cas3003 ALL 41 482 10601 1 Prodcas01v2cas0068 ALL 42 483 15401 1 Prodcas01v2cas0071 ALL 43 484 15401 1 Prodcas01v2cas0071 ALL 44 485 15401 1 Prodcas01v2cas0170 ALL 45 486 15401 1 Prodcas01v2cas0170 ALL 46 487 15401 1 Prodcas01v2cas0068 ALL 47 488 15401 1 Prodcas01v2cas0068 ALL 48 489 10601 1 Prodcas01v2cas0305 ALL 49 491 15401 1 Prodcas01v2cas0305 ALL 50 492 15401 1 Prodcas01v2cas0305 ALL 51 490 10601 1 Prodcas01v2cas0234 ALL 52 493 15401 1 Prodcas01v2cas0234 ALL 53 494 15401 1 Prodcas01v2cas0234 ALL 54 496 15401 1 Prodmdx01prdmdx91a ALL 55 495 10306 1 Prodmdx01prdmdx91a ALL 56 497 15401 1 Prodmdx01prdmdx91a ALL 57 499 15401 1 Prodcas01v2cas0023 ALL 58 500 10601 1 Prodcas01v2cas0023 ALL 59 498 15401 1 Prodcas01v2cas0023 ALL 60 502 15401 1 Prodfixcas02v2fix3a ALL 61 501 15401 1 Prodfixcas02v2fix3a ALL 62 503 10701 1 Prodfixcas02v2fix3a ALL 63 506 15401 1 Prodcas01v2cas0209 ALL 64 505 15401 1 Prodcas01v2cas0209 ALL 65 504 10601 1 Prodcas01v2cas0209 ALL 66 508 15401 1 Prodcas01v2cas2006 ALL 67 507 15401 1 Prodcas01v2cas2006 ALL 68 511 10601 1 Prodcas01v2cas2006 ALL 69 510 15401 1 Prodcas01v2cas0026 ALL 70 512 10601 1 Prodcas01v2cas0026 ALL 71 509 15401 1 Prodcas01v2cas0026 ALL 72 513 10701 1 Prodfixcas02v2fix7 ALL 73 516 10601 1 Prodcas01v2cas0187 ALL 74 515 15401 1 ProdPRDCONS6InstrumentationMonitor ALL 75 514 15401 1 ProdPRDCONS6InstrumentationMonitor ALL 76 518 15401 1 Prodfixcas02v2fix7 ALL 77 517 15401 1 Prodfixcas02v2fix7 ALL 78 519 15401 1 Prodcas01v2cas0187 ALL 79 520 15401 1 Prodcas01v2cas0187 ALL 80 521 10701 1 Prodfixcas01v2fix9 ALL 81 523 15401 1 Prodfixcas01v2fix9 ALL 82 522 15401 1 Prodfixcas01v2fix9 ALL 83 525 15401 1 Prodmdcas02v2mdcas200 ALL 84 524 15401 1 Prodmdcas02v2mdcas200 ALL 85 526 10601 1 Prodcas01v2cas0158 ALL 86 529 10601 1 Prodcas01v2cas3022 ALL 87 528 15401 1 Prodcas01v2cas0158 ALL 88 527 15401 1 Prodcas01v2cas0158 ALL 89 530 10701 1 Prodfixcas04v2fix8 ALL 90 531 10701 1 Prodfixcas10v2fix3b ALL 91 534 10601 1 Prodcas01v2cas0244 ALL 92 533 15401 1 Prodcas01v2cas3022 ALL 93 532 15401 1 Prodcas01v2cas3022 ALL 94 536 15401 1 Prodfixcas04v2fix8 ALL 95 535 15401 1 Prodfixcas04v2fix8 ALL 96 538 15401 1 Prodfixcas10v2fix3b ALL 97 537 15401 1 Prodfixcas10v2fix3b ALL 98 539 15401 1 Prodcas01v2cas0244 ALL 99 540 15401 1 Prodcas01v2cas0244 ALL 100 541 10601 1 Prodcas01v2cas0056 ALL 101 542 10601 1 Prodcas01v2cas0182 ALL 102 543 15401 1 Prodcas01v2cas0056 ALL 103 544 15401 1 Prodcas01v2cas0056 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 ProdAMQBrokermdgc01a.out ProdLogWatchermdgc01a
4816 4515 23902 0 ProdAMQBrokermdgc01a.out ProdLogWatchermdgc01a
4817 4516 23902 0 ProdAMQBrokermdgc01a.out ProdLogWatchermdgc01a
4818 4517 23902 0 ProdAMQBrokermdgc01a.out ProdLogWatchermdgc01a

[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$filename' from the line:

$query = "LOAD DATA INFILE '$projectDir$filename' 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$filename' 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:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3271'| '3522'| '12512'| '4892'| 'Prodcas01v2cas1003'| 'CME 13/10.5.108.184:43672@1765346/BD/ConsumerProxy@803111635');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3273'| '3523'| '12512'| '1148'| 'Prodcas01v2cas1004'| 'CME16/10.5.108.185:34528@8936339/BD/ConsumerProxy@-789513853');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3275'| '3524'| '10615'| '18623'| 'Prodcas01v2cas0171'| 'CD449/dbcashbr3:8102@30276098/PST/ConsumerProxy@-686960907');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3276'| '3525'| '23902'| '<FINE> <org.apache.activemq.broker.region.TopicSubscription> < Wed 2009/02/04 08:40:17:600 > <org.apache.activemq.broker.region.TopicSubscription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707,4,main]> Discarding state cleared, delta-discarded(559). TopicSubscription: consumer=ID:mdgc01a-55672-1233729340206-0:130:1:2, destination=topic:///ProdRecap/IDL:consumers/RecapConsumer:1.0---RecapLocalMD01---local, destinations=1, dispatchedQueue=1992, delivered=3838877, matched=0, discarded=24317'| 'ProdAMQBrokermdgc01a.out'| 'ProdLogWatchermdgc01a');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3277'| '3526'| '23902'| '<FINE> <org.apache.activemq.broker.region.TopicSubscription> < Wed 2009/02/04 08:40:19:516 > <org.apache.activemq.broker.region.TopicSubscription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707,4,main]> Discarding state cleared, delta-discarded(592). TopicSubscription: consumer=ID:mdgc01a-55672-1233729340206-0:130:1:2, destination=topic:///ProdRecap/IDL:consumers/RecapConsumer:1.0---RecapLocalMD01---local, destinations=1, dispatchedQueue=1986, delivered=3853891, matched=0, discarded=24909'| 'ProdAMQBrokermdgc01a.out'| 'ProdLogWatchermdgc01a');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3278'| '3527'| '23902'| '<FINE> <org.apache.activemq.broker.region.TopicSubscription> < Wed 2009/02/04 08:40:25:723 > <org.apache.activemq.broker.region.TopicSubscription.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707,4,main]> Discarding state cleared, delta-discarded(2172). TopicSubscription: consumer=ID:mdgc01a-55672-1233729340206-0:130:1:2, destination=topic:///ProdRecap/IDL:consumers/RecapConsumer:1.0---RecapLocalMD01---local, destinations=1, dispatchedQueue=1962, delivered=3903086, matched=0, discarded=27081'| 'ProdAMQBrokermdgc01a.out'| 'ProdLogWatchermdgc01a');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3279'| '3528'| '25930'| '41'| 'ProdBC90x1OHServerHybridprdbc90a'| 'ORDER_HISTORY_QUEUE_6');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3280'| '3529'| '12512'| '2232'| 'Prodcas01v2cas1004'| 'CME16/10.5.108.185:34528@8936339/BD/ConsumerProxy@-789513853');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3282'| '3530'| '10615'| '17487'| 'Prodcas01v2cas0171'| 'CD449/dbcashbr3:8102@30276098/PST/ConsumerProxy@-686960907');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('3283'| '3531'| '10605'| '45'| 'Prodcas01v2cas0241'| 'POATP/CASQuote');
 
 

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.

 /** dump this particular table to the string buffer */
    private static void dumpTable(Connection dbConn, StringBuffer result, String tableName) {
        try {
            // First we output the create table stuff
            PreparedStatement stmt = dbConn.prepareStatement("SELECT * FROM "+tableName);
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
 
            // Now we can output the actual data
            result.append("\n\n-- Data for "+tableName+"\n");
            System.out.println(result.toString());
            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.replaceAll("'","\\'");
                        result.append("'"+outputValue+"'");
//                        result.append(outputValue);
                    }
                }
                result.append(");\n");
                System.out.println(result.toString());
                result = null;
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            System.err.println("Unable to dump table "+tableName+" because: "+e);
        }
    }
 

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.

BEGIN{FS=sprintf("%c",1); x=1;}
{print $1, $2, $3, $4, $5, $6, $7 > (dbDir "/alarmNotificationTmp.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,substr($(n+8),pos+1),value,$(n+10),$(n+11) > (dbDir "/alarmNotificationDetail.txt");
   x=x+1;}}
 

[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_Excel_Writer from pear class?

[ndedhia1]

The problem is that I have this field called TRIPVALUE, that I have assigned as a VARCHAR, and when I try to dump its content into either an xls or html file, the output is incorrect. If the value of the field TRIPVALUE is something numeric, for example, 43353 or 59494 or 223, etc, the data shown in the xls or html file is CORRECT, but if the value is string, such as,

<FINE> <org.apache.activemq.broker.region.TopicSubscripti on> < Wed 2009/02/04 08:40:17:600 > <org.apache.activemq.broker.region.TopicSubscripti on.add> <Thread[ActiveMQ Transport: tcp:///127.0.0.1:37707,4,main]> Discarding state cleared, delta-discarded(559). TopicSubscription: consumer=ID:mdgc01a-55672-1233729340206-0:130:1:2, destination=topic:///ProdRecap/IDL:consumers/RecapConsumer:1.0---RecapLocalMD01---local, destinations=1, dispatchedQueue=1992, delivered=3838877, matched=0, discarded=24317

or

<WARNING> <LWL> < Mon 2009/02/02 07:48:26:982 > <Thread[Thread Name [==>#17 POA={FrontendPOA} <==],5,POA ThreadAdministrator]> PendingRequestManager.waitForCompletion: Request timed out after 30001399912 ns, falseNotifies(0). RequestId(11), typeId(IDL:businessServices/OrderHandlingService:2.0), operation(acceptOrder), orbName(ProdBC30x1OHServerHybridprdbc30b), iiopHost(prdbc30b), iiopPort(18202), tiopHost(prdbc30b), tiopPort(18701),

the value in the html or xls file is a 0, which is INCORRECT.
Any help would be appreciated and i am not familiar with the Spreadsheet_Excel_Writer from pear class thanks.

[Stewart Ross]

Hi. I can't pretend to understand or follow the complications within the one project of trying to use vbar separated fields ('|'), comma separated fields within them, and start-end sequences '\\ blah \ blah \' as well. It smacks of a lack of coherent thought in defining what is being done, and is a recipe for considerable confusion as to how to extract the strings.

What exactly Excel has to do with all this is also very unclear.

There is a limitation with Excel that you need to know, and is the reason for my post. In all its versions prior to Excel 2007 the number of characters in a single cell is limited to 255. Your TRIPVALUE long string fields are around 500 characters long - which if you really are intending to have Excel involved somewhere will result in severe truncation of the string concerned as soon as it exceeds 255 characters. If Excel is involved, and you are not using Excel 2007, your long strings will not survive the transfer to Excel intact.

Even so, as earlier posters have pointed out, the fact that you obtain a 0 when the TRIPVALUE is a form of string but not when it is a number does suggest there is an an error in the way the substring is derived if it is 0 before you ever get to Excel.

Straying from my Access/VBA home into this forum, I can't help but notice the considerable help you have been given already with your problem (posting more than 300 lines of code and expecting it to be read is really optimistic - but it was!), and the less-than-clear context in which you have expressed what is happening, despite many posts. I reckon the proliferation of separators in your methods reflects some confusion over what is being done, and how it is being achieved, and until you resolve this I can see no easy way forward for you - but deadlines can be quite a motivator when the time comes!

-Stewart

[ndedhia1]

I agree that it has been confusing and I didnt want to post hundreds of lines of code, so I was giving bits and pieces of what was asked for. After I gave the parts that were asked for, then I posted all the code incase someone wanted to reference any of it to better understand my problem.

In the table definitions, I have tried to make the TRIPVALUE field less then 255 characters but would still get a 0 as the value. I did that by doing a VARCHAR(156).

I had to separate the fields using a PIPES " | " because in the TRIPVALUE field, some of the data has COMMAS and was being separated inbetween the TRIPVALUE, rather then after each individual field from my tables. Since there are no PIPES in any of the data, it was an easy choice for separating the fields.

I am using 2003s version of EXCEL.

thanks

[Stewart Ross]

Atli has already identified the problem - what is being placed in your MySQL database involves numeric values for TRIPVALUE, despite the Varchar definition you referred to earlier. From what you have posted your INSERT statements are OK - so either:

1. the field definition of the TRIPVALUE field in your MySQL database is not actually a Varchar but a numeric type, or
2. the insertion routine that is taking the pipes-separated fields is interpreting your data as numeric (as pointed out by Atli in post #26), and consequently inserting a 0 value for the TRIPVALUE field when faced with text.

This is something I have seen in other contexts when importing data into Access databases from Excel where the data is not explicitly typecast - if something looks like a number in the first few rows the import routines will often then decide that the correct type is numeric, and subsequently error out when faced with text many rows further on in the same field.

As a simple test, and to eliminate the insertion routine as the error, you could replace the references to the TRIPVALUE field values in the INSERT INTO statement with an always-text string constant such as

| '<here is something I know will be treated as text and only text>'|

If this is inserted correctly then you will know that the 0 results from loose-typecast importing - but if it imports as a 0 you know that either the table's field definition is incorrect or the import of that field is not going as you expected it to.

-Stewart

[200dogz]

Hi ndedhia1,

Have you tried putting double quotes around the text you're extracting to the csv file? From what I remember that can allow the text to have commas in it.

[ndedhia1]

I tried putting double quotes around the text and the commas still separated the fields in incorrect places.

[ndedhia1]

Stewart, I did what you asked and put <here is something I know will be treated as text and only text> into the value for TRIPVALUE:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('568'| '1007'| '15401'| '<here is something I know will be treated as text and only text>'| 'Prodcas01v2cas0120'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('566'| '1008'| '10601'| '<here is something I know will be treated as text and only text>'| 'Prodcas01v2cas0103'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('567'| '1009'| '15401'| '<here is something I know will be treated as text and only text>'| 'Prodfixcas03v2fix2a'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('569'| '1010'| '15401'| '<here is something I know will be treated as text and only text>'| 'Prodfixcas05v2fix5'| 'ALL');
 

I wound up getting a 0 for the value for TRIPVALUE in the excel file:

09:00:01:000000    BC30B HTS Q>750,Q<5000    Medium    ProdBC30x1HybridTradeServer1prdbc30b    MarketDataQueue_14  0       BC30B HTS Q > 750    Instrumentor
 

I tried a few other things. I put a bunch of 9's into the INSERT INTO statement:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('559'| '1000'| '15401'| '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'| 'Prodcfix06v2prdcfix2'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('560'| '1001'| '15401'| '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'| 'Prodcfix06v2prdcfix2'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('565'| '1002'| '15401'| '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'| 'Prodcas01v2cas0235'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('561'| '1003'| '15401'| '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'| 'Prodcas01v2cas0235'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('562'| '1004'| '15401'| '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'| 'Prodcas01v2cas0120'| 'ALL');
 

and got a value of 2147483647 for the TRIPVALUE in the EXCEL file:

09:00:01:000000    BC30B HTS Q>750,Q<5000    Medium    ProdBC30x1HybridTradeServer1prdbc30b    MarketDataQueue_14    2147483647  BC30B HTS Q > 750    Instrumentor
 

I also tried this: where I put a 9 before the text to see what the value of TRIPVALUE would be in the excel file:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('559'| '1000'| '15401'| '9 here is something I know will be treated as text and only text'| 'Prodcfix06v2prdcfix2'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('560'| '1001'| '15401'| '9 here is something I know will be treated as text and only text'| 'Prodcfix06v2prdcfix2'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('565'| '1002'| '15401'| '9 here is something I know will be treated as text and only text'| 'Prodcas01v2cas0235'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('561'| '1003'| '15401'| '9 here is something I know will be treated as text and only text'| 'Prodcas01v2cas0235'| 'ALL');
 

and I got the value of 9 in the EXCEL file:

09:00:01:000000    BC30B HTS Q>750,Q<5000    Medium    ProdBC30x1HybridTradeServer1prdbc30b    MarketDataQueue_14      9    BC30B HTS Q > 750    Instrumentor
 

I also tried with just one character, i:

INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('559'| '1000'| '15401'| 'i'| 'Prodcfix06v2prdcfix2'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('560'| '1001'| '15401'| 'i'| 'Prodcfix06v2prdcfix2'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('565'| '1002'| '15401'| 'i'| 'Prodcas01v2cas0235'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('561'| '1003'| '15401'| 'i'| 'Prodcas01v2cas0235'| 'ALL');
INSERT INTO ALARMNOTIFICATIONDETAIL VALUES ('562'| '1004'| '15401'| 'i'| 'Prodcas01v2cas0120'| 'ALL');
 

and again, got a value of 0 for TRIPVALUE in the EXCEL file:

07:00:10:000000    BC98B OHS Q > 40    Low    ProdBC98x1OHServerHybridprdbc98b    POAQ/ProdUserSessionEvent    0    BC98B OHS Q > 40    Instrumentor
 

[Stewart Ross]

OK - but we knew already that the Excel data was in error, and as inferred in your previous posts that the error lies somewhere after your INSERT statement has been produced but BEFORE the data is output to Excel. So have you checked as I asked that the data for the TRIPVALUE field is correct in your MySQL table (the result of the processing of your INSERT statement)? If it is, then your subsequent parsing of this field to extract something useful from it is in error. If it is not correct in the MySQL table then it is how this field is being inserted that is in error. It is very difficult to assist you when it is still not clear where the error is being introduced despite many posts trying to advise you where to look. Only very systematic debugging at your end can resolve this.

As the INSERT statements seem to be correctly formed you must trace each step in the processing chain from there onwards and isolate the one that is responsible for the error. Make no assumptions at all about what is working - trace each path one by one and verify the output before you accept that the process involved does what is expected. If you cannot see directly how to do so you must insert trace code into your routines to allow you to show and test all the values involved.

Also, do yourself a favour and devise a smaller testset of data to work on - it is so much easier to fault find on 20 or less rows than it is on the hundreds of rows you seemed to be using in your earlier posts...

-Stewart

PS Atli pointed out in post 22 that you appeared to have a problem with the parsing of the data into your MySQL/Oracle tables - yet here we are still discussing the same thing - why??

[ndedhia1]

inside of my php code:
I commented out the line:
unlink($filename)
where
$filename = "mysql_".$table.".txt";

That allowed me to check the MySQL tables and those are correct and a 0 is still put into the TRIPVALUE of the EXCEL file:

559| 1000| 15401| <here is something I know will be treated as text and only text>| Prodcfix06v2prdcfix2| ALL
560| 1001| 15401| <here is something I know will be treated as text and only text>| Prodcfix06v2prdcfix2| ALL
565| 1002| 15401| <here is something I know will be treated as text and only text>| Prodcas01v2cas0235| ALL
561| 1003| 15401| <here is something I know will be treated as text and only text>| Prodcas01v2cas0235| ALL
 

thanks

[Stewart Ross]

And what have you done to trace/debug WHY this is happening, now that it is very clear there is a parsing problem on the contents of the field?? What methods are you using to parse that field? Be aware that there is no chance that we will dig through 400 lines of code on your behalf to try to find out when that is exactly what you should be doing...

I export data to Excel workbooks on an automated basis every day. At peak I have generated around 200 excel workbooks containing around 500 worksheets populated with exported data from more than 150 table views via a VBA Excel export class of my own design - and have never had the kind of failure you are experiencing.

-Stewart

[Atli]

After caching up, the problem seems obvious, although I am unable to pinpoint exactly where it is occurring.
Like Stewart, I suggest that you create create a small piece of sample data, and go through the code, step by step, making sure that the data is valid at that point in the code.
It's as simple as putting echo statements at strategic positions so that you can monitor it's progress.

The problem, however, appears to be: your value, which is meant to be a string, is being interpreted as a number.

As a result, when you pass "1", it is incorrectly being read as the number 1, rather than the character 1.
The end result appears to be the same: you get 1 in your output file, but it is important to note that internally, the value is being read incorrectly.

But, when you pass it text, the code attempts to convert it into a number. When it fails to do so, the number 0 is used instead.

And, furthermore, when you pass it a huge string of nines, it will be interpreted as a number, but because it is being converted into an unsigned-integer, the maximum value (2^31, or ~2.1 billion) will be used instead.

To fix this, you need to find where this conversion is taking place.
Search your code for any place where the value of your "tripvalue" field is placed inside a variable.
Make sure that the variable it is stored in is a string, rather than a number.

In PHP, this could be accomplished by doing:

$string = (string)$input;

Also make absolutely sure that your MySQL tables and any other place where this value is stored, uses an appropriate string type, like VARCHAR(255), or something equivalent.

[ndedhia1]

I want to thank you and STEWART for all the help. There is a file called resetTables.sql that drop all the tables and then create the tables and had TRIPVALUE as int(38). Once I changed that to VARCHAR, all seemed to work correctly.

 drop table alarmnotificationdetail; 
drop table alarmnotification; 
drop table alarmdefinition; 
drop table alarmcondition; 
CREATE TABLE ALARMCONDITION (DATABASEIDENTIFIER INT(38) NOT NULL, CONDITIONNAME VARCHAR (200) NOT NULL, CONDITIONTYPE INT(38) NOT NULL, CONDITIONSUBJECTNAME VARCHAR (200) NOT NULL, CONDITIONCONTEXTNAME VARCHAR (200) NOT NULL, CONDITIONCONTEXTTYPE VARCHAR (200) NOT NULL, CONDITIONFIELDNAME VARCHAR (200) NOT NULL, CONDITIONFIELDTYPE VARCHAR (200) NOT NULL, CONDITIONTHRESHOLD VARCHAR (200) NOT NULL, CONDITIONOPERATOR VARCHAR (200) NOT NULL, PRIMARY KEY(DATABASEIDENTIFIER)); 
CREATE TABLE ALARMDEFINITION (DATABASEIDENTIFIER INT(38) NOT NULL,ALARMDEFINITIONNAME VARCHAR (200) NOT NULL,ALARMDEFINITIONSEVERITY INT(38) NOT NULL,PRIMARY KEY(DATABASEIDENTIFIER)); 
CREATE TABLE ALARMNOTIFICATION(LOGGINGVERSION INT(4),LOGGINGSTYLE CHAR(1),NOTIFICATIONID INT(255),MILLISECONDS VARCHAR(255),TIMESTAMP DATETIME,ACTIVATIONID INT(38),DEFINITIONID INT(38),DEFINITIONSEVERITY INT(38),PRIMARY KEY(NOTIFICATIONID)); 
 CREATE TABLE ALARMNOTIFICATIONDETAIL(CONDITIONINDEX INT(38),NOTIFICATIONID INT(38),CONDITIONID VARCHAR (500),TRIPVALUE VARCHAR (1000),TRIPSUBJECTNAME VARCHAR (200),TRIPCONTEXTNAME VARCHAR (200),PRIMARY KEY(CONDITIONINDEX));
 

I do however have one more small problem that I was hoping someone could help me with the syntax of my code.

I have a TIMESTAMP variable right now that displays this:

 7:17:34:000000    BC97B OHS Q > 40    Low    ProdBC97x1OHServerHybridprdbc97b    ORDER_HISTORY_QUEUE_6    41    BC97B OHS Q > 40    Instrumentor
 

I want to add milliseconds to that TIMESTAMP.

Inside of my "mysql_".$table.".txt", the TIMESTAMP is correct with MILLISECONDS

1.0| B| 3186| 1234876654640| 2009-2-17 7:17:34:640| 26011| 26009| 3
 

I think that the problem is the same as what I was having with the TIMESTAMP problem where the resetTables.sql file is creating the table, ALARMNOTIFICATION incorrectly:

CREATE TABLE ALARMNOTIFICATION(LOGGINGVERSION INT(4),LOGGINGSTYLE CHAR(1),NOTIFICATIONID INT(255),MILLISECONDS VARCHAR(255),TIMESTAMP DATETIME,ACTIVATIONID INT(38),DEFINITIONID INT(38),DEFINITIONSEVERITY INT(38),PRIMARY KEY(NOTIFICATIONID));
 

Here is the SELECT statement which I have provided in an earlier post:

$query = "SELECT a2.conditionindex, a1.timestamp, a3.alarmdefinitionname, a3.alarmdefinitionseverity, a2.tripsubjectname, a2.tripcontextname, a2.tripvalue, a5.conditionname, a5.conditiontype FROM alarmnotification AS a1, alarmnotificationdetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notificationid=a2.notificationid AND a1.definitionid=a3.databaseidentifier AND a2.conditionid=a5.databaseidentifier ORDER BY a1.timestamp, a3.alarmdefinitionseverity";
 

and here is how I am displaying it on my EXCEL file:

if ((substr($row['timestamp'],11,12)>=$timeFrame_min) && (substr($row['timestamp'],11,12)<$timeFrame_max)) {
            fwrite($handle, "<tr>");
            fwrite($handle, "<td>" . date("H:i:s:u", strtotime($row['timestamp'])) . "</td>");
            fwrite($handle, "<td>" . $row['alarmdefinitionname'] . "</td>");
            if ($row['alarmdefinitionseverity'] == 1)
                fwrite($handle, "<td> High </td>");
            elseif ($row['alarmdefinitionseverity'] == 2)
                fwrite($handle, "<td> Medium </td>");
            elseif ($row['alarmdefinitionseverity'] == 3)
                fwrite($handle, "<td> Low </td>");
            fwrite($handle, "<td>" . $row['tripsubjectname'] . "</td>");
            fwrite($handle, "<td>" . $row['tripcontextname'] . "</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>");
            elseif ($row['conditiontype'] == 3)
                fwrite($handle, "<td> Log Message </td>");
            else
                fwrite($handle, "<td> ERROR#NOTENOUGHINFO# </td>");
            fwrite($handle, "</tr>");
        }
 

I know this is kind of a different problem that I was talking about in earlier posts but since you have been able to review some of my code and deals with some of the same code, I thought it would be better to post it here.

Thank you for the help!!!

[Atli]

I'm glad you found the problem :]

As to your other question.
MySQL has a know bug in regard to milliseconds.
None of the available date/time formats provided in MySQL so far support the use of milliseconds.
If you include them in your INSERT statements, they will simply be ignored.
(See 10.3.1. The DATETIME, DATE, and TIMESTAMP Types)

The easiest workaround is to store the millisecond part in a different field, like explained in this blog. There are also other methods, like storing the date in a 64bit integer, but those are more complicated to use.

[ndedhia1]

I looked at that blog but to be honest, I am having some trouble with the syntax.
When doing a create table, I have a field called MILLISECONDS :

CREATE TABLE ALARMNOTIFICATION(LOGGINGVERSION INT(4),LOGGINGSTYLE CHAR(1),NOTIFICATIONID INT(255),MILLISECONDS INT(255),TIMESTAMP DATETIME,ACTIVATIONID INT(38),DEFINITIONID INT(38),DEFINITIONSEVERITY INT(38),PRIMARY KEY(NOTIFICATIONID));
 

and this field can be used to create hours, minutes, seconds and milliseconds.
I am not the best with mysql and creating the SELECT statements so I have a few questions.
Do I put the new SELECT statement after the SELECT statement I already have:

$query = "SELECT a2.conditionindex, a1.timestamp, a3.alarmdefinitionname, a3.alarmdefinitionseverity, a2.tripsubjectname, a2.tripcontextname, a2.tripvalue, a5.conditionname, a5.conditiontype FROM alarmnotification AS a1, alarmnotificationdetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notificationid=a2.notificationid AND a1.definitionid=a3.databaseidentifier AND a2.conditionid=a5.databaseidentifier ORDER BY a1.timestamp, a3.alarmdefinitionseverity";
 

also, where and what do I put in the FWRITE line:

fwrite($handle, "<td>" . date("H:i:s:u", strtotime($row['timestamp'])) . "</td>");
 

I am unsure how to incorporate the MILLISECONDS field with the TIMESTAMP field and add milliseconds to the time that I already have that works.

THIS IS WHAT I HAVE TRIED AND AM STILL TRYING TO MAKE IT WORK:

I added this to my select statement:

$query = "SELECT a2.conditionindex, a1.timestamp, a1.milliseconds, a3.alarmdefinitionname, a3.alarmdefinitionseverity, a2.tripsubjectname, a2.tripcontextname, a2.tripvalue, a5.conditionname, a5.conditiontype FROM alarmnotification AS a1, alarmnotificationdetail AS a2, alarmdefinition AS a3, alarmcondition AS a5 WHERE a1.notificationid=a2.notificationid AND a1.definitionid=a3.databaseidentifier AND a2.conditionid=a5.databaseidentifier ORDER BY a1.timestamp, a3.alarmdefinitionseverity";
 

I also added this to my FWRITE statement:

fwrite($handle, "<td>" . date("H:i:s:((TIME_TO_SEC(a1.milliseconds))*1000)", strtotime($row['timestamp'])) . "</td>");
 

this was my output:

07:10:16:((CST0FebE_CST-0600_thEC(am1.0210TuesdayTuesday1016America/Chicago2009-02-17T07:10:16-06:00200921716))*1000)    BC94A OHS Q > 40    Medium    ProdBC94x1OHServerHybridprdbc94a    ORDER_HISTORY_QUEUE_1    78    BC94A OHS Q > 40    Instrumentor    
 

obviously that isnt correct.


thanks

[ndedhia1]

Hi..i was wondering if anyone is helping me with this and if there were any updates.

thanks

[Stewart Ross]

Atli very helpfully pointed you in the right direction, giving you links to other sites where posters had worked around the same problem. There is little more that can be done in a forum like this.

It is clear by reading the posts listed that there is no bug fix for the lack of millisecond accuracy in MySQL storage. In my opinion this is not an insurmountable issue - what is wrong with implementing an additional field to store the millisecond component separately, as Atli has suggested?

From what I can see you mention an additional field, but you are seeking to try to make the additional code work in the same way as the time field you already have, using MySQL time functions. But this is unlikely to work for you when MySQL cannot store times to millisecond accuarcy.

I suggest that instead of trying to extract a single time value you need to consider the time component in two parts: a most signifcant time which MySQL itself will store for you but not to millisecond accuracy, and a least significant time which you yourself will need to store in a separate field. It will be a number representing millisecond time - not a time datatype as such.

It means that comparisons on times are in two parts. If the most significant part is the same you need then to consider the least significant parts separately to identify one time being greater or lesser than the other. However, if the most significant times are not equal you do not need to consider the least significant parts at all in order to identify which time is greater or lesser than the other - the standard MySQL time functions will do that for you.

Even allowing for your uncertainties with SQL I am sure that you have the capability to write the small amount of code needed to extract the millisecond component separately from the time as a number of some kind and store it in the additional field. You certainly know more about your application than any of us ever could, so instead of asking if you are going to get more help - which is asking us to analyse your code - why not map out the steps in a logical way, have a go again at the problem for yourself then post your partial solution if it does not work out? It's the only way that you will truly have a full understanding of the solution in any event.

-Stewart

[ndedhia1]

I understand what you are saying with the two components aspect of my timestamp value but this is what I am having some problems with. When I do a create table, I have both a milliseconds field and timestamp field. Do I use the timestamp field to create both components or in my SELECT statement, do I have to select a1.milliseconds also and then do the calculations to that field?

thanks

[Atli]

If you store milliseconds in an additional integer field, MySQL will treat it as a simple integer. It will not be aware that this is a part of any date/time field.

Whatever you need to do that includes this integer field, it has to be done manually. You have to insert the millisecond value separately from the date/time field, you have to modify it and select it separately, you have to fetch it and do whatever calculations you need done to it separately.

From MySQL's perspective, the date/time and integer fields are in no way connected. If you want to use them together, you have to manually tell the query how to do so, or fetch them both and process them in your front-end application.

What you are doing here is outside the normal MySQL functionality, so how it actually works depends on how you make it work.
There is no standard example or a simple answer, simply because there is no standard or simple way to do this.