Re: Re: [firebird-support] Steps to Set Wait/No Wait Transaction
Hi,
dbClick on IBTransaction component and you got Transaction editor - and there
you have all values
google for each value
regards,
Karol Bieniaszewski
W dniu 2017-01-13 07:08:19 użytkownik Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support] napisał:
I am using TIBSQL, TIBDatabase and TIBTransaction components. This code is not
fitting there.
Would you please provide me Firebird Tools Group mail ID?
On Friday, 13 January 2017 11:15 AM, "Maya Opperman m...@omniaccounts.co.za
[firebird-support]" wrote:
Hi Vishal,
>I need to understand how to set Wait/No Wait Transaction in firebird.
I think you mean in Delphi? Might be better to ask on the Firebird tools group,
but I have replied below
>Also I have one doubt that I when we start transaction through code as shown
>below, the transaction locks particular rows in the table or it locks entire
>table?
I think you will be fine as long as you use generator to get your ID’s if you
use auto-incremented numeric primary keys.
Delphi code snippet for FIBPlus would be:
sqlMain.Transaction.TPBMode := tpbDefault;
sqlMain.Transaction.TRParams.Clear;
sqlMain.Transaction.TRParams.Add('read_committed');
sqlMain.Transaction.TRParams.Add('rec_version');
sqlMain.Transaction.TRParams.Params.Add('nowait');
//try
sqlMain.Transaction.StartTransaction;
try
//Have Insert SQL Execution here
sqlMain.Transaction.Commit;
except on E: Exception do
begin
sqlMain.Transaction.Rollback;
raise;
end;
end;
FireDac would be quite similar.
Cheers
Maya
Re: [firebird-support] Steps to Set Wait/No Wait Transaction
I am using TIBSQL, TIBDatabase and TIBTransaction components. This code is not
fitting there.
Would you please provide me Firebird Tools Group mail ID?
On Friday, 13 January 2017 11:15 AM, "Maya Opperman m...@omniaccounts.co.za
[firebird-support]" wrote:
Hi Vishal, >I need to understand how to set Wait/No Wait Transaction in
firebird. I think you mean in Delphi? Might be better to ask on the Firebird
tools group, but I have replied below >Also I have one doubt that I when we
start transaction through code as shown below, the transaction locks particular
rows in the table or it locks entire table? I think you will be fine as long as
you use generator to get your ID’s if you use auto-incremented numeric primary
keys. Delphi code snippet for FIBPlus would be:
sqlMain.Transaction.TPBMode := tpbDefault; sqlMain.Transaction.TRParams.Clear;
sqlMain.Transaction.TRParams.Add('read_committed');
sqlMain.Transaction.TRParams.Add('rec_version');
sqlMain.Transaction.TRParams.Params.Add('nowait'); //try
sqlMain.Transaction.StartTransaction; try //Have Insert SQL Execution here
sqlMain.Transaction.Commit; except on E: Exception do begin
sqlMain.Transaction.Rollback; raise; end; end; FireDac would be
quite similar. Cheers Maya #yiv6077816467 #yiv6077816467 --
#yiv6077816467ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}#yiv6077816467 #yiv6077816467ygrp-mkp hr {border:1px solid
#d8d8d8;}#yiv6077816467 #yiv6077816467ygrp-mkp #yiv6077816467hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv6077816467 #yiv6077816467ygrp-mkp #yiv6077816467ads
{margin-bottom:10px;}#yiv6077816467 #yiv6077816467ygrp-mkp .yiv6077816467ad
{padding:0 0;}#yiv6077816467 #yiv6077816467ygrp-mkp .yiv6077816467ad p
{margin:0;}#yiv6077816467 #yiv6077816467ygrp-mkp .yiv6077816467ad a
{color:#ff;text-decoration:none;}#yiv6077816467 #yiv6077816467ygrp-sponsor
#yiv6077816467ygrp-lc {font-family:Arial;}#yiv6077816467
#yiv6077816467ygrp-sponsor #yiv6077816467ygrp-lc #yiv6077816467hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6077816467
#yiv6077816467ygrp-sponsor #yiv6077816467ygrp-lc .yiv6077816467ad
{margin-bottom:10px;padding:0 0;}#yiv6077816467 #yiv6077816467actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6077816467
#yiv6077816467activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6077816467
#yiv6077816467activity span {font-weight:700;}#yiv6077816467
#yiv6077816467activity span:first-child
{text-transform:uppercase;}#yiv6077816467 #yiv6077816467activity span a
{color:#5085b6;text-decoration:none;}#yiv6077816467 #yiv6077816467activity span
span {color:#ff7900;}#yiv6077816467 #yiv6077816467activity span
.yiv6077816467underline {text-decoration:underline;}#yiv6077816467
.yiv6077816467attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv6077816467 .yiv6077816467attach div a
{text-decoration:none;}#yiv6077816467 .yiv6077816467attach img
{border:none;padding-right:5px;}#yiv6077816467 .yiv6077816467attach label
{display:block;margin-bottom:5px;}#yiv6077816467 .yiv6077816467attach label a
{text-decoration:none;}#yiv6077816467 blockquote {margin:0 0 0
4px;}#yiv6077816467 .yiv6077816467bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6077816467
.yiv6077816467bold a {text-decoration:none;}#yiv6077816467 dd.yiv6077816467last
p a {font-family:Verdana;font-weight:700;}#yiv6077816467 dd.yiv6077816467last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6077816467
dd.yiv6077816467last p span.yiv6077816467yshortcuts
{margin-right:0;}#yiv6077816467 div.yiv6077816467attach-table div div a
{text-decoration:none;}#yiv6077816467 div.yiv6077816467attach-table
{width:400px;}#yiv6077816467 div.yiv6077816467file-title a, #yiv6077816467
div.yiv6077816467file-title a:active, #yiv6077816467
div.yiv6077816467file-title a:hover, #yiv6077816467 div.yiv6077816467file-title
a:visited {text-decoration:none;}#yiv6077816467 div.yiv6077816467photo-title a,
#yiv6077816467 div.yiv6077816467photo-title a:active, #yiv6077816467
div.yiv6077816467photo-title a:hover, #yiv6077816467
div.yiv6077816467photo-title a:visited {text-decoration:none;}#yiv6077816467
div#yiv6077816467ygrp-mlmsg #yiv6077816467ygrp-msg p a
span.yiv6077816467yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6077816467
.yiv6077816467green {color:#628c2a;}#yiv6077816467 .yiv6077816467MsoNormal
{margin:0 0 0 0;}#yiv6077816467 o {font-size:0;}#yiv6077816467
#yiv6077816467photos div {float:left;width:72px;}#yiv6077816467
#yiv6077816467photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv6077816467
#yiv6077816467photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:now
RE: [firebird-support] Steps to Set Wait/No Wait Transaction
Hi Vishal,
>I need to understand how to set Wait/No Wait Transaction in firebird.
I think you mean in Delphi? Might be better to ask on the Firebird tools group,
but I have replied below
>Also I have one doubt that I when we start transaction through code as shown
>below, the transaction locks particular rows in the table or it locks entire
>table?
I think you will be fine as long as you use generator to get your ID’s if you
use auto-incremented numeric primary keys.
Delphi code snippet for FIBPlus would be:
sqlMain.Transaction.TPBMode := tpbDefault;
sqlMain.Transaction.TRParams.Clear;
sqlMain.Transaction.TRParams.Add('read_committed');
sqlMain.Transaction.TRParams.Add('rec_version');
sqlMain.Transaction.TRParams.Params.Add('nowait');
//try
sqlMain.Transaction.StartTransaction;
try
//Have Insert SQL Execution here
sqlMain.Transaction.Commit;
except on E: Exception do
begin
sqlMain.Transaction.Rollback;
raise;
end;
end;
FireDac would be quite similar.
Cheers
Maya
[firebird-support] Steps to Set Wait/No Wait Transaction
Hi All, I need to understand how to set Wait/No Wait Transaction in firebird. Also I have one doubt that I when we start transaction through code as shown below, the transaction locks particular rows in the table or it locks entire table? Delphi code snippet should be: try sqlMain.Transaction.StartTransaction; //HaveInsert SQL Execution here sqlMain.Transaction.Commit; except sqlMain.Transaction.Rollback; end; Thanks in advance. With Best Regards. Vishal
Re: [firebird-support] Firebird : join table from two database
Hi,
look at my example that I'm using for preparing two DB fou join into one
big.
My be it will help you.
-- Prepare data table for replication
-- Ensure that data in both databases are unique
-- No overlap during replication will occur
CREATE OR ALTER PROCEDURE Repl$SynchronizeDataTable(
MasterDB VARCHAR(200),
MasterUserVARCHAR(20),
MasterPassVARCHAR(20),
RelationName RDB$RELATION_NAME,-- Table that exists in
both databases and have to be synchronized
PrimaryKeyF RDB$FIELD_NAME, -- Primary key name
GenName RDB$RELATION_NAME,-- Used te retrive new id
if needed during synchronization
MasterIgnoreWhere VARCHAR(200) DEFAULT NULL --
) RETURNS (
Msg VARCHAR(70),
PrimaryKeyMaster INTEGER,
PrimaryKeyTarget INTEGER
)
AS
DECLARE ds VARCHAR(500);
DECLARE ds2 VARCHAR(500);
DECLARE dsu VARCHAR(500);
DECLARE ds_l VARCHAR(500);
DECLARE M_id INTEGER;
DECLARE GM_id INTEGER;
DECLARE cnt_total INTEGER;
DECLARE cnt INTEGER;
DECLARE MaxL_id INTEGER;
BEGIN
ds = 'SELECT '||TRIM(PrimaryKeyF)||' FROM '||RelationName;
IF(MasterIgnoreWhere IS NOT NULL)THEN
ds = ds ||' WHERE '||MasterIgnoreWhere;
cnt_total = 0;
cnt = 0;
ds_l = 'SELECT '||PrimaryKeyF||' FROM ' || RelationName || ' WHERE
'||PrimaryKeyF||' = :M_id';
-- Update statement for local DB
dsu = 'UPDATE '||TRIM(RelationName)||' SET '||PrimaryKeyF||'=:GM_id
WHERE '||PrimaryKeyF||'=:id';
Msg = dsu; SUSPEND;
-- Adjust sequence
ds2 = 'SELECT MAX('||TRIM(PrimaryKeyF)||') FROM ' || RelationName;
EXECUTE STATEMENT (:ds2) INTO :MaxL_id;
IF(MaxL_id IS NULL) THEN MaxL_id = 0;
Msg = 'Max local id:'||MaxL_id;
SUSPEND;
ds2 = 'EXECUTE BLOCK AS
DECLARE g_id INTEGER;
BEGIN
IF('||MaxL_id||'>GEN_ID('||GenName||',0)) THEN BEGIN
g_id =
GEN_ID('||GenName||',MAXVALUE('||MaxL_id||'-GEN_ID('||GenName||',0),0));
-- be paranoid, another transaction can change generator
END
END';
EXECUTE STATEMENT ds2
ON EXTERNAL DATA SOURCE MasterDB
AS USER MasterUser
PASSWORD MasterPass;
-- Check conflict and resolve it
FOR EXECUTE STATEMENT ds
ON EXTERNAL DATA SOURCE MasterDB
AS USER MasterUser
PASSWORD MasterPass
INTO :M_id DO BEGIN
cnt_total = cnt_total + 1;
PrimaryKeyTarget = NULL;
EXECUTE STATEMENT (:ds_l) (M_id := :M_id) INTO :PrimaryKeyTarget;
IF(PrimaryKeyTarget IS NOT NULL)THEN BEGIN
cnt = cnt + 1;
-- We expect that generator are always created without ""
ds2 = 'EXECUTE BLOCK RETURNS(id INTEGER) AS BEGIN '||
'EXECUTE PROCEDURE
MASA$Sequence_Check_Pool(UPPER('''||GenName||'''));'||
'id = NEXT VALUE FOR '||GenName||';'||
'SUSPEND;'||
'END';
EXECUTE STATEMENT ds2
ON EXTERNAL DATA SOURCE MasterDB
AS USER MasterUser
PASSWORD MasterPass
INTO :GM_id;
PrimaryKeyMaster = M_id;
EXECUTE STATEMENT (:dsU) (GM_id := :GM_id, id:=:PrimaryKeyTarget);
Msg = 'New id:'||GM_id;
SUSPEND;
END
END
Msg = 'Total:'||cnt_total;
SUSPEND;
Msg = 'Conflicts:'||cnt;
SUSPEND;
END
^
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:skopa...@elektlabs.cz
http://www.elektlabs.cz
On 12.1.2017 21:44, startx252...@yahoo.fr [firebird-support] wrote:
>
> Hi all,
> What is the way to make a join from 2 tables in two respective database (in
> same server) ?
>
>
> ex :
> Db1
> TableA
>
>
> Db2
> TableB
>
>
> how can i make somethink like this :
>
>
> SELECT tA.MATRICRS, tB.name, tB.age
> FROM TableA tA
> JOIN TABLEB tB ON ( tA.ID = tB.ID)
>
>
>
>
>
>
> ps: I use FlameRobin to write sql statement.
>
>
Re: [firebird-support] Firebird : join table from two database
12.01.2017 21:44, startx252...@yahoo.fr [firebird-support] wrote: > What is the way to make a join from 2 tables in two respective database (in > same server) ? No. -- WBR, SD. ++ 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Firebird : join table from two database
Hi all, What is the way to make a join from 2 tables in two respective database (in same server) ? ex : Db1 TableA Db2 TableB how can i make somethink like this : SELECT tA.MATRICRS, tB.name, tB.age FROM TableA tA JOIN TABLEB tB ON ( tA.ID = tB.ID) ps: I use FlameRobin to write sql statement.
Re: [firebird-support] Time zones
If I may be permitted to stick my oar in here Oracle databases allow timestamp with timezone data types, as well as simple timezone data types. Every database I've come across in my day job, I'm an Oracle contract DBA, uses either date (aka date and time) or simple timezone data types. Some of the internal stuff does use timezones, and it's a right PITA working out what the actual time a job got scheduled at, for example. Timezones, just say no! :-) Other opinions are of course available. They are wrong! (Joke) Cheers, Norm. -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Re: [firebird-support] How to identify if field of table is identity or not?
just be careful, shits happens ;)
Now, its done:
EXECUTE BLOCK
returns (
result_table varchar(255),
result_idkey varchar(255),
result_done varchar(1)) as
declare variable source_list varchar(8192);
declare variable l_item varchar(255);
declare variable l_lastpos integer;
declare variable l_nextpos integer;
declare variable l_npos integer;
declare variable l_idkey_max bigint = 0;
declare variable l_is_identity integer;
begin
--source_list='cv.id_cv,clientes.id_cliente,fornecedores.id_fornecedor';
source_list='';
if (:source_list='') then
begin
for select rel.rdb$relation_name, rf.rdb$field_name
from rdb$relations rel
inner join rdb$relation_fields rf on
(rf.rdb$relation_name=rel.rdb$relation_name)
where rf.rdb$identity_type is not null
into :result_table, :result_idkey do
begin
source_list=:source_list||:result_table||'.'||:result_idkey||',';
end
end
if (substring(:source_list from octet_length(:source_list) for 1)<>',')
then source_list = :source_list || ',';
l_lastpos = 1;
l_nextpos = position(',', :source_list, l_lastpos);
while (:l_nextpos > 1) do
begin
l_item = substring(:source_list from :l_lastpos for :l_nextpos -
:l_lastpos);
result_done='N';
if (trim(l_item)<>'') then
begin
l_npos = position('.', l_item);
if (l_npos>0) then
begin
result_table = substring(:l_item from 1 for l_npos - 1);
result_idkey = substring(:l_item from l_npos +1);
-- se a tabela existir, então captura a numeracao mais alta e a
transfere para o sequenciador
if (exists(SELECT 1 FROM RDB$RELATIONS WHERE
UPPER(RDB$RELATION_NAME) = UPPER(:result_table))) then
begin
execute statement 'select max('||:result_idkey||') FROM
'||:result_table into :l_idkey_max;
if (:l_idkey_max is null)
then l_idkey_max=0;
l_idkey_max=:l_idkey_max+1;
-- identificando se o campo é identity ou não
l_is_identity=0;
if (exists(
select 1 from rdb$relations rel
inner join rdb$relation_fields rf on
(rf.rdb$relation_name=rel.rdb$relation_name)
where
(UPPER(rel.rdb$relation_name) = UPPER(:result_table)) and
(UPPER(rf.rdb$field_name) = UPPER(:result_idkey)) and
(rf.rdb$identity_type is not null)
)) then l_is_identity=1;
if (l_is_identity>0) then
begin
--execute statement 'ALTER TABLE '||:result_table||' ALTER
COLUMN '||:result_idkey|| ' RESTART WITH '||CAST(:l_idkey_max as
VARCHAR(8))||';';
result_done = 'Y';
end
suspend;
end
end
end
l_lastpos = :l_nextpos + 1;
l_nextpos = position(',', :source_list, l_lastpos);
end
end
2017-01-12 14:33 GMT-02:00 Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] :
>
>
>
> BTW: "recompute all identity fields" sounds like an accident waiting to
> happen. You should normally have no reason to reset this.
>
>
Re: [firebird-support] How to identify if field of table is identity or not?
On 12-1-2017 17:22, hamacker sirhamac...@gmail.com [firebird-support] wrote:
> hi All,
>
> I did a procedure that recompute all identity fields of a list with
> items separate by comma, but I need a method to check if table.field is
> identity or not. It´s a procedure of mainnitence.
For Firebird 3 identity fields, you can use (RF is RDB$RELATION_FIELDS):
CASE WHEN RF.RDB$IDENTITY_TYPE IS NULL THEN CAST('NO' AS VARCHAR(3))
ELSE CAST('YES' AS VARCHAR(3)) END AS IS_IDENTITY
BTW: "recompute all identity fields" sounds like an accident waiting to
happen. You should normally have no reason to reset this.
> My code:
> EXECUTE BLOCK
> returns (
> result_table varchar(255),
> result_idkey varchar(255),
> result_done varchar(1)) as
> declare variable source_list varchar(8192);
> declare variable l_item varchar(255);
> declare variable l_lastpos integer;
> declare variable l_nextpos integer;
> declare variable l_npos integer;
> declare variable l_idkey_max bigint = 0;
> declare variable l_is_identity integer;
> begin
> source_list='cv.id_cv,clientes.id_cliente,fornecedores.id_fornecedor';
> source_list = :source_list || ',';
> l_lastpos = 1;
> l_nextpos = position(',', :source_list, l_lastpos);
> while (:l_nextpos > 1) do
> begin
> l_item = substring(:source_list from :l_lastpos for :l_nextpos -
> :l_lastpos);
> result_done='N';
> if (trim(l_item)<>'') then
> begin
> l_npos = position('.', l_item);
> if (l_npos>0) then
> begin
> result_table = substring(:l_item from 1 for l_npos - 1);
> result_idkey = substring(:l_item from l_npos +1);
> -- se a tabela existir, então captura a numeracao mais alta e a
> transfere para o sequenciador
> if (exists(SELECT * FROM RDB$RELATIONS WHERE
> UPPER(RDB$RELATION_NAME) = UPPER(:result_table))) then
> begin
> execute statement 'select max('||:result_idkey||') FROM
> '||:result_table into :l_idkey_max;
> if (:l_idkey_max is null)
> then l_idkey_max=0;
> l_idkey_max=:l_idkey_max+1;
> l_is_identity=1;
> -- todo: How to identify if field of table is identity or not?
> if (l_is_identity>0) then
> begin
> execute statement 'ALTER TABLE '||:result_table||' ALTER
> COLUMN '||:result_idkey|| ' RESTART WITH '||CAST(:l_idkey_max as
> VARCHAR(8))||';';
> result_done = 'Y';
> end
> suspend;
> end
> end
> end
> l_lastpos = :l_nextpos + 1;
> l_nextpos = position(',', :source_list, l_lastpos);
> end
> end
--
Mark Rotteveel
Re: [firebird-support] How to identify if field of table is identity or not?
12.01.2017 17:22, hamacker sirhamac...@gmail.com [firebird-support] wrote: > I need a method to check if table.field is identity or not. RTFM README.identity_columns.txt -- WBR, SD. ++ 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] How to identify if field of table is identity or not?
hi All,
I did a procedure that recompute all identity fields of a list with items
separate by comma, but I need a method to check if table.field is identity
or not. It´s a procedure of mainnitence.
My code:
EXECUTE BLOCK
returns (
result_table varchar(255),
result_idkey varchar(255),
result_done varchar(1)) as
declare variable source_list varchar(8192);
declare variable l_item varchar(255);
declare variable l_lastpos integer;
declare variable l_nextpos integer;
declare variable l_npos integer;
declare variable l_idkey_max bigint = 0;
declare variable l_is_identity integer;
begin
source_list='cv.id_cv,clientes.id_cliente,fornecedores.id_fornecedor';
source_list = :source_list || ',';
l_lastpos = 1;
l_nextpos = position(',', :source_list, l_lastpos);
while (:l_nextpos > 1) do
begin
l_item = substring(:source_list from :l_lastpos for :l_nextpos -
:l_lastpos);
result_done='N';
if (trim(l_item)<>'') then
begin
l_npos = position('.', l_item);
if (l_npos>0) then
begin
result_table = substring(:l_item from 1 for l_npos - 1);
result_idkey = substring(:l_item from l_npos +1);
-- se a tabela existir, então captura a numeracao mais alta e a
transfere para o sequenciador
if (exists(SELECT * FROM RDB$RELATIONS WHERE
UPPER(RDB$RELATION_NAME) = UPPER(:result_table))) then
begin
execute statement 'select max('||:result_idkey||') FROM
'||:result_table into :l_idkey_max;
if (:l_idkey_max is null)
then l_idkey_max=0;
l_idkey_max=:l_idkey_max+1;
l_is_identity=1;
-- todo: How to identify if field of table is identity or not?
if (l_is_identity>0) then
begin
execute statement 'ALTER TABLE '||:result_table||' ALTER
COLUMN '||:result_idkey|| ' RESTART WITH '||CAST(:l_idkey_max as
VARCHAR(8))||';';
result_done = 'Y';
end
suspend;
end
end
end
l_lastpos = :l_nextpos + 1;
l_nextpos = position(',', :source_list, l_lastpos);
end
end
Re: [firebird-support] Time zones
On 12/01/17 14:09, Tim Ward t...@telensa.com [firebird-support] wrote: > Can someone point me in the right direction please? There have been many attempts to justify storing a time stamp complete with timezone directly in a time field, but the real answer is that it is always wrong. Store location data in addition to a UTC time stamp and that way you can always display a correct time, and more importantly correctly move a time when passing over a DST change. The one thing that is missing from the 'offset' supplied by a browser is any means of identifying if the offset will be the same next month. You need to know the correct time zone and not just the current offset which is why a time with an offset may be wrong half of the year. Another piece of the jigsaw is the problem of identifying what the current offset data is in relation to a timezone. If you have created a UTC normalized time and have a timezone which gives you an offset, then the DST rules change, you will only know of the change if you have recorded the version of the rule set you normalised the time with, and the current rule set. So timezone/version is the additional data that should be recorded once working with UTC normalized times. And if you are running a system supporting several time zones then the server clock should always be set to UTC time. Trying to calculate UTC then Local time from a server time that may also have DST variations creates no end3of edge cases :) Store all times as UTC unless you are ONLY working with one timezone, but even that is tricky if your time zone uses DST ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
Re: [firebird-support] Time zones
12.01.2017 15:09, Tim Ward t...@telensa.com [firebird-support] wrote: > Can someone point me in the right direction please? Firebird does not handle time zones at all. CURRENT_TIMESTAMP and CURRENT_TIME return local time. -- WBR, SD. ++ 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Time zones
On 12-1-2017 15:09, Tim Ward t...@telensa.com [firebird-support] wrote: > Sorry if this is a really basic question, but some time spent searching > has failed to find for me the definitive detailed documentation on > exactly how Firebird handles time zones (I'm particularly interested, to > start with, in using CURRENT_TIMESTAMP to create a value in a TIMESTAMP > field, and later reading it out and using it to display in a UI, but > that's only to start with). > > Can someone point me in the right direction please? > Basically, Firebird doesn't really do anything with time zones, Firebird uses the timezone of the server for current_timestamp, current_time and current_date (and 'today' and 'now'). Timestamps (etc) in Firebird have no time zone information, so it is not possible to distinguish between say today 12:00 in UTC or UTC+1. This is important to realize if you access or write dates from systems in another time zone. For example the JDBC specification requires that times are handled in the timezone of the JVM, so if Firebird server is in UTC and the JVM is in UTC+1 you will get discrepancies when using `current_timestamp` in Firebird and things like `LocalDate.now()` on the Java side. PS In the future, could you start a new message instead of replying to an existing thread. In my mail client this message is grouped under the "FB 2.5 migrate to 3.0" thread instead of its own. -- Mark Rotteveel
[firebird-support] Time zones
Sorry if this is a really basic question, but some time spent searching has failed to find for me the definitive detailed documentation on exactly how Firebird handles time zones (I'm particularly interested, to start with, in using CURRENT_TIMESTAMP to create a value in a TIMESTAMP field, and later reading it out and using it to display in a UI, but that's only to start with). Can someone point me in the right direction please? -- Tim Ward
Re: Re: [firebird-support] FB 2.5 migrate to 3.0
Dear Helen, Today I did a clean installation for FB 3.0 (from zip package) and get DB migrated to version 3.0. It makes me so happy. The real reason for previous problem is caused by security database file initialization step. *** Initialize security database by isql (FB 3.0). > isql -user sysdba MYIMPORT SQL> create user SYSDBA password 'masterkey'; SQL> commit; SQL> quit; *** After this, everything works fine. Thanks for your great help anyway. Best regards, James From: Helen Borrie hele...@iinet.net.au [firebird-support] Date: 2017-01-10 17:02 To: firebird-support Subject: Re: [firebird-support] FB 2.5 migrate to 3.0 > Thanks for your answer. I think I got help from you also during last time > migration test. :-) > I tested it with your advice but it still doesn't work. I am not convinced you have that authentication configured properly nor that your application is really loading the Fb3 client library. Some reality checks: 1. Make sure that you deleted the # symbol when you configured AuthServer. 2. It might be worthwhile to change the ordering of the list in AuthClient, so that Legacy_Auth appears first. 2. Get rid of SRP from the UserManager list. Make it so that Legacy_UserManager is only entry. 3. Don't forget to save firebird.conf, and to restart Firebird. 4. Put fbclient.dll in the same folder as your application. It would probably be a good idea to reboot the client machine, while you are at it, in case the old version is still loaded. > I would like to test it with a fresh installation of FB 3.0 later. > I should make my description more clear for my steps. > 0. First of all, all applications are running in Windows 7. FB x32 versions > are used for testing. > 1. Use gbak (FB 2.5) to backup Firebird 2.5 database file. > 2. Use gbak (FB 3.0) to restore backuped FBK file to a new database file. > 3. Use isql, to run: SQL> alter user sysdba set password 'masterkey'; > respond: Database: myimport,&n bsp;User: SYSDBA Without the modifications to firebird.conf, that step operates on the SYSDBA that is configured under UserManager SRP. It will NOT work with applications connecting via Legacy_UserManager. You have to create SYSDBA for use with Legacy_UserManager. You need to use the USING PLUGIN clause, as indicated in my previous posting. > 4. Open new database file in IBExpert with FB 3.0 client library > (fbclient.dll), it works. > 5. Run my own applicatoin with FB 3.0 client library which works > with FB2.5 client library, error message shows below: > [FireDAC][Phys][FB]Your user name and password are not defined. Ask > your database administrator to set up a Firebird login. > > Connection string inside my code > > object dbcMain: TFDConnection > Params.Strings = ( > ; 'DriverID=FB' > 'User_Name=sysdba' > 'Password=masterkey') > But it will not work if either (or both) 1) the application is loading the Fb 2.5 client 2) firebird.conf configuration is a mismatch with the client BTW, you are not REALLY using masterkey for your SYSDBA password, are you? HB
[firebird-support] Typescript ORM for Firebird (with support for older versions like 1.5)
Hi! There is very promising Typescript ORM https://github.com/typeorm/typeorm https://github.com/typeorm/typeorm As more and more server side development moves to JavaScript and Typescript, there is need for Firebird ORM for Typescript. Is there ongoing work on this (maybe as part of other project).
