java - Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "ActivitySession" does not exist -


i have function in postgres parse json records called thirdpartydataparse() , looks below

create or replace function thirdpartydataparse() returns text $$ declare  sessionid no scroll cursor select asn."id",asn."userid",asn."activityid",pd."datasourceid",ad."dump"    "development"."activitysession" asn inner join "development"."persondatasource" pd on pd."userid" = asn."userid" inner join "development"."activitysessiondump" ad     on asn."id"=ad."activitysessionid" asn."createdat" between now() - interval '5 days' , now() , pd."datasourceid"=1 read only; titles text default ''; rec record; jsonrec record;  begin  open sessionid; loop  fetch sessionid rec;    if not found         exit ;    end if;    if rec."activityid"=1 , rec."datasourceid"=1     execute 'select $1::json#>''{activities-steps,0}''->>''value'' "steps"' jsonrec using rec."dump";    end if; end loop; return titles; end; $$ language plpgsql;

i calling function in java like

try (callablestatement propercase = con.preparecall("{? = call thirdpartydataparse() }")) {   propercase.registeroutparameter( 1, types.varchar );   propercase.execute(); }

but getting below error in console

exception in thread "main" org.postgresql.util.psqlexception: error: relation "activitysession" not exist   where: pl/pgsql function thirdpartydataparse() line 10 @ open     @ org.postgresql.core.v3.queryexecutorimpl.receiveerrorresponse(queryexecutorimpl.java:2453)     @ org.postgresql.core.v3.queryexecutorimpl.processresults(queryexecutorimpl.java:2153)     @ org.postgresql.core.v3.queryexecutorimpl.execute(queryexecutorimpl.java:286)     @ org.postgresql.jdbc.pgstatement.executeinternal(pgstatement.java:432)     @ org.postgresql.jdbc.pgstatement.execute(pgstatement.java:358)     @ org.postgresql.jdbc.pgpreparedstatement.executewithflags(pgpreparedstatement.java:171)     @ org.postgresql.jdbc.pgcallablestatement.executewithflags(pgcallablestatement.java:81)     @ org.postgresql.jdbc.pgpreparedstatement.execute(pgpreparedstatement.java:160)     @ sun.reflect.nativemethodaccessorimpl.invoke0(native method)     @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62)     @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)     @ java.lang.reflect.method.invoke(method.java:498)     @ org.postgresql.ds.pgpooledconnection$statementhandler.invoke(pgpooledconnection.java:426)     @ com.sun.proxy.$proxy4.execute(unknown source)     @ oneraise.radis.thread.backgroundtask.thirdpartybackgoundtask.main(thirdpartybackgoundtask.java:173)

then removed callable statement & tried prepared statement still getting error

my prepared statement code 

try (preparedstatement stmt = backgroundobj.getdbconnection(prop).preparestatement("select thirdpartydataparse()")) {     stmt.execute(); }

exception 

exception in thread "main" org.postgresql.util.psqlexception: error: relation "activitysession" not exist   where: pl/pgsql function thirdpartydataparse() line 10 @ open     @ org.postgresql.core.v3.queryexecutorimpl.receiveerrorresponse(queryexecutorimpl.java:2453)     @ org.postgresql.core.v3.queryexecutorimpl.processresults(queryexecutorimpl.java:2153)     @ org.postgresql.core.v3.queryexecutorimpl.execute(queryexecutorimpl.java:286)     @ org.postgresql.jdbc.pgstatement.executeinternal(pgstatement.java:432)     @ org.postgresql.jdbc.pgstatement.execute(pgstatement.java:358)     @ org.postgresql.jdbc.pgpreparedstatement.executewithflags(pgpreparedstatement.java:171)     @ org.postgresql.jdbc.pgpreparedstatement.execute(pgpreparedstatement.java:160)     @ sun.reflect.nativemethodaccessorimpl.invoke0(native method)     @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62)     @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)     @ java.lang.reflect.method.invoke(method.java:498)     @ org.postgresql.ds.pgpooledconnection$statementhandler.invoke(pgpooledconnection.java:426)     @ com.sun.proxy.$proxy3.execute(unknown source)     @ oneraise.radis.thread.backgroundtask.thirdpartybackgoundtask.main(thirdpartybackgoundtask.java:173)

error: relation "activitysession" not exist

the error occurs because table activitysession not exist. names of tables case-sensitive. means example "activitysession" != "activitysession". believe has caused error.

so use real database , table names in query.


Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more