Re: [firebird-support] Query help

[Kjell Rilbe]

Den 2012-07-24 03:14 skrev Leyne, Sean såhär:
>
> Kjell,
>
> > > Trying to figure out a way to do a search/query with decent 
> performance.
> > >
> > > Table "Master" contains roughly 100 million records and table "Detail"
> > > contains roughly 200 million records.
> >
> > > Unfortunately, this will cause a natural scan of the 100 million
> > > record "Master" table, and roughly 200 million subselects...
> > >
> > > Can anyone figure out a way to avoid a full table scan?
>
> The best solution that I have come up with is:
>
> SELECT M.*
> FROM (
> SELECT D."Master" as M_ID, SUM( CASE WHEN (D."Removed" IS NULL) THEN 1 
> ELSE 0 END) as NotCurrentCount
> FROM "Detail" D
> GROUP BY 1
> ) T
> JOIN "Master" M ON M."ID" = T.M_ID
> WHERE
> T.NotCurrentCount = 0
>
> This does require a natural scan of the Detail table*, but it will 
> avoid the 200 million sub-selects; which I suspect will mean a much 
> faster performance.
>

Good thinking! Thanks! :-) I actually just need the id:s of the master 
records, so no real need to join the master at all. Why didn't I think 
of that before?

Kjell

-- 
--
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



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





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
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:
http://docs.yahoo.com/info/terms/

[firebird-support] Re: table locked from excel?

[crizz11]

gr8 thanks so much for your quick response, i can also see options in the ODBC 
settings to make this connection read only etc

cheers
Chris
--- In firebird-support@yahoogroups.com, Helen Borrie  wrote:
>
> At 11:22 AM 24/07/2012, crizz11 wrote:
> >If I access a table in a firebird database from excel using ODBC is that 
> >table automatically in a locked state until i quit excel?
> 
> No. Firebird doesn't lock tables the way you are used to with Access, for 
> example.  Firebird employs what is known as "optimistic locking", whose exact 
> behaviour is determined by the way the enclosing transaction is configured. 
> 
> What you will have is a transaction started through the ODBC interface.  The 
> isolation level of that transaction determines how stable your application's 
> view will remain throughout the transaction and the behaviour when two or 
> more transactions want to modify the same row.  SNAPSHOT isolation (which I 
> think is the default for the Firebird ODBC driver) means that Excel continues 
> to see the set as it was at the start of the transaction.  READ COMMITTED 
> isolation means that Excel can refresh the set to get an updated view of what 
> other transactions have committed since its own transaction started.
> 
> If another transaction has a pending update on a row in the set that Excel is 
> viewing, a lock conflict occurs and Excel won't be able to update that row.  
> If Excel has an update pending on a row in that set, other transactions won't 
> be able to update that row.
>  
> ./hb
>

RE: [firebird-support] Query help

[Leyne, Sean]

Kjell,

> > Trying to figure out a way to do a search/query with decent performance.
> >
> > Table "Master" contains roughly 100 million records and table "Detail"
> > contains roughly 200 million records.
> 
> > Unfortunately, this will cause a natural scan of the 100 million
> > record "Master" table, and roughly 200 million subselects...
> >
> > Can anyone figure out a way to avoid a full table scan?

The best solution that I have come up with is:

SELECT M.*
FROM (
SELECT D."Master" as M_ID, SUM( CASE WHEN (D."Removed" IS NULL) THEN 1 
ELSE 0 END) as NotCurrentCount
FROM "Detail" D
GROUP BY 1
  ) T
  JOIN "Master" M ON M."ID" = T.M_ID
WHERE
  T.NotCurrentCount = 0

This does require a natural scan of the Detail table*, but it will avoid the 
200 million sub-selects; which I suspect will mean a much faster performance.


Sean

* you will want to force a natural scan by changing:
D."Master" as M_ID
to read:
D."Master"+0 as M_ID
if necessary

Re: [firebird-support] table locked from excel?

[Helen Borrie]

At 11:22 AM 24/07/2012, crizz11 wrote:
>If I access a table in a firebird database from excel using ODBC is that table 
>automatically in a locked state until i quit excel?

No. Firebird doesn't lock tables the way you are used to with Access, for 
example.  Firebird employs what is known as "optimistic locking", whose exact 
behaviour is determined by the way the enclosing transaction is configured. 

What you will have is a transaction started through the ODBC interface.  The 
isolation level of that transaction determines how stable your application's 
view will remain throughout the transaction and the behaviour when two or more 
transactions want to modify the same row.  SNAPSHOT isolation (which I think is 
the default for the Firebird ODBC driver) means that Excel continues to see the 
set as it was at the start of the transaction.  READ COMMITTED isolation means 
that Excel can refresh the set to get an updated view of what other 
transactions have committed since its own transaction started.

If another transaction has a pending update on a row in the set that Excel is 
viewing, a lock conflict occurs and Excel won't be able to update that row.  If 
Excel has an update pending on a row in that set, other transactions won't be 
able to update that row.
 
./hb

[firebird-support] table locked from excel?

[crizz11]

If I access a table in a firebird database from excel using ODBC is that table 
automatically in a locked state until i quit excel?

thanks

Re: [firebird-support] Query help

[Kjell Rilbe]

Den 2012-07-22 21:59 skrev Kjell Rilbe såhär:
>
> Trying to figure out a way to do a search/query with decent performance.
>
> Table "Master" contains roughly 100 million records and table "Detail"
> contains roughly 200 million records.
>
> Both have a bigint "Id" primary key and "Detail"."Master" links to
> "Master"."Id" in a regular master-detail relationship. "Detail"."Master"
> is indexed, but there's no FK constraint (never mind why).
>
> "Detail"."Removed" TIMESTAMP is nullable and NOT indexed.
> "Detail"."RemovedBy" bigint is nullable (FK to a third table) and IS
> indexed.
>
> If a detail has been removed, "Removed" is set to the time of removal
> and "RemovedBy" is set to the id of a record containing data about the
> removal, e.g. who did it and so forth.
>
> Business logic says that if a master has any details at all, at least
> one of them should be current, i.e. NOT be removed. So, at least one
> detail should have "Removed" and "RemovedBy" set to null.
>
> I've found a breech of this rule in my data and I want to find if there
> are more.
>

Having solved that (after 9 hours query execution), I'm thinking "How 
can I prevent this from happening again?"

So, could this have been prevented using some kind of DB constraint?
I.e. Make it impossible to remove a details without also adding a 
current one...?

The O/R mapper probably can't be guaranteed to issue the addition of a 
current detail (insert) before issuing the removal of the old one 
(update...set "Removed" and "RemovedBy" to non-null).

Kjell

-- 
--
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



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





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
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:
http://docs.yahoo.com/info/terms/

Re: RES: SV: [firebird-support] FB on Linux installation question

[jugglingjester]

Kernel Version 2.6.32-279 at the time of testing
FileSystem is ext4.

May be useful for you: my notes on installing, we've passsed them to a customer 
for replicate the installation.

installation source centos 6.3 minimal iso

Language: english 
Keyboard: fitting to your needs
Storage: Basic
hostname: fitting to your needs
Network
  eth0
  connect automatically
  IPv4
Static configuration, fitting to your network
  IPv6
Ignore
any other NICs are ignored 

Disk
  Replace all
  Review and modify if necessary
  BootLoader in device root
Set root password
Install Packages
reboot req'ed
login
disable selinux
in /etc/selinux/config
change 
 SELINUX=enforcing
to
 SELINUX=disabled
disable firewall 
[root@centos db]# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
[root@centos db]# service iptables stop
iptables: Flushing firewall rules: [  OK  ]
iptables: Setting chains to policy ACCEPT: filter  [  OK  ]
iptables: Unloading modules:   [  OK  ]
[root@centos db]# chkconfig iptables off
[root@centos db]#

also for ipv6
sync, reboot

first update:
#yum check-update
#yum update, reboot as there were kernel updates
#yum install xinetd
#yum install mc
preparing for firebird installation, using no other repo, as there is the 
likely chance to break either yum or, in worst case the system, due to 
different policies, see http://wiki.centos.org/AdditionalResources/Repositories/
#yum install lynx
#mkdir /root/install
#lynx www.firebirdsql.org, look for download, fetch the FirebirdCS...tar.gz,
 download and save to disk
 quit lynx
#cd /root/install
#tar xvzf FirebirdCS...tar.gz
#cd Fire should bring you to the freshly created dir
#./install.sh

.
.
Please enter new Password for SYSDBA user: masterkey

first test, local only
#/opt/firebird/bin/isql
SQL>create database "dummy.fdb";
SQL>commit;
SQL>exit;
#ls -l 
-rw-rw   1 root root 667648 Jul 17 11:27 dummy.fdb
setting database user and file permissions
#chown firebird: dummy.fdb
#ls -l
-rw-rw 1 firebird firebird 667648 Jul 17 11:27 dummy.fdb

First test from external...

hth, Marcus

--- In firebird-support@yahoogroups.com, "Fabiano"  wrote:
>
> What are the file system used?
> 
> What the kernel version of CentOS 6.3?
> 
> Thanks
> 
>  
> 
> De: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] Em nome de jugglingjester
> Enviada em: segunda-feira, 23 de julho de 2012 13:42
> Para: firebird-support@yahoogroups.com
> Assunto: Re: SV: [firebird-support] FB on Linux installation question
> 
>  
> 
>   
> 
> 
> 
> Hej, 
> 
> i've done a little bit of connection and load testing the last days.
> 
> Used an old HP Compaq 6910p, 2 Cores, 4 GB RAM, 4 GB SWAP as server, running
> on CEntOS 6.3. Installed with the minimal ISO, using only the CEntOS
> repository.
> Installed Firebird 2.5.1 from the .tar.gz, not the rpm. 
> 
> Our developers have provided me with a testing tool to open n connections
> and start a query in each connection. 
> 
> 500 Connections+ from 4 client, no error at all. Peaking at roundabout
> 750-800 connections the "server" wasn't able to response due to massive
> swapping. Less than 5 % RAM and 5 % swap free, left the clients and the
> server for an hour (went for lunch :-) in that condition - not an error at
> all. 
> 
> hth, Marcus
> 
> --- In firebird-support@yahoogroups.com
>  , Poul Dige  wrote:
> >
> > Hi Fabiano,
> > 
> > We tried 2.5.1 SC on Ubuntu 11. We had quite strange experience with the
> FB-server constantly crashing (every 20 minutes or so) with anything from
> 40-150 connections, AFAIR. No data corruption, but lots of annoyed
> customers. We switched to W2k8 and the problem was gone. I'd love to find
> out what went wrong but we were in no position to "experiment" as it was
> production environment, so it just had to work. Unfortunately, as I'd prefer
> to run it under Linux.
> > 
> > I don't say that you will experience the same, but do be aware if it
> happens!
> > 
> > Best regards
> > Poul
> > 
> > 
> > 
> > Fra: firebird-support@yahoogroups.com
> 
> [mailto:firebird-support@yahoogroups.com
>  ] På vegne af Fabiano
> > Sendt: 23. juli 2012 15:17
> > Til: firebird-support@yahoogroups.com
>  
> > Emne: [firebird-support] FB on Linux installation question
> > 
> > 
> > 
> > Hi all.
> > 
> > A new customer Will use a Linux machine to run the Firebird server.
> Actually
> > he will be the first customer to do that.
> > 
> > I have read that (at last with FB 1.5 to 2.0) the linux kernel need to be
> > 2.4.x to run Classic with a lot of connections.
> > 
> > This issue also occurs on FB 2.5.1 Classic?
> > 
> > Another tip is that writes on ext4 file system is slower than ext3 if
> > 'barrier' was ON. I can figure out t

RES: SV: [firebird-support] FB on Linux installation question

[Fabiano]

What are the file system used?

What the kernel version of CentOS 6.3?

Thanks

 

De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de jugglingjester
Enviada em: segunda-feira, 23 de julho de 2012 13:42
Para: firebird-support@yahoogroups.com
Assunto: Re: SV: [firebird-support] FB on Linux installation question

 

  



Hej, 

i've done a little bit of connection and load testing the last days.

Used an old HP Compaq 6910p, 2 Cores, 4 GB RAM, 4 GB SWAP as server, running
on CEntOS 6.3. Installed with the minimal ISO, using only the CEntOS
repository.
Installed Firebird 2.5.1 from the .tar.gz, not the rpm. 

Our developers have provided me with a testing tool to open n connections
and start a query in each connection. 

500 Connections+ from 4 client, no error at all. Peaking at roundabout
750-800 connections the "server" wasn't able to response due to massive
swapping. Less than 5 % RAM and 5 % swap free, left the clients and the
server for an hour (went for lunch :-) in that condition - not an error at
all. 

hth, Marcus

--- In firebird-support@yahoogroups.com
 , Poul Dige  wrote:
>
> Hi Fabiano,
> 
> We tried 2.5.1 SC on Ubuntu 11. We had quite strange experience with the
FB-server constantly crashing (every 20 minutes or so) with anything from
40-150 connections, AFAIR. No data corruption, but lots of annoyed
customers. We switched to W2k8 and the problem was gone. I'd love to find
out what went wrong but we were in no position to "experiment" as it was
production environment, so it just had to work. Unfortunately, as I'd prefer
to run it under Linux.
> 
> I don't say that you will experience the same, but do be aware if it
happens!
> 
> Best regards
> Poul
> 
> 
> 
> Fra: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com
 ] På vegne af Fabiano
> Sendt: 23. juli 2012 15:17
> Til: firebird-support@yahoogroups.com
 
> Emne: [firebird-support] FB on Linux installation question
> 
> 
> 
> Hi all.
> 
> A new customer Will use a Linux machine to run the Firebird server.
Actually
> he will be the first customer to do that.
> 
> I have read that (at last with FB 1.5 to 2.0) the linux kernel need to be
> 2.4.x to run Classic with a lot of connections.
> 
> This issue also occurs on FB 2.5.1 Classic?
> 
> Another tip is that writes on ext4 file system is slower than ext3 if
> 'barrier' was ON. I can figure out the best file system to this customer.
> Ext4 with/without barrier or ext3? The best for me is stability, I don't
> want to easily corrupt my database on a fast partition. (power down
> problems, etc).
> 
> What are the best fast/secure balanced partition type?
> 
> And finally - forced writes. I read that this configuration works on Linux
> with FB 2.5.x. What is the best configuration?
> 
> It someone have any tips of software installation on linux and maybe
> hardware please fells free to answer.
> 
> Thanks, Fabiano.
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> [Non-text portions of this message have been removed]
>





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

RES: [firebird-support] FB on Linux installation question

[Fabiano]

What the file system used?

What are the kernel version?

Thanks

 

De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de Poul Dige
Enviada em: segunda-feira, 23 de julho de 2012 11:18
Para: firebird-support@yahoogroups.com
Assunto: SV: [firebird-support] FB on Linux installation question

 

  

Hi Fabiano,

We tried 2.5.1 SC on Ubuntu 11. We had quite strange experience with the
FB-server constantly crashing (every 20 minutes or so) with anything from
40-150 connections, AFAIR. No data corruption, but lots of annoyed
customers. We switched to W2k8 and the problem was gone. I'd love to find
out what went wrong but we were in no position to "experiment" as it was
production environment, so it just had to work. Unfortunately, as I'd prefer
to run it under Linux.

I don't say that you will experience the same, but do be aware if it
happens!

Best regards
Poul

Fra: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com
 ] På vegne af Fabiano
Sendt: 23. juli 2012 15:17
Til: firebird-support@yahoogroups.com
 
Emne: [firebird-support] FB on Linux installation question

Hi all.

A new customer Will use a Linux machine to run the Firebird server. Actually
he will be the first customer to do that.

I have read that (at last with FB 1.5 to 2.0) the linux kernel need to be
2.4.x to run Classic with a lot of connections.

This issue also occurs on FB 2.5.1 Classic?

Another tip is that writes on ext4 file system is slower than ext3 if
'barrier' was ON. I can figure out the best file system to this customer.
Ext4 with/without barrier or ext3? The best for me is stability, I don't
want to easily corrupt my database on a fast partition. (power down
problems, etc).

What are the best fast/secure balanced partition type?

And finally - forced writes. I read that this configuration works on Linux
with FB 2.5.x. What is the best configuration?

It someone have any tips of software installation on linux and maybe
hardware please fells free to answer.

Thanks, Fabiano.

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

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





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

Re: SV: [firebird-support] FB on Linux installation question

[jugglingjester]

Hej, 

i've done a little bit of connection and load testing the last days.

Used an old HP Compaq 6910p, 2 Cores, 4 GB RAM, 4 GB SWAP as server, running on 
CEntOS 6.3. Installed with the minimal ISO, using only the CEntOS repository.
Installed Firebird 2.5.1 from the .tar.gz, not the rpm. 

Our developers have provided me with a testing tool to open n connections and 
start a query in each connection. 

500 Connections+ from 4 client, no error at all. Peaking at roundabout 750-800 
connections the "server" wasn't able to response due to massive swapping. Less 
than 5 % RAM and 5 % swap free, left the clients and the server for an hour 
(went for lunch :-) in that condition - not an error at all. 

hth, Marcus

--- In firebird-support@yahoogroups.com, Poul Dige  wrote:
>
> Hi Fabiano,
> 
> We tried 2.5.1 SC on Ubuntu 11. We had quite strange experience with the 
> FB-server constantly crashing (every 20 minutes or so) with anything from 
> 40-150 connections, AFAIR. No data corruption, but lots of annoyed customers. 
> We switched to W2k8 and the problem was gone. I'd love to find out what went 
> wrong but we were in no position to "experiment" as it was production 
> environment, so it just had to work. Unfortunately, as I'd prefer to run it 
> under Linux.
> 
> I don't say that you will experience the same, but do be aware if it happens!
> 
> Best regards
> Poul
> 
> 
> 
> Fra: firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com] På vegne af Fabiano
> Sendt: 23. juli 2012 15:17
> Til: firebird-support@yahoogroups.com
> Emne: [firebird-support] FB on Linux installation question
> 
> 
> 
> Hi all.
> 
> A new customer Will use a Linux machine to run the Firebird server. Actually
> he will be the first customer to do that.
> 
> I have read that (at last with FB 1.5 to 2.0) the linux kernel need to be
> 2.4.x to run Classic with a lot of connections.
> 
> This issue also occurs on FB 2.5.1 Classic?
> 
> Another tip is that writes on ext4 file system is slower than ext3 if
> 'barrier' was ON. I can figure out the best file system to this customer.
> Ext4 with/without barrier or ext3? The best for me is stability, I don't
> want to easily corrupt my database on a fast partition. (power down
> problems, etc).
> 
> What are the best fast/secure balanced partition type?
> 
> And finally - forced writes. I read that this configuration works on Linux
> with FB 2.5.x. What is the best configuration?
> 
> It someone have any tips of software installation on linux and maybe
> hardware please fells free to answer.
> 
> Thanks, Fabiano.
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> [Non-text portions of this message have been removed]
>

SV: [firebird-support] FB on Linux installation question

[Poul Dige]

Hi Fabiano,

We tried 2.5.1 SC on Ubuntu 11. We had quite strange experience with the 
FB-server constantly crashing (every 20 minutes or so) with anything from 
40-150 connections, AFAIR. No data corruption, but lots of annoyed customers. 
We switched to W2k8 and the problem was gone. I'd love to find out what went 
wrong but we were in no position to "experiment" as it was production 
environment, so it just had to work. Unfortunately, as I'd prefer to run it 
under Linux.

I don't say that you will experience the same, but do be aware if it happens!

Best regards
Poul



Fra: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
På vegne af Fabiano
Sendt: 23. juli 2012 15:17
Til: firebird-support@yahoogroups.com
Emne: [firebird-support] FB on Linux installation question



Hi all.

A new customer Will use a Linux machine to run the Firebird server. Actually
he will be the first customer to do that.

I have read that (at last with FB 1.5 to 2.0) the linux kernel need to be
2.4.x to run Classic with a lot of connections.

This issue also occurs on FB 2.5.1 Classic?

Another tip is that writes on ext4 file system is slower than ext3 if
'barrier' was ON. I can figure out the best file system to this customer.
Ext4 with/without barrier or ext3? The best for me is stability, I don't
want to easily corrupt my database on a fast partition. (power down
problems, etc).

What are the best fast/secure balanced partition type?

And finally - forced writes. I read that this configuration works on Linux
with FB 2.5.x. What is the best configuration?

It someone have any tips of software installation on linux and maybe
hardware please fells free to answer.

Thanks, Fabiano.

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



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

[firebird-support] FB on Linux installation question

[Fabiano]

Hi all.

 

A new customer Will use a Linux machine to run the Firebird server. Actually
he will be the first customer to do that. 

I have read that (at last with FB 1.5 to 2.0) the linux kernel need to be
2.4.x to run Classic with a lot of connections. 

This issue also occurs on FB 2.5.1 Classic?

 

Another tip is that writes on ext4 file system is slower than ext3 if
'barrier' was ON. I can figure out the best file system to this customer.
Ext4 with/without barrier or ext3? The best for me is stability, I don't
want to easily corrupt my database on a fast partition. (power down
problems, etc).

 

What are the best fast/secure balanced partition type?

 

And finally - forced writes. I read that this configuration works on Linux
with FB 2.5.x. What is the best configuration?

 

It someone have any tips of software installation on linux and maybe
hardware please fells free to answer.

Thanks, Fabiano.

 



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

Re: [firebird-support] Dynamic Input Parameter in Stored Procedure

[Thomas Steinmaurer]

> Is this possible to create stored proc with dynamic parameter as an input?
>
> For example, I have a table like this :
>
> id value
> == =
> 1 'A'
> 2 'B'
> 3 'C'
> 4 'D'
> 5 'E'
>
> How to create stored proc to returns values from above table but with dynamic 
> id
> as an input?
>
> For ex, how to return values, if with id in (1,2) or id in (1,2,5), etc.
>
> In select statement, it will equal to statement like this :
> select value from table where id in (1,2) or
> select value from table where id in (1,2,5)

I see two ways here:

1) As a VARCHAR input parameter in combination with using [FOR] EXECUTE 
STATEMENT 'SELECT ... WHERE ' || string || '... INTO ...'

2) By using a global temporary table (GTT): Insert the requested IDs 
into a GTT outside the SP and then JOIN the base table with the GTT in 
the SP.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

RE: [firebird-support] Dynamic Input Parameter in Stored Procedure

[Svein Erling Tysvær]

>Hi all,
>
>Is this possible to create stored proc with dynamic parameter as an input?
>
>For example, I have a table like this :
>
>id value
>== =
>1 'A'
>2 'B'
>3 'C'
>4 'D'
>5 'E'
>
>How to create stored proc to returns values from above table but with dynamic 
>id as an input?
>
>For ex, how to return values, if with id in (1,2) or id in (1,2,5), etc.
>
>In select statement, it will equal to statement like this :
>select value from table where id in (1,2) or select value from table where id 
>in (1,2,5)

If you mean a dynamic number of parameters, then no, this is not possible to do 
directly. Of course, in a stored procedure you could take one VARCHAR as an 
input parameter and then split up and check individual parts in a loop, e.g. 
something like

I = 1;
iLength = length(MyVarCharParameter
While (I <= ilength) do
begin
  I2 = cast(substring(MyVarCharParameter) from I for 1) as Integer;
  FOR SELECT Value FROM MyTable WHERE ID = I2 INTO :MyOutputParameter do
SUSPEND;
End

HTH,
Set

[firebird-support] Dynamic Input Parameter in Stored Procedure

[trskopo]

Hi all,

Is this possible to create stored proc with dynamic parameter as an input?

For example, I have a table like this :

id value
== =
1 'A'
2 'B'
3 'C'
4 'D'
5 'E'

How to create stored proc to returns values from above table but with dynamic id
as an input?

For ex, how to return values, if with id in (1,2) or id in (1,2,5), etc.

In select statement, it will equal to statement like this :
select value from table where id in (1,2) or
select value from table where id in (1,2,5)

Thanks in advance,
Best regards,
incendio.

[firebird-support] Dynamic Parameter in Stored Procedur

[trskopo]

Hi all,

Is this possible to create stored proc with dynamic parameter as an input?

For example, I have a table like this :

id value
== =
1  'A'
2  'B'
3  'C'
4  'D'
5  'E'

How to create stored proc to returns values from above table but with dynamic 
id as an input? 

For ex, how to return values, if with id in (1,2) or id in (1,2,5), etc.

In select statement, it will equal to statement like this :
select value from table where id in (1,2) or
select value from table where id in (1,2,5)

Thanks in advance,
Best regards,
incendio.

[firebird-support] Re: Firebird 1.5.x runs in Windows Server 2008 ?

[clockhat]

Hi daniel,

I am trying to run FB 1.5 on server 2008 64bit.
I installed it on server and runs ok.
when trying to connect pc's to server to run. it doesn't connect
it gives a win32 error

Are there any steps to perform to overcome this?

cas

--- In firebird-support@yahoogroups.com, Daniel Albuschat  
wrote:
>
> 2009/12/7 Jay 
> 
> >
> >
> > Hi,
> >
> > Can Firebird 1.5.x run in Windows Server 2008 ?
> >
> > TIA.
> >
> > Jay
> >
> 
> Yes, we're using Firebird 1.5 for over a year now on a Windows Server 2008
> 64-bit system.
> Works flawlessly.
> 
> Regards,
> 
> Daniel Albuschat
> 
> -- 
> eat(this); // delicious suicide
> 
> 
> [Non-text portions of this message have been removed]
>