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
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
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--
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
Tema
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
Au
Hi,
<
Hi,
1. Why not single Firebird database with all tables?
2. Why not recent Firebird which is FB3 not FB2.5?
Pozdrawiam,
Karol Bieniaszewski
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 packe
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.
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
>>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 fir
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
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
>>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
>> 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
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 whol
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
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
…..
>> 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
Look also at hex literals
https://firebirdsql.org/refdocs/langrefupd25-hexbinstrings.html
Pozdrawiam,
Karol Bieniaszewski
Hi.
When you gain slowdows
Run
gstat -h
and put here results.
Problem probably is because of some long running transaction.
Regards,
Karol Bieniaszewski
Hi.
Look at gstat output
You probably have very long version this particular record
Regards,
Karol Bieniaszewski
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
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 t
Hi.
I had never such issue.
But mayby setting FB service autostart to „delayed” will help you.
regards,
Karol Bieniaszewski
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 compar
>>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
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 w
Thank you Pavel for explanation.
Now i see what is the key point of this „platform” and it is promissing.
>> - that http is best and most efficient method of communication between
>> services, especially between ones that may live also in the same
>> executable / process? We need "elastic" effe
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 POSITI
>>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
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 A
Good points Mark.
Then maybe some switch to restore process should be added?
Now this is nightmare for the end user.
regards,
Karol Bieniaszewski
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 po
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.exe8836
>>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
> 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 caveat
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
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
I see that you have only 1024 page buffers.
Do you use SuperServer or Classic?
If Superserver then increase it.
Regards,
Karol Bieniaszewski
You can do this without window function also in FB<3, but less efective. Do
simple 2 subselects in your select.
Regards,
Karol Bieniaszewski
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 i
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,
K
>>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.GALLER
>> 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
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
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
>> "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
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
Hi,
I do not suppose it is possible. But why do you need that info?
regards,
Karol Bieniaszewski
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
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
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 almo
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
>>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
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
>>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
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
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
>>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 wi
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
>> 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
Now possibility:
Exctract + cast
Substring + cast
But you can vote on something more usefull:
http://tracker.firebirdsql.org/browse/CORE-5623
regards,
Karol Bieniaszewski
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
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.R
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 red
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
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
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
I do not suppose that this is Firebird message.
This looks like client program message.
regards,
Karol Bieniaszewski
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
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
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
Hi
First change group from yahoo to google group.
And without whole reproducible script no one can help i suppose
Regards,
Karol Bieniaszewski
74 matches
Mail list logo
