🚀 go-pugleaf

Frank Gruber wrote:
> Is there a way to issue the recompilation of stored procedures
> without stating the whole sourcecode of the SP ?

This is done easy with many of the tools (IBExpert.com for example)

> It seems that if a SP changes all SPs calling that one have to
> be recompiled ?

Yes.

> Regards,
> Frank.


--
Bojidar Alexanrov
Kodar Ltd.
http://www.Kodar.net

> This is done easy with many of the tools (IBExpert.com for example)

Which I currently use but which does not solve my problem in this case.
I need to supply a tool for our customers which they can use to update
their db to a newer release our new software requires. So I have to do
it programatically and I don't want to include the whole source of all
depending SP.

Regards,
Frank.

Frank Gruber wrote:
>> This is done easy with many of the tools (IBExpert.com for example)
>
> Which I currently use but which does not solve my problem in this
> case. I need to supply a tool for our customers which they can use to
> update their db to a newer release our new software requires. So I
> have to do it programatically and I don't want to include the whole
> source of all depending SP.
>
> Regards,
> Frank.

Open SQL monitor from Tools, then run Recompile all strored procedures from
DataBase menu and will see what code
IBExpert actualy use.

Here one of mine:

select pr.rdb$procedure_name,
pr.rdb$procedure_source,
pp.rdb$parameter_name,
pp.rdb$parameter_type,
fs.rdb$field_name,
fs.rdb$field_type,
fs.rdb$field_length,
fs.rdb$field_scale,
fs.rdb$field_sub_type,
fs.rdb$segment_length,
fs.rdb$dimensions,
cr.rdb$character_set_name,
co.rdb$collation_name,
pp.rdb$parameter_number,
fs.rdb$character_length
,FS.RDB$FIELD_PRECISION
from rdb$procedures pr
left join rdb$procedure_parameters pp on pp.rdb$procedure_name =
pr.rdb$procedure_name
left join rdb$fields fs on fs.rdb$field_name = pp.rdb$field_source
left join rdb$character_sets cr on fs.rdb$character_set_id =
cr.rdb$character_set_id
left join rdb$collations co on ((fs.rdb$collation_id = co.rdb$collation_id)
and
(fs.rdb$character_set_id = co.rdb$character_set_id))
where pr.rdb$procedure_name = 'COPY_MATERIAL_TYPE'
order by pp.rdb$parameter_type, pp.rdb$parameter_number

Then it forms standart header Alter Procedure with the params and after that
the source of procedure.


--
Bojidar Alexanrov
Kodar Ltd.
http://www.Kodar.net

> Open SQL monitor from Tools, then run Recompile all strored procedures
from
> DataBase menu and will see what code
> IBExpert actualy use.

Ok thank's for your help.
I was hoping that there were a way of recompiling
a sp without stating the source of it. Otherwise I have to
include the sources of all sps in my update db
program even if it's source did not change.

Regarda,
Frank.

In article <3deb7e66@newsgroups.borland.com>, bojo@kodar.net says...
> > It seems that if a SP changes all SPs calling that one have to
> > be recompiled ?
> 
> Yes.
> 
	Not exactly.  Read about updating procedures in use in the 
documentation.  If you don't want to update the other procs you can just 
have everyone (including yourself) disconnect from the DB.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Frank Gruber wrote:
>> Open SQL monitor from Tools, then run Recompile all strored
>> procedures from DataBase menu and will see what code
>> IBExpert actualy use.
>
> Ok thank's for your help.
> I was hoping that there were a way of recompiling
> a sp without stating the source of it. Otherwise I have to
> include the sources of all sps in my update db
> program even if it's source did not change.
>
> Regarda,
> Frank.

If you use this sql in your update tool you will extract existing source of
a depend sp and recompile it
with it


--
Bojidar Alexanrov
Kodar Ltd.
http://www.Kodar.net

> If you use this sql in your update tool you will extract existing source
of
> a depend sp and recompile it
> with it

Sorry I did not look at the sql code in detail.

Thank's.
Regards,
Frank

> > Yes.
> >
> Not exactly.  Read about updating procedures in use in the
> documentation.  If you don't want to update the other procs you can just
> have everyone (including yourself) disconnect from the DB.

Thank's for potinting to the docs, but I'm still a bit confused.
Does it mean that if I recompile a SP during maintenance time
(nobody is connected to the db) I don't need to recompile
dependent SP's ?

Thank's for your help.

Regards,
Frank.

In article <3dec7964$1@newsgroups.borland.com>, HVFDMIPUMFPS@spammotel.com 
says...
> Thank's for potinting to the docs, but I'm still a bit confused.
> Does it mean that if I recompile a SP during maintenance time
> (nobody is connected to the db) I don't need to recompile
> dependent SP's ?
> 
	Yes.  Obviously, you won't see the effect until you disconnect and 
reconnect yourself.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

In article <3dec7964$1@newsgroups.borland.com>, Frank Gruber says...

Hi,

> Does it mean that if I recompile a SP during maintenance time
> (nobody is connected to the db) I don't need to recompile
> dependent SP's ?

Yes, that normally works.  What we do when updating meta data is to do a 
backup/restore after the update, which although not required, makes me 
feel better that everything is in place <g>

Phil

> Yes.  Obviously, you won't see the effect until you disconnect and
> reconnect yourself.

Thank's for confirmation.

Regards,
Frank.

> Yes, that normally works.  What we do when updating meta data is to do a
> backup/restore after the update, which although not required, makes me
> feel better that everything is in place <g>

Maybe it will make me also feel better. I will give it a try.

Regards,
Frank.

In article <3decca70$1@newsgroups.borland.com>, HVFDMIPUMFPS@spammotel.com 
says...
> Maybe it will make me also feel better.
> 
	Well, it's exactly the same as dropping and recreating all procs in 
the DB.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer