MSSQL results
[1/17] from: fmarcia:marcopoly at: 30-Sep-2002 9:41
Hi all,
I'm trying to retrieve several sets of data from an unique stored
procedure.
For example, the following stored procedure:
CREATE PROCEDURE getOrder @MY_ID int
AS
SELECT * FROM orders where order_id = @MY_ID
SELECT * FROM orders_details where order_id = @MY_ID
GO
called by: insert command "getOrder 1234"
just returns the first set of data.
Is there a way to retrieve the two sets?
Franck.
[2/17] from: greggirwin:mindspring at: 1-Oct-2002 10:28
Hi Franck,
<< Nobody's got an answer?
Is there something like the "mydb_next_result" of PHP with Rebol? >>
I don't know how many folks are running REBOL against SQL here. I haven't
seen it come up very often, so my guess is that not many folks are doing it.
I know I haven't needed to.
--Gregg
[3/17] from: fmarcia:marcopoly at: 1-Oct-2002 18:04
Nobody's got an answer?
Is there something like the "mydb_next_result" of PHP with Rebol?
Franck.
-----Message d'origine-----
De=A0: Franck MARCIA
Envoy=E9=A0: lundi 30 septembre 2002 09:41
=C0=A0: [rebol-list--rebol--com]
Objet=A0: [REBOL] MSSQL results
Hi all,
I'm trying to retrieve several sets of data from an unique stored
procedure.
For example, the following stored procedure:
CREATE PROCEDURE getOrder @MY_ID int
AS
SELECT * FROM orders where order_id = @MY_ID
SELECT * FROM orders_details where order_id = @MY_ID
GO
called by: insert command "getOrder 1234"
just returns the first set of data.
Is there a way to retrieve the two sets?
Franck.
[4/17] from: gscottjones:mchsi at: 1-Oct-2002 11:39
Hi, Franck,
From: "Franck MARCIA"
> I'm trying to retrieve several sets of data from an unique
> stored procedure.
<<quoted lines omitted: 7>>
> just returns the first set of data.
> Is there a way to retrieve the two sets?
And later ...
From: "Franck MARCIA"
> Nobody's got an answer?
>
> Is there something like the "mydb_next_result" of PHP with Rebol?
I've no specific experience with REBOL in conjunction with MSSQL, especially
in regard to stored procedures. However, the answer may be more generic
than just REBOL interacting with MSSQL.
After you use:
insert command "getOrder 1234"
how are you requesting the data from the port?
--Scott Jones
[5/17] from: doug:vos:eds at: 1-Oct-2002 16:39
So are you using rebol/command with ODBC?
That is the only way I have talked to MS-SQL.
I always use somethign like this when talking to a MS-SQL server:
sql: {SELECT field1,field2 FROM table_A}
insert dbport sql
result-rows: copy dbport
The same thing works for mySQL also.
[6/17] from: fmarcia:marcopoly at: 2-Oct-2002 9:40
Hi Scott,
I agree with you: it's not specific to MSSQL.
I'm using this kind of code, like specified in documentation:
order: copy command
Then, I'm using clients word.
I tried to copy from command port a second time but it doesn't work.
In any case, I just can retrieve the first set of data :-(
I'm new with Rebol... is there another way to request the data from the port?
Franck.
-----Message d'origine-----
De=A0: G. Scott Jones [mailto:[gscottjones--mchsi--com]]
Envoy=E9=A0: mardi 1 octobre 2002 18:39
=C0=A0: [rebol-list--rebol--com]
Objet=A0: [REBOL] RE : MSSQL results
Hi, Franck,
From: "Franck MARCIA"
> I'm trying to retrieve several sets of data from an unique
> stored procedure.
<<quoted lines omitted: 7>>
> just returns the first set of data.
> Is there a way to retrieve the two sets?
And later ...
From: "Franck MARCIA"
> Nobody's got an answer?
>
> Is there something like the "mydb_next_result" of PHP with Rebol?
I've no specific experience with REBOL in conjunction with MSSQL, especially
in regard to stored procedures. However, the answer may be more generic
than just REBOL interacting with MSSQL.
After you use:
insert command "getOrder 1234"
how are you requesting the data from the port?
--Scott Jones
[7/17] from: fmarcia:marcopoly at: 2-Oct-2002 10:09
Thank you for your answer, Doug,
Yes, I'm using ODBC to talk to MS-SQL.
But if you use something like:
sql: {
select top 1 * from customers
select count(*) nb from customers
}
insert dbport sql
result-rows: copy dbport
You can't retrieve the second set of data. And that's my problem.
Franck.
-----Message d'origine-----
De=A0: Vos, Doug [mailto:[doug--vos--eds--com]]
Envoy=E9=A0: mardi 1 octobre 2002 22:40
=C0=A0: ['rebol-list--rebol--com']
Objet=A0: [REBOL] Re: RE : MSSQL results
So are you using rebol/command with ODBC?
That is the only way I have talked to MS-SQL.
I always use somethign like this when talking to a MS-SQL server:
sql: {SELECT field1,field2 FROM table_A}
insert dbport sql
result-rows: copy dbport
The same thing works for mySQL also.
[8/17] from: al:bri:xtra at: 2-Oct-2002 21:28
> But if you use something like:
> sql: {
<<quoted lines omitted: 4>>
> result-rows: copy dbport
> You can't retrieve the second set of data. And that's my problem.
Have you tried something like a second 'copy? For example:
sql: {
select top 1 * from customers
select count(*) nb from customers
}
insert dbport sql
result-rows: copy dbport
result-rows2: copy dbport ; Get the second set?
I know nothing more, sorry.
I hope that helps!
Andrew Martin
ICQ: 26227169 http://valley.150m.com/
[9/17] from: gscottjones:mchsi at: 2-Oct-2002 4:37
From: "Franck MARCIA"
...
> I agree with you: it's not specific to MSSQL.
> I'm using this kind of code, like specified in
> documentation:
order: copy command
> Then, I'm using clients word.
> I tried to copy from command port a second time
> but it doesn't work.
>
> In any case, I just can retrieve the first set of data :-(
>
> I'm new with Rebol... is there another way to request the data from the
port?
Hi, Franck,
Looking over things, I think here is how I would proceed:
Open a database connection (of course, substituting the "real" names where
needed :-):
my-db: open scheme://database-name
Then check to be sure that the default number of rows is at your desired
minimum:
probe my-db/locals
You should receive something back like:
make object! [ auto-commit: true access: `write rows: 10 ]
If the rows is set to 1, then you may have your answer about why only one
result is returned. I am anticipating that you will see "row: 10". Next
you get your port connection:
my-db-port: first my-db
The port is supposed to inherit from the database options, but recheck
anyway:
probe my-db-port/locals
Again, you should see something like:
make object! [ auto-commit: true access: `write rows: 10 ]
where rows is more than 1! Next you send your SQL command:
insert command "getOrder 1234"
There are (at least) four ways to read from the port. The first, you have
already tried. It *is* supposed to return all the records (at least up to
the number specified in the options). A second way can be done one at a
time:
a-record: first my-db-port
This command can be repeated for more records, but it will return an error
when the port is empty. A similar, alternative approach is to use pick for
one record at a time until 'none is returned:
a-record: pick my-db-port 1
or use this in a loop:
while [a-record: pick my-db-port 1][print a-record]
The fourth way is to specify exactly how many records to be copied:
blk-of-records: copy/part my-db-port 5
This is really just a variation on the method that you have already tried,
so I doubt it will yield anything different (as long as the database and
port connections options have set row to more than 1).
If none of these approaches yield any useful results or surprising answers,
then I would try sending SQL statements that comprise the stored procedure
*one at a time* to see if you can at least get more than one row returning
at a time. I do not believe that REBOL will allow two back to back SQL
statements to be sent *at the same time*, when using a DSN, because the
results of the second will ablate the results of the first, if I understand
correctly. (Other databases, like MySQL, that are port access only,
apparently will stack results, which is what I remember when I used Allen
K's mysql scheme. You have to keep polling the port to clear the port.)
If all else fails, open your window, look to be sure no one is below your
window, and shove your entire computer system out the window, and then
travel to Tibet and become an ascetic monk! :-)
Or, perhaps more useful, let the list know what you found. Maybe something
else will become obvious.
Good luck!
--Scott Jones
[10/17] from: fmarcia:marcopoly at: 2-Oct-2002 12:01
Thanks, Scott.
I already tried to send sql statements directly instead of using stored procedures...
it didn't work (exactly, it worked the same as SP: there's no error but Rebol "forgets"
the second set of data - the second!).
If you've got 2 tables with, respectively, 10 and 100 rows and try this:
connection: open [scheme: 'odbc target: "bla bla bla"]
command: first connection
sql-string: {
select count(*) from first_table
select count(*) from second_table }
insert command sql-string
totals: copy command
totals will point to: [[10]].
>From there... no way to get the results of the 2nd statement.
I tried:
- to copy twice from the port
- to convert the result of copy to a block (!?)
- to use copy in a statement like
foreach [one two] (copy command) []
But I don't try to throw my computer through the window...
... is there a support from Rebol Corp? Perhaps Rebol can't do it!
Franck.
-----Message d'origine-----
De=A0: G. Scott Jones [mailto:[gscottjones--mchsi--com]]
Envoy=E9=A0: mercredi 2 octobre 2002 11:37
=C0=A0: [rebol-list--rebol--com]
Objet=A0: [REBOL] Re: MSSQL results
From: "Franck MARCIA"
...
> I agree with you: it's not specific to MSSQL.
> I'm using this kind of code, like specified in
> documentation:
order: copy command
> Then, I'm using clients word.
> I tried to copy from command port a second time
> but it doesn't work.
>
> In any case, I just can retrieve the first set of data :-(
>
> I'm new with Rebol... is there another way to request the data from the
port?
Hi, Franck,
Looking over things, I think here is how I would proceed:
Open a database connection (of course, substituting the "real" names where
needed :-):
my-db: open scheme://database-name
Then check to be sure that the default number of rows is at your desired
minimum:
probe my-db/locals
You should receive something back like:
make object! [ auto-commit: true access: `write rows: 10 ]
If the rows is set to 1, then you may have your answer about why only one
result is returned. I am anticipating that you will see "row: 10". Next
you get your port connection:
my-db-port: first my-db
The port is supposed to inherit from the database options, but recheck
anyway:
probe my-db-port/locals
Again, you should see something like:
make object! [ auto-commit: true access: `write rows: 10 ]
where rows is more than 1! Next you send your SQL command:
insert command "getOrder 1234"
There are (at least) four ways to read from the port. The first, you have
already tried. It *is* supposed to return all the records (at least up to
the number specified in the options). A second way can be done one at a
time:
a-record: first my-db-port
This command can be repeated for more records, but it will return an error
when the port is empty. A similar, alternative approach is to use pick for
one record at a time until 'none is returned:
a-record: pick my-db-port 1
or use this in a loop:
while [a-record: pick my-db-port 1][print a-record]
The fourth way is to specify exactly how many records to be copied:
blk-of-records: copy/part my-db-port 5
This is really just a variation on the method that you have already tried,
so I doubt it will yield anything different (as long as the database and
port connections options have set row to more than 1).
If none of these approaches yield any useful results or surprising answers,
then I would try sending SQL statements that comprise the stored procedure
*one at a time* to see if you can at least get more than one row returning
at a time. I do not believe that REBOL will allow two back to back SQL
statements to be sent *at the same time*, when using a DSN, because the
results of the second will ablate the results of the first, if I understand
correctly. (Other databases, like MySQL, that are port access only,
apparently will stack results, which is what I remember when I used Allen
K's mysql scheme. You have to keep polling the port to clear the port.)
If all else fails, open your window, look to be sure no one is below your
window, and shove your entire computer system out the window, and then
travel to Tibet and become an ascetic monk! :-)
Or, perhaps more useful, let the list know what you found. Maybe something
else will become obvious.
Good luck!
--Scott Jones
[11/17] from: gscottjones:mchsi at: 2-Oct-2002 6:23
From: "Franck MARCIA"
> Thanks, Scott.
> I already tried to send sql statements directly instead of using stored
> procedures... it didn't work (exactly, it worked the same as SP: there's
no
> error but Rebol "forgets" the second set of data - the second!).
Well, that is curious and seems to contradict the documentation. I am no
expert, but it is sounding like REBOL can only handle sending one request
and getting one table (dataset) back at a time, more like the following
(which I am assuming *does* work):
connection: open [scheme: 'odbc target: "bla bla bla"]
command: first connection
sql-orders: {SELECT * FROM orders where order_id = 1234}
insert command sql-string
sql-orders-blk: copy command
sql-orders-details: {SELECT * FROM orders_details where order_id = 1234}
sql-orders-details-blk: copy command
close connection
My SQL skills are rusty, but if you do not need to preserve the individual
table datasets, then I guess the other option is to do a table join. My
guess is that you are well aware of this, and are simply trying to access
MSSQL in a way that is similar to PHP (by the way, I could not find that
command *_next_result as in mydb_next_result in the PHP manual pages).
But I seem to be rambling...
> But I don't try to throw my computer through the window...
>
> ... is there a support from Rebol Corp? Perhaps Rebol can't do it!
OK, then you could donate your computer to a local charity, and then join a
monastic order :-)
... Or ...
Send a very nice email to feedback at rebol dot com stating your
observations and hope that maybe an RT employee will take mercy on your soul
and send you a definitive answer.
... Or ...
Send a request to cindy at rebol dot com requesting information on
commercial support.
... Or ...
Resubmit this very same question back to the mail list next week. Maybe a
different group of folks will be reading it, and know more about the issue.
If it were me and all the above had failed, I would simply make sequential
requests to the database, or use a JOIN, or if performance were critical,
make a new stored procedure that sends the join back, or make each SQL
statement into separate stored procedures. Just thinking out loud in case
you haven't explored some of these ideas.
If you find a solution, be sure to share it with the list. Invariably, some
one is going to ask the same thing, and it is nice to know. Good luck.
--Scott Jones
[12/17] from: doug:vos:eds at: 2-Oct-2002 8:40
So what about another approach where you set up two ports:
sql-A: {select top 1 * from customers}
sql-B: {select count(*) nb from customers}
insert dbport-1 sql-A
insert dbport-2 sql-B
result-rows-A: copy dbport-1
result-rows-B: copy dbport-2
This seems like it would work OK.
Not familiar with your situation...
What do you lose from this approach?
-DV
[13/17] from: fmarcia:marcopoly at: 2-Oct-2002 14:52
Thanks a lot Scott,
Just have a look at: http://www.php.net/manual/en/function.mssql-next-result.php to see
what I'm trying to do with Rebol (and what PHP can do).
Franck.
PS: I definitely want to keep my computer.
-----Message d'origine-----
De=A0: G. Scott Jones [mailto:[gscottjones--mchsi--com]]
Envoy=E9=A0: mercredi 2 octobre 2002 13:23
=C0=A0: [rebol-list--rebol--com]
Objet=A0: [REBOL] Re: MSSQL results
[...]
[14/17] from: fmarcia:marcopoly at: 2-Oct-2002 14:58
In fact, it works nice with your approach.
The problem is that I will have to rewrite all my stored procs and that I loose the logic
"from 1 statement to several dataset".
I'm going to try another approach: contact RT support.
Franck.
-----Message d'origine-----
De=A0: Vos, Doug [mailto:[doug--vos--eds--com]]
Envoy=E9=A0: mercredi 2 octobre 2002 14:40
=C0=A0: ['rebol-list--rebol--com']
Objet=A0: [REBOL] Re: RE : Re: RE : MSSQL results
So what about another approach where you set up two ports:
sql-A: {select top 1 * from customers}
sql-B: {select count(*) nb from customers}
insert dbport-1 sql-A
insert dbport-2 sql-B
result-rows-A: copy dbport-1
result-rows-B: copy dbport-2
This seems like it would work OK.
Not familiar with your situation...
What do you lose from this approach?
-DV
[15/17] from: rebol:optushome:au at: 2-Oct-2002 23:28
I haven't got REBOL/Command on this machine so I'm just going from memory
here.
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)
(I'm surprised you didn't get an error, some/most ODBC drivers would report
a Syntax error for two separate Select statements in one string, if they
weren't joined or nested. e.g conn.execute() under ASP does)
If you really want to emulate returning two or more recordsets in the totals
block you could do something like this. (not tested)
return-recordsets: func [
db-port [port!] "DB Command Port"
SQL-Statements [block!] "Block of SQL statement strings"
/local datasets
][
datasets: copy []
for each SQL SQL-Statements [
insert db-port SQL
append/only datasets copy db-port
]
datasets
]
; --- Run the function
SQL-Cmds: [{select count(*) from first_table} {select count(*) from
second_table} ]
totals: return-recordsets db-command-port sql-cmds
Cheers,
Allen K
[16/17] from: petr:krenzelok:trz:cz at: 2-Oct-2002 15:17
Franck MARCIA wrote:
>Thanks a lot Scott,
>
>Just have a look at: http://www.php.net/manual/en/function.mssql-next-result.php to
see what I'm trying to do with Rebol (and what PHP can do).
>
If PHP can do it, so Rebol can imo ... have you looked at alternative
mySQL driver done by Dockimbel? It is based upon socket communication,
so I think that in that case, there is no difference between PHP and
Rebol. If you are talking about /Command ODBC support thought, that can
be different story ...
http://rebol.softinnov.org/mysql/
-pekr-
[17/17] 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
Notes
- Quoted lines have been omitted from some messages.
View the message alone to see the lines that have been omitted