what is error 22 in database ?

Databases
1

Hi,

I’ve got an error 22 when I update a recordset in a postgresql database
did not find any relevant information about this error ?
it is on a table that has foreign keys on it
other tables with no foreignkey did not show the error.

any clue(s) ?
thanks.

2

What’s the error message from the database? The error message usually has a pretty decent human readable description of the problem.

FWIW, I never check the error code. It’s irrelevant to what I need. The ErrorMessage is the important part.

3

it’s a weird message …

ERROR: syntax error at or near "default" LINE 1: ...xtension = $4, value = $5, include_mailings = $6, default = ... ^
I do not generate these $4,$5 or $6
I use rs.idxfield(i).stringvalue = “xxx”
it seems the rs.update generates this statement and the postgresql plugin sends it to the database server.

4

just thinking as I wrote the above message… default is a reserved keyword in postgres ?

5

Yes, looks like “default” is a reserved word in PostgreSQL and cannot be used for identifiers.

https://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html

6

[quote=269887:@Jean-Yves Pochez]Hi,

I’ve got an error 22 when I update a recordset in a postgresql database
did not find any relevant information about this error ?
it is on a table that has foreign keys on it
other tables with no foreignkey did not show the error.

any clue(s) ?
thanks.[/quote]

What SQL statement is triggering the error?

7

it was the “default” field that is a reserved keyword.
I changed the field name and all is ok now
thanks all for the help.

8

You can use reserved keywords for field names. Depending on the SQL flavour (don’t have experience with Postgresql myself) you can enclose those names between double quotes or square brackets.

Example:

INSERT INTO MyTable ("Default") VALUES ('abc')

or (in other SQL databases):

INSERT INTO MyTable ([Default]) VALUES ('abc')
9

From experience, I would change the field name from “default” to “defaultvalue” or whatever makes sense. “Default” is such a generic name it doesn’t mean anything so your database field names should be just as descriptive as your Xojo variable names - something that makes sense at a glance. If I have to decipher the meaning of a field name I’ve already lost the battle.

And remember, you’re not designing the names of your variables and field names not for 6 days from now but for 6 years from now. You won’t remember so being descriptive is better than vague. Assume the programmer coming after you is an axe wielding psychopath that knows your address.

10

While you CAN use reserved words, you really shouldn’t. And if you do, you can’t use all the built-in functions like RecordSet.Update.

11

I changed “default” to “is_default” and it is very sufficient like that !

12

BTW, Postgres usually stops you from creating a field called default.

alter TABLE test add column default text;

ERROR: syntax error at or near “default”
SQL Status:42601

I assume you used PGAdmin to create the field and PGAdmin internally uses this syntax to create the field (probably in attempt to honor your capitalization):

alter TABLE test add column “default” text;

which the PG server won’t object to. So I usually create my fields with plain SQL to avoid things like that.