[REBOL] Re: MSSQL results
From: gscottjones:mchsi at: 2-Oct-2002 11:07
Hi, Allen,
From: "Allen Kamp"
> I haven't got REBOL/Command on this machine so
> I'm just going from memory here.
I've never had REBOL/Command, so I've just been going on my psychic
impression.
:-\
> These are two separate (unrelated & unjoined) SQL
> statements, they should be inserted and retrieved separately.
> The driver returns 1 recordset (multiple records) per executed
> SQL request, not multiple recordsets. (some languages
> have wrapper objects that do this for you)
<snipped side bar observation>
> If you really want to emulate returning two or more recordsets in the
totals
> block you could do something like this. (not tested)
<snipped beautiful, but untested code>
This is definitely a prettier version of what I was suggesting. After
following his link to the 4.* documentation on the equivalent PHP command (I
was looking at the 3.*), the command clearly seems to target stored
procedures that may return 2 (or more) separate datasets. So I down loaded
the PHP source, hoping to see how they distinquish the end of one dataset
and the beginning of another dataset. All I saw were pointers (a pointer to
a pointer to a result) without obvious (to me) code looking for a divider.
This suggests that the datasets (notice the plural form) are sent in a
fairly primitive fashion. Looking at my psychic code source for /Command
[ :-) ] , I can "see" no reason my REBOL couldn't return successfully
return the result. Then another idea dawned on me, PHP access to MSSQL
requires that the client services package be installed on the server, and
then the PHP client code is compiled against what I assume is the native
MSSQL access code!!! Whereas, REBOL is accessing MSSQL through ODBC. My
guess at this juncture is that Allen may have hinted at the "real" problem
when he mentioned ODBC. Doing some more poking around I discovered that not
all ODBC drivers are created equal, and that some may have trouble with
returns on stored procedures (perhaps similar to Allen hinting that ODBC
should have complained about sending two SQL statements in one request).
In summary, I am guessing that the problem may not be REBOL, per se, or
MSSQL, but with using ODBC to access MSSQL. If this was already totally
obvious to others, then I apologize. It wasn't obvious to me. :-)
Another interesting article that I ran across dealing with MSSQL client
access from Linux. The author describes a number of alternatives, including
even a MS patch for MSSQL 7.0 allowing access through tcp/ip. At any rate,
there may be other crucial nuggets in this article, so here is the link
(preserved for posterity on escribe for the next poor soul to cross this
problem):
http://www.phpbuilder.com/columns/alberto20000919.php3
I can make no promises that my analysis has actually narrowed the problem,
but I'm feeling more confident that the problem lies in the limitations of
ODBC. Franck, if you discover more useful information, please do share it
with the list, as I feel certain that this issue will arise again and again.
(By the way, Allen, I like the clean way that you encapsulated fetching
multiple SQL statements, and also say "Hi" to Steve S., the "up-over" Kansas
City guy who is now "down-under" in Brisbane.)
And, Franck, maybe suggesting tossing or donating the computer was just a
bit radical. I think it will be OK if you keep it!
:-)
--Scott Jones