[firebird-support] Select stored in a table

[Bogdan Mihalache]

I have this Sql statement strored in a table :

Select sum(secunde_fact)  as cantitate  From agenda Where id_contract =
:lcIdContract and  id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura
And (agenda.data_factura between :ldDela And :ldPanala )   And facturabil =
1 and deleted = 0

 

I wanna used in a stored procedure, like this :

lcIdClient = id_client ;

lcIdContract   = id_contract ;

lcIdRamura = id_ramura ;

lcIdSubramura  = id_subramura ;

ldDela = lcDela ;

ldPanala   = lcPanala  ;

lnAn   = Extract (Year From ldDela) ;

lnLuna = Extract (Month From ldDela)  ;

For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4,
aa.int_start,

aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret,
aa.id_pret_um,

aa.felpret, aa.ispretfix, ac.id_subramura

From contracte_det_pret aa

Left Join nom_contracte_pret_um ab on aa.id_pret_um
= ab.id

Left Join nom_contracte_pretac on aa.id_criteriu_pret
= ac.id

Where aa.id_contract =  :lcIdContract And ac.id_subramura =
:lcIdSubramura

Into :um, :sql, :val1, :val2,  :val3, :val4, :int_start,

:int1, :int2, :int3, :int4, :lcIdCriteriuPret,
:lcIdPretUm,

:felpret, :ispretfix, :lcIdSubramura

   do

   begin

 if (lower(:felpret) = 'pretunitar')

  then

begin

  Execute Statement Sql Into :lnCantitate ;

  lnCantitate = coalesce(:lnCantitate, 0) ;

  valoare = lnCantitate *  val1 ;

  suspend;

end

 

 

I am using IBExpert as a tool to view my database. In debug mode it works
just fine. But if i hit play, i`m receivind an error, that he can`t fiind
column lcIdContract, at this line(when he is trying to execute the sql
statement) :Execute Statement Sql Into :lnCantitate ;

 

 

If i remove the variables(:lcIdContract, :lcIdRamura, etc.)  from the sql
statement, that is stored in the table, it`s working, but I really need to
keep them, and keep the sql in the table.  Does anyone has a solution to my
problem?

 

Bogdan MIHALACHE

Re: [firebird-support] Cannot connect to remote FB Server

[dennisktliew]

Hi marcus, 

 Thanks for your reply. Appreciate it.
 

 Yes it is able to ping, from server to client and vice versa.
 

 As for the routing information, i will try to get from the network 
administrator.
 

 Thanks.

RE: [firebird-support] Select stored in a table

[Svein Erling Tysvær]

>I have this Sql statement strored in a table :
>Select sum(secunde_fact)  as cantitate  From agenda Where id_contract = 
>:lcIdContract and  id_ramura = :lcIdRamura 
>And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela And 
>:ldPanala )   And facturabil = 1 and deleted = 0
>
>I wanna used in a stored procedure, like this :
>lcIdClient     = id_client ;
>        lcIdContract   = id_contract ;
>    lcIdRamura = id_ramura ;
>        lcIdSubramura  = id_subramura ;
>    ldDela = lcDela ;
>    ldPanala   = lcPanala  ;
>    lnAn   = Extract (Year From ldDela) ;
>    lnLuna = Extract (Month From ldDela)  ;
>    For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, 
>aa.int_start,
>    aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, 
> aa.id_pret_um,
>    aa.felpret, aa.ispretfix, ac.id_subramura
>    From contracte_det_pret aa
>    Left Join nom_contracte_pret_um ab on aa.id_pret_um = 
>ab.id
>    Left Join nom_contracte_pret    ac on aa.id_criteriu_pret   = 
>ac.id
>    Where aa.id_contract =  :lcIdContract And ac.id_subramura = 
>:lcIdSubramura
>    Into :um, :sql, :val1, :val2,  :val3, :val4, :int_start,
>    :int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm,
>    :felpret, :ispretfix, :lcIdSubramura
>   do
>       begin
> if (lower(:felpret) = 'pretunitar')
>  then
>    begin
>  Execute Statement Sql Into :lnCantitate ;
>  lnCantitate = coalesce(:lnCantitate, 0) ;
>      valoare = lnCantitate *  val1 ;
>  suspend;
>    end
>
>
>I am using IBExpert as a tool to view my database. In debug mode it works just 
>fine. But if i hit play, 
>i`m receivind an error, that he can`t fiind column lcIdContract, at this 
>line(when he is trying to 
>execute the sql statement) :    Execute Statement Sql Into :lnCantitate ;
>
>
>If i remove the variables(:lcIdContract, :lc IdRamura, etc.)  from the sql 
>statement, that is stored in 
>the table, it`s working, but I really need to keep them, and keep the sql in 
>the table.  Does anyone has 
>a solution to my problem?

Things are slightly more complex with parameters, Bogdan, though just slightly: 
You need to pass the parameters into your execute statement (think of execute 
statement as self contained, with no knowledge of things outside of itself), 
i.e.

EXECUTE STATEMENT (SQL) (lcIdContract, lcIdRamura, lcIdSubramura, ldDela, 
ldPanala) Into :lnCantitate ;

(take a look at www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html for 
syntax & examples)

HTH,
Set

Re: [firebird-support] Select stored in a table

[Virna Constantin]

:sql=replace(:sql,':lcIdContract',:lcIdContract);
:sql=replace(:sql,':lcIdRamura',:lcIdRamura);
...



On Tuesday, March 4, 2014 12:24 PM, Bogdan Mihalache 
 wrote:
 
 



I have this Sql statement strored in a table :
Select sum(secunde_fact)  as cantitate  From agenda Where id_contract = 
:lcIdContract and  id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura 
And (agenda.data_factura between :ldDela And :ldPanala )   And facturabil = 1 
and deleted = 0
 
I wanna used in a stored procedure, like this :
lcIdClient     = id_client ;
    lcIdContract   = id_contract ;
    lcIdRamura = id_ramura ;
    lcIdSubramura  = id_subramura ;
    ldDela = lcDela ;
    ldPanala   = lcPanala  ;
    lnAn   = Extract (Year From ldDela) ;
    lnLuna = Extract (Month From ldDela)  ;
    For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, 
aa.int_start,
    aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, 
aa.id_pret_um,
    aa.felpret, aa.ispretfix, ac.id_subramura
    From contracte_det_pret aa
    Left Join nom_contracte_pret_um ab on aa.id_pret_um = 
ab.id
    Left Join nom_contracte_pret    ac on aa.id_criteriu_pret   = 
ac.id
    Where aa.id_contract =  :lcIdContract And ac.id_subramura = 
:lcIdSubramura
    Into :um, :sql, :val1, :val2,  :val3, :val4, :int_start,
    :int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm,
    :felpret, :ispretfix, :lcIdSubramura
   do
   begin
 if (lower(:felpret) = 'pretunitar')
  then
    begin
  Execute Statement Sql Into :lnCantitate ;
  lnCantitate = coalesce(:lnCantitate, 0) ;
  valoare = lnCantitate *  val1 ;
  suspend;
    end
 
 
I am using IBExpert as a tool to view my database. In debug mode it works just 
fine. But if i hit play, i`m receivind an error, that he can`t fiind column 
lcIdContract, at this line(when he is trying to execute the sql statement) :    
Execute Statement Sql Into :lnCantitate ;
 
 
If i remove the variables(:lcIdContract, :lcIdRamura, etc.)  from the sql 
statement, that is stored in the table, it`s working, but I really need to keep 
them, and keep the sql in the table.  Does anyone has a solution to my problem?
 
Bogdan MIHALACHE

RE: [firebird-support] Select stored in a table

[Bogdan Mihalache]

Meanwhile i have found that link, but i was wondering if there is a way to
pass the parameters in a variable or something like that. 

I have made a new fiels in the table that stores the sql statement, and put
in there the variables like this :

-  lcIdContract := :lcIdContract, lcIdRamura := :lcIdRamura,
lcIdSubramura := :lcIdSubRamura, ldDela := :ldDela, ldPanala := :ldPanala

My problem is that there is more than one sql statement there, with
different parameters name and count. 

 

i`ve tried "execute statement (sql) (parametes)" since there it says that i
can use an other expresion (When a statement has parameters, it must be
placed in parentheses when EXECUTE STATEMENT is called, regardless whether
it is given directly as a string, as a variable name, or by another
expression.) but i think that i don`t really understant how this works.

 

As a question is there an aproximate way to use this : execute statement
(sql) (parametes) ? or something similar to it? Has anyone tried something
like this? What was your aproach?

 

Thank you verry much!

 

Bogdan

 

 

 

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvar
Sent: Tuesday, March 4, 2014 12:52 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Select stored in a table

 

  

>I have this Sql statement strored in a table :
>Select sum(secunde_fact)  as cantitate  From agenda Where id_contract =
:lcIdContract and  id_ramura = :lcIdRamura 
>And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela
And :ldPanala )   And facturabil = 1 and deleted = 0
>
>I wanna used in a stored procedure, like this :
> lcIdClient = id_client ;
>  lcIdContract   = id_contract ;
>lcIdRamura = id_ramura ;
>lcIdSubramura  = id_subramura ;
>ldDela = lcDela ;
>ldPanala   = lcPanala  ;
>lnAn   = Extract (Year From ldDela) ;
>lnLuna = Extract (Month From ldDela)  ;
>For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4,
aa.int_start,
>aa.int1, aa.int2, aa.int3, aa.int4,
aa.id_criteriu_pret, aa.id_pret_um,
>aa.felpret, aa.ispretfix, ac.id_subramura
>From contracte_det_pret aa
>Left Join nom_contracte_pret_um ab on aa.id_pret_um
= ab.id
>Left Join nom_contracte_pretac on aa.id_criteriu_pret
= ac.id
>Where aa.id_contract =  :lcIdContract And ac.id_subramura =
:lcIdSubramura
>Into :um, :sql, :val1, :val2,  :val3, :val4, :int_start,
>:int1, :int2, :int3, :int4, :lcIdCriteriuPret,
:lcIdPretUm,
>:felpret, :ispretfix, :lcIdSubramura
>   do
>   begin
> if (lower(:felpret) = 'pretunitar')
>  then
>begin
>  Execute Statement Sql Into :lnCantitate ;
>  lnCantitate = coalesce(:lnCantitate, 0) ;
>  valoare = lnCantitate *  val1 ;
>  suspend;
>end
>
>
>I am using IBExpert as a tool to view my database. In debug mode it works
just fine. But if i hit play, 
>i`m receivind an error, that he can`t fiind column lcIdContract, at this
line(when he is trying to 
>execute the sql statement) :Execute Statement Sql Into :lnCantitate ;
>
>
>If i remove the variables(:lcIdContract, :lc IdRamura, etc.)  from the sql
statement, that is stored in 
>the table, it`s working, but I really need to keep them, and keep the sql
in the table.  Does anyone has 
>a solution to my problem?

Things are slightly more complex with parameters, Bogdan, though just
slightly: You need to pass the parameters into your execute statement (think
of execute statement as self contained, with no knowledge of things outside
of itself), i.e.

EXECUTE STATEMENT (SQL) (lcIdContract, lcIdRamura, lcIdSubramura, ldDela,
ldPanala) Into :lnCantitate ;

(take a look at www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html
  for
syntax & examples)

HTH,
Set

RE: [firebird-support] Select stored in a table

[Bogdan Mihalache]

This works just fine.

 

Thank you!(multumesc!)

 

Bogdan

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] On Behalf Of Virna Constantin
Sent: Tuesday, March 4, 2014 1:03 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Select stored in a table

 

  

:sql=replace(:sql,':lcIdContract',:lcIdContract);

:sql=replace(:sql,':lcIdRamura',:lcIdRamura);

...

 

On Tuesday, March 4, 2014 12:24 PM, Bogdan Mihalache 
 wrote:

 

I have this Sql statement strored in a table :

Select sum(secunde_fact)  as cantitate  From agenda Where id_contract = 
:lcIdContract and  id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura 
And (agenda.data_factura between :ldDela And :ldPanala )   And facturabil = 1 
and deleted = 0

 

I wanna used in a stored procedure, like this :

lcIdClient = id_client ;

lcIdContract   = id_contract ;

lcIdRamura = id_ramura ;

lcIdSubramura  = id_subramura ;

ldDela = lcDela ;

ldPanala   = lcPanala  ;

lnAn   = Extract (Year From ldDela) ;

lnLuna = Extract (Month From ldDela)  ;

For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, 
aa.int_start,

aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, 
aa.id_pret_um,

aa.felpret, aa.ispretfix, ac.id_subramura

From contracte_det_pret aa

Left Join nom_contracte_pret_um ab on aa.id_pret_um = 
ab.id

Left Join nom_contracte_pretac on aa.id_criteriu_pret   = 
ac.id

Where aa.id_contract =  :lcIdContract And ac.id_subramura = 
:lcIdSubramura

Into :um, :sql, :val1, :val2,  :val3, :val4, :int_start,

:int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm,

:felpret, :ispretfix, :lcIdSubramura

   do

   begin

 if (lower(:felpret) = 'pretunitar')

  then

begin

  Execute Statement Sql Into :lnCantitate ;

  lnCantitate = coalesce(:lnCantitate, 0) ;

  valoare = lnCantitate *  val1 ;

  suspend;

end

 

 

I am using IBExpert as a tool to view my database. In debug mode it works just 
fine. But if i hit play, i`m receivind an error, that he can`t fiind column 
lcIdContract, at this line(when he is trying to execute the sql statement) :
Execute Statement Sql Into :lnCantitate ;

 

 

If i remove the variables(:lcIdContract, :lcIdRamura, etc.)  from the sql 
statement, that is stored in the table, it`s working, but I really need to keep 
them, and keep the sql in the table.  Does anyone has a solution to my problem?

 

Bogdan MIHALACHE

 

 





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

[firebird-support] Firebird trace and EXECUTE_STATEMENT_FINISH

[Svend Meyland Nicolaisen]

Hi

 

I am using the trace functionality of Firebird 2.5.2. Can anybody tell me
why EXECUTE_STATEMENT_FINISH only is logged for some statements. For most of
my statements I get the following sequence in the trace:

 

PREPARE_STATEMENT

EXECUTE_STATEMENT_START

CLOSE_CURSOR

FREE_STATEMENT

 

And occasionally I get:

 

PREPARE_STATEMENT

EXECUTE_STATEMENT_START

EXECUTE_STATEMENT_FINISH

CLOSE_CURSOR

FREE_STATEMENT

 

Kind regards,

Svend Meyland Nicolaisen

RE: [firebird-support] Firebird trace and EXECUTE_STATEMENT_FINISH

[Svend Meyland Nicolaisen]

Hi again

 

Sorry for the question. The answer is right there in the documentation at
ibphoenix. "Finish is only traced, if execution time of the logged statement
exceeds the value of this parameter".

 

/Svend

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svend Meyland
Nicolaisen
Sent: Tuesday, March 04, 2014 2:46 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Firebird trace and EXECUTE_STATEMENT_FINISH

 

  

Hi

 

I am using the trace functionality of Firebird 2.5.2. Can anybody tell me
why EXECUTE_STATEMENT_FINISH only is logged for some statements. For most of
my statements I get the following sequence in the trace:

 

PREPARE_STATEMENT

EXECUTE_STATEMENT_START

CLOSE_CURSOR

FREE_STATEMENT

 

And occasionally I get:

 

PREPARE_STATEMENT

EXECUTE_STATEMENT_START

EXECUTE_STATEMENT_FINISH

CLOSE_CURSOR

FREE_STATEMENT

 

Kind regards,

Svend Meyland Nicolaisen

[firebird-support] 32 or 64-bit

[Freddy Holst Christensen]

Hey All


I have installed Firebird 2.5 (x64).

In the installation directory:   c:\program Files\Firebird\Firebird_2.5 
there is a file fbclient.dll
And in C:\Program Files\Firebird\Firebird\WOW64 there is also af 
fbclient.dll, about 1,5 larger than the first.


If I create a 32-bit, and use the first fbclient.dll, it works fine

But when I try to create a 64-bit program, I get problems.
When I use the first fbclient.dll, I get eroors like Access Violation
And the second, will not load.


Any Ideas?

I use C++ Builder XE5, IBDac from DevArt-  c:\Deavart.com, and Windows7 
64-bit


Regrads
Freddy

Odp: [firebird-support] 32 or 64-bit

[liviusliv...@poczta.onet.pl]

Hi,

I don't know IBDAC but if it work as dbexpress you need not only 64 bit 
Firebird client but also 64 bit driver

Regards,
Karol Bieniaszewski

- Reply message -
Od: "Freddy Holst Christensen" 
Do: 
Temat: [firebird-support] 32 or 64-bit
Data: wt., mar 4, 2014 15:23
Hey All



I have installed Firebird 2.5 (x64).



In the installation directory:   c:\program Files\Firebird\Firebird_2.5 

there is a file fbclient.dll

And in C:\Program Files\Firebird\Firebird\WOW64 there is also af 

fbclient.dll, about 1,5 larger than the first.



If I create a 32-bit, and use the first fbclient.dll, it works fine



But when I try to create a 64-bit program, I get problems.

When I use the first fbclient.dll, I get eroors like Access Violation

And the second, will not load.



Any Ideas?



I use C++ Builder XE5, IBDac from DevArt-  c:\Deavart.com, and Windows7 

64-bit



Regrads

Freddy

Re: [firebird-support] 32 or 64-bit

[Mark Rotteveel]

On 4-3-2014 15:23, Freddy Holst Christensen wrote:
> Hey All
>
>
> I have installed Firebird 2.5 (x64).
>
> In the installation directory:   c:\program Files\Firebird\Firebird_2.5
> there is a file fbclient.dll
> And in C:\Program Files\Firebird\Firebird\WOW64 there is also af
> fbclient.dll, about 1,5 larger than the first.
>
> If I create a 32-bit, and use the first fbclient.dll, it works fine

I'd expect that to fail as that fbclient.dll is 64 bit (the one in WOW64 
is 32 bit!). Most likely it is loading a 32 bit fbclient.dll from 
another location.

> But when I try to create a 64-bit program, I get problems.
> When I use the first fbclient.dll, I get eroors like Access Violation
> And the second, will not load.

As the second is 32 bit it will not load for a 64 bit program.

> I use C++ Builder XE5, IBDac from DevArt-  c:\Deavart.com, and Windows7
> 64-bit

You will need to show some code that can reproduce this behavior.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Cannot connect to remote FB Server

[marcus]

Hej,

dennisktl...@yahoo.com wrote:
> Hi marcus,
>
>
> Thanks for your reply. Appreciate it.
>
> Yes it is able to ping, from server to client and vice versa.
>
> As for the routing information, i will try to get from the network
> administrator.
>
> Thanks.
If ping is allowed between both parties - have you tried a telnet from 
client to server @ port 3050? Btw: you do have entries for port 3050 in 
the services on both sides?

hth, Marcus


> 

Re: [firebird-support] Cannot connect to remote FB Server

[dennisktliew]

I did not try telnet, but i did try using TCP/IP tools to check if the port 
3050 is opened. I placed a TCP listener tool at the server side, and a TCP 
Client tool at remote side. After shutting down FB service, the TCP listener is 
able to start listening at 3050 and able to receive messages sent over from TCP 
Clinet tool. Is this testing good enough?

Re: [firebird-support] Cannot connect to remote FB Server

[Helen Borrie]

At 10:31 p.m. 3/03/2014, dennisktl...@yahoo.com wrote:


>I have problem connecting to FB server from a remote PC sitting on different 
>subnet mask. It said "Cannot attach to services manager" everytime I tried to 
>establish connection using TCP/IP mode.
>
>Server IP: 172.30.18.104
>Gateway: 172.30.16.1
>
>Client IP: 172.30.0.244
>Gateway: 172.30.0.1
>
>
>Firewall is off at both side.
>Port 3050 is opened.
>Anti-virus sofware is off.
>
>
>Local conncetion to database at server is fine.
>
>If I connect from a client PC which resides on the same network segment, it is 
>working fine. I suspect is has something to do with the packet routing or have 
>I missed anything here?
>
>Your help is very much appreciated. Thanks in advanced. 

"Cannot attach to services manager" isn't an error you would expect for a 
straight database connection.  What are you actually trying to do from the 
remote client?

Show us the connection string you are using to attempt the remote connection.  
Make sure you mention whether the host server is running Windows or a POSIX OS, 
as the syntaxes are different.  

Try a tracert to the Firebird host IP address, too. That should help to 
establish the last IP address in the route.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__