Postgress SQL
Results 1 to 4 of 4

Thread: Postgress SQL

  1. #1
    Junior Member
    Join Date
    Apr 2005
    Posts
    8

    Postgress SQL

    So I know almost nothing about SQL and I came across a project a work. I was originally going to set up an ODBC connection from a server running my company's software to a Postgress server. Unfortunately, we have had quite a few setbacks. We wrote a php script to help us export the data that we needed from the Postgress database, and it worked the way it was supposed to. The only problem was that it takes 45 minutes for a query to run. That seems a little over the top, so we did some research and it appears that the driver we have for the database(newest one we could find), takes the entire table that we are querying and pulls it across the network to the machine that initiates the query and runs the query then. I have had a few ideas and thought maybe someone could help me fine tune them. Is it possible to run the query on the Postgress server and automate it? How about a batch file? I know that it's a little archaic, but I'm getting desperate!!!!. The query taking 45 minutes wouldn't be such a big deal, except that the data is kind of time sensitive. My company has notification software for schools and the query is for period attendance. I will be happy to supply any information requested, as long as it doesn't break my "close your mouth" contract. HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1

  2. #2
    Just Another Geek
    Join Date
    Jul 2002
    Location
    Rotterdam, Netherlands
    Posts
    3,403
    What driver are you using?

    http://nl2.php.net/pgsql
    Oliver's Law:
    Experience is something you don't get until just after you need it.

  3. #3
    Senior Member
    Join Date
    Jul 2004
    Posts
    469
    Sounds like you might have some problems in your php code. There are always two ways to do things. One would be to bring the data accross, and manipulate it in php. The other is to manipulate it via a query on the SQL server and just bring back the required data. I would venture to guess you are doing the first.

    You might suggest a code review of sorts to find out if the code is using the SQL server efficiently.

  4. #4
    Junior Member
    Join Date
    Apr 2005
    Posts
    8
    Per Sir Dice's request the driver is MERANT 3.6 32-BIT Progress SQL92v9.1D. That is how the driver is listed in the system DSN tab. Here is the code, this is just a test bc we still need to get a preffered connection before we decide the exact query.

    <?

    $out = "/datadump.csv";
    $user = "username";
    $pass = "?????????";
    $dsn = "databasename";
    // select the date we wish to export attendance for
    $todaydate = date('Y-m-d');

    // build the query
    $query = 'select "STUDENT-ATND-DETAIL"."ATND-DATE", "STUDENT-ENTITY"."STUDENT-STATUS", "STUDENT-ENTITY"."STUDENT-ID", "STUDENT"."STUDENT-ID", "STUDENT-ATND-DETAIL"."AAT-ID" from PUB.STUDENT Join PUB."STUDENT-ATND-DETAIL" ON STUDENT."STUDENT-ID" = "STUDENT-ATND-DETAIL"."STUDENT-ID" Join PUB."STUDENT-ENTITY" ON STUDENT."STUDENT-ID" = "STUDENT-ENTITY"."STUDENT-ID" where "STUDENT-ENTITY"."STUDENT-STATUS" = ' . "'" . "A" . "' and " . 'PUB."STUDENT-ATND-DETAIL"."ATND-DATE" = ' . "'" . $todaydate . "'";
    echo $query . "\r\n";

    function andList ($row) {
    $line = NULL;
    for ($x = 0; $x < count($row); $x++) {
    if ($line == NULL) {
    $line = $row[$x];
    } else if ( $x == (count($row) -1) ) {
    $line .= " and " . $row[$x];
    } else {
    $line .= ", " . $row[$x];
    }
    }
    return $line;
    }

    // connect to the DSN
    echo "Connecting to DSN." . "\r\n";
    if (!$dbconnect = odbc_connect($dsn, $user, $pass)) {
    echo "Failed to connect!" . "\r\n";
    } else {
    echo "Connected to the DSN." . "\r\n";
    }

    // load and execute the query against the DSN
    echo "Executing query." . "\r\n";
    if (!$dbdata = odbc_exec($dbconnect, $query)) {
    echo "Failed to execute query!" . "\r\n";
    } else {
    echo "Executed query." . "\r\n";
    }

    // open the output file
    if (!$datafile = fopen($out, "w+")) {
    echo "Failed to open output file" . "\r\n";
    } else {
    echo "Opened output file." . "\r\n";
    }

    // read the returned rows
    while ($row = odbc_fetch_array($dbdata) ) {

    // check against today's date
    //if ($row["ATND-DATE"] != $todaydate) {
    // continue;
    //}

    // get the attendance codes in an array
    //$attcodes = explode(";", $row["ATT-ID"]);
    //echo "\r\n";
    //echo $row["ATT-ID"] . "\r\n";
    //echo $attcodes . "\r\n";

    // set up an empty array to proccess the attendance codes into
    //$attperiods = array("");

    // process the attendance codes into numbers only catching the U code
    //for ($y = 1; $y < count($attcodes); $y++) {
    // if (($attcodes[$y] != "") AND ($attcodes[$y] == "U")) {
    // $attperiods[] = $y;
    // }
    //}
    //echo $attperiods . "\r\n";

    // format the attendance field
    //$outperiods = andlist($attperiods);
    //echo $outperiods . "\r\n";

    // create a line for the output data
    $tmpline = '"' . $row["ATND-DATE"];
    $tmpline .= '","' . $row["STUDENT-ID"];
    //$tmpline .= '","' . $outperiods;
    $tmpline .= "\"\r\n";

    // if there were no matching attendance codes skip the student
    //if ($outperiods == NULL) {
    // echo "-";
    // continue;
    //} else {
    // echo "+";
    //}

    // write the output line
    fwrite($datafile, $tmpline);

    }
    echo "\r\n";

    // close the output file
    fclose($datafile);
    echo "Closed ouput file." . "\r\n";

    ?>

    I left out the server information for obvious reasons. I would like to stress that I am anything but proficient with php and am in the process of trying to learn it. I didn't write this code, another guy from my company did, but am working with it to see about making it successful. Is there a way to automate the query on the machine? The user says that they have a nice query that runs quickly on the machine, but that there is no way to automate it bc you have to open the tool and run the query even though it is saved in a format similar to a data view.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 Security News

     Patches

       Security Trends

         How-To

           Buying Guides