At least someone to verify my claims...

> -----Original Message-----
> From: Fabricio Araujo [mailto:fabricioa@...]
> Sent: Jueves 29 de Junio de 2000 0:25
>
> On Tue, 27 Jun 2000 04:50:12 -0400, Claudio Valderrama C. wrote:
> >
> >- There's no selectable sprocs, but a sproc can return a CURSOR.
>
> Or a dataset directly, if the code of the procedure is only a SELECT.

Just curious, how do I trap its results, with a TQuery with an EXECUTE
instruction? I never have tried.

> >- @@TRANCOUNT (T-SQL)
> >Returns the number of active transactions for the current connection.
> > Maybe a solution looking for a problem? Not sure.
>
> @@TRANCOUNT is there because these M$ beast allows a stored proc
> to start/commit/rollback a transaction. Because of this, they
> need this control.
> IB don't let it in SP or triggers. Only a client can
> start/commit/rollback txns.

Sure, but I thought it might be useful under some conditions to have that
information. Because IB doesn't understand txn nesting, a @@TRANCOUNT
equivalent would return the number of CONCURRENT active transactions in the
current connection and not the nesting of the current txn, that doesn't
exist as you pointed out.

> >It would be useful if in the case new non-standard system
> >vars are included, they are preceded by some sign (MsSql uses @), so we
> >don't forbid new plain words. Making "#version" a reserved word inside
> >sprocs causes less problems than making "version" a reserved word.
>
> Geez... MSSQL SPs aren't unreadable enough? Anyway, it make sense.
> But these things of putting "signs" often makes the code appeat to be
> a spaghetti...

Maybe [] instead of @ like in []Fabricio??? :-)
MsSql procedures aren't unreadable, the problem is you didn't take the MSCE
course on spaghetti ciphering and deciphering. (Irony here.)

> >- Extended Stored Procs are allowed. They are procedures written in an
> >external language and compiled in a DLL.
>
> Interesting, but used with care. A thread safe way of writing
> this must be
> provided, since these X-SPs are more powerful and (obviously)
> can represent
> a new danger to metadata and data if bad writen.

I guess they can do a lot of things both good and bad, but cannot comment
without knowing more... I never have written one... I have MSVC, maybe one
of these days.

> At my viewpoint is a DOUBLE risk: result sets means that you manypulate
> SQL in them. So, what type of SQL interface will be used?
> BDE? (I think that will be tempted to do these crazy act) IBO?
> IBX? Raw API?

As they are called by the engine, they have some way of passing/receiving
parameters from the engine. They are implemented in DLLs (surprise <grin>).
They can use ODBC, the Open Data Services API and dblib (here goes a partial
answer to your clever question: BDE uses dblib to connect to MsSql, so it
can be used). Extended procs can use the transaction space of the calling
process if they bind, so they have a common transaction lock space.
I think this is +/- the same than having an UDF that connects to the db and
does some work... the difference is that in IB, this is discouraged and
therefore no official API is offered for those tricks. :-)

> Jim's JDBC-style API?
> If it can manipulate result sets, means also it can
> modiy/insert/delete, no?

If such extended proc calls srv_getbindtoken and with such result, it calls
sp_bindsession, yes, it will join the current txn, so it can do more than a
SELECT. [I'm learning, so one of these days I will crash MsSql. ;-)]

> So, a bad written baby can corrupt your DB file (as usual in bad behaved
> UDFs) more it can drive your data crazy...

Agree.

> Also, a procedure calling another NEEDS to include the
> >RETURNING_VALUES part if the other proc returns values, even if
> the caller
> >is interested only in the side effect of the called proc.
>
> Something like the Extended Syntax in Delphi. If it is disabled,
> functions must have
> a host variable for their returns, even if you aren't interested
> in their results.

Sir, I had forgotten that since Delphi seems to turn on this flag by
default. Either a config parameter or a dialect... this sends me again to
one of my original issues: there must be some way for an IB sproc to know
more about the environment. I know the risk to make procs unreadable, but a
developer is given a tool, he/she is free to use or misuse such tool.
Currently, a proc cannot know the active role (if any) neither the dialect
number nor a proposed new setting like the one you wrote. IMHO, there must
be a way to query some "metadata" that you can't know today. You can write
an UDF to read ibconfig (isc_config) but since some settings are in effect
only after a server restart, you would need to get this info, too and
compare datetimes... no, too convoluted. (I didn't want to Cc IB-Priorities
because Charlie wrote that with the current requests, if ISC were to
impelement them, some years would have to pass. Maybe I will use copy/paste
only to the part the fits inside IB-Priorities.)

> These always loved temp tables... ;-) In my MSSQL times it make some
> ugly Quickreports act like they are a much more than decent
> report writer... ;-)

I risk Ann getting angry with me, but I've found that even in IB6,
sometimes the creation/destruction of tables "in real time" (with other
connections active) cause corruption and I couldn't isolate a reproducible
test case.
OT: maybe your reporting needs are advanced. QR and QR Pro have me covered.


[procedures/FK access to another db]

> Ressurect GDML? Increased power SQL interface?

The problem with GDML is that it defeats the claim of IB being standard.
Probably for some tasks, GDML is nice and powerful and maybe the server
could include an alternative GDML parser the way the DSQL parser was moved
to the server in v4. So, the lesson is that GDML can be an option, but you
shouldn't be required to use it for your tasks or you risk being called a
cheater with IB-SQL compliance. Your second option seems the only route in
an standard world... in an embedded world, too, maybe.

> >- You can group stored procedures of the same name. The syntax
> to call one
> >of these is really clumsy, example taken from the help file:
> >EXECUTE my_proc;2
> >but at least there's some support. IB doesn't know anything about
> >overloading on procedures.
>
> Hmmm? I miss the magic here: how it can help us, independent of
> the documentation
> hassles...?

First: given the current syntax in IB procedures, you can't expect that a
possible implementation would be so clumsy and idiot as in MsSql. I'm
calling for overloading. Second: ok, I accept that I'm biased towards C++.
Third: the idea is to be able to build UDFs with same name and distinct
parameters. This way, a transparent function for any datatype is no more a
priority to implement as built-in in the core engine, because it can be done
in an UDF. Currently, you have to write:
myUdf_char
myUdf_int
myUdf_double
myUdf_numeric
or alternatively, write your UDF for a string and pray for the other types
being converted to cstring as you expect... now, you have to parse the
string and try to guess what's the original datatype, etc. You could accept
a second parameter with a number of the datatype, but since IB doesn't have
a typeid() operator, you'll have to hardcode the rdb$type number as given in
the system tables when calling this "generic" UDF. I won't go further, hope
you got a feel of the mess.

> >- Trimming of chars longer than expected. In IB, this generates
> the dreaded
> >"arithmetic overflow or string truncation" error msg.
>
> Hmmmm

An option in the config file, do you mean with "Hmmmmm"? Some interesting
things could be done with an explicit cast if this exception wouldn't kick
us in the back-part but of course a simple insert that doesn't fit a field
should be reported as always. Otherwise, substr should be an embedded
function... probably this is wiser.

> >OT but IB uses system var USER... is USER or CURRENT_USER the standard?

I'm still in doubt. I will have to ask *someperson* at Cognos.

> MSSQL is a very ugly monster: fat, bad humoured and have
> emotionally unstable companions
> (sometimes always appear some one cursing it's graphical tools...) ;-)))
> IB is light and cute. At least, more cute than the monster... :-)
> Thank you, Claudio, to help me to remember this.
>
> []s Fabricio

Remember that we can't convince Mr. Gates to not be ugly from our pov, so
this list is about managing conversions from everything to IB... and this
may require ugly tricks or some enhancements. One person proposed to manage
compatibility with other engines as a dialect, for example.

C.