Re: Updating old code for new engine

Bob McConnell wrote:
> Good morning,
>
> I have been setting up a server at home as a workbench to learn PHP,
> Postgres, and a few other tools. I recently installed an application
> (PHPRecipeBook) that was written for Postgres 7.2.3. But I have 8.2.3
> installed. When I try to access the recipe book, I get a few errors;
> surprise, surprise. Is there any way to identify what adjustments I
> need to make in the queries to compensate for the difference in target
> versions? My dream option would be a form in which I enter the
> original target and the new target and get back a list of all the
> syntax changes I need to account for. I started to walk through the
> release notes, but there are far too many changes spread too widely to
> make sense of.
>
> The first level errors I get are:
>
> SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR
> recipe_name LIKE 'a%' ORDER BY recipe_name
> ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY
> clause or be used in an aggregate function
>
I'm not sure, but I don't think the statement was ever correct SQL. In
any case, you would need to add a GROUP BY clause.

> SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name
> LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0
> ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE
> 'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^
>
Again, I don't think this was ever correct.

> I fully expect there will be more after I get these corrected. My
> setup is as follows:

I haven't used phprecipebook before, but it looks it is generating quite
incorrect SQL queries. If it was truly written for version 7.2.3, that
means that it probably hasn't been updated in several years, so you
might want to look seriously at what phprecipebook offers compared to
alternatives. I really know nothing about the software, so I could be
very off base, but the SQL errors you are getting are pretty basic.

Sean