[firebird-support] Doubt about TempCacheLimit parameter...

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

Firebird ver 2.5.7.27050 64 bitsSuperserverWindows Server 2012R2

What is the unit of TempCacheLimit?
Thanks in advance

Re: [firebird-support] Bulk upload options

[fabia...@itbizolutions.com.au [firebird-support]]

Just found this article https://www.arbinada.com/en/node/1425


Firebird: bulk insert and bulk export
Submitted by Serguei_Tarassov on Fri, 02/08/2013 - 16:50
Firebird doesn't support INSERT BULK command or bcp.exe utility. But the 
external tables can be a good replacement. Simple example is below.

Check that firebird.conf file allow to create external tables. Write or 
uncomment line like

ExternalFileAccess = Full

Create external table


CREATE TABLE BCP_DATA EXTERNAL FILE 'с:\temp\temp.txt' (
  ID CHAR(10) NOT NULL,
  NAME CHAR(100),
  CRLF CHAR(2)  -- CHAR(1) for Linux
);
COMMIT;
Bulk export from some Firebird table (ensure that temp.txt file is empty):


INSERT INTO BCP_DATA(ID, NAME, CRLF)
SELECT ID, NAME, ascii_char(13) || ascii_char(10) FROM MY_REAL_TABLE;
As result, you have temp.txt file in "fixed length columns" format

Bulk import (ensure that temp.txt has data in fixed length format):


INSERT INTO MY_REAL_TABLE(ID, NAME)
SELECT ID, NAME FROM BCP_DATA;
I think this solutions will do




- Original Message - 
From: "fabia...@itbizolutions.com.au [firebird-support]" 

To: 
Sent: Wednesday, May 16, 2018 12:43 PM
Subject: [firebird-support] Bulk upload options


>I am looking for the fastest way to run a bulk insert.
> I believe one way would be using isql.exe , I need to plan a solution to 
> upload into a single table around 500 million records. Does anyone have 
> experience with bulk updates via isql? Would it be X times faster than 
> using a normal application inserting the records inside a begin 
> transaction and commit at the end?
> The issue is I have a remote database (not a FB database) and my options 
> are reading the remote DB and writing into FB one record at the time with 
> a loop, or asking the remote DB to dump the table into a txt file, then 
> compressing the file with winrar or whatever compression, and then using 
> that file to execute a bulk upload to FB," This is an ongoing issue, is 
> not a one of, so I am trying to design the best performing solution, not a 
> one of trick.
> I know the volume of data will be around 250 Gb per run, hence that could 
> be compressed at the server non-FB and sent to the FB server via internet, 
> the compressed size would be probably just 5% of the uncompressed size as 
> it is all text, so we are talking about 12 Gb aprox, the only question is 
> "would then uploading the txt file into a table  be quicker than a line by 
> line insert into table?
>
>
>
>
> 

[firebird-support] Bulk upload options

[fabia...@itbizolutions.com.au [firebird-support]]

I am looking for the fastest way to run a bulk insert. 
 I believe one way would be using isql.exe , I need to plan a solution to 
upload into a single table around 500 million records. Does anyone have 
experience with bulk updates via isql? Would it be X times faster than using a 
normal application inserting the records inside a begin transaction and commit 
at the end?
 The issue is I have a remote database (not a FB database) and my options are 
reading the remote DB and writing into FB one record at the time with a loop, 
or asking the remote DB to dump the table into a txt file, then compressing the 
file with winrar or whatever compression, and then using that file to execute a 
bulk upload to FB," This is an ongoing issue, is not a one of, so I am trying 
to design the best performing solution, not a one of trick.
 I know the volume of data will be around 250 Gb per run, hence that could be 
compressed at the server non-FB and sent to the FB server via internet, the 
compressed size would be probably just 5% of the uncompressed size as it is all 
text, so we are talking about 12 Gb aprox, the only question is "would then 
uploading the txt file into a table  be quicker than a line by line insert into 
table?
 

 

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

 Thank you for your help. It was very helpful!


On Sunday, May 13, 2018, 12:50:26 AM CDT, Helen Borrie hele...@iinet.net.au 
[firebird-support]  wrote:  
 
     
Javier wrote:
>
> Ok, but how do I restore the statistics? Using "update" statements?

You do not "restore the statistics". The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index. Low value =
high selectivity = Good; high value = low selectivity = bad. The
value immediately after the call reflects the state of the index at
that point. So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.

After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again. Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete. Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.

The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh. That will tell you the
optimizer's choices based on the current selectivity and record count.

You can save that plan in a text file. If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one. That might or
might not work positively over time, of course.

On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.

Hth.
Helen


  #yiv1957919616 #yiv1957919616 -- #yiv1957919616ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1957919616 
#yiv1957919616ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1957919616 
#yiv1957919616ygrp-mkp #yiv1957919616hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv1957919616 #yiv1957919616ygrp-mkp #yiv1957919616ads 
{margin-bottom:10px;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad 
{padding:0 0;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad p 
{margin:0;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad a 
{color:#ff;text-decoration:none;}#yiv1957919616 #yiv1957919616ygrp-sponsor 
#yiv1957919616ygrp-lc {font-family:Arial;}#yiv1957919616 
#yiv1957919616ygrp-sponsor #yiv1957919616ygrp-lc #yiv1957919616hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1957919616 
#yiv1957919616ygrp-sponsor #yiv1957919616ygrp-lc .yiv1957919616ad 
{margin-bottom:10px;padding:0 0;}#yiv1957919616 #yiv1957919616actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1957919616 
#yiv1957919616activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1957919616
 #yiv1957919616activity span {font-weight:700;}#yiv1957919616 
#yiv1957919616activity span:first-child 
{text-transform:uppercase;}#yiv1957919616 #yiv1957919616activity span a 
{color:#5085b6;text-decoration:none;}#yiv1957919616 #yiv1957919616activity span 
span {color:#ff7900;}#yiv1957919616 #yiv1957919616activity span 
.yiv1957919616underline {text-decoration:underline;}#yiv1957919616 
.yiv1957919616attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv1957919616 .yiv1957919616attach div a 
{text-decoration:none;}#yiv1957919616 .yiv1957919616attach img 
{border:none;padding-right:5px;}#yiv1957919616 .yiv1957919616attach label 
{display:block;margin-bottom:5px;}#yiv1957919616 .yiv1957919616attach label a 
{text-decoration:none;}#yiv1957919616 blockquote {margin:0 0 0 
4px;}#yiv1957919616 .yiv1957919616bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1957919616 
.yiv1957919616bold a {text-decoration:none;}#yiv1957919616 dd.yiv1957919616last 
p a {font-family:Verdana;font-weight:700;}#yiv1957919616 dd.yiv1957919616last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1957919616 
dd.yiv1957919616last p span.yiv1957919616yshortcuts 
{margin-right:0;}#yiv1957919616 div.yiv1957919616attach-table div div a 
{text-decoration:none;}#yiv1957919616 div.yiv1957919616attach-table 
{width:400px;}#yiv1957919616 div.yiv1957919616file-title a, #yiv1957919616 
div.yiv1957919616file-title a:active, #yiv1957919616 
div.yiv1957919616file-title a:hover, #yiv1957919616 div.yiv1957919616file-title 
a:visited {text-decoration:none;}#yiv1957919616 div.yiv1957919616photo-title a, 

Re: [firebird-support] Re: Udr GenRows sample Pascal

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Thank you botch Norbert and Dimitry for your help

problem was with fill method
the method is to use move index [1] of string and casting or StrLCopy
or simplest possible this one which does not require any casting:

StrPLCopy(outMessage.ss, s, High(outMessage.ss))

regards,
Karol Bieniaszewski


From: liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Sent: Tuesday, May 15, 2018 11:19 AM
To: firebird-support@yahoogroups.com 
Subject: Re: Re: [firebird-support] Re: Udr GenRows sample Pascal

  
> It depends on information in IMessageMetadata.
> 
> > I have tried:
> > ss: Array[0..199] of Char;
> > ss: Array[0..199] of Byte;
> > ss: Array[0..199] of AnsiChar;
> 
> This is a right declaration for CHAR(200).
> 

OK 

> > ss: String[200];
> > 
> > with last string[200] i got result prefixed with size byte
> 
> Because short strings in Delphi are prefixed with length byte.
> 
> -- 
> WBR, SD.
> 

Yes i know that i only "show" that with this declaration i got something 
but with all above i got empty string
I will look into fill procedure maybe i miss something obvious

regards,
Karol Bieniaszewski

[firebird-support] Firebird ADO.NET: ExecuteNonQuery Returns -1 On Successful Insert

[blackfalconsoftw...@outlook.com [firebird-support]]

Hello.
 

 I am using the Firebird 2.5 Embedded Edition with the Firebird ADO.NET 
provider version 5.5 or 5.7.  I am not sure which, since it has been so long 
since I set up the references for my project and the assembly being used is not 
taken from the actual provider library that has all the files in it..
 

 In any event, during a test of my current application development I 
successfully inserted a record into a table using a Firebird stored procedure 
with the ADO.NET ExecuteNonQuery method, which should return a "records 
affected" count of "1".
 

 Instead, I am finding that the returned "records affected" count is "-1".
 

 I have never seen this before with all the other database engines I have 
worked with over the years.  Nonetheless, I believe I have seen this issue 
raised with Firebird ADO.NET before but some time ago.

 

 Does anyone have any idea as to what is causing this?
 

 Thank you...
 

 Steve Naidamast
 blackfalconsoftw...@outlook.com
 
 

[firebird-support] MON$Attachment privileges

[Rustam rusta...@ukr.net [firebird-support]]

> This break logic in our application.

Agree - I don't supply role on connection in my apps at all, but to allow user 
to view attachments I must do this. Also, I need a possibility to grant 
privileges to regular user for managing server users (like SYSDBA) - as i know 
this is possible, but much more overhead then for MON$ATTACHMENTS.


> Hi,
> 
> 
> thank you. I see that in FB3 this is the only way :(
> This break logic in our application. Regular user ca not have privileges to 
> change db structure but with RDB$Admin role he can :(
> I see that this is implemented only in FB4
> tracker.firebird.org/browse/CORE-2557
> 

Re: [firebird-support] MON$Attachment privileges

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
thank you. I see that in FB3 this is the only way :(This break logic in our 
application. Regular user ca not have privileges to change db structure but 
with RDB$Admin role he can :(I see that this is implemented only in 
FB4tracker.firebird.org/browse/CORE-2557

Regards,Karol Bieniaszewski
null

[firebird-support] MON$Attachment privileges

[Rustam rusta...@ukr.net [firebird-support]]

To do this you must follow this steps (for FB 2.5):


- grant role RDB$ADMIN to user in your working DB  
- user must login with RDB$ADMIN role into your DB.


After that he can access MON$ATTACHMENTS.

 


  Hi,
 
how can i grant select on MON$Attachements to normal user
that he/she can see all attachements not only self?
 
I use Firebird 3.
 
regards,
Karol Bieniaszewski

Re: Re: [firebird-support] Re: Udr GenRows sample Pascal

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

>It depends on information in IMessageMetadata.
> 
> > I have tried:
> > ss: Array[0..199] of Char;
> > ss: Array[0..199] of Byte;
> > ss: Array[0..199] of AnsiChar;
> 
>This is a right declaration for CHAR(200).
> 

OK 

> > ss: String[200];
> > 
> > with last string[200] i got result prefixed with size byte
> 
>Because short strings in Delphi are prefixed with length byte.
> 
> -- 
>WBR, SD.
> 

Yes i know that i only "show" that with this declaration i got something 
but with all above i got empty string
I will look into fill procedure maybe i miss something obvious

regards,
Karol Bieniaszewski

Re: [firebird-support] Re: Udr GenRows sample Pascal

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

15.05.2018 6:54, 'livius' liviusliv...@poczta.onet.pl [firebird-support] wrote:
> what kind of buffer?

   It depends on information in IMessageMetadata.

> I have tried:
> ss: Array[0..199] of Char;
> ss: Array[0..199] of Byte;
> ss: Array[0..199] of AnsiChar;

   This is a right declaration for CHAR(200).

> ss: String[200];
> 
> with last string[200] i got result prefixed with size byte

   Because short strings in Delphi are prefixed with length byte.

-- 
   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 and PySide

[alauren...@gmail.com [firebird-support]]

Hi,
 

 I want to connect a QT/Pyside Application to a firebird 3 server using ODBC 
but I get only "Error":
 app = QApplication(sys.argv)
 db = QtSql.QSqlDatabase.addDatabase('QODBC')
 db.setHostName('localhost')
 db.setDatabaseName('iNew.fdb')
 db.setUserName('SYSDBA')
 db.setPassword('masterkey')
 if not db.open():
 print('Error')
 
 sys.exit(1)
 

 So please, someone who use PySide/QT can tell me what am I doing wrong?
 

 Thank's a lot!
 

 

 ps: I can't use QIBASE

[firebird-support] MON$Attachment privileges

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
how can i grant select on MON$Attachements to normal user
that he/she can see all attachements not only self?
 
I use Firebird 3.
 
regards,
Karol Bieniaszewski