On Tue, 27 Jun 2000 04:50:12 -0400, Claudio Valderrama C. wrote:

> Ok, Mom, I'm sure there are others that deal better with MsSql. I've been
>doing some consultancy on MsSql for the last 5 years but I'm not an expert
>on procedures... in fact, this is the part I've touch less:
>
>- CASE statement. Do I need to explain? Hope that not. In compiled languages
>like Delphi, CASE allows for compiler optimizations. Also, it can be easier
>to code for developers when facing several decisions based on the same
>expression.
>
>- 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.

>
>- That's interesting, it resembles our discussion about db-wide triggers:
>«
>Automatic Execution of Stored Procedures:
>When you mark stored procedures for automatic execution, these stored
>procedures are executed every time Microsoft® SQL Server starts.
>»

Interesting.

>- At least IB detects these issues that in MsSql are left to the
>developer... umh, I will include it among the IB abilities:
>«
>Note Changing the name or definition of a stored procedure can cause any
>dependent objects to fail when executed if those dependent objects are not
>also updated to reflect the changes made to the stored procedure.
>Note Renaming a stored procedure does not change the name of the stored
>procedure in the text of the procedure s definition. To change the name of
>the stored procedure in the definition, modify the stored procedure
>directly.
>[snip and this is worse than VB] This process is called deferred name
>resolution because objects referenced by the stored procedure need not exist
>when the stored procedure is created, but only when it is executed.
>»

IB is cool.

>
>- @@ROWCOUNT (T-SQL)
>Returns the number of rows affected by the last statement.
> Currently, IB only provides these values for the Embedded SQL or DSQL
>interfaces.

Good point.

>
>- @@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.

>- We already have USER variable. This is standard, I think. We need ROLE or
>ROLE_NAME, too.

Good for identifying users....

>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...

>
>- Extended Stored Procs are allowed. They are procedures written in an
>external language and compiled in a DLL. Don't confuse them with UDFs,
>because Extended Procs can return several parameters or result sets... maybe
>this is the Java-inside-IB idea of Jim.

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.
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?
Jim's JDBC-style API?
If it can manipulate result sets, means also it can modiy/insert/delete, no?
So, a bad written baby can corrupt your DB file (as usual in bad behaved
UDFs) more it can drive your data crazy...

>
>- Identifiers, including variables' names, parameters' names and procs and
>tables' names can go up to 128 characters. IB limit of 31 chars is annoying
>for people that come from Access and other databases.

Good!

>
>- Stored procedures can have optional parameters if one defines a default
>for those parameters. The default can be NULL, too. The default allows for
>wilcards that can be used by the LIKE operator.
> There's no counterpart in IB yet. Either you pass all parameters or fail in
>compilation time. Also, a procedure calling another NEEDS to include the
>RETUTRNING_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.
If enabled, you can use your functions as normal procedures (between other things).

>
>- Depending on available memory, the maximum size of a stored procedure is
>128 MB.
> IB only allows for 32K. I don't need 128 MB to match MsSql, but at least
>128 KB, in case of emergencies...a few times I've hit the current IB
>maximum.

I think that at least 256K give us the best breathing we need and space to grow
in future.

>- If you create a private temporary table inside a stored procedure, the
>temporary table exists only for the purposes of the stored procedure; it
>disappears when you exit the stored procedure.

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... ;-)

> In IB, it can't be done. However, it's needed for complex task where
>selectable procedures fall short, for example multi-passes over a recordset
>before returning it. Also, this facility can help with server-side crosstab
>queries (matrix transposition).

Temp tables are powerful. Let go for it.

<snip some Mickey$oft IB plagium> ;-))))

>
>- MsSql's procedures can alter data on tables on other databases. Triggers
>can make references to other databases. FK relationships can target other
>databases on the same server. Procedures can connect even to remote servers.
> Apart from QLI, this is science fiction in IB still.

Ressurect GDML? Increased power SQL interface?

>
>- 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...?

>
>- Helen's trick with IB5.1 works on MsSql: you can issue a complex SQL's
>SELECT statement and the resultset is returned to the client that executed
>the stored proc.

Guy, that exist even in MSSQL 6.5 (I looked into it from Jan/98 to May/99, in
different projects).

>
>- Modulo and bitwise operators. Only available in IB through UDFs, I think
>at least modulo should be built-in.

OK.

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

Hmmmm

>
>- Coalesce to deal with NULLs. Limited counterpart with UDFs. Also, UDFs
>need an official way to signal NULLs.

THIS will help much guys around the world...

>
>OT but IB uses system var USER... is USER or CURRENT_USER the standard?
>
> Excuse me for writing Interbase without the capitalized "b", this is the
>reason I prefer to write IB and avoid problems.
>;-)
>
> I'm preparing an extensive comparison of types between Monster-Server (aka
>SqlServer) and flea-Server (aka Interbase). I didn't wanted to offend IB, I
>only wanted to mean it's lightweight.

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

[]s Fabricio
Delphi C/S Developer