[SQL] Problem with batch insert

[zkn]

Hi,

First I'm sorry if this message shouldn't be posted in this mailing list but I 
wasn't sure if it should be here or in jdbc mailing list.

I have a weird problem with batch insert. It works fine on my development 
machine but when I upload to the server it fails. Here is the log:

DEBUG [http-8443-2] - {conn-100123} Preparing Statement: insert into 
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) 
DEBUG [http-8443-2] - {pstm-100124} Executing Statement: insert into 
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) 
DEBUG [http-8443-2] - {pstm-100124} Parameters: [23, 229, 23, 141]
DEBUG [http-8443-2] - {pstm-100124} Types: [java.lang.Integer, 
java.lang.Integer, java.lang.Integer, java.lang.Integer]
DEBUG [http-8443-2] - Unable to translate SQLException with SQL state '42601', 
error code '0, will now try the fallback translator
DEBUG [http-8443-2] - Returning JDBC Connection to DataSource
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad 
SQL grammar []; nested exception is 
com.ibatis.common.jdbc.exception.NestedSQLException: 
--- The error occurred while applying a parameter map. 
--- Check the addSectionsToTargetsFilter-InlineParameterMap. 
--- Check the statement (update failed). 
--- Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","
Position: 113


And here is the log for the same operation on my development machine:

DEBUG [http-8080-1] - {conn-16} Preparing Statement: insert into 
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) , (?, ?) 
DEBUG [http-8080-1] - {pstm-17} Executing Statement: insert into 
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) , (?, ?) 
DEBUG [http-8080-1] - {pstm-17} Parameters: [28, 1603, 28, 1605, 28, 1604]
DEBUG [http-8080-1] - {pstm-17} Types: [java.lang.Integer, 
java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.Integer, 
java.lang.Integer]
DEBUG [http-8080-1] - Returning JDBC Connection to DataSource

Postgre version on the server 8.1 on development 8.3
Any clues?
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Problem with batch insert

[zkn]

On 18.02.2010, at 17:32, Tom Lane wrote:

> zkn  writes:
>> I have a weird problem with batch insert. It works fine on my development 
>> machine but when I upload to the server it fails. Here is the log:
> 
>> DEBUG [http-8443-2] - {conn-100123} Preparing Statement: insert into 
>> target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) 
> 
>> DEBUG [http-8443-2] - Unable to translate SQLException with SQL state 
>> '42601', error code '0, will now try the fallback translator
> 
> 42601 is a syntax error, although I think you ought to complain to
> somebody about the fact that your tools don't show you the actual server
> error message.
> 
>> Postgre version on the server 8.1 on development 8.3
> 
> 8.1 does not support multiple rows in a VALUES construct.

Yes, it was the postgre version.

> 
>   regards, tom lane
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 

Re: [SQL] Unable to get UPDATE ... FROM syntax correct

[zkn]

update audit set key = (select extension.number from extension where 
audit.record_id = extension_id)
On 19.02.2010, at 11:25, Gordon Ross wrote:

> I have two tables:
> 
>   Table "public.audit"
>   Column   |Type  | Modifiers
> +--+---
> id | integer  | (serial)
> record_id  | integer  | not null
> key| character varying|
> (...)
> 
> 
>  Table "public.extension"
>  Column |   Type |  Modifiers
> -++
> id  | integer| (serial)
> number  | integer|
> (...)
> 
> 
> The column "key" was recently added to the "audit" table, and I wish to
> populate it with the value form the "number" column in the extension table.
> The join between the tables is audit.record_id = extension.id
> 
> I tried:
> 
> UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id
> = extension.number;
> 
> But that returns saying "UPDATE 0"
> 
> However, doing:
> 
> SELECT audit.record_id, extension.number FROM audit, extension WHERE
> audit.record_id = extension.id;
> 
> Works fine.
> 
> Can someone tell me what I'm doing wrong ?
> 
> Thanks,
> 
> GTG
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql