Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Postgresql + ADO

  1. #1
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

    Default Postgresql + ADO

    i have a function that returns a record , however ado stored procedure component is telling me i need a column definition list.
    any ideas. more than likely its getting that info from the ODBC driver for Postgresql
    Anything or Anyone that fails to grow will eventually die. {AI}
    -------------------------------------------------
    Tomorrow is the future!
    Today Is the Tomorrow you made Yesterday!{AI}

  2. #2
    Join Date
    Mar 2003
    Posts
    492
    Rep Power
    0

    Default

    Postgres usually returns that error if your function returns out parameters (column list) and no definition. This happens when you call the function as 'SELECT function()' instead of 'SELECT * FROM function().' When you use the latter, Postgres treats the result of the function as results of a normal query and return tuples as if it were from a table.

    Could you try a query of the same function using the 'SELECT * FROM' method?

  3. #3
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

    Default

    When i do a query of the sort

    select * from some_function the results are as expected
    however when i run the stored procedure from Delphi using the ado stored procedure i get the error..

    i changed the procedure and have not quite what i wanted but a working solution . Just so you know i switched from using the posgresdac to having an odbc souce and using the
    ADO set of components.
    Anything or Anyone that fails to grow will eventually die. {AI}
    -------------------------------------------------
    Tomorrow is the future!
    Today Is the Tomorrow you made Yesterday!{AI}

  4. #4
    Join Date
    Mar 2003
    Posts
    492
    Rep Power
    0

    Default

    Sorry to see you're still having issues with the query and parameters (PostgresDAC)? Did you try the test case I sent?

    The problem I found with the ODBC and ADO setup is Performance; can be an extreme pain when running serious queries.

    I have not dived too deeply in the ADO sources but I suspect it is using that style of calling convention for stored procs (SELECT stored proc). If it is the case then you will have to use a query because Postgres recommends using the 'SELECT * FROM' method for functions returning results.

  5. #5
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

    Default

    I know ill take a performance hit with the ODO setup, but just got tired of waiting for PDAC to work.........was kind of disappointed, since i am taking out a mysql server for a Postgresql server and to not even have the DAC working seems like a slap in my face. Considering i was the one who said yes lets take that one out and put this one in. In any case time is againts this project as its any upgrade (much needed ) so for now ill go with what works slow or not.
    Anything or Anyone that fails to grow will eventually die. {AI}
    -------------------------------------------------
    Tomorrow is the future!
    Today Is the Tomorrow you made Yesterday!{AI}

  6. #6
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

    Default

    weired thing is when i do rum the short version select som_proc in the console it works very well. but not in the application.
    Anything or Anyone that fails to grow will eventually die. {AI}
    -------------------------------------------------
    Tomorrow is the future!
    Today Is the Tomorrow you made Yesterday!{AI}

  7. #7
    Join Date
    Mar 2003
    Posts
    492
    Rep Power
    0

    Default

    When returning results, if you use OUT parameters (the return type of the function automatically becomes a record) and you use the short version, you get a list of the values but not a row. For example, a function test() with two out parameters 'name' and 'id' will return this result '(name value, id value).' But this is not a true result set. Whereas SELECT * FROM will return:
    name | id
    ----------
    value | value

    x rows fetched (y ms).

    So you have to be careful with thinking you are getting a result set. With the first one you do have to declare the column definitions manually, and with the second, the parser does this for you automatically because the function then gets treated as a normal relation.

    With regards to PostgresDAC, I still think that the issue you are having is with the double quotes. Sorry to see you're having so much issues with it, everyone I know is up and running perfectly. As a matter of fact, PG Lightening Admin from Tony Caduto uses PostgresDAC and it's a very fine program.

  8. #8
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

    Default

    I am sure PostgresDAC is quite kewl unfortunately time to figure the quirks out is time i don't have. i am sure there is something i am overlooking(missing) that keeps it from running the way it should. in the meantime i have to see how best i can get this application out the door. By the way i have hading another weired problem (just my luck) where is you use a table component from the ADO component set ....it will not pick up the index from the table assigned to it....
    Anything or Anyone that fails to grow will eventually die. {AI}
    -------------------------------------------------
    Tomorrow is the future!
    Today Is the Tomorrow you made Yesterday!{AI}

  9. #9
    Join Date
    Mar 2003
    Posts
    492
    Rep Power
    0

    Default

    Quote Originally Posted by Artificial_Intelligence View Post
    By the way i have hading another weired problem (just my luck) where is you use a table component from the ADO component set ....it will not pick up the index from the table assigned to it....
    That is weird. Just did a test case and picked both index and foreign keys. What version of PG are you using? 8.0 + ?

  10. #10
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

    Default

    I am using version 8.2, well i was, switched to MSDE.
    Anything or Anyone that fails to grow will eventually die. {AI}
    -------------------------------------------------
    Tomorrow is the future!
    Today Is the Tomorrow you made Yesterday!{AI}

Posting Permissions

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