ODP: [firebird-support] Trigger created as inactive
Hi First change group from yahoo to google group. And without whole reproducible script no one can help i suppose Regards, Karol Bieniaszewski
[firebird-support] Next attachment ID
Hi How can i read Next Attachement ID? I know that i can read it by gstat -h but how to read it from connection? Is there something simple like for OAT – read from MON$DATABASE or API isc_transaction_info? Regards, Karol Bieniaszewski
ODP: [firebird-support] IMPORTANT MESSGAGE: This group is moving
Hi I have joined but i cannot change name to show instead my email name. I have followed hint on the group to go to settings – but to change this settings i must login. I do not have google accout to login. How to change it? Regards, Karol Bieniaszewski
ODP: ODP: ODP: ODP: [firebird-support] SQL slower after N executions?
You can try also pseudo inner join (left join with where clause) Instead of T1 INNER JOIN T2 ON T1.ID1=T2.ID2 change it to T1 LEFT JOIN T2 ON T1.ID1=T2.ID2 WHERE T2. ID2 IS NOT NULL regards, Karol Bieniaszewski
ODP: [firebird-support] Timestamp in different regions
I do not suppose that this is Firebird message. This looks like client program message. regards, Karol Bieniaszewski
ODP: ODP: ODP: [firebird-support] SQL slower after N executions?
I suppose you have two different queries – one with where clause and one without on one of the tables involved in the query. Are you sure that the queries are the same? But also it is quite usual that after new data changes the plan is about to change. Is this true for your case that some table got more records after fill then previously second table? Especially this one?: You can try also modify your query to have static plan (some +0) or update index statistics after some count of operations. Regards, Karol Bieniaszewski [Non-text portions of this message have been removed]
ODP: ODP: [firebird-support] SQL slower after N executions?
Simply run query with join two tables MON$ATATACHEMENTS and MON$TRANSACTIONS – you can also be interested with joining with MON$STATEMENTS And then you can see which attachement consume this transaction (you can retrive IP port and proces ID (PID) Pozdrawiam, Karol Bieniaszewski
ODP: [firebird-support] SQL slower after N executions?
Hi There are many possibilities without access i can only hint you: Look at MON$Tranasctions maybe you have active one which stop garbage collecion. Look also at sort buffer setting if firebird.conf Look at settings about buffers in database itself (gfix -h show you value). Look also at automatic sweep settings (also gfix -h show you value). Update Firebird to most recent version official is 3.0.5 but i use most recent snapshot without problems. More can be tell after some details provided. regards, Karol Bieniaszewski
ODP: [firebird-support] Re: Explicit Login with Windows Domain username
Hi Ask self – do you have Windows Domain supported 2 passwords? No. You can have 2 different autentication methods. On Firebird3 you have autentication plugins. You can implement one self or use apropiate design on your rest service. Better to not allow all users to have same password is to redesign you REST service. You should have login method on your rest service and user should recive token. And he/she should comunicate with this token in new requestes to other rest methods. I can only hint to also use hmac with this token design to do not allow someone to recive token only and override user privileges. Regards, Karol Bieniaszewski
ODP: [firebird-support] Re: is it possible to drop all indexes, except those related to PKand FK?
Hi
If you join with contraints you have better control.
An personally i prefere easier way, like simple select.
If you really need to delete all instead of PK and FK you should not exclude
uniques as you ommit custom unique indexes.
SELECT
'DROP INDEX ' || TRIM(I.RDB$INDEX_NAME) || ';', RC.RDB$CONSTRAINT_TYPE
FROM
RDB$INDICES I
LEFT JOIN RDB$RELATION_CONSTRAINTS RC ON RC.RDB$CONSTRAINT_NAME =
I.RDB$INDEX_NAME
WHERE
I.RDB$SYSTEM_FLAG <> 1
AND RC.RDB$CONSTRAINT_TYPE NOT IN ('PRIMARY KEY', 'FOREIGN KEY')
–eventually add also ‘UNIQUE’ here if you really need
regards,
Karol Bieniaszewski
ODP: [firebird-support] Statement freezes firebird
Hi „select first(8) ID from TBL_TEST where ID not in (select first(2) ID from TBL_TEST order by ID desc) order by ID desc” few things: Just hint but „first” is not function First(8) is same as First 8 You should avoid NOT IN queries and use EXISTS/NOT EXISTS instead. But if you really need it then such subquery should be well indexed. >> „Is there a way to force limiting the result set before sorting it?” Create descending index on ID column, than this subquery will go throught index without sorting. It will be instant i suppose Regards, Karol Bieniaszewski
ODP: [firebird-support] Round the Time
Now possibility: Exctract + cast Substring + cast But you can vote on something more usefull: http://tracker.firebirdsql.org/browse/CORE-5623 regards, Karol Bieniaszewski
ODP: [firebird-support] Re: A recommendation (database migration)
>> That’s part of the issue - I’m a Delphi guy and not sure I could get it >> cross platform. Delphi is crossplatform. It suport Windows, macOS, iOS, Android and now Linux from one codebase. Regards, Karol Bieniaszewski
ODP: [firebird-support] Insert into Select
Hi Whay not two separate "update or insert” for parent and child? You can use „merge” for massive „"update or insert” on single table. Regards, Karol Bieniaszewski
ODP: [firebird-support] What key word specifies a search for an entire word?
>>Not sure what pattern is. Pattern replace unknown leter(s) sumbols are „%” multiple letters, „_” single letter e.g. ‘Ne%flix’ will find Neflix, Netflix, Netflix …. >>I prefer the word LIKE. Is easier to avoid errors vs. using '='. Are there >>advantages to using '='? If you use like with param Firebird cannot use index on field firstName or e.g. expression index Upper(firstName) As your param have unknown value. It can be ‘Netflix’ or ‘%Netflix’, or whatever But if you use „=” it simply can use index if such exists.. Regards, Karol Bieniaszewski.
ODP: [firebird-support] What key word specifies a search for an entire word?
Do you use pattern or simple string? If it is simple string then better is using „=” instead of like. "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) = lower( @p0 )" Regards, Karol Bieniaszewski
ODP: [firebird-support] Grant execute on function.
Is „users1” a user name or role name? If role, then check if user have this role granted and it connect using the role. Regards, Karol Bieniaszewski
ODP: [firebird-support] ISC ERROR CODE:335544721
>>I havent' Interbase installed on this computer, only Firebird... Are you sure? Have you checked gds32.dll in Windows/System32 and Windows/SysWoW64 if it is Firebird? Regards, Karol Bieniaszewski
ODP: [firebird-support] How to index this table
Hi Your problem is that you have (X or X) and (Y or Y) Firebird cannot use composite index here. Maybe it can somehow make something like (X1 and Y1) or (X1 and Y2) or … or (X1 and YN) or (X2 and Y1) or (X2 and Y2) …. (XN and YN) But it will be never optimal. Create separate indexes for magasin and caisse – then Firebird can use BITMAP OR + BITMAP AND Another optimisation will be instead of multiple OR put all values into temporary table and then join with it. Any other optimisations depend on value propagation in particular fields involved in the where clause. Regards, Karol Bieniaszewski
ODP: AW: [firebird-support] Converting with parameters stored in variables?
>>Trunc instead of pow? I only mean that you do not need to operate on strings to separate number. A = 123.45 – numeric not a double A1 = TRUNC(A) A2 = (A-A1)*POWER(10,3) Then A1 = 123 A2 = 450 SELECT LPAD(TRUNC(X.A), 10, '0') || '.' || LPAD((X.A-TRUNC(X.A))*X.B, 3, '0'), FROM (SELECT 100.12 AS A, POWER(10,3) AS B FROM RDB$DATABASE) X Regards, Karol Bieniaszewski
ODP: [firebird-support] Converting with parameters stored in variables?
Hi This is called padding. Simple example (but not what you want you must padd also decimal point) SELECT LPAD(CAST(100 AS NUMERIC(10,3)), 14, '0') FROM RDB$DATABASE But you must tell us what is your real problem, as you need padding for what? Pozdrawiam, Karol Bieniaszewski
ODP: [firebird-support] memory bug?
Hi >>we are using firebird 3.0.1 Superserver 32 Bit on a Windows Server 2008 32 >>Bit. First, why 3.0.1 it contain many bugs fixed already. Use official 3.0.4 or snapshot version. >>Currently we have all 60 days the problem, that our Applications works not >>well, the firebird-process uses almost 2GB of RAM and this is seemingly the >>limit of an 32 Bit process. In this case, I cannot connect >>with my tool to >>see the monitoring tables, no new connection can be established. What is you buffers settings (in firebird.conf, or database.conf or in db itself). And maybe its time to upgrade hardware as current one looks not enought for your needs? regards, Karol Bieniaszewski
ODP: [firebird-support] Missing system triggers for foreign key constraints
Hi Really 2.5.2? It is from November 06, 2012. You miss few years or bugfixing and improvement. Current version of 2.5 serises is 2.5.9. Regards, Karol Bieniaszewski
ODP: [firebird-support] Unique Constraints and NULLs
Hi, If you do not need referential constraint based on this unique only you need to have uniques. Then you can simply create expression index, e.g.: CREATE UNIQUE INDEX IXAE_blah_C1C2 ON blach COMPYTED BY(COL1 || ‘_‘ || COALESCE(COL2)); Regards, Karol Bieniaszewski
ODP: [firebird-support] Re: Objects cached in FB memory
Hi, I do not suppose it is possible. But why do you need that info? regards, Karol Bieniaszewski
ODP: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9
Hi Do you use FB3 Super Server or Classic? What value of PagePuffers and PageSize? I see big reads but also big indexes reads but fetches are lower. There is also one plan difference MOVI INDEX (MOVI_PROC) vs MOVI INDEX (MOVI_PROC, MOVI_FECH) But i am intersted how this looks like at second run with big cache Regards, Karol Bieniaszewski
ODP: [firebird-support] Start/Stopping service
>> "Error occured during OpenService" >>>The specified service doens't exists as installed service (sorry I had to >>>translate from french) Run it as Windows admin Regards, Karol Bieniaszewski
ODP: [firebird-support] Install a DB Firebird on a web site
Hi, As your description is not precise i post few possibilities. 1. Simply run regular Firebird and connect to network by e.g. RDP over VPN or something like that. 2. Implement application server e.g. by using DataSnap and use it in new places from e.g. smartphone 3. If you have or need web server, and this web server is only one accesing your db, then you can simply use embeded Firebird server on any hosting. I use it on cheap Windows hosting and connect many clients by web page. This really depend on your situation, plans and needs Regards, Karol Bieniaszewski
ODP: [firebird-support] Question about index use
Are you sure that this query looks like this? For me such query use indexes in both cases left and inner. Show full example with plans Regards, Karol Bieniaszewski
ODP: ODP: [firebird-support] Turn on WireCompression on Firebird 3
>> where have you run and got 0.078s? Tested in flamerobin. Now tested in ISQL – result: 0.003 sec NAME === Amsterdam Current memory = 37027264 Delta memory = 254320 Max memory = 37109808 Elapsed time= 0.003 sec Buffers = 2048 Reads = 21 Writes = 0 Fetches = 255 Second run NAME === Amsterdam Current memory = 37027264 Delta memory = 0 Max memory = 37109808 Elapsed time= 0.002 sec Buffers = 2048 Reads = 0 Writes = 0 Fetches = 9 Regards, Karol Bieniaszewski
ODP: ODP: [firebird-support] Silly selection problem ...
>>Can you explain how? I do not study your topic much and i can be wrong here. But maybe you got the idea about what i am thinking about. SELECT (SELECT PRE.ITEM_CONTENT_ID FROM FISHEYE_GALLERY_IMAGE_MAP PRE WHERE PRE.GALLERY_CONTENT_ID = :parent_id AND PRE.ITEM_POSITION < FGIM.ITEM_POSITION ORDER BY PRE.ITEM_POSITION DESC ROWS 1) AS PRE_ITEM_CONTENT_ID , (SELECT POST.ITEM_CONTENT_ID FROM FISHEYE_GALLERY_IMAGE_MAP POST WHERE POST.GALLERY_CONTENT_ID = :parent_id AND POST.ITEM_POSITION > FGIM.ITEM_POSITION ORDER BY POST.ITEM_POSITION ASC ROWS 1) AS POST_ITEM_CONTENT_ID FROM FISHEYE_GALLERY_IMAGE_MAP FGIM WHERE FGIM.ITEM_CONTENT_ID = :content_id Regards, Karol Bieniaszewski
ODP: [firebird-support] Turn on WireCompression on Firebird 3
Hi. This is not Firebird issue. You must look into something between. For me first run is: Total execution time: 0.078s Second buffered is: Total execution time: 0.000s 3 seconds is really long time. There must be something involved between. 3 seconds on even 1RPM HDD is also long.. Regards, Karol Bieniaszewski
ODP: [firebird-support] Get client-IP-Adress
There is nothing built in. Think about. You can connect to client 1 through RDP and on the client 1 run another RDP to client 2 maschine, and from this maschine another RDP to client 3…. Where you do connection than this is the client. As say by others you must do outside logic self, and store info in some context variable or something like this. Regards, Karol Bieniaszewski
ODP: [firebird-support] Silly selection problem ...
You can do this without window function also in FB<3, but less efective. Do simple 2 subselects in your select. Regards, Karol Bieniaszewski
ODP: ODP: [firebird-support] Re: Strange behavior on very large table
I see that you have only 1024 page buffers. Do you use SuperServer or Classic? If Superserver then increase it. Regards, Karol Bieniaszewski
ODP: [firebird-support] Re: Strange behavior on very large table
You have 91345 versions but Max versions is 1. Then i do not think that performance problem is here. You have quite big fill 94%, that there is a chence that new page must be allocated – but here still you have free slots. You have gap between oldest active nad next transaction. It is not big but you must look if this do not cause problem somewhere else Which affect overall performance. No more to say. I do not know anything about indexes, referencess and queries involved in this system. Regards, Karol Bieniaszewski
ODP: [firebird-support] Strange behavior on very large table
Hi. Run gstat and look at info about this table. Look also at page size of your database. It can be small in 1.5 version, if yes, change it to bigger value especially 16K. PS. 900,000 rows is quite small table also for Firebird 1.5 regards, Karol Bieniaszewski
ODP: ODP: ODP: [firebird-support] Stored procedure stopsfunctioningcorrectlyafter restore under Firebird 3
> Then maybe some switch to restore process should be added? >>You can always post a feature request in the tracker. I will do, thanks > Now this is nightmare for the end user. >>I'd hardly call it a nightmare, and the alternative to use sources is >>not much better: it just has different caveats. Using sources can only be treated as can compile under new version or not. But as you pointed that someone (developer) can hide sources than i suppose that adding restore switch or mayby implement some crypto possibility for sources will be much better. Someone can crypt sources and then during restore with switch recompile he must provide some crypto key. Regards, Karol Bieniaszewski
ODP: [firebird-support] Firebird3 files - Updated
>>set FIREBIRD_LOCK env variable at system level and reboot. >>Be careful as wrong usage could damage database. Thank you Vlad. Can you explain more about risk? Regards, Karol Bieniaszewski
[firebird-support] Firebird3 files - Updated
Hi.
I have FB3 installed on drive P: on Windows 10
P:\Firebird3\firebird.exe -s FB3_3305
Firebird.conf
TempDirectories = P:\temp
System var
FIREBIRD_TMP = P:\temp
But i see that Firebird create some files on C: drive
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb12_monitor_e71801005000
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb12_monitor_e71802007b01
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836FileC:\ProgramData\firebird\fb12_trace
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb_lock_e71801005000
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb_lock_e71802007b01
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb_trace.{4AFAB562-01BE-4865-7285-C2F2DFAD0AC4}
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb_trace.{4AFAB562-01BE-4865-7285-C2F2DFAD0AC4}
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb_trace.{4AFAB562-01BE-4865-7285-C2F2DFAD0AC4}.000
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836File
C:\ProgramData\firebird\fb_trace.{4AFAB562-01BE-4865-7285-C2F2DFAD0AC4}.000
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836FileC:\ProgramData\firebird\fb_trace_m03a5e
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836FileC:\ProgramData\firebird\fb_user_mapping
How to prevent this action?
Regards,
Karol Bieniaszewski
[Non-text portions of this message have been removed].
[firebird-support] Firebird3 files
Hi. I have FB3 installed on drive P: on Windows 10 P:\Firebird3\firebird.exe -s FB3_3305 Firebird.conf TempDirectories = P:\temp System var FIREBIRD_TMP = P:\temp But i see that Firebird create some files on C: drive How to prevent this action? Regards, Karol Bieniaszewski [Non-text portions of this message have been removed]
ODP: ODP: [firebird-support] Stored procedure stops functioningcorrectlyafter restore under Firebird 3
Good points Mark. Then maybe some switch to restore process should be added? Now this is nightmare for the end user. regards, Karol Bieniaszewski
ODP: [firebird-support] Query optimization on FB3
Hi. There are 2 common ways 1. Create global temporary table Do INSERT INTO T(DSTART, DEND) SELECT … and then do simple join with this table 2. Use derived table e.g. SELECT DATENLOGGING.* … FROM (SELECT D.DSTART, D.DEND FROM TABLED) X LEFT JOIN DATENLOGGING ON DATENLOGGING.DATUMZEIT>=X. DSTART AND DATENLOGGING.DATUMZEIT<=X.DEND WHERE DATENLOGGING.KEY IS NOT NULL /* do hidden inner join by providing some not nullable field check */ Regards, Karol Bieniaszewski
ODP: [firebird-support] Stored procedure stops functioning correctlyafter restore under Firebird 3
>>Recompiling stored >>procedures, triggers, etc is always a good thing to do when upgrading. >>Mark Why it is not done automatically on restore if there is source present? Pozdrawiam, Karol Bieniaszewski
ODP: [firebird-support] Built in RegEx Capability?
Hi.
For your particular case you do not need regex at all. Simple substring +
position.
Example:
Select
D.NAZWA
, SUBSTRING(D.NAZWA FROM POSITION('V', D.NAZWA)+1 FOR POSITION('p', D.NAZWA)-3)
AS V
, SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)+1 FOR POSITION(' ',
SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)))-2) AS P
from
(SELECT 'V12 p234 The state of the nation.pdf' AS NAZWA FROM RDB$DATABASE) D
Pozdrawiam,
Karol Bieniaszewski
ODP: [firebird-support] Question about delay in fetch operation
Yes, this wideo is usefull and show where the problem is. 1. Something is not satisfy by ypour filter and go throught 1M rows when you have this one additional record vs 21K. 2. First run go from disk – 59K reads from disk vs 2.5K. Look about your where clause and also if your inner join catch whole index segments (explained plan can help here). Also increase cache of this database. Regards, Karol Bieniaszewski
ODP: ODP: [firebird-support] Re: Introducing Firebird Butler
>>You completely misunderstood the announcement. The Firebird Butler is a >>thing that we develop. I am really interested if i am only one And because of this i am talking about any example as a first steep. >> Steam-like deployment platform for Butler services provided by Firebird Ok, than is this as a distribution platform for „small” services or what? >> So far, there is no single line of code available to public that you could use Do you think that this was too early announced especially on support group then? Normally i am real enthusiast of „new” ideas especially in products which i use, but without exaple hmm... >> But either REST or Widnows services are NOT good enough for IBPhoenix >> purposes Can you extend this sentence, especially why? What is in this planning „Butler” what is better? This description can bring more light on the purpose. regards, Karol Bieniaszewski
ODP: [firebird-support] Re: Introducing Firebird Butler
Thank you to be involved into discussion. But i am still oposite. Example do not required any unique description. Example is not less/more than only shortcut to description. It is introduction to description. Description can talk about example or will be totally unreleated. But example is comparable to sentence „one image is better than thousand of words”. If i understand correctly this „Introducing Firebird Butler”. I better see this not as something „blown” and based on something external to Firebird. If i can propose somthing in this matter, then maybe Firebird engine should have some additional listen port. On that port firebird listen on incomming traffic in simple REST messaging like JSON. On Firebird side we can declare some specific trigger which can consume such JSON or whatever. But if we think about services build for Firebird than, think how simple is to write e.g. Windows Service in Delphi. How easy is to write and PROTECT! REST service in Delphi. Do you suppose that someone will be build such service in this „Introducing Firebird Butler”? Are you sure that someone will be reading virtual documentation without seeing simple example at start? Without knowing anything about benefits compared to simple Windows Service or REST service? If you or someone can show me (and maybe others are also interested) any real benefit, example, than i am more than interested in this. And i am not only interested in using it, but i then can offer time to implement something. But without real introduction i will stay with REST/Windows services based on Delphi. Thay work for me for e.g. IOT with big traffic and any problems. regards, Karol Bieniaszewski
ODP: [firebird-support] Firebird startup issue
Hi. I had never such issue. But mayby setting FB service autostart to „delayed” will help you. regards, Karol Bieniaszewski
ODP: [firebird-support] Introducing Firebird Butler
Hi. I read many pages and still do not know about what it is all about. I am guy who like differenet learning pattern. Consider 2 patterns. I) 1. Description 2. Example II) 1. Example 2. Description If you thing about then you will see that only second pattern is acceptable.. Example first then multiple pages of description. regards, Karol Bieniaszewski
ODP: [firebird-support] Firebird database executing DML queries very slowly.
Hi Is this from problematic time with connected clients? If yes than i do no see problems. You must provide more details about issue. What exact comand is slow? What are the query plan and query sats? Did you tested HDD isues? … Regards, Karol Bieniaszewski
ODP: [firebird-support] Question about delay in fetch operation
Hi. Look at gstat output You probably have very long version this particular record Regards, Karol Bieniaszewski
ODP: [firebird-support] Firebird database executing DML queries very slowly.
Hi. When you gain slowdows Run gstat -h and put here results. Problem probably is because of some long running transaction. Regards, Karol Bieniaszewski
ODP: [firebird-support] How to insert characters for barcode code 128to table via code?
Look also at hex literals https://firebirdsql.org/refdocs/langrefupd25-hexbinstrings.html Pozdrawiam, Karol Bieniaszewski
ODP: [firebird-support] Re: very slow firebird embedded connection on a web application
>> May I ask why you are using the embedded version of Firebird for a web >> application? I suppose he use shared hosting and have not possibility to run full server.. I do this self many times. Regards, Karol Bieniaszewski
ODP: [firebird-support] udf dll
I suppose you do some wrong things like: 1. Wrong calling convention – should by „cdecl” not „stdcall” 2. You allocate strings locally and return it to the engine but you should allocate it by „ib_util_malloc”. 3. Your declaration of udf is wrong. 4. You raise some exceptions inside your udf ….. regards, Karol Bieniaszewski
ODP: ODP: [firebird-support] substring similar - "Invalid SIMILARTOpattern"
Thank you once again. Now it is much clear that sql standard have more functions and much useful. I will wait for future improvements in Firebird. I stay longer with udfs for now. Regards, Karol Bieniaszewski
ODP: [firebird-support] substring similar - "Invalid SIMILAR TOpattern"
Thank you very much Mark for detailed info but i have more questions.
I am really curious.
Is this sql standard concept that i must do this in this crap way?
SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' escape
'#') FROM RDB$DATABASE
Also strange that i must consume whole string by reg expression not only part
of it.
This can be as an option but as default it is strange for me.
Why not simply do:
SELECT substring('ab11c12bcd' similar '[0-9]+' itemNumber 1) FROM RDB$DATABASE
Will simply return „11”
SELECT substring('ab11c22bcd' similar '[0-9]+' itemNumber 2) FROM RDB$DATABASE
Will simply return 22
Now i have 2 udf like this:
REG_MATCH
REG_MATCH_COUNT
and i supposed that i can replace it with built in one, but i see that this is
really terrible.
Above udfs i can use in this way
SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 1) FROM RDB$DATABASE
Return 11
SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 2) FROM RDB$DATABASE
Return 22
SELECT REG_MATCH_COUNT(‘ab11c22bcd’, ‘[0-9]+’) FROM RDB$DATABASE
Return 2
I can use it in the where clause:
SELECT * FROM MY_TABLE T WHERE REG_MATCH_COUNT(T.FIELD, ‘[0-9]+’)>2
Or
SELECT * FROM MY_TABLE T WHERE REG_MATCH(T.FIELD, ‘[0-9]+’, 1)=’11’
regards,
Karol Bieniaszewski
ODP: ODP: [firebird-support] Converting dates and the ISO-8601 format
>> Yes but in the scenario you describe - all computers are on the same network. No, you can have web server on e.g. shared hosting and you can connect to it from every place. You can show web page on your phone, tablet … Regards, Karol Bieniaszewski
ODP: [firebird-support] Converting dates and the ISO-8601 format
>>I trying to find a web host that will run a firebird server is very hard Why? Do you hear about embeded Firebird? I use FB embeded on the hosting environment without the problem Regards, Karol Bieniaszewski
ODP: [firebird-support] update or insert inside a stored procedure
As Mark say „Please provide a reproducible example.” >> also like i said if i run this same query as a standalone outside the SP it >> run fine .. Single query is not the answer because in stored procedure you have loop with multiple records regards, Karol Bieniaszewski
ODP: [firebird-support] Apparently timeout on queries
Also, as you use superclassic you have separate cache per connection. It can be also problem here. Test on Firebird3 „SuperServer” and you will see if the problem is with resources caching problem. Regards, Karol Bieniaszewski
ODP: ODP: [firebird-support] trigger not firing?
>>The table "sto_depositos" (it means warehouses) is populated entering all the >>warehouses manually. >>It barely changes. Then when it is changing you can have situation like this? Transaction 1 start Transaction 2 start Tranaction 1 change sto_depositos Transaction 2 manipulate data and fire that trigger, which do not see modification from Transaction 1 as it is not commited yet. Transaction 2 commit; Transaction 1 commit; As you can see, you can loose records… I do not know if this is in your case.. Regards, Karol Bieniaszewski
ODP: [firebird-support] trigger not firing?
It depend how you table sto_depositos is populated and when you set activo=1? Pozdrawiam, Karol Bieniaszewski Od: shg_siste...@yahoo.com.ar [firebird-support] Wysłano: wtorek, 25 września 2018 16:49 Temat: [firebird-support] trigger not firing? Hello! I use FB 2.5. I know that this its almos imposible, but just in case I ask. Is there any chance that a trigger is not firing at all? I have this very simple trigger to create one record for each warehouse in the "stock" table. But I've found that quite a lot of articles (in the stock table) doesn't have its records in the "sto_cantidades" table. It is possible that some kind of corruption causes that? or should I chek my entire code and see if that records are somehow being deleted? thanks!! CREATE OR ALTER trigger stock_ai5 for stock active after insert position 5 AS declare variable loc_id fk_id; begin for select id from sto_depositos where activo = 1 into :loc_id do begin insert into sto_cantidades (id_stock, id_sto_depositos, cantidad) values (new.id, :loc_id, 0); end end [Non-text portions of this message have been removed]
ODP: [firebird-support] Question about multithread: readonlyquery ina single transaction
Thank you very much for confirmation Performance is not always vital. Flexibility many times have bigger priority. Regards, Karol Bieniaszewski Od: Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] Wysłano: środa, 19 września 2018 20:13 Temat: Re: ODP: [firebird-support] Question about multithread: readonlyquery ina single transaction 19.09.2018 13:26, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] wrote: > Is something like this safe? Yes. But it has no performance difference from the case when everything happen in single thread. -- WBR, SD.
ODP: [firebird-support] Question about multithread: readonly query ina single transaction
This are great news! Does it mean that if i have e.g. 2 threads And 1 thread do select 300 records and leave other 700 for next packet fetch And 2 thread do select 300 records from different query and leave e.g. 900 records for future fetch. And now thread 1 continue fetch and get another packet 300 records … Is something like this safe? If yes, this are great news Regards, Karol Bieniaszewski Od: Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] Wysłano: środa, 19 września 2018 11:18 Temat: Re: [firebird-support] Question about multithread: readonly query ina single transaction 18.09.2018 12:33, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > is this true? I read many times that this is not safe. Starting from Firebird 2.5 (at least) every API call uses YEntry guard that lock entryMutext in corresponding attachment. You can look into jrd/why.cpp for further details. -- WBR, SD.
ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
Hi, 1. Why not single Firebird database with all tables? 2. Why not recent Firebird which is FB3 not FB2.5? Pozdrawiam, Karol Bieniaszewski
ODP: [firebird-support] Prevent overlaping dates inconcurentenvironment
Hi, <
ODP: [firebird-support] Prevent overlaping dates in concurentenvironment
Hi,
quite interesting solution wihich minimize possibility of overlapping but do
not eliminate it.
Consider first sample about two transactions
What is the difference between normal two transactions and two autonomous
transactions?
Autonomous transaction 1 start
Autonomus transaction 2 start
Autonomous transaction 1 insert record
Autonomous transaction 2 insert record
Autonomous transaction 1 commit
Autonomous transaction 2 commit
I am wrong?
Pozdrawiam,
Karol Bieniaszewski
Od: Omacht András aoma...@mve.hu [firebird-support]
Wysłano: środa, 12 września 2018 21:23
Do: firebird-support@yahoogroups.com
Temat: RE: [firebird-support] Prevent overlaping dates in concurentenvironment
Hi All!
Let’s see an another solution.
!!! This won’t work if you are using snapshot transactions. !!!
(If you need snapshot you have to solve the rollback problem with an another
solution.)
CREATE EXCEPTION EXCEPTION_EX 'Somewhere someting went terrible wrong…';
Table for checking the collisions:
CREATE TABLE MEETINGS_UQ (
ROOM INTEGER NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL,
TR_NO INTEGER NOT NULL
);
ALTER TABLE MEETINGS_UQ ADD CONSTRAINT MEETINGS_UQ_PK PRIMARY KEY (ROOM,
DATE_FROM, DATE_TO);
CREATE DESCENDING INDEX MEETINGS_UQ_I1 ON MEETINGS_UQ (TR_NO);
CREATE OR ALTER TRIGGER MEETINGS_UQ_BI FOR MEETINGS_UQ
ACTIVE BEFORE INSERT POSITION 0
as
begin
-- Paste here the very sophisticated business logic
if (exists (select 1 from meetings_uq muq where muq.room = new.room)) then
begin
exception exception_ex 'Room is full!';
end
end
Table for inserted data:
CREATE TABLE MEETINGS (
ROOM INTEGER NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable tr_no integer;
begin
tr_no = current_transaction;
in autonomous transaction do
begin
insert into MEETINGS_UQ (ROOM, DATE_FROM, DATE_TO, TR_NO)
values (new.room, new.date_from, new.date_to, :tr_no);
end
end
In case of rollback:
CREATE OR ALTER TRIGGER DATABASE_ON_TR_ROLLBACK
ACTIVE ON TRANSACTION ROLLBACK POSITION 1
as
declare variable tr_no integer;
begin
tr_no = current_transaction;
in autonomous transaction do
begin
delete from meetings_uq muq where muq.tr_no = :tr_no;
end
end
Test1:
isql1:
insert into meetings(room, date_from, date_to) values (1, 'TODAY', 'TODAY');
isql2:
insert into meetings(room, date_from, date_to) values (2, 'TODAY', 'TODAY');
isql3:
insert into meetings(room, date_from, date_to) values (1, 'TODAY', 'TODAY');
GL_EXCEPTION_EX.
Room is full!.
At trigger 'MEETINGS_UQ_BI' line: 8, col: 7
At trigger 'MEETINGS_BI' line: 9, col: 7.
Test2:
isql1:
insert into meetings(room, date_from, date_to) values (3, 'TODAY', 'TODAY');
rollback;
isql2:
insert into meetings(room, date_from, date_to) values (3, 'TODAY', 'TODAY');
commit;
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 7:50 PM
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Prevent overlaping dates in concurent
environment
Hi,
Solution is good only for dates and only in small range but considering date
with time or numbers you see that solution is realy limited.
As Tomasz say will be good to see „systematic solution”
And to check overlaping your sample is quite ok but is ineficient
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|R1---|
|--R2--|
Better is check when dates do not overlap and do negation – only 2
possibilities
NOT (DATE_TO1mailto:firebird-supp...@yahoogroups.com]
Sent: Wednesday, 12 September, 2018 14:41
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurent
environment
Hi Tomasz!
Tested on 2.5.8, dialect 1:
CREATE TABLE RESERVED_DATE (
RES_DATE DATE NOT NULL
);
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
(RES_DATE);
CREATE TABLE MEETINGS (
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable curr_date date;
begin
curr_date = new.date_from;
while (curr_date <= new.date_to) do
begin
insert into reserved_date (res_date) values (:curr_date);
curr_date = dateadd(1 day to curr_date);
end
end
run on first transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.01', '2018.09.10')
run parallel on secound transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.08', '2018.09.15')
violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
"RESERVED_DATE".
Problematic key value is ("RES_DATE" = '8-SEP-2018').
At trigger 'MEETINGS_BI' line: 9, col: 7.
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 1:59 PM
To:
ODP: [firebird-support] Re: Identify wirecompression
Yes, it help me. I can obtain this info on client side And i can store this info into separate table to simply obtain on the server side. In Fb4 i see this will be simpler. regards, Karol Bieniaszewski Od: hv...@users.sourceforge.net [firebird-support] Wysłano: środa, 12 września 2018 18:19 Temat: [firebird-support] Re: Identify wirecompression > how to identify that connection use WireCompression effectively? See http://tracker.firebirdsql.org/browse/CORE-5536 and http://tracker.firebirdsql.org/browse/CORE-5601 Hope it helps, Vlad [Non-text portions of this message have been removed]
ODP: [firebird-support] Prevent overlaping dates in concurent environment
Hi,
Solution is good only for dates and only in small range but considering date
with time or numbers you see that solution is realy limited.
As Tomasz say will be good to see „systematic solution”
And to check overlaping your sample is quite ok but is ineficient
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|R1---|
|--R2--|
Better is check when dates do not overlap and do negation – only 2
possibilities
NOT (DATE_TO1mailto:firebird-supp...@yahoogroups...com]
Sent: Wednesday, 12 September, 2018 14:41
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurent
environment
Hi Tomasz!
Tested on 2.5.8, dialect 1:
CREATE TABLE RESERVED_DATE (
RES_DATE DATE NOT NULL
);
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
(RES_DATE);
CREATE TABLE MEETINGS (
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable curr_date date;
begin
curr_date = new.date_from;
while (curr_date <= new.date_to) do
begin
insert into reserved_date (res_date) values (:curr_date);
curr_date = dateadd(1 day to curr_date);
end
end
run on first transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.01', '2018.09.10')
run parallel on secound transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.08', '2018.09.15')
violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
"RESERVED_DATE".
Problematic key value is ("RES_DATE" = '8-SEP-2018').
At trigger 'MEETINGS_BI' line: 9, col: 7.
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 1:59 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Prevent overlaping dates in concurent
environment
On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Create a (reserved_dates) table with date field, and make that field unique..
> When a user inserts a date into the reservation table a trigger immadiate
> inserts this date to the reserved_dates table too. Then the unique key will
> stop secound insert instead of the first transaction is not commited.
This won't work. All dates may be different and the intervals may still
overlap.
Karol, that's an interesting issue and I'm really curious if there's a
clever solution.
So far I've checked the check (pun intended):
create table TST1 (
d1 timestamp,
d2 timestamp,
constraint no_overlap check (
not exists (
select * from TST1 t1
where exists (
select * from TST1 t2
where t1.d1 between t2.d1 and t2.d2
or t1.d2 between t2.d1 and t2.d2
)
)
)
);
insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B
-- commit A (no errors)
-- commit B (no errors)
and it doesn't work. You can still insert two overlapping pairs and both
transactions get committed without errors, resulting in overlapping
intervals being inserted. So, unless you change the transaction
isolation level (I always use read committed), I don't have more ideas
at the moment.
have a good one
Tomasz
--
__--==--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==--__
__ Information from ESET Mail Security, version of virus signature
database 18037 (20180912) __
The message was checked by ESET Mail Security.
http://www.eset.com
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
ODP: [firebird-support] Before Insert or After Insert trigger?
Hi, >> Now I need to set a fields in the same table like this to track the >>updates performed: If you need to update same table then „before” update trigger is the best choice. If you use after and run there UPDATE command you must take into accout recursive call. Your update command run then triggers once again and if your check condition do not stop another UPDATE command you got endless recursion, stopped in some point by engine with an error >>Updating another table from a Before trigger is made in a transaction >>that rolling back if exception occurs? If you do not run it in different transaction context - like IN AUTONOMOUS TRANSACTION - then yes regards, Karol Bieniaszewski Od: Luigi Siciliano luigi...@tiscalinet.it [firebird-support] Wysłano: czwartek, 6 września 2018 08:53 Do: firebird-support@yahoogroups.com Temat: [firebird-support] Before Insert or After Insert trigger? Hallo, I have an After Insert/Update trigger for a table to update another table like this: if (NEW.AGGIORNA_CONDIZIONI = 1) then begin update ARTICOLI_CONDIZIONI_CLIENTI ACC SET end else if ... Now I need to set a fields in the same table like this to track the updates performed: CONDIZIONI_AGGIORNATE = NEW.AGGIORNA_CONDIZIONI, AGGIORNA_CONDIZIONI = 0 /* no conditions to update */ To do this I must change from After Insert/Update trigger in Before Insert/Update trigger. Is this safe or I must do other behaviour? Updating another table from a Before trigger is made in a transaction that rolling back if exception occurs? Thanks. -- Luigi Siciliano -- ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
ODP: [firebird-support] Need help with Insert Query
Hi, Use INSERT INTO SELECT .. e.g. INSERT INTO MAILSERVERS(NAME, SERVER, REQUIREAUTH, SMTPPORT) SELECT A.IP_ADDRESS, A.IP_ADDRESS, 0, 25 FROM ip_addresses A Look also at „MERGE” statement which is much more powerfull then simple insert statement Pozdrawiam, Karol Bieniaszewski Od: 'Vaughan Wickham' v...@zeb.com.au [firebird-support] Wysłano: piątek, 31 sierpnia 2018 06:01 Do: firebird-support@yahoogroups.com Temat: [firebird-support] Need help with Insert Query Hello, I haven’t used SQL for some time and I need some help with how to go about constructing the following query. I have two tables: 1. IP_addresses 2. MailServers I have an external table which is defined as follows: Create Table ip_addresses EXTERNAL File 'C:/ip_fixed_length.txt' ( IP_ADDRESS CHAR(15) CHARACTER SET ASCII, LINE_BREAK CHAR(2) CHARACTER SET ASCII DEFAULT x'0D0A' ); I want to read the contents of the ip_addresses table and then add a new row to an existing table called: MAILSERVERS for each row (i.e. IP_ADDRESS) that exists in the IP_ADDRESSES table The MAILSERVERS table (destination) contains the following fields: NAME SERVER REQUIREAUTH SMTPPORT For each row that I add to the MAILSERVERS table I want to set the values as follows: NAME = IP_ADDRESS (from ip_addresses) SERVER = IP_ADDRESS (from ip_addresses) REQUIREAUTH = 0 SMTPPORT = 25 I think I need to use the INSERT command, but all the examples that I can find assume that the fieldname (aka column) that is being referred to in the INSERT command exists in both tables. However in my case the field names are different (and I can’t make them the same because there are two columns in the MailServers table) I guess what I could do if it was the easiest way would be to modify my external table. 1. Duplicate the IP_address column 2. Rename the columns: NAME, SERVER 3. Then use INSERT 4. I figure there has to be a better way than this though The problem that I have at the moment, is that I can’t work out how to do what I want (i.e. construct a query that is close to what I need that I can then tinker with to get the desired result) Appreciate any advice / examples to give me a starting point Thank you Regards, Vaughan [Non-text portions of this message have been removed]
