[REBOL] Re: MSSQL results
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