Re: [firebird-support] Create view on external table

[Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support]]

Ok, Thanks   Martijn and Dimitry

Eric

Le lun. 6 janv. 2020 à 16:57, 'Martijn Tonies (Upscene Productions)'
m.ton...@upscene.com [firebird-support] 
a écrit :

>
>
> Eric,
>
> Yes, the optimizer can use an index with a WHERE on a VIEW, but only when
> the view is local, not when using EXECUTE STATEMENT on an external database.
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
>
> *From:* Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support]
> *Sent:* Monday, January 6, 2020 4:52 PM
> *To:* firebird-support@yahoogroups.com
> *Subject:* Re: [firebird-support] Create view on external table
>
>
>
> Index are used with a view when the source is a table and not if it's a
> stored proc
> This view CUST_VIEW use index : SELECT * FROM CUSTOMER => SELECT * FROM
> CUST_VIEW WHERE IDCUSTOMER = 1 , INDEX on IDCUSTOMER is used
> The view CUST_SPVIEW don't use index : select * FROM STORED_PROC_CUSTOMER
> =>  SELECT * FROM CUST_SPVIEW WHERE IDCUSTOMER = 1 , No index
>
> That why I need to select directly an external table in a view, instead a
> stored procedure
>
> Thanks
> Eric
>
>
>
> Le lun. 6 janv. 2020 à 16:35, Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support]  a écrit :
>
>>
>>
>> 06.01.2020 14:48, Eric Guéguiniat eric.gueguin...@gmail.com
>> [firebird-support] wrote:
>> > I can't use Stored procedure to do this, because index are not used
>> with it
>>
>> Index cannot be used with ES either, so there is no difference and no
>> need in a view.
>>
>> --
>> WBR, SD.
>>
> 
>

Re: [firebird-support] Create view on external table

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Eric,

Yes, the optimizer can use an index with a WHERE on a VIEW, but only when the 
view is local, not when using EXECUTE STATEMENT on an external database.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com



From: Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support] 
Sent: Monday, January 6, 2020 4:52 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Create view on external table




Index are used with a view when the source is a table and not if it's a stored 
proc 
This view CUST_VIEW use index : SELECT * FROM CUSTOMER => SELECT * FROM 
CUST_VIEW WHERE IDCUSTOMER = 1 , INDEX on IDCUSTOMER is used
The view CUST_SPVIEW don't use index : select * FROM STORED_PROC_CUSTOMER =>  
SELECT * FROM CUST_SPVIEW WHERE IDCUSTOMER = 1 , No index

That why I need to select directly an external table in a view, instead a 
stored procedure

Thanks
Eric



Le lun. 6 janv. 2020 à 16:35, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support]  a écrit :


  06.01.2020 14:48, Eric Guéguiniat eric.gueguin...@gmail.com 
[firebird-support] wrote:
  > I can't use Stored procedure to do this, because index are not used with it

  Index cannot be used with ES either, so there is no difference and no need in 
a view.

  -- 
  WBR, SD.

Re: [firebird-support] Create view on external table

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

06.01.2020 16:52, Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support] 
wrote:
> That why I need to select directly an external table in a view, instead a 
> stored procedure

   You cannot. Firebird doesn't support that.


-- 
   WBR, SD.

Re: [firebird-support] Need help upgrading win 7 to win 10

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 05/01/2020 15:55, John M buff...@gmail.com [firebird-support] wrote:
>> > My DB is very small at < 200MB.   Is it better to upgrade to FB 2.5.9
>> > before the win upgrade?  If so, what is the best way to do this upgrade?
>> >
>> > I have spent several hours already googling answers, but none I find
>> > seem to give me a clear path.
>>
>> Upgrading Windows is not really related to upgrading Firebird, which is
>> probably why you can't find a clear path by Googling for it. If you had
>> kept up-to-date with Firebird 2.5 updates, this question wouldn't even
>> have come up.
>>
>> I would recommend upgrading Firebird before the update, then if any
>> problems arise during the Windows upgrade, you know they are not related
>> to the Firebird upgrade. You could also do the reverse, but as 2.5.2 was
>> built long before Windows 10 even existed, it is better to upgrade to
>> 2.5.9 first.
>>
> ON this point.   I didn't find FB upgrade instructions.  When I did a 
> test upgrade of just FB on a Win 10 computer from 2.5.3 to 2.5.9, I 
> didn't see an "upgrade" option.   Should I assume I need to uninstall FB 
> 2.5.2 and then install FB 2.5.9?

Normally, between point releases, uninstalling the old version and 
installing the new version is sufficient, because the on-disk structure 
(ODS) is the same between those versions.

However, some of the older versions of 2.5 had issues with indexes that 
require an index rebuild to fix, and backing up and restoring the 
database is the way to do that.

>> When upgrading Firebird 2.5.2 to Firebird 2.5.9, it is probably a good
>> idea to use backup and restore, especially when your database was
>> created with 2.5.1.
>>
> I'm pretty sure I switched from IB to FB using 2.5.2.   If so, would 
> this step be  necessary? Is there a way to check the DB version?

The problem in the earlier 2.5 versions is with the same database 
version, so checking the database version will not provide sufficient 
information. However for sake of completeness, you can check the ODS 
version using:

gstat -h 

ODS 11.2 means it is a Firebird 2.5 database.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Create view on external table

[Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support]]

Index are used with a view when the source is a table and not if it's a
stored proc
This view CUST_VIEW use index : SELECT * FROM CUSTOMER => SELECT * FROM
CUST_VIEW WHERE IDCUSTOMER = 1 , INDEX on IDCUSTOMER is used
The view CUST_SPVIEW don't use index : select * FROM STORED_PROC_CUSTOMER
=>  SELECT * FROM CUST_SPVIEW WHERE IDCUSTOMER = 1 , No index

That why I need to select directly an external table in a view, instead a
stored procedure

Thanks
Eric



Le lun. 6 janv. 2020 à 16:35, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support]  a écrit :

>
>
> 06.01.2020 14:48, Eric Guéguiniat eric.gueguin...@gmail.com
> [firebird-support] wrote:
> > I can't use Stored procedure to do this, because index are not used with
> it
>
> Index cannot be used with ES either, so there is no difference and no need
> in a view.
>
> --
> WBR, SD.
> 
>

Re: [firebird-support] Create view on external table

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

06.01.2020 14:48, Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support] 
wrote:
> I can't use Stored procedure to do this, because index are not used with it

   Index cannot be used with ES either, so there is no difference and no need 
in a view.


-- 
   WBR, SD.

Re: [firebird-support] Remote backup using gbak

[Kevin Stanton kevin.stan...@rdb-solutions.com [firebird-support]]

Mark - thank you very much!

I do want the backup file on the server so all is good there.

I have found that the spaces do cause an issue.  Where I’m at right now is the 
following:

gbak -b -user sysdba -password masterkey -service win2012server:service_mgr   
c:/~wrk/lumber.fdb/common/lumber_remote.fbk -v -y 
"/common/lumber_remote.log”

My next task is to try an alias pointing to the path with spaces.  Most of my 
installs are set up with the same folder structure, “c:\fb databases\…"


Thanks again!

Kevin







 

> On Jan 3, 2020, at 8:28 AM, Mark Rotteveel m...@lawinegevaar.nl 
> [firebird-support]  wrote:
> 
> On 02/01/2020 16:31, Kevin Stanton kevin.stan...@rdb-solutions.com 
>  
> [firebird-support] wrote:
> > The error was:
> > 
> > Gbak error: I/O error for file “c:\fbThe system cannot find file specified.”
> > Gbak error: Error while trying to open file.
> > Gbak error: The system cannot find the file specified.
> 
> Try a path without a space in it, or define an alias and use that alias 
> to connect.
> 
> Also, I think you should use
> 
> gbak -b -user sysdba -password masterkey -service 
> win2012server:service_mgr "c:/fb databases/lumber/lumber.fdb" 
> "/common/lumber_remote.fbk"
> 
> That is:
> - remove win2012server from the database path, you already specified the 
> server in the `-service`
> - use forward slash instead of backslash
> 
> However, when you create the backup this way, it will create it on the 
> server. This doesn't seem to be what you want to do.
> 
> AFAIK, Firebird 1.5 doesn't have streaming backup through the service 
> manager, so you can't make a local backup directly to the client.
> 
> Mark
> -- 
> Mark Rotteveel
> 
> 



[Non-text portions of this message have been removed]

[firebird-support] Create view on external table

[Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support]]

Hello,

Is it possible to create a view on external table ?
I need to replace a table of the DB by an external table. Like this I don't
need to modify my stored procedures
I can't use Stored procedure to do this, because index are not used with it

I tried to do this, but it's not correct :

CREATE OR ALTER VIEW TEST
AS
EXECUTE statement 'SELECT * FROM TEST'
ON EXTERNAL DATA SOURCE 'c:\mtdb.fdb' as USER 'SYSDBA' PASSWORD 'masterkey';

Thank you
Eric