SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sequencing question / troubleshooting

    I'm using postgresql 7.4. I'm trying to create a sequence named "seq1", then run a query that drops a bunch of records into tbl95(one of the fields in tbl95 defaults to "nextval('public.seq1'::text)") and then when done it deletes the sequence. It's for dumping financial info into a "checks" table. The sequence is suppose to generate check numbers based on the users input parameter called "checkno". But when the page is opened I get this error:

    ERROR: relation "public.seq1" does not exist

    So it's quite obvious it's not creating the sequence, but I'm not sure why. Any ideas, anyone? Here's the code I'm using:


    // begin create seq1 to generate checknos
    $checkno__Seq1 = '-1';
    if (isset($_GET['checkno'])) {
    $checkno__Seq1 = $_GET['checkno'];
    }
    $query_Seq1 = sprintf("CREATE SEQUENCE public.seq1 INCREMENT 1 START '%s'", $checkno__Seq1);
    // end create seq1 to generate checknos

    // begin Recordset
    $session1__Recordset1 = '-1';
    if (isset($HTTP_SESSION_VARS['col1'])) {
    $session1__Recordset1 = $HTTP_SESSION_VARS['col1'];
    }
    $recordid66__Recordset1 = '-1';
    if (isset($_GET['recordid66'])) {
    $recordid66__Recordset1 = $_GET['recordid66'];
    }
    $query_Recordset1 = sprintf("INSERT INTO tbl95 ( col13, col2, col27, col28, col30, col31, col32, col33, col34, col16 ) SELECT view1.col23, view1.col2, view1.physician, view1.totaltopay, view1.col4, view1.col5, view1.col6, view1.col7, view1.col8, '%s' AS user FROM view1 WHERE (view1.col23='%s')", $session1__Recordset1,$recordid66__Recordset1);
    $Recordset1 = $base2->SelectLimit($query_Recordset1) or die($base2->ErrorMsg());
    $totalRows_Recordset1 = $Recordset1->RecordCount();
    // end Recordset

    // destroy checkno sequence
    $destroycheckno__Seq1 = "DROP SEQUENCE public.seq1";
    // end of destroy checkno sequence

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sadly, i don't do php, so i can't tell for sure, but it doesn't look like you have any error trapping, so if the CREATE SEQUENCE statement fails, you still go ahead and attempt the INSERT

    the START value of CREATE SEQUENCE looks like it's a string

    pretty sure that's not right

    also, what's $checkno__Seq1 for, and why is there a comma in front of it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 is correct. The single quotes around your start value will cause an error in Postgres.

    This is what you want:

    $query_Seq1 = sprintf("CREATE SEQUENCE public.seq1 INCREMENT 1 START %s", $checkno__Seq1);
    // end create seq1 to generate checknos

    And yea some error checking would help

  4. #4
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Although you create the SQL sttaement to create the sequence ($query_Seq1), you never execute that query , hence the sequence is not created.

  5. #5
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks guys. i got it going now.

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post the solution so others can learn

  7. #7
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I updated this part and it works now:

    // begin create seq1 to generate checknos
    $checkno__Seq1 = '-999999';
    if (isset($_GET['checkno'])) {
    $checkno__Seq1 = $_GET['checkno'];
    }
    $query_Seq1 = sprintf("CREATE SEQUENCE public.seq1 INCREMENT 1 START %s", $checkno__Seq1);
    $Seq1 = $base2->SelectLimit($query_Seq1) or die($base2->ErrorMsg());
    // end create seq1 to generate checknos


Bookmarks

Posting Permissions

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