Mailing List Archive: 49091 messages
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

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