Re: pg and number of parameters by insert

Hi Jean,

Jean-Christian Imbeault wrote:

> pginfo wrote:
> >
> > Hmm, Ok I try to explain:
>
> Try again. I can't quite get what you mean.
>
> >If I write: insert into table_1 values ('f1_value',10);
> >
> >It is error because I try to insert 2 fields in 3-fields table. The
> >reason is that I can make write error.
>
> If your table is:
>
> create table_1 (
> a text,
> b int,
> c int
> );
>
> Why should insert into table_1 values ('f1_value',10) be an error?
>

Because I send not 3 parameters.

> Explain to me why the above SQL query is wrong and maybe I can help you.
> But I can't see why a DB should give an error. If you want to force the
> user to give values for *every* column when doing an insert then all you
> need to do is create a table like this:
>
> create table_1 (
> a text NOT NULL,
> b int NOT NULL,
> c int NOT NULL
> );
>
> Maybe I am missing your point?
>

I will give you a example:

I use java + pg.
I have table t1 (a name, b int ).
I have 2 methods add1 and add2:

public int add1(String parameter1, int parameter2)
{
...
...
stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + ")" ); // it make insert
...
}

public int add2(String parameter1, int parameter2)
{
...
...
stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + ")" ); // it make insert
...
}

And if I add new field to my table. The field is varchar(100) and can be
empty . The empty string is NULL in pg.

After it I need to werwite my methods add1 and add2. But what will happen if
I forgot to make changes in add2:

public int add1(String parameter1, int parameter2,String parameter3)
{
...
...
stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + "'" + parameter3 + "')" ); // it make insert
...
}

public int add2(String parameter1, int parameter2,String parameter3 )
{
...
...
stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + ")" ); // it make insert
...
}

All two methods will not report error.
Actualy I can not easy detect my mistake, because the DB do not report the
error.

It is very important, because we write big projects and it is possible to
forgot this method.

And also I can not set the field to NOT NULL, because the user will not be
able to send empty parameter3.

I hoppe it is clear.

Exist any other way to to force the
pg to check if all fields are in insert statement?

regards,
ivan.

> Jc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)