Re: SQL Support in 6.0
This WebDNA talk-list message is from 2004
It keeps the original formatting.
numero = 56192
interpreted = N
texte = Trevor, The new SQL contexts architecture at this point allows for implementing = full support for SQL92, level 3 compliance. This means that under the = hood the architecture provides us with the latitude to implement = whatever it is we need to implement. That's hopefully good news. Now I'll try to answer your question by starting with our current and = only implementation... support for mySQL. The current mySQL production = release is 4.0 and it does not support stored procs. mySQL is = targetting 5.0 for SQL:200n stored proc support. Now, as far as our = architecture is concerned, we aim to officially support only those = features that are standardized (standards drafted by the committee = consisting of ANSI/ISO, among others). As I said, for now, we are = targetting SQL92, level 3 compliance. The discussion of SQL-compliance = is relavent, believe it or not. Consider Oracle and MSSQL. They each = implement their own flavors of SQL for stored procedures, PL/SQL for the = former and T-SQL for the latter. PL/SQL and T-SQL are pretty much = supersets of SQL92, level 3 compliance for stored procedures. That is, = these flavors of SQL do not necessarily restrict themselves to feature = implentation dictated by the SQL92, level 3 standard. They comply = (mostly), of course, and then add features on top of that. My personal = opinion is that this matters most not in everyday SQL usage (selects, = inserts, updates, etc.) but in stored proc implementation. Evidently = returning multiple recordsets is not SQL92, level 3 compliant (nor is it = SQL99 compliant) and so far I believe only RDBMSs implementing T-SQL = support that feature, MSSQL specifically. Oracle 8.0, for example, does = not directly support returning multiple recordsets from a stored proc as = far as I know. However, returning multiple recordsets from stored procs = may or may not be SQL:200n compliant. It's impossible to say at this = point since the standard has not yet been published. That being said, given that SQL92, level 3 compliance was the goal, = currently there is a one-to-one correspondence between SQLExecute and = SQLResult (when applicable); i.e. if a resultset is produced by the SQL = you execute, you should expect one and only recordset to be returned. = However, we also recognize that there is a large chunk of utility lost = by not offering full support of stored procs for those that use MSSQL = (or any other RDBMS that implements T-SQL). So, though I cannot commit = to a date (that is thankfully out of my hands *smirks*), I can at least = inform you that we are engaging discussion about the possibility of = adding support for returning multiple recordsets from stored procedures. = Again, the SQL:200n standard may dictate that feature as a requirement = and we certainly don't want to be left in the dust. Lastly, regarding when MSSQL support will be implemented for the new SQL = contexts... that is actually on our "TO DO" plate. Expect support for = relatively soon. Furthermore, we are aiming at releasing support for = Oracle at the same time, with plans further down the road to implement = support for other major RDBMSs (Informix and POSTGre SQL for example). = We are also considering adding more to SQLResult to allow for accessing = error reporting info that is more "programmer-friendly"... in addition = to the current implentation (SQL errors are sent to the output stream if = you attempt to SQLExecute some bad SQL). Hope this helps. - Steve Contreras -----Original Message----- From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On Behalf Of Crist, Trevor Sent: Tuesday, February 17, 2004 1:02 PM To: WebDNA Talk Subject: Re: SQL Support in 6.0 >=20 > Are you referring to the old [SQL] context? The new SQL contexts does = not > use ODBC to communicate with a MySQL server. No. I am referring to the new commands. And the question was about = MSSQL, not MySQL. If I execute this: [SQLExecute ref=3DMyConnection&var=3DMyResults] exec getAuthorsAndBooks @AuthorName =3D 'Smith' [/SQLExecute] And the Stored Procedure named getAuthorsAndBooks looks something like = this: CREATE PROC getAuthorsAndBooks=20 @Author varchar(20) AS=20 Select FirstName, LastName from Authors WHERE Author =3D @AuthorName Select Title from Books WHERE Author =3D @AuthorName GO How does the SQLResults parse the two different results sets? Also, if I happen to put in invalid SQL, like this: [SQLExecute ref=3DMyConnection&var=3DMyResults] exec getAuthorsAndBooks @AuthorName =3D Smith [/SQLExecute] Does that mean that the Server Message gets returned where the = 'SQLExecute' command is on my WebDNA page?=20 "Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'johnson'." And, again, what is the timeline for supporting MSSQL with these new = commands?=20 Thanks. - Trevor >=20 > -----Original Message----- > From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On Behalf Of > Crist, Trevor > Sent: Tuesday, February 17, 2004 11:50 AM > To: WebDNA Talk > Subject: SQL Support in 6.0 >=20 >=20 > What is the timeline for support of MSSQL? >=20 > Also, I don't see any way for the new SQL features to handle: > - Returns of multiple rowsets (typical when executing a stored = procedure) > - Ability to handle error and other message types that are generated = by > the SQL server. >=20 > These two issues in my mind are the biggest limitations with WebCat's = SQL > support (aside from the ODBC bottlenecks). >=20 > Are there plans to add this support? If so, what is the timeline? >=20 > We have begun moving much of our system over to .asp. However, if = these > features are supported, we will buy version 6 and keep on using = WebDNA. >=20 > Thanks. >=20 > - Trevor >=20 >=20 >=20 > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004 >=20 >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list
. > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to digest@talk.smithmicro.com> > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to digest@talk.smithmicro.com> > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004 >=20 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004 =20 ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to = Web Archive of this list is at: http://webdna.smithmicro.com/ ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
Associated Messages, from the most recent to the oldest:
Trevor, The new SQL contexts architecture at this point allows for implementing = full support for SQL92, level 3 compliance. This means that under the = hood the architecture provides us with the latitude to implement = whatever it is we need to implement. That's hopefully good news. Now I'll try to answer your question by starting with our current and = only implementation... support for mySQL. The current mySQL production = release is 4.0 and it does not support stored procs. mySQL is = targetting 5.0 for SQL:200n stored proc support. Now, as far as our = architecture is concerned, we aim to officially support only those = features that are standardized (standards drafted by the committee = consisting of ANSI/ISO, among others). As I said, for now, we are = targetting SQL92, level 3 compliance. The discussion of SQL-compliance = is relavent, believe it or not. Consider Oracle and MSSQL. They each = implement their own flavors of SQL for stored procedures, PL/SQL for the = former and T-SQL for the latter. PL/SQL and T-SQL are pretty much = supersets of SQL92, level 3 compliance for stored procedures. That is, = these flavors of SQL do not necessarily restrict themselves to feature = implentation dictated by the SQL92, level 3 standard. They comply = (mostly), of course, and then add features on top of that. My personal = opinion is that this matters most not in everyday SQL usage (selects, = inserts, updates, etc.) but in stored proc implementation. Evidently = returning multiple recordsets is not SQL92, level 3 compliant (nor is it = SQL99 compliant) and so far I believe only RDBMSs implementing T-SQL = support that feature, MSSQL specifically. Oracle 8.0, for example, does = not directly support returning multiple recordsets from a stored proc as = far as I know. However, returning multiple recordsets from stored procs = may or may not be SQL:200n compliant. It's impossible to say at this = point since the standard has not yet been published. That being said, given that SQL92, level 3 compliance was the goal, = currently there is a one-to-one correspondence between SQLExecute and = SQLResult (when applicable); i.e. if a resultset is produced by the SQL = you execute, you should expect one and only recordset to be returned. = However, we also recognize that there is a large chunk of utility lost = by not offering full support of stored procs for those that use MSSQL = (or any other RDBMS that implements T-SQL). So, though I cannot commit = to a date (that is thankfully out of my hands *smirks*), I can at least = inform you that we are engaging discussion about the possibility of = adding support for returning multiple recordsets from stored procedures. = Again, the SQL:200n standard may dictate that feature as a requirement = and we certainly don't want to be left in the dust. Lastly, regarding when MSSQL support will be implemented for the new SQL = contexts... that is actually on our "TO DO" plate. Expect support for = relatively soon. Furthermore, we are aiming at releasing support for = Oracle at the same time, with plans further down the road to implement = support for other major RDBMSs (Informix and POSTGre SQL for example). = We are also considering adding more to SQLResult to allow for accessing = error reporting info that is more "programmer-friendly"... in addition = to the current implentation (SQL errors are sent to the output stream if = you attempt to SQLExecute some bad SQL). Hope this helps. - Steve Contreras -----Original Message----- From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On Behalf Of Crist, Trevor Sent: Tuesday, February 17, 2004 1:02 PM To: WebDNA Talk Subject: Re: SQL Support in 6.0 >=20 > Are you referring to the old [SQL] context? The new SQL contexts does = not > use ODBC to communicate with a MySQL server. No. I am referring to the new commands. And the question was about = MSSQL, not MySQL. If I execute this: [SQLExecute ref=3DMyConnection&var=3DMyResults] exec getAuthorsAndBooks @AuthorName =3D 'Smith' [/SQLExecute] And the Stored Procedure named getAuthorsAndBooks looks something like = this: CREATE PROC getAuthorsAndBooks=20 @Author varchar(20) AS=20 Select FirstName, LastName from Authors WHERE Author =3D @AuthorName Select Title from Books WHERE Author =3D @AuthorName GO How does the SQLResults parse the two different results sets? Also, if I happen to put in invalid SQL, like this: [SQLExecute ref=3DMyConnection&var=3DMyResults] exec getAuthorsAndBooks @AuthorName =3D Smith [/SQLExecute] Does that mean that the Server Message gets returned where the = 'SQLExecute' command is on my WebDNA page?=20 "Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'johnson'." And, again, what is the timeline for supporting MSSQL with these new = commands?=20 Thanks. - Trevor >=20 > -----Original Message----- > From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On Behalf Of > Crist, Trevor > Sent: Tuesday, February 17, 2004 11:50 AM > To: WebDNA Talk > Subject: SQL Support in 6.0 >=20 >=20 > What is the timeline for support of MSSQL? >=20 > Also, I don't see any way for the new SQL features to handle: > - Returns of multiple rowsets (typical when executing a stored = procedure) > - Ability to handle error and other message types that are generated = by > the SQL server. >=20 > These two issues in my mind are the biggest limitations with WebCat's = SQL > support (aside from the ODBC bottlenecks). >=20 > Are there plans to add this support? If so, what is the timeline? >=20 > We have begun moving much of our system over to .asp. However, if = these > features are supported, we will buy version 6 and keep on using = WebDNA. >=20 > Thanks. >=20 > - Trevor >=20 >=20 >=20 > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004 >=20 >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to digest@talk.smithmicro.com> > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to digest@talk.smithmicro.com> > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004 >=20 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004 =20 ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to = Web Archive of this list is at: http://webdna.smithmicro.com/ ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
"Steve Contreras"
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
Error Lob.db records error message not name (1997)
Caching problem... (2000)
Scoping rules in WebDNA 4.0 (2000)
Redirect Possible???????? (2001)
[WebDNA] Permission Settings (2009)
FEW QUESTIONS (1997)
referrer and no caches (1997)
[CART] (1997)
re-sorting founditems (2002)
default value from Lookup (1997)
Trouble with Category search (2000)
[carts] and databases (1997)
(1998)
Not really WebCat (1997)
Closing Databases (1998)
WCS Newbie question (1997)
sendmail for email (was Netforms) (1998)
Cancel Subscription (1996)
Fwd: Problems with Webcatalog Plug-in (1997)
Country & Ship-to address & other fields ? (1997)