Обсуждение: CURSORs and selects with parameters
When using cursors through the libpq interface, do I need to be
passing in the parameters when I'm FETCHing from them?
I've got some code that uses PQexecParams and does the equivalent of:
DECLARE a_cursor CURSOR FOR SELECT foo, bar FROM baz WHERE field = $1
FETCH NEXT a_cursor
but when I get to the FETCH I'm getting back the error:
ERROR: no value found for parameter 1
which is fair enough, as I'm not *passing* in any parameters to the
fetch. (Yes, I double-checked--the parameter count in the
PQexecParams call is 1 for the cursor declaration, and 0 for the
fetch) Do I have to?
--
Dan
--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk
On Tue, Feb 15, 2005 at 02:32:56PM -0500, Dan Sugalski wrote: > > I've got some code that uses PQexecParams and does the equivalent of: > > DECLARE a_cursor CURSOR FOR SELECT foo, bar FROM baz WHERE field = $1 > FETCH NEXT a_cursor > > but when I get to the FETCH I'm getting back the error: > > ERROR: no value found for parameter 1 What version of PostgreSQL are you using? I think this is fixed in 8.0. http://archives.postgresql.org/pgsql-general/2005-01/msg00812.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/
At 12:50 PM -0700 2/15/05, Michael Fuhr wrote: >On Tue, Feb 15, 2005 at 02:32:56PM -0500, Dan Sugalski wrote: >> >> I've got some code that uses PQexecParams and does the equivalent of: >> >> DECLARE a_cursor CURSOR FOR SELECT foo, bar FROM baz WHERE field = $1 >> FETCH NEXT a_cursor >> >> but when I get to the FETCH I'm getting back the error: >> >> ERROR: no value found for parameter 1 > >What version of PostgreSQL are you using? I'm using 7.4.5. > I think this is fixed >in 8.0. > >http://archives.postgresql.org/pgsql-general/2005-01/msg00812.php D'oh! Missed that one in the search. Time to find some upgrade packages, then. Thanks. -- Dan --------------------------------------it's like this------------------- Dan Sugalski even samurai dan@sidhe.org have teddy bears and even teddy bears get drunk
I'm looking specifically for a tool to help compare 2 database schemas (nominally, production and development) and generate the appropriate SQL (that can be later executed) to bring the to schema's into sync. Option1 : pg_dump -s firstdatabasename > first pg_dump -s seconddatabasename > second diff first second (obviously wont generate SQL to remedy the differences) Option2: pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php) (old abandoned perl project that doesn't work...) Option 3: pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php) (old abandoned TCL project which requires AOLServer to run???) Option 4: EMS PostgreSQL DB Comparer (http://www.sqlmanager.net/products/postgresql/dbcomparer) Slick tool...fairly cheap ($69)...doesnt let you exclude certain specific tables/sequences/databases, but otherwise, pretty functional. What does the rest of the postgreSQL admin community do in order to "bring-live" database schema changes from their development environment to production? Are there other options/techniques out there? Any help would be appreciated. Jeff
On Tue, 2005-02-15 at 15:01 +0000, Jeff Amiel wrote: > I'm looking specifically for a tool to help compare 2 database schemas > (nominally, production and development) and generate the appropriate SQL > (that can be later executed) to bring the to schema's into sync. [snip] > What does the rest of the postgreSQL admin community do in order to > "bring-live" database schema changes from their development environment > to production? The project I'm working on at the moment has a very dynamic schema. Most of our software releases have some requirement to tweak columns or add new tables, etc. We nominally use Power Designer for managing our schema but in reality, the main thing we use it for is drawing the pretty schema diagrams (and it's not even very good at that). Obviously, before any schema change can be rolled out to production, the precise method which will be used to apply the change in production must be tested in development and staging. Therefore, each software release includes some number of schema patch files which must be applied in order. We have a Perl script for applying patches which basically just feeds each .sql file to psql (we find the psql \set macro functionality useful in the patches) in order. If a patch is applied successfully, we update a row in a config table in the database to indicate the last patch applied. When we re-run apply_patches.pl, it looks at the config item to see what patch-level the database is up to and then apply each of the newer patches. Another part of the puzzle is that our regression test suite includes a schema 'test'. This basically just uses the first technique you outlined - the test creates one database from the Power Designer generated model and uses pg_dump -s on it; then creates another database from the original unpatched schema, applies all the patches and dumps it too. If a diff on the dumps reveals no differences then the test passes, otherwise the test fails - which usually means we need to update the Power Designer model. (The test script has to do a bit of reordering and whitespace normalising to avoid false positives). Although GUI design tools can be seductive, our developers seem to be more comfortable with using vi on a .sql file. Usually what they want to do is "exactly like this other thing but with this minor difference" which will always be easier in vi than in a GUI tool. So in summary, we build our schema patches manually. And we use our manual processes to drive the use of our GUI designer tool. If you want to try our apply patches script, I've dropped a copy here: http://wellington.pm.org/archive/postgresql/apply_patches.tar.gz Cheers Grant