[firebird-support] info

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

Attachments in you email is dropped. Try describe you problem with words.

[firebird-support] Info

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

As I answer you on your first time question - use this query to get next ID. 
Then supply this ID in your next INSERT query.

select
   gen_id(GEN_TABLENAME, 1)
from RDB$DATABASE

Where GEN_TABLENAME - generator for your A.ID column. In this case you not need 
to declare you column as IDENTITY. Instead create GENERATOR and BEFORE INSERT 
trigger like this:





CREATE GENERATOR GEN_TABLENAME;

SET TERM ^ ;

CREATE OR ALTER TRIGGER TABLENAME_BI FOR TABLENAME
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.ID is null) then new.ID = gen_id(GEN_TABLENAME, 1);
end
^

SET TERM ; ^

[firebird-support] Lock Conflict

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

How can I receive this error when inserting a new row and prior to committing?






May be, 2-nd query starts another transaction (in an unobvious way), while 1-st 
transaction still active.

Why do you not upgrade your Firebird and DB to 2.5 version?

[firebird-support] info

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

select
  gen_id(GEN_XXX, 1)
from RDB$DATABASE


Where GEN_XXX - generator for your A.ID column

[firebird-support] Select with calculated field problem

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

Try this:

select
 A.CODSUB,
 A.CODOB,
 A.CODSBB,
 A.CANT*B.CANT
from LSUBANS A
join X01 B on B.CODSBB = A.CODSUB

[firebird-support] Performance problem - input wanted

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

Try to determine slowest query:

- "SELECT ...  FROM  AFDELING A  INNER JOIN STAMDATA_PRG_EXT ..."
or
- "select v509Index from VAREFRVSTR_DETAIL ..."
or
- "Insert into VareFrvStr_Detail ..."


Problem can be with indexes - check whether all indexes exists in new DB (after 
restore).
Also check your INSERT triggers on VAREFRVSTR_DETAIL (if any).

[firebird-support] Rename a foreign key

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

> I never do a database structure change when users are connected and do not 
> allow them to use the system at this time. So in my case, it is to slow

It's almost always like that, when you altering DB structure - you must 
disconnect all users to avoid problems.

[firebird-support] Rename a foreign key

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

>   have you tried altering rdb$indices ?

No, I don't have such necessity.

Re: [firebird-support] Re: Rename a foreign key

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

>   Use "ALTER TABLE ... DROP CONSTRAINT ..." and then "ALTER TABLE ... ADD 
> CONSTRAINT ... FOREIGN KEY ..."
> 
> Thats no fast if the table is huge. This method I know. 

I think, "slow" here is not a problem - you do it only one time, and not on 
regular basis.

If you mean that this is slow when do it manually, because of large number of 
FK's - you can use some automation, e.g.:
- export all FK's to *.sql script (as full CREATE CONSTRAINT queries). Or, you 
can execute custom query on table RDB$RELATION_CONSTRAINTS, using 
RDB$RELATION_NAME as base for new FK name, to build "creator" script with new 
names.
- using text search/replace, create separate *.sql for DROP-ing originals
- manually edit names
- then 1) execute DROP-ing script 2) execute "creator" script with new names.

Also you can try update FK names in RDB$RELATION_CONSTRAINTS table (may be with 
automation), but i don't know if this enough for DB to work correctly after 
this.

[firebird-support] Rename a foreign key

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

Use "ALTER TABLE ... DROP CONSTRAINT ..." and then "ALTER TABLE ... ADD 
CONSTRAINT ... FOREIGN KEY ..."

Where you see such FK name as "RDB$FOREIGN60"? It created by you or some DB 
management tool? As I know, there is no automatically generated ("system") 
FK's. So, you must create them manually and assign meaningful name to it.

[firebird-support] Firebird 3.0 create database syntax

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

Here is CREATE DATABASE syntax: 
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-db.html
 



Try use single quotes:




create database 'localhost:milenio-central.fdb' user 'milenio' 
password 'secreto' page_size 16384 default character set utf8 collation 
unicode; 
















14 листопада 2017, 04:07:06, від "palevi pablo.l...@horus.es 
[firebird-support]" :

>   Hi,
> 
> In firebird 2.x you can specify some parameters when creating a 
> database, i.e.:
> 
>     create database "localhost:milenio-central.fdb" user "milenio" 
> password "secreto" page_size 16384 default character set utf8 collation 
> unicode;
> 
> However in fb 3.0 I get this error:
> 
>     Use CONNECT or CREATE DATABASE to specify a database
>     SQL> create database "localhost:milenio-central.fdb" user milenio 
> password "secreto" page_size 16384 default character set utf8 collation 
> unicode;
>     Statement failed, SQLSTATE = 42000
>     Dynamic SQL Error
>     -SQL error code = -104
>     -Token unknown - line 1, column 17
>     -"localhost:milenio-central.fdb"
> 
> if I strip out the "character set" and "collation" parameters, then the 
> order runs fine again, but ¿what are the allowed parameters for "create 
> database" in fb 3.0? ¿and what are the alternatives for unsupported ones?
> 
> Thanks!

[firebird-support] Re[2]: string right truncation error with LIKE predicate and UTF8 DB

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

> > This query fails:
> > select * from TABLE1 where FIELD1 like 'абв';
> 
> 
> The "bug" here is that the message for all string input errors is the
> same.  Transliteration of the input argument in the failing query
> would occur as in your third query above if the LIKE argument was
> valid.  IMHO, it should give an invalid DSQL error, not that generic
> string error.

I think, there is no DSQL error - LIKE predicate must correctly process
arguments without mask symbols, as he do this for 'abc' (or 'абв' when DB
default charset not UTF8). Not sure, but think that SQL standard not define
error for this case of LIKE.

> If you wanted to pass a query like that, you should use STARTING [WITH]:
> select * from TABLE1 where FIELD1 STARTING WITH 'абв';

Yes, I also came to this solution, more correct is just FIELD1 = 'абв'.
We call this 'spike'. There is cases where this is impossible or difficult:
- this is impossible, where used third party components without sources
(like EhLib for Delphi, where LIKE used in grid filter; we fix this in sources),
or used third party applications.
- requires additional processing, when we allow users of our client application
perform data searching with LIKE (user can enter search pattern with or without 
'%').

[firebird-support] string right truncation error with LIKE predicate and UTF8 DB

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

Hi, people!




I know, that same bugs was discussed early in bug tracker, although bug not 
fixed, I think they not the same.




I got an error executing SELECT query with LIKE predicate in case of UTF8 
database, 
non-UTF8 connection charset and national characters in LIKE argument without 
mask symbols '%' and '_'




Error is 

"arithmetic exception, numeric overflow, or string truncation
-string right truncation"




Reproduce step by step:




1) Create database with default character set = 'UTF8'
2) Connect to DB with ANSI character set, e.g. 'WIN1251'


isql -ch WIN1251

3) Execute SELECT query on any table with VARCHAR fields of any length
with LIKE predicate on VARCHAR field, that meet following criterias:
- argument not contain mask symbols '%' and '_'
- argument length greater then 1 symbol
- argument contain national symbols

4) Got an error, e.g. in ISQL:
"
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
"




EXAMPLE:




Start Command Prompt, CD to Firebird's 'bin' folder and execute:

chcp 1251

Change console window font to Lucida Console (to allow cp1251 characters in 
console)




Start ISQL:
isql -ch WIN1251


Execute following queries in ISQL (change paths and credentials appropriately):

create database 'c:\databases\bug.fdb' user 'SYSDBA' password 'masterkey'
default character set UTF8 collation UNICODE_CI_AI;




(to connect to existing DB use command
connect c:\databases\bug.fdb user SYSDBA password masterkey;

)


create table TABLE1 (
FIELD1 varchar(50)
);

insert into TABLE1 (FIELD1) values ('abcdef');
insert into TABLE1 (FIELD1) values ('123456');
insert into TABLE1 (FIELD1) values ('абвгде'); -- WIN1251 characters string 
commit;


This queries work fine:

select * from TABLE1 where FIELD1 like 'abc%';
select * from TABLE1 where FIELD1 like 'abc';
select * from TABLE1 where FIELD1 like 'абв%';





This query fails:

select * from TABLE1 where FIELD1 like 'абв';








In last 2 queries in LIKE argument used string with national characters (in 
this case - WIN1251)


===

Tested on FB 2.5.2 Win32, and FB 2.5.7 Win64 (Superservers)

Tested in ISQL and some other SQL tools with same conditions - bug reproduced.




Connection character sets other then WIN1251 - not tested

Server  platforms other then Windows - not tested

Database default collations other then  UNICODE_CI_AI - not tested