Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

since version 3. external UDF function named TRIM cannot be removed [CORE5513] #5782

Open
firebird-automations opened this issue Mar 31, 2017 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Konstantin Streletsky (streletsky)

I have updated my database from 2.5 to 3.0.1 and catch a problem when trying to remove TRIM function from UDFs.
I'm trying DROP EXTERNAL FUNCTION "TRIM" and get error
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, column 24.
"TRIM".

In firebird 2.5 I can remove it directly from rdb$functions, but in FB3 does not allow doing this.
It three any chance to remove this function using FB3, because in FB3 external functions must be granted and I can't have granted trim as well.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Not reproduced:

#⁠ ./isql -z employee
ISQL Version: LI-V3.0.1.32609 Firebird 3.0
Server version:
LI-V3.0.1.32609 Firebird 3.0
Database: employee, User: SYSDBA
SQL> DECLARE EXTERNAL FUNCTION "TRIM" cstring(256) returns int by value entry_point 'zz' module_name 'qq' ;
SQL> DROP EXTERNAL FUNCTION "TRIM";
SQL>

@firebird-automations
Copy link
Collaborator Author

Commented by: Konstantin Streletsky (streletsky)

on employee.fdb it works fine, but on my database something not good.
could be a reason, that i have sql dialect = 1 ?

C:\B52Soft\db\test2>isql.exe -z -user sysdba -password 7921 EMPLOYEE.FDB
ISQL Version: WI-V3.0.1.32609 Firebird 3.0
Server version:
WI-V3.0.1.32609 Firebird 3.0
WI-V3.0.1.32609 Firebird 3.0/XNet (SB)/P14
WI-V3.0.1.32609 Firebird 3.0/XNet (SB)/P14
Database: EMPLOYEE.FDB, User: SYSDBA
SQL> DECLARE EXTERNAL FUNCTION "TRIM" cstring(256) returns int by value entry_po
int 'zz' module_name 'qq' ;
SQL> DROP EXTERNAL FUNCTION "TRIM";
SQL> quit;

C:\B52Soft\db\test2>isql.exe -z -user sysdba -password 7921 B52.GDB
ISQL Version: WI-V3.0.1.32609 Firebird 3.0
Server version:
WI-V3.0.1.32609 Firebird 3.0
WI-V3.0.1.32609 Firebird 3.0/XNet (SB)/P14
WI-V3.0.1.32609 Firebird 3.0/XNet (SB)/P14
Database: B52.GDB, User: SYSDBA
SQL> DECLARE EXTERNAL FUNCTION "TRIM" cstring(256) returns int by value entry_po
int 'zz' module_name 'qq' ;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 27
-"TRIM"
SQL>

@firebird-automations
Copy link
Collaborator Author

Commented by: Konstantin Streletsky (streletsky)

I sort out this issue only by creating full sql dump of database and removing UDF declaration.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Database was in dialect 1 or 2?

@firebird-automations
Copy link
Collaborator Author

Commented by: Konstantin Streletsky (streletsky)

database with probem has dialect 1

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Konstantin,

so, could you change db dialect to 3, drop the function, and revert db dialect back to 1 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: Konstantin Streletsky (streletsky)

Vlad Khorsun,
Great, it works, TY

C:\Program Files\Firebird\Firebird_3_0>gfix -sql_dialect 3 -user sysdba -password masterkey c:\db\test.gdb

C:\Program Files\Firebird\Firebird_3_0>sql -user sysdba -password mastery c:\db\test.gdb

Database: c:\db\test.gdb, User: SYSDBA
SQL> show sql dialect;
Client SQL dialect is set to: 3 and database SQL dialect is: 3
SQL> DROP EXTERNAL FUNCTION "TRIM";
SQL> COMMIT;
SQL> quit;

C:\Program Files\Firebird\Firebird_3_0>gfix -sql_dialect 1 -user sysdba -password masterkey c:\db\test.gdb

C:\Program Files\Firebird\Firebird_3_0>isql -user sysdba -password masterkey c:\db\test.gdb
Database: c:\db\test.gdb, User: SYSDBA
SQL> show sql dialect;
Client SQL dialect is set to: 1 and database SQL dialect is: 1
SQL> show functions;
Global functions

Function Name Invalid Dependency, Type
================================= ======= =====================================
ABS
ADDMONTH
ASDATE
ASFLOAT
ASINT
ASREAL
ASSEMBLYDATE
ASSTRING
BLOBTOFILE
BLOBTOSTR
CALCEXPR
CHARREPL
CHECKKODENTRY
CHECKKODENTRYEX
CHR
DATEDAY
DATEMONTH
DATETOSTR
DATEYEAR
DAYOFWEEK
DAYS
EAN13
EAN8
FORMATDATE
GENPSW
ISBIT
LASTDAYOFMONTH
LOCASE
LOCATE
MAX_DBL
MIN_DBL
NUMTOSTR
ODDDATE
PADL
ROUNDKOEFF
STRFORMAT
STRLEN
STRTOFILE
SUBSTR
TESTEXPR
TIMETONUM
TIMETOSTR
TRUNCATE
UPCASE
SQL> quit;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant