Dynamic SQL in DELETE statement causes Ebase to crash

[Segi]

I have a database resource with this DELETE statement:

Code: Select all

DELETE FROM PermissionAssignments &&WHERECLAUSE

When the user checks a checkbox, the change event builds the where clause

Code: Select all

if (tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.PermissionWhereClauseID.value != 1) {
     tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.WHERECLAUSE.value="WHERE UserID=&&ResolutionUserID AND PermissionID=&&ResolutionPermissionID";
} else {
     tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.WHERECLAUSE.value="WHERE UserID=&&ResolutionUserID";
}

I then call tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.deleteRow(); to delete the row and tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.updateTable(); to update the database.

The call to updateTable() causes this error

Code: Select all

Wrapped com.ebasetech.xi.exceptions.FormRuntimeException: unable to set column ResolutionUserID with type INTEGER from table column with type INTEGER, types incompatible? - Invalid parameter index 1. (ADMIN_INVALIDPERMISSIONS_REMOVEPERMS_CHANGE#17)

Why doesn't Ebase let me dynamically build the where clause and why am I getting an error substituting an INTEGER for an INTEGER. This is completely valid SQL and should work

[AJDulk]

It would be better to take the where clause out the table, use a standard field instead (instead of tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.WHERECLAUSE.value you would then be using fields.ADMIN_USERS_WITH_INVALID_PERMISSIONS_WHERECLAUSE.value).

What I think is happening is that you are setting the where clause at the row you are going to delete. The moment you delete the row, you delete the where clause. Since the where clause is deleted, you now get an error.

[t4nu]

Hi,
I usually put that (WHERECLAUSE) as a dynamic, and then map it to a fields. When the time to create the criteria, I just assign that criteria to that field.
So far I always can get what I want.

I have a database resource with this DELETE statement:

Code: Select all

DELETE FROM PermissionAssignments &&WHERECLAUSE

When the user checks a checkbox, the change event builds the where clause

Code: Select all

if (tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.PermissionWhereClauseID.value != 1) {
     tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.WHERECLAUSE.value="WHERE UserID=&&ResolutionUserID AND PermissionID=&&ResolutionPermissionID";
} else {
     tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.WHERECLAUSE.value="WHERE UserID=&&ResolutionUserID";
}

I then call tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.deleteRow(); to delete the row and tables.ADMIN_USERS_WITH_INVALID_PERMISSIONS.updateTable(); to update the database.

The call to updateTable() causes this error

Code: Select all

Wrapped com.ebasetech.xi.exceptions.FormRuntimeException: unable to set column ResolutionUserID with type INTEGER from table column with type INTEGER, types incompatible? - Invalid parameter index 1. (ADMIN_INVALIDPERMISSIONS_REMOVEPERMS_CHANGE#17)

Why doesn't Ebase let me dynamically build the where clause and why am I getting an error substituting an INTEGER for an INTEGER. This is completely valid SQL and should work