On Tue, 2011-07-05 at 18:00 -0400, robert mckennon wrote: > I'm trying to determine if a user is in the database...If they are > not, then I want to add them.
Seeming simple task, always has to have some complications :) > in DB2, the return code is different when a "SELECT" statement returns > a value, or if the result is empty, or if the query failed etc.. Thats quite nice of them, but really only one code to return status, and a bunch to represent failure. Technically if a sql statement returns nothing, it still was a successful command. I am not sure I would agree if it returns non 0 for a empty result. But thats quite moot, I am not seeking to change DB2 output codes. Likely already been some debates or such there. > But in postgresql, it seems that the return code is 0 if the "SELECT" > statement returns an entry or not, just as long as it doesn't fail. That would be correct behavior, and their exit codes are documented. http://www.postgresql.org/docs/9.1/static/app-psql.html#AEN80350 > <code snippet> > # is the user_id in the Database? > psql xxxx -U postgres -c"select * from users where USER_ID = '$user_id';" > result=$? > echo "result from select is: $result" > > # if the user is NOT in the Database, then add them. > if [ $result = "1" ] ; then > psql xxxx -U postgres -c"insert into users (last_name, first_name, > user_id, email_adx) values ('$last_name', '$first_name', '$user_id', > '$email_adx');" > fi > </code snippet> > > So when I run the script...(which parses a list of users, and checks > the postgres db), the return code is always 0. As it should be, since the command succeeded, you just did not get the output you were desiring. But thats not related to the command being successful or not. > So how can one determine the outcome of the "SELECT" statement? ( I > guess I could use mysql instead of postgresql) Different RDBMS is one way to go, but a pretty extreme way to work to around this problem. But if your switching RDBMS anyway then it can be moot. > I saw one post where they recommended grepping the output, but that > seems silly to me. >From some googling it seems you might have to do something along those lines. Either have the command return output to a bash variable and test that, or grep it. Not sure why you would find that route silly. Its less than ideal since your used to the DB2 way of doing things. But having to use another command line app to process the output of another is pretty core to *nix systems. That really seems to be the correct way to go about this and is likely more portable. If your query the same table in any database, only the output format should change if anything. If you can control output format. Then you can use grep to parse the output in a much more consistent manner regardless of RDBMS. A much better approach than to rely on return codes. Which can as you have found out the hard way vary. But really one should only rely on codes for success or failure, with many codes for failure, but only one for success. End of day, a sql statement that returns nothing is not a failed query. Thus any command line sql statement should return 0 unless the sql statement fails to execute. -- William L. Thomson Jr. Obsidian-Studios, Inc. http://www.obsidian-studios.com --------------------------------------------------------------------- Archive http://marc.info/?l=jaxlug-list&r=1&w=2 RSS Feed http://www.mail-archive.com/[email protected]/maillist.xml Unsubscribe [email protected]
