PostgreSQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesPostgreSQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 9th, 2013, 06:50 AM
plangan plangan is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 3 plangan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 44 m 12 sec
Reputation Power: 0
Error 42601: No destination for result data. Using RefCursor

Help - I am going round in circles!
I am using the following method to call a POSTGRES function:

public DataTable rtnPatientDetails(int p_patientID)
{
DataTable v_userDT = new DataTable();
try
{
string v_connString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", v_host, v_port, v_user, v_pass, v_dbName);

using (NpgsqlConnection v_connection = new NpgsqlConnection(v_connString))
{
// Open the PgSQL Connection.
v_connection.Open();
string v_functionName = "pat_getpatient";

using (NpgsqlCommand v_command = new NpgsqlCommand(v_functionName, v_connection))
{
using (NpgsqlTransaction v_tranaction = v_connection.BeginTransaction())
{
v_command.CommandType = CommandType.StoredProcedure;
v_command.Parameters.Add("id", p_patientID);
using (NpgsqlDataAdapter v_userDA = new NpgsqlDataAdapter(v_command))
{
v_userDA.Fill(v_userDT);
}
v_tranaction.Commit();
}
}
}
}
catch (NpgsqlException v_exception)
{
throw v_exception;
}
catch (Exception v_exception)
{
throw v_exception;
}
return v_userDT;
}

However the follow error is returned:

Error: 42601: query has no destination for result data:

The POSTGRES function I am calling is:

CREATE OR REPLACE FUNCTION pat_getpatient(int) RETURNS SETOF refcursor AS
$$
DECLARE
v_recordSet refcursor;
BEGIN
OPEN v_recordSet FOR
select * from v_patient where id=$1;
RETURN NEXT v_recordSet ;
return ;
EXCEPTION
WHEN OTHERS THEN
select inserterror( 'E', 'pat_rtnpatient', 'act_individual', substring(SQLERRM from 1 for 200)) ;
return ;
END;
$$
LANGUAGE plpgsql;

I have used this method many times with no problem. Previously where I have gotten the 42601 error it has usally been of the result of an error in my SQL.
But this time I cannot see one. The sql making up the view works fine (it was previously in the method). The view is:

create or replace view v_patient
AS
select a.id,
a.sal_id,
c.descr,
a.first_name,
a.middle_name,
a.surname,
a.known_name,
a.home_tel,
a.office_tel,
a.mobile,
a.email,
a.active,
d.mrn,
d.hospital_no,
TO_CHAR(d.dob, 'DD-MON-YYYY HH:MI:SS') AS "dob",
d.gender AS "gender_id",
e.descr AS "gender",
d.ward AS "ward_id",
i.descr AS "ward",
d.bed,
d.mothers_name,
d.fathers_name,
d.guardians_name,
d.premature,
d.fetal_echo,
d.ethnic_group_id,
f.descr AS "ethnic_group",
d.language_id,
g.descr AS "language",
TO_CHAR(d.presentation_date, 'DD-MON-YYYY HH:MI:SS') AS "presentation_date",
TO_CHAR(d.jpc_date, 'DD-MON-YYYY HH:MI:SS') AS "jpc_date",
TO_CHAR(d.last_seen_date, 'DD-MON-YYYY HH:MI:SS') AS "last_seen_date",
TO_CHAR(d.final_dc_date, 'DD-MON-YYYY HH:MI:SS') AS "final_ac_date",
d.blood_grp_id,
j.descr AS "blood_group",
d.referral_id,
k.descr AS "referral",
d.death_id,
l.descr as "death",
d.unit_id,
o.descr AS "medical_centre"
from act_individual a
left outer join act_type b on a.typ=b.id
left outer join act_salutation c on a.sal_id=c.id
right join act_patient d on a.id=d.id
left outer join maint_gender e on d.gender=e.id
left outer join maint_ethnic f on d.ethnic_group_id=f.id
left outer join maint_language g on d.language_id=g.id
left outer join maint_ward i on (d.unit_id=i.med_centre_id and d.ward=i.id)
left outer join maint_blood j on d.blood_grp_id=j.id
left outer join maint_referral k on d.referral_id=k.id
left outer join maint_death l on d.death_id=l.id
left outer join maint_medical_centre o on d.unit_id=o.id ;

42601 usually implies that there is no variable to put the result od the query in. But in me case I have defined one - 'refcursor'. I am lost can anyone see what is wrong?

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesPostgreSQL Help > Error 42601: No destination for result data. Using RefCursor

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap