ERROR: function cm_delete_card()

sebastian.alzogaray · January 29, 2018, 8:06pm

Hy everybody

Recently update to CMDBUILD 2.4.3 from 2.3.3 , and i have the next issue when I try to delete a card.

Call: services/json/management/modcard/deletecard

--------------------------------------------------

Error: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call cm_delete_card()}]; nested exception is org.postgresql.util.PSQLException: ERROR: function cm_delete_card() does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts

Please, help

i check the fuction on database and exists

Thanks

Regards from Argentina

tyler.veinot · February 1, 2018, 4:49pm

Having the same issue using 2.5.0 on Tomcat 8.5 with PostgreSQL 9.6 JDBC Driver postgresql-42.2.1.jar. I noticed it when tying to delete a chart and when I tried to delete an empty Dashboard I made.

Function exists in my database as well.

tecnoteca · February 1, 2018, 5:55pm

The cm_delete_card() PostgreSQL function existed in version 2.3.x and exists in version 2.4.x.
However, in version 2.3 it had two arguments:
– Function: public.cm_delete_card(integer, oid)
while in version 2.4 it has three arguments:

-- Function: public.cm_delete_card(integer, oid, text)

Obviously for PostgreSQL are different functions if the "signature" is different.
Check that you have applied the patch [webapp]/WEB-INF/patches/2.3.4-02.sql
CMDBuild Team

sebastian.alzogaray · February 1, 2018, 6:59pm

Thanks for the help but I continue with the problem.

I recreate the fuction with patches/2.3.4-02.sql

More details from the errror:

Call: services/json/management/modcard/deletecard

--------------------------------------------------

Error: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call cm_delete_card()}]; nested exception is org.postgresql.util.PSQLException: ERROR: function cm_delete_card() does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Position: 15

Previously Tecnoteca wrote:

The cm_delete_card() PostgreSQL function existed in version 2.3.x and exists in version 2.4.x.
However, in version 2.3 it had two arguments:
-- Function: public.cm_delete_card(integer, oid)
while in version 2.4 it has three arguments:

-- Function: public.cm_delete_card(integer, oid, text)
Obviously for PostgreSQL are different functions if the "signature" is different.
Check that you have applied the patch [webapp]/WEB-INF/patches/2.3.4-02.sql
CMDBuild Team

tecnoteca · February 2, 2018, 10:14am

In
CMDBuild 2.5 deleting a card works correctly (you can check it on the
online demo) so there is probably some difference in your database.
We suggest you install a 2.5 version from scratch and compare, between the two instances, the functions that cause the error.
CMDBuild Team

tecnoteca · February 6, 2018, 10:33am

"In CMDBuild 2.5 deleting a card works correctly"

Maybe in a new CMDDBuild 2.5 installation works correctly, but some different people have problems in update CMDBuild to release 2.4 or 2.5.

We've a 2.3.4 release and we're trying to update to 2.5 but we've also this problem:

org.postgresql.util.PSQLException: ERROR: function cm_delete_card() does not exist

while we deleting a card.

In cmdbuild.log there is this warn before cm_delete_card() ERROR:

WARN 2018-02-06 09:52:47 [org.springframework.jdbc.core.metadata.CallMetaDataProvider] Error while retrieving metadata for procedure columns: org.postgresql.util.PSQLException: Cannot cast to boolean: "2"
ERROR 2018-02-06 09:52:47 [jsonrpc ] A org.springframework.jdbc.BadSqlGrammarException occurred calling method class org.cmdbuild.servlets.json.management.ModCard.deleteCard: CallableStatementCallback; bad SQL grammar [{call cm_delete_card()}]; nested exception is org.postgresql.util.PSQLException: ERROR: function cm_delete_card() does not exist

could be a problem related to postgres driver?

Please give us an hint to solve this problem, anyway we can't update to current release

tecnoteca · February 6, 2018, 5:50pm

Can you attach or send us a copy of your 2.4.x database without data or with a few test data ?
CMDBuild Team

juan.fuentes · February 19, 2018, 12:47pm

Previously Tecnoteca wrote:

In CMDBuild 2.5 deleting a card works correctly (you can check it on the online demo) so there is probably some difference in your database.
We suggest you install a 2.5 version from scratch and compare, between the two instances, the functions that cause the error.
CMDBuild Team

We are experimenting the same issue

Sysadmin pals have confirmed me that the patch was successfully applied:

INFO 2018-02-09 11:35:53 [cmdbuild] applying patch 'DefaultPatchManager.DefaultPatch[version=2.3.4-02,description=Updates cm_delete_card function,category=<nul

l>,file=/xxxxxx/apache-tomcat-7xxx/webapps/cmdbuild/WEB-INF/patches/2.3.4-02.sql]'

And I have check DB and funcion is correctly defined:

create function cm_delete_card(cardid integer, tableid oid, username text) returns void

I have also downloaded the source code and check the exact line that makes the call to the dm_delete_card function:

public EntryDeleteCommand(final JdbcTemplate jdbcTemplate, final CMEntry entry) {
   this.call = new SimpleJdbcCall(jdbcTemplate) //
         .withProcedureName("cm_delete_card");
   this.in = new MapSqlParameterSource() //
         .addValue("cardid", entry.getId()) // --> Long (PG = integer)
         .addValue("tableid", entry.getType().getId()) // --> Long (PG = oid)
         .addValue("username", entry.getUser()); // --> String (PG = text)
}

I don't know about plpgsql... Maybe field types are not compatible??

cardId: integer -> Long

tableid: oid -> Long

username: text -> String

Thanks in advance. Solve this issue is quite important for us.... We are moving from SOAP to REST go get rid of all the XML/SOAP stuff. But this is blocking

Regards

tecnoteca · February 19, 2018, 1:22pm

Can you attach or send us a copy of your 2.4.x or 2.3.x database without data or with a few test data ?
CMDBuild Team

juan.fuentes · February 21, 2018, 12:51pm

Previously Tecnoteca wrote:

Can you attach or send us a copy of your 2.4.x or 2.3.x database without data or with a few test data ?
CMDBuild Team

Hi again. Can you give me an email address where i can send you db structure? I prefer not to upload to forum

Thanks

tecnoteca · February 21, 2018, 4:12pm

You can write to tecnoteca@tecnoteca.com

CMDBuild Team

tecnoteca · February 22, 2018, 11:15am

Hi,

which version of postgres.jar are you using? can you try with postgresql-9.4.1212.jar or older?

Thank you, regards,

CMDBuild Team

juan.fuentes · February 22, 2018, 12:59pm

Previously Tecnoteca wrote:

Hi,

which version of postgres.jar are you using? can you try with postgresql-9.4.1212.jar or older?

Thank you, regards,

CMDBuild Team

OK. After installing a clean CMDBuild + pgsql 9.6 in my windows box it seems to work the delete card feature ;) I'll ask sysadmins to upgrade test environment (they used a quite old pg version) and try again

Thanks :)

juan.fuentes · February 27, 2018, 12:12pm

Updating correct jar file in tomcat libs cm_delete_card() function works !!!

Thanks a lot :)

otaci.martins · February 27, 2018, 3:56pm

Previously Tecnoteca wrote:

Hi,
which version of postgres.jar are you using? can you try with postgresql-9.4.1212.jar or older?

Thank you, regards,

CMDBuild Team

I'm having a similar issue when deleting relations of a card (in relations TAB). The problem was solved when replaced driver postgresql-42.2.1.jar (the most recent) by postgresql-42.2.1.jar.

devanshu.chakraborty · April 15, 2018, 5:10am

Hi ,

You mentioned, "The problem was solved when replaced driver postgresql-42.2.1.jar (the most recent) by postgresql-42.2.1.jar". I think the names of both the jar files that you mentioned are same. Can you please re-confirm, which exact PostgresSQL Database version and the exact driver jar file version you are using?

Thanks

Devanshu

Previously Otaci wrote:

Previously Tecnoteca wrote:
Hi,
which version of postgres.jar are you using? can you try with postgresql-9.4.1212.jar or older?

Thank you, regards,

CMDBuild Team

I'm having a similar issue when deleting relations of a card (in relations TAB). The problem was solved when replaced driver postgresql-42.2.1.jar (the most recent) by postgresql-42.2.1.jar.

tecnoteca · April 16, 2018, 5:06pm

Sorry! My mistake.

The problem was solved when replaced driver postgresql-42.2.1.jar (the most recent) by postgresql-9.4.1212.

https://jdbc.postgresql.org/download/postgresql-9.4.1212.jar

Previously Devanshu Chakraborty wrote:

Hi ,

You mentioned, "The problem was solved when replaced driver postgresql-42.2.1.jar (the most recent) by postgresql-42.2.1.jar". I think the names of both the jar files that you mentioned are same. Can you please re-confirm, which exact PostgresSQL Database version and the exact driver jar file version you are using?

Thanks

Devanshu