I keep getting the 22P02 error: invalid input syntax for integer: "{1,2,3}" (see comment in service call).
Here is my service call:
...
using (var command = new NpgsqlCommand("mediabase.create_media", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("title", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.title;
command.Parameters.Add("uniqueFilename", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.uniqueFilename;
command.Parameters.Add("description", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.description;
command.Parameters.Add("categoryIds", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.categoryIds; /* here the value is "1,2,3" */
command.Parameters.Add("typeId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.typeId;
command.Parameters.Add("ownerId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.ownerId;
command.Parameters.Add("statusId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.statusId;
command.Parameters.Add("gpsLocation", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.gpsLocation;
command.Parameters.Add("locationNameId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.locationId;
command.Parameters.Add("uploadUserId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.uploadUserId;
command.Parameters.Add("uploadDate", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.uploadDate;
command.Parameters.Add("uploadIp", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.uploadIp;
command.Parameters.Add("metadataId", NpgsqlTypes.NpgsqlDbType.Integer).Value = metadataId;
command.Parameters.Add("sysEnvironment", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.sysEnvironment;
command.Parameters.Add("languageId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.languageId;
command.Parameters.Add("publicationIds", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.publicationIds;
command.Parameters.Add("limitations", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.limitations;
mediaId = Convert.ToInt32(command.ExecuteScalar());
}
pgTransaction.Commit();
...
And the stored procedure:
CREATE OR REPLACE FUNCTION mediabase.create_media(
title character varying,
uniquefilename character varying,
description character varying,
categoryids character varying,
typeid integer,
ownerid integer,
statusid integer,
gpslocation character varying,
locationnameid integer,
uploaduserid integer,
uploaddate character varying,
uploadip character varying,
metadataid integer,
sysenvironment character varying,
languageid integer,
publicationids character varying,
limitations character varying)
RETURNS integer AS
$BODY$
declare _mediaId integer;
declare _point varchar;
declare _gps_location geometry;
declare _id text;
begin
IF (gpslocation <> '') THEN
_point = 'POINT(' || gpslocation || ')';
_gps_location = ST_Transform(ST_GeomFromText(_point, 4326), 900913);
ELSE
_gps_location = NULL;
END IF;
insert into mediabase.media (
title,
unique_filename,
description,
owner_id,
status_id,
gps_location,
type_id,
location_name_id,
upload_user_id,
upload_date,
upload_ip,
metadata_id,
system_environment,
language_id,
limitations)
values (
title,
uniqueFilename,
description,
ownerId,
statusId,
_gps_location,
typeId,
locationNameId,
uploadUserId,
uploadDate,
uploadIp,
metadataId,
sysEnvironment,
languageid,
limitations)
returning id into _mediaId;
-- insert category ids
FOR _id IN SELECT string_to_array (categoryids,',')
LOOP
INSERT into mediabase.media_categories (media_id, category_id)
values (_mediaId, (_id::int));
END LOOP;
-- insert publication ids
FOR _id IN SELECT string_to_array (publicationids,',')
LOOP
INSERT into mediabase.media_publications (media_id, publication_id)
values (_mediaId, (_id::int));
END LOOP;
return _mediaId;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
As you can see in the service method, the input of parameter categoryids is the string "1,2,3", and the NpgsqlDbType is Varchar. And at the stored procedure the input type is character varying.
Here is the execution log:
2013-01-17 08:56:42 CET ERROR: invalid input syntax for integer: "{1,2,3}"
2013-01-17 08:56:42 CET CONTEXT: SQL statement "INSERT into mediabase.media_categories (media_id, category_id)
values (_mediaId, (_id::integer))"
PL/pgSQL function "create_media" line 54 at SQL statement
2013-01-17 08:56:42 CET STATEMENT: select * from mediabase.create_media(('Penguins')::varchar,('079117ec-676f-4022-9950-69e55c2a2600_Penguins.jpg')::varchar,('Description...')::varchar,('1,2,3')::varchar,(1)::int4,(1)::int4,(1)::int4,('')::varchar,(1)::int4,(1)::int4,('17/01/2013 08:56:42')::varchar,('::1')::varchar,(399)::int4,('dev')::varchar,(1)::int4,('1,2')::varchar,('Limitations...')::varchar)
Why is this error poping up?
characteridsasinteger[]? Then you can simply omit all the casts and usegenerate_subscripts()for the insert instead of looping. Anyway,string_to_array('{1,2,3}', ',')results in{"{1",2,"3}"}. – dezso Jan 16 '13 at 14:09generate-subscripts(), and the correct split of the string. Thanks. – Ambran Jan 16 '13 at 14:25media.categoryIdsas added as a parameter is a String1,2,3, not an array?{1,2,3}is the PostgreSQL representation of the array[1,2,3]so maybe you're passing an array and nPgSQL is converting it for you? Otherwise, where are the brackets coming from? – Craig Ringer Jan 16 '13 at 14:25media.categoryIdsis defenetly a string. I would actualy like to do that with an array, but had problems coverting. Lets say I'm sending an array of integers as @dezso sugested: 1) In the service method, whichNpgsqlDbTypeshould it be sent with? 2) What is the syntax for recieving it on the stored procedure side? Doing it like that will probably solv the problem. – Ambran Jan 16 '13 at 14:46log_statement = allin your session, or globally inpostgresql.conf, then re-test and edit your question to show the exact text of the SQL statement executed by nPgSQL and the exact text of the error message, both taken from the PostgreSQL log files? – Craig Ringer Jan 16 '13 at 23:32