Re: [firebird-support] Is there an easy way for input rows from a script file?
HTH = Hope this (alternatively: that) helps 2017-02-16 3:06 GMT+01:00 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] < firebird-support@yahoogroups.com>: > > > Thank you very much Set, I will try your advice. > > Off topic: can you say me what the letters HTH means? > > Greetings. > > Walter. > > > On Wed, Feb 15, 2017 at 6:31 PM, setysvar setys...@gmail.com > [firebird-support] wrote: > >> >> >> Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente' >> sistemas2000profesio...@gmail.com [firebird-support]: >> >> Hello everybody >> >> If I connect to a database "Database1" using ISQL, I can write something >> like: >> >> OUTPUT MyFile.DAT; >> SELECT * FROM MyTable; >> OUTPUT; >> >> And all the rows of "MyTable" will go to the text file "MyFile.DAT". >> That's ok and works fine. >> But now, I want to connect to "Database2", which also have a table called >> "MyTable" and with the same structure. >> After that, I want to insert into "MyTable" (of "Database2") the rows >> contained in "MyFile.DAT" >> How can I do such thing without a lot of effort writing an INSERT command >> in each line of "MyFile.DAT"? >> >> Greetings. >> Walter. >> >> >> Hi Walter. I'm not directly answering your question (mainly due to not >> using isql myself, so I don't know the answer), but I can think of two >> alternative ways. >> >> (1) (the more standard answer) Make sure your output file is in a fixed >> length format, then create an external table for this file, transfer the >> data using INSERT INTO "MyTable" SELECT * FROM >> and then finally drop the external table. >> >> (2) Use qli rather than isql to transfer data directly using something >> similar to this recipe: >> https://www.ibphoenix.com/resources/documents/how_to/doc_42 >> qli is an ancient part of InterBase, probably used before SQL was >> invented, you can find it in the same directory as isql. >> >> I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5), but >> it worked nicely when I needed it. Though I don't think qli has been >> updated for the last few Firebird versions, so maybe it doesn't work >> anymore. And you may have a hard time finding information about it beyond >> the document referred to above. Though I suppose: >> >> ready "Database1" as src; >> ready "Database2" as trg; >> trg."MyTable" = src."MyTable"; >> >> doesn't need much explanation and should be simple for you to try on a >> test database. I expect it to replace the target rather than add to it >> (though I don't know), but the one time I used qli, I think I was >> transferring to an empty table. >> >> HTH, >> Set >> >> > > > >
Re: [firebird-support] Re: Is there an easy way for input rows from a script file?
Thank you Vlad, but is not for me, is for an article of my blog, I am showing the alternatives for exporting data. firebird21.wordpress.com Greetings. Walter. On Thu, Feb 16, 2017 at 3:44 AM, hv...@users.sourceforge.net [firebird-support] wrote: > > > ---In firebird-support@yahoogroups.com, > wrote : > > Hello everybody > > If I connect to a database "Database1" using ISQL, I can write something > like: > > OUTPUT MyFile.DAT; > SELECT * FROM MyTable; > OUTPUT; > > And all the rows of "MyTable" will go to the text file "MyFile.DAT". > That's ok and works fine. > > But now, I want to connect to "Database2", which also have a table called > "MyTable" and with the same structure. > > After that, I want to insert into "MyTable" (of "Database2") the rows > contained in "MyFile.DAT" > > How can I do such thing without a lot of effort writing an INSERT command > in each line of "MyFile.DAT"? > > If you need to export some data from one Firebird database to another > Firebird database, use EXECUTE STATEMENT ... ON EXTERNAL and forget about > files. > > Regards, > Vlad > > >
Re: [firebird-support] Is there an easy way for input rows from a script file?
Thank you Set, now I know the meaning of HTH. Greetings. Walter. On Thu, Feb 16, 2017 at 4:09 AM, Svein Erling Tysvær setys...@gmail.com [firebird-support] wrote: > > > HTH = Hope this (alternatively: that) helps > > 2017-02-16 3:06 GMT+01:00 'Walter R. Ojeda Valiente' > sistemas2000profesio...@gmail.com [firebird-support] < > firebird-support@yahoogroups.com>: > >> >> >> Thank you very much Set, I will try your advice. >> >> Off topic: can you say me what the letters HTH means? >> >> Greetings. >> >> Walter. >> >> >> On Wed, Feb 15, 2017 at 6:31 PM, setysvar setys...@gmail.com >> [firebird-support] wrote: >> >>> >>> >>> Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente' >>> sistemas2000profesio...@gmail.com [firebird-support]: >>> >>> Hello everybody >>> >>> If I connect to a database "Database1" using ISQL, I can write something >>> like: >>> >>> OUTPUT MyFile.DAT; >>> SELECT * FROM MyTable; >>> OUTPUT; >>> >>> And all the rows of "MyTable" will go to the text file "MyFile.DAT". >>> That's ok and works fine. >>> But now, I want to connect to "Database2", which also have a table >>> called "MyTable" and with the same structure. >>> After that, I want to insert into "MyTable" (of "Database2") the rows >>> contained in "MyFile.DAT" >>> How can I do such thing without a lot of effort writing an INSERT >>> command in each line of "MyFile.DAT"? >>> >>> Greetings. >>> Walter. >>> >>> >>> Hi Walter. I'm not directly answering your question (mainly due to not >>> using isql myself, so I don't know the answer), but I can think of two >>> alternative ways. >>> >>> (1) (the more standard answer) Make sure your output file is in a fixed >>> length format, then create an external table for this file, transfer the >>> data using INSERT INTO "MyTable" SELECT * FROM >>> and then finally drop the external table. >>> >>> (2) Use qli rather than isql to transfer data directly using something >>> similar to this recipe: >>> https://www.ibphoenix.com/resources/documents/how_to/doc_42 >>> qli is an ancient part of InterBase, probably used before SQL was >>> invented, you can find it in the same directory as isql. >>> >>> I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5), >>> but it worked nicely when I needed it. Though I don't think qli has been >>> updated for the last few Firebird versions, so maybe it doesn't work >>> anymore. And you may have a hard time finding information about it beyond >>> the document referred to above. Though I suppose: >>> >>> ready "Database1" as src; >>> ready "Database2" as trg; >>> trg."MyTable" = src."MyTable"; >>> >>> doesn't need much explanation and should be simple for you to try on a >>> test database. I expect it to replace the target rather than add to it >>> (though I don't know), but the one time I used qli, I think I was >>> transferring to an empty table. >>> >>> HTH, >>> Set >>> >>> >> >> >> > >
[firebird-support] FB3 No permission for usage access to exception
I'm confused about an error when accessing an FB3 database - "No permission for USAGE access to Exception E_Exception". When I run a program while logged into database as SYSDBA I do not get this error. I ran "grant usage on exception e_exception to user NICOLED" and "grant usage on exception e_exception to role WAREHOUSE". When I look at RDB$USER_PRIVILEGES I can see RDB$RELATION_NAME for "E_EXCEPTION" with RDB$PRIVILEGE of "G". I understand that FB3 now requires USAGE access to exceptions, but how do I grant it?
[firebird-support] Re: FB3 No permission for usage access to exception
Problem, solved. This was my error from an incorrect role name used in the connection string.
[firebird-support] Firebird 3 execution plan
Hi, Firebird3 SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS FROM SENSOR_DATA SD INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR WHERE S.ID=1 ORDER BY SD.ID DESC plan is "wrong" PLAN SORT (JOIN (S INDEX (PK_SENSOR), SD INDEX (FK_SENSOR_DATA__SENSOR))) Executing statement... Statement executed (elapsed time: 0.000s). 64355 fetches, 12 marks, 32 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 51195 index, 10343 seq. Delta memory: 484688 bytes. Total execution time: 0.311s Script execution finished. SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS FROM SENSOR_DATA SD INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR WHERE SD.ID_SENSOR=1 ORDER BY SD.ID DESC plan is "wrong" PLAN SORT (JOIN (S INDEX (PK_SENSOR), SD INDEX (FK_SENSOR_DATA__SENSOR))) Executing statement... Statement executed (elapsed time: 0.000s). 52954 fetches, 2 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 50990 index, 0 seq. Delta memory: 0 bytes. Total execution time: 0.211s Script execution finished. SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS FROM SENSOR_DATA SD LEFT JOIN SENSOR S ON S.ID=SD.ID_SENSOR WHERE SD.ID_SENSOR=1 ORDER BY SD.ID DESC plan is "expected" PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR)) Executing statement... Statement executed (elapsed time: 0.000s). 60 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 10 index, 0 seq. Delta memory: -11992 bytes. Total execution time: 0.023s Script execution finished. SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS FROM SENSOR_DATA SD INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR+0 WHERE S.ID=1 ORDER BY SD.ID DESC plan is "expected" PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR)) Executing statement... Statement executed (elapsed time: 0.000s). 173 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 19 index, 0 seq. Delta memory: -146056 bytes. Total execution time: 0.023s Script execution finished. can this plan be selected by Firebird in somehow automatically without going to left join or +0 solution? regards, Karol Bieniaszewski
[firebird-support] Re: Firebird 3 execution plan
16.02.2017 21:20, liviuslivius wrote: > > Firebird3 There's no such a version. > plan is "expected" > PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR)) What's definition of IXDU_SENSOR_DATA__ID? Dmitry
Re: [firebird-support] Re: Firebird 3 execution plan
Hi Dmitry, current snapshot: WI-V3.0.2.32691 Firebird 3.0 CREATE UNIQUE DESCENDING INDEX IXDU_SENSOR_DATA__ID ON SENSOR_DATA (ID); ID bigint selectivity PK_SENSOR ASC ID 0.25 UK_SENSOR__SENSOR_UID ASC SENSOR_UID 0.25 FK_SENSOR_DATA__SENSOR ASC ID_SENSOR 0.25 IXDU_SENSOR_DATA__ID DESC ID 0.02 PK_SENSOR_DATA ASC ID 0.02 regards, Karol Bieniaszewski 16.02.2017 21:20, liviuslivius wrote: > > Firebird3 There's no such a version. > plan is "expected" > PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR)) What's definition of IXDU_SENSOR_DATA__ID? Dmitry __._
Re: [firebird-support] Is there an easy way for input rows from a script file?
> On Feb 15, 2017, at 6:31 PM, setysvar setys...@gmail.com [firebird-support] > wrote: > > > > Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente' > sistemas2000profesio...@gmail.com [firebird-support]: >> Hello everybody >> >> If I connect to a database "Database1" using ISQL, I can write something >> like: >> >> OUTPUT MyFile.DAT; >> SELECT * FROM MyTable; >> OUTPUT; >> >> And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's >> ok and works fine. >> But now, I want to connect to "Database2", which also have a table called >> "MyTable" and with the same structure. >> After that, I want to insert into "MyTable" (of "Database2") the rows >> contained in "MyFile.DAT" >> How can I do such thing without a lot of effort writing an INSERT command in >> each line of "MyFile.DAT"? >> >> Greetings. >> Walter. > > Hi Walter. I'm not directly answering your question (mainly due to not using > isql myself, so I don't know the answer), but I can think of two alternative > ways. > > (1) (the more standard answer) Make sure your output file is in a fixed > length format, then create an external table for this file, transfer the data > using INSERT INTO "MyTable" SELECT * FROM and > then finally drop the external table. > > (2) Use qli rather than isql to transfer data directly using something > similar to this recipe: > https://www.ibphoenix.com/resources/documents/how_to/doc_42 > qli is an ancient part of InterBase, probably used before SQL was invented, > you can find it in the same directory as isql. > > I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5), but it > worked nicely when I needed it. Though I don't think qli has been updated > for the last few Firebird versions, so maybe it doesn't work anymore. And you > may have a hard time finding information about it beyond the document > referred to above. Though I suppose: > > ready "Database1" as src; > ready "Database2" as trg; > trg."MyTable" = src."MyTable"; > > doesn't need much explanation and should be simple for you to try on a test > database. I expect it to replace the target rather than add to it (though I > don't know), but the one time I used qli, I think I was transferring to an > empty table. For what it's worth, qli won't magically delete existing records. But it probably won't work either Cheers, Ann > > >
