I like it! By making things optional, with defaults set appropriately, we can avoid breaking existing stylesheets while still offering additional functionality when desired.
I will try to put some code together to do this. I am not sure exactly when this will happen. Some things have come up that I need to work on. Hopefully I will get to it some time next week. Sounds like I need to spend some time with DTM. Maybe I will try to do the parameter stuff first and submit that to the list. The multiple result sets may take a bit longer. Art -----Original Message----- From: John Gentilin [mailto:gentijo@;eyecatching.com] Sent: Thursday, October 24, 2002 2:37 PM To: [EMAIL PROTECTED] Subject: Re: SQL Extension enhancement/replacement Art, Here is how I think we should proceed. >>> Parsing variables in the SQL Query string.<<< You are correct, this is very subjective, although I am very concerned about adding markup in the SQL line that may cause trouble for somebody's environment such as the [] used by Access. Lets treat it as an optional feature. i.e. add a set of methods to XConnection such as setFeature(String, String) String getFeature(String) So now we have two features, "streaming" and "inline-variables" For inline variables, the default is off, so there should be no altering of the SQL Query string, otherwise parse away. The existing functions for streaming mode should be left alone but marked as depreciated. Note: If we can utilize and of the existing Xalan Feature setting code we can do that too. I did a quick look but it did not seem to be completely applicable. i.e Set from the SS as well as from code. >>> Handling Multiple Result Sets.<<< Adding in the ResultSet element is probably the most logical thing to do, but we can't just spring this change on the user base. Again, let's handle it as a feature, "multiple-results". Its default value is false, or the current data model, true will produce the new data model. I would approach it by either subclassing SQLDocument or copying it to SQLDocument2. This requires you to actually understand the DTM, you can't just hack in calls and hope it works. Also the SQL Extension uses the DTM in very unique way which has caused a discussion or two in the past. Here are the primary differences. Metadata: The Metadata elements are built up from the constructor of SQLDocument. As the elements are put together, an array m_colHeadersIdx is populated to track the Node ID's of the first attribute in the metadata element for each column. Inside the metadata/col, the attributes are linked together through the DTM. Now for each column of each row in the row-set, the metadata is the same so there is no need to make a copy of the data. What we do its set the first attribute node for a col to equal the DTM Node ID of the respective col in the ColHeadersIdx array. You will need to manage a set of ColHeaderIdx's depending on what result set you are currently working with. Streaming Mode In streaming mode, after the first row is read we just populate the old data with the new values. Also the Next Node ID for the current row points back to itself, so the document is stuck in an endless loop until the end of the result set is hit. Once you hit the end of the result set, you can't just set everything back to zero and restart, you will need to step back in the DTM and link in a whole new result-set node then set it up to be filled correctly in both streaming and non streaming modes. Also the notion of finding result-set[n] may not be as easy as you think. The only thing you get when a node is walked is a NodeID, so you will also need to track the Node ID each time you cross a result set so you can determine what result-set you are being asked to produce data for. What you were doing wrong before is complicated to explain in an email. Further investigation of how the DTM works is required, I also suggest to look at the DTMDocument code. If you want, send me a message and we can schedule a call and I will try to explain the workings of the DTM. >>> Changing the Data Model in General <<< Before we make any changes to the data model, I think we need to propose a vote or a call for requests. If there is a possible feature that we will add in the future it would be nice if the data model supported it now. As a suggestion I think we should further wrap the whole data set with a <query> element. I am also interested in the structure of your Output Parameters element. Regards John G [EMAIL PROTECTED] wrote: > Hi John, > > I probably should not have said that, I know better. I am kind of up > against the wall on a project that will use SQL with Xalan. I need > output parameters to be working ASAP. I am a bit behind where I should > be, so I have been not to pleasant lately. Anyhow I will try to do > whatever I can to facilitate the enhancements to the SQL library. > > My original suggestion on the output format was to change it to > something > like: > > <sql> > <row-set> > <metadata> > <column-header/>... > </metadata> > <row> > <col>...</col>... > </row> > . > . > . > </row-set> > . > . > . > </sql> > > I know very little about how the DTM works. In my ignorance, I assumed > that if I changed the <row-set>s to behave like <row>s it would work. > In your earlier e-mail you implied that this is not the case, > something about IDs. You also mentioned something about the traversal > of the <row-set>. I re-read that e-mail many times, I am still not > sure that I "get it". It sounded reasonable (even if I did not > completely understand it). So, I tried doing just what you had > suggested. Leaving SQLDocument alone and adding getNextResultSet(). In > coding this I was concerned about the concurrency issue that you > mention. I am also concerned that it requires the user to take > specific action to retrieve subsequent result sets. In some cases the > number of result sets from a stored procedure may vary. An XSLT could > do multiple getNextResultSet() calls until there are no more, but it > would seem simpler to just do the one query() call and pass the > results through an appropriate transformation, doing something like > row-set[#] to get a specific result set or just letting a template > handle the <row-set>s when it does not matter (for example). > > Databases vary, but in general each result set should be processed > before subsequent result sets. Likewise the output parameters should > only be retrieved after all results have been fetched. In the context > of Xalan this probably means that if you want to access something in > result set 1 while your current context is result set 2, you would > need to use non-streaming mode. Essentially the result sets would be > like rows. > > On parameter passing. It seems to me that extension functions > accessing Xalan variables is a supported technique. It is certainly > easy enough to do. Since this mechanism exists, I am hesitant to add > another mechanism (addParameter()). Also, as I mentioned, in my case > often what I want to use to set a parameter is already in a variable. > Other than that it may be subjective as to which syntax is cleaner. My > parsing code (although very > simple) is still smart enough to handle things like stored procedures named > OUT. It also handles embedded strings, etc. What it needs to parse is pretty > simple (?= CALL sp arg OUT,...) so it does not need to be very smart to work > well. I had never thought about column names in "[]". So JDBC does not > abstract this? If not that could be a problem (since parameters '?' can > appear almost anywhere). If we look for [type=name] and pass through > anything that does not match the full pattern would that work? In other > words, can a column name contain '='? Off the top of my head I think that > SQL says no. Anyhow, we can certainly get parameters in with addParameter(). > > For the output paremeters. It seems that we need to return the > parameters with the DTM from the query(). The obvious choices (to me > anyway) are to add another child under <sql> somewhere. This could > either be something that looks exactly like a <row-set> or perhaps > just similar to a <row-set> but with a different name. Personally I do > not know that it matters much, although since we have this great XML > (self describing data format), seems a shame not to be able to tell if > what we have is a result set or output parameters. Either way I would > still prefer it to be another child of <sql> (as in the example) > instead of from another call to getNextResultSet() (or even > getOutputParameters() for that matter, although I am less opposed to a > function like getOutputParameters() than getNextResultSet()). > > You mention "addOutputParameterWithType()" as in keeping with the JDBC > design pattern. I would have to disagree here as well. For adding > parameters output parameters CallableStatement just inherits the > setXXX method of PreparedStatement. There is an additional step to > register the output parameters. Being lazy, I try to use existing > patterns/syntax/APIs where ever possible. However I am not sure that > the JDBC pattern is that suitable to Xalan. At least as far as the > get/set/register things go. Even in Java I find myself putting > wrappers around the parameter stuff so that I do not need to deal with > it. > > Well this e-mail is getting a bit long, so I am going to give it a > rest for a bit. I am not exactly sure how to proceed from here. If > there are problems with doing something like getNextResultSet(), may > not want to proceed with that. I loathe duplicating code (especially > code that has complicated > semantics) so subclassing SQLDocument does not appeal to me at the moment. > Also, the more I think about it, the less I like having the output > parameters being indistinguishable from a result set. What about adding a > <output-parameters> element? I keep thinking that I prefer just having one > document (<sql>) with multiple <row-set>s and perhaps <output-parameters> > children. What if we did something like: > > <sql> > <result-set> > <metadata/> <!-- content not included --> > <row-set> > <row/> <!-- content not included --> > </row-set> > </result-set> > <result-set/><!-- repeats for multiple result sets, content > not included. --> > <output-parameters/> <!-- content not included --> </sql> > > This way sql/result-set could work about the same as sql did > previously, or maybe sql/result-set[1] would be better. > > Well, I said I was going to take a break... so... > > I'll be back... > Art > > -----Original Message----- > From: John Gentilin [mailto:gentijo@;eyecatching.com] > Sent: Tuesday, October 22, 2002 6:01 PM > To: Art Welch > Cc: [EMAIL PROTECTED] > Subject: Re: SQL Extension enhancement/replacement > > Art, > > The SQL extension is not my baby, it belongs to the whole community. I > just happen to be the first point of contact in managing it. I am also > not resistant to change, even if breaks existing interfaces, as long > as there is a clear rational that the change will add value and is > worth the effort. > > I think in general, handling callable statements and multiple result > sets will be a welcome addition to the code. As for the other changes, > if you could make a case for what they may fix that was broken before, > or what functionality they add and how it makes the existing code > better, I would be willing to put it up for a vote. I tried to address > some of the issues I saw with the code in my previous response, I am > interested in what you don't agree in. > > To address the other issues of OUT and INOUT parameters. I suggest > that we keep with the design same pattern (the same one that was > abstracted from the JDBC drivers) and add methods such as > addOutputParameterWithType()... This action could create a HashMap (is > Xalan allowed to use JAVA2 object yet ??) holding the name / value. > The in cquery, using those parameters as a mechanism for the > registerOutputParameter JDBC calls. > > I would stay away from parsing items out of the Query String such as > the OUT keyword because you can't regulate what someone will call > their SP. As silly as it may sound, what if I wanted to call the SP > "OUT", it would probably kill your parse routine. Same with the > [name=type] since SQL calls to an Access DB sometimes require the [ ] > be place around the column name. I can't tell you how many times I > have been called in to support a DB that was hack together with column > names that include spaces and quotes. :-( > > As for building up ResultSets to house the output parameters, that > sounds like it may be the long way around. Why not sub class > SQLDocument and override the constructor, extractSQLMetaData, > addRowToDTMFromResultSet. > > In the constructor, add the HashMap that will be used to pull the > output parameters from the last of the result set. > > In extractSQLMetaData & addRowToDTMFromResultSet, use the HashMap as a > pseudo ResultSet. > > On a side note, adding a function to XConnection to get the next > result set may have a design caveat. The XConnection can be used to > produce more than one query. Its probably the odd case where the XSL > designer notices that 2 XConnections would give you separate contexts. > > If you perform a query that has multiple results sets then while > processing that query, perform another query your unprocessed result > sets will probably be lost. To fix this, we could move the > getNextResultSet() to the SQLDocument itself since it always maintains > the current query context. The notion would be that SQLDocuments are > chained. > > You never did answer my previous question. Can you concurrently > process result sets on a multi result set query without loosing the > context ?? > > Thank you for all the effort you are putting into the SQL Extensions. > > Regards > John G > > -------------------------------------- > John Gentilin > Eye Catching Solutions Inc. > 18314 Carlwyn Drive > Castro Valley CA 94546 > > Contact Info > [EMAIL PROTECTED] > Ca Office 1-510-881-4821 > NJ Office 1-732-422-4917 -- -------------------------------------- John Gentilin Eye Catching Solutions Inc. 18314 Carlwyn Drive Castro Valley CA 94546 Contact Info [EMAIL PROTECTED] Ca Office 1-510-881-4821 NJ Office 1-732-422-4917
