On Fri, 30 Jun 2000 03:38:44 -0400, Claudio Valderrama C. wrote:

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

If I remember correctly (the last time I touched a MSSQL installation is about
Apr/99) a TQuery would do the task.

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

I missed it. Ok.

>
>
>> >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??? :-)

Ahahahahahaha!!!!!!!!

>MsSql procedures aren't unreadable, the problem is you didn't take the MSCE
>course on spaghetti ciphering and deciphering. (Irony here.)

Ohhhhhhhhhhhhhhhhhhhh..... ;-)))))))))))))))))))))

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

If I remember correctly, SQL Server uses a call gate to use in-DLL UDFs. So, it's a little slower
than IB in this case.
I touched MSVC a day, geez, I find it so clumsy... I prefer BCB... ;-)

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

Hmmm... DbLib? These one lib are not biased with MSSQL/old Sybase ones?
I remembered this is like the raw IB API for MSSQL.
Yes, BDE use it because this is recognized as the raw, native, API for MSSQL.

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

Appear a little with what I heard about DB2. It don't have PLSQL (or a equivalent)
for SP and TRIGGERS. All this work is done with C++ routines. (I'M NOT SURE ABOUT THIS,
I never touch a DB2 install to verify this).

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

As often...

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

I think the second much more dangerous, since this only be noticed when
that MOST-IMPORTANT financial reports of end-of-commercial-year begins
to show anomalies... The first will stop the company, but no harm is made on
the true (backup'd) data....

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

I think this as good thing, since we'll have a schedule to projects.

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

This was not me, but because a financial control system of a federal TI company. Most of
the crazy rules of such a beast generated tons of gigantic SPs, many destinated to reports.
If I remember correctly ( this was in 1998), when our systems engineer/dba/project cordinator/
whatever you want :-))) printed it to me to study , it generated about 120 A4 pages.

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

If you implement some features not-standard in DSQL you'll defeat it also.

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

True function overload? Geez, this way we'll need to create a C++ or Object Pascal
compier in IB... ;-)))

[a lot of trouble that will be solver with overloading].

>I won't go further, hope
>you got a feel of the mess.

Be sure of this. This is a real hassle. Maybe Diane know the way SQL guys
are dealing with it and this enlighten a good way to do this...

[snip]

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

I think the substr way more appropriate for the inserts, but trimming is also
a good thing for castings in SELECTS...

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

Me too.

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

A parsing utility will be more appropriate. This will get the definitions and translate this
to IB. It not need to be a dialect, though.
I think dialects are good things to avoid break existing applications, not to make IB
fatter making it behave like these MSSQL beasts.
These aux dialest will also prevent new people incoming from these realms to get deep and discover
that IB is not ACCESS, nor MSSQL, nor whatever and it is, in fact, InterBase; with many
great features that don't exist in such monsters.
I don't think this is good.



[]s Fabricio
Delphi C/S Developer