Hi Mark,
thanks for the reply. It all sounds reasonable, although if I were to use
it, I would consider my first task to be to create some higher level
framework that suited me. I guess that is because one (well, my 'sort' of
user anyway) does a lot of of database access and wants it to be as
seamless as possible.
I know that you're thinking about the lower level now, but consider an
array of directories as then one can refer to columns by name. I know
there are some interfaces (like windows ADODBC, and I seem to recall JDBC
was a bit like this) where you have to work really hard to fetch each row,
and then you have to work really hard to fetch each field from the row. It
feels a whole lot more rexxish to me to be able to say
osql~exec(myQuery)
do row over osql~rows
if row['name'] = 'Smith' then do ... end
end
I even modify the directories like so:
.directory~new~~setmethod('UNKNOWN',"if self~hasIndex(lower(arg(1))) then
return self[lower(arg(1))] ; else return .nil")
And then force the field names to lower case so that one doesn't have to
know the case of the field names to use them.
Anyway, I offer this because you sounded like you wanted to hear from
someone who used a rdbms from rexx and I do a lot.
keep up the great work.
Jon
On 3 June 2012 21:47, Mark Miesfeld <[email protected]> wrote:
> On Sat, Jun 2, 2012 at 1:25 PM, Sahananda (Jon) Wolfers <
> [email protected]> wrote:
>
> Jon,
>
> Please also refer to my follow up to Rick's other post.
>
> > One thing that occurs to me is that ooSQL.cls is not a specific enough
> name
> > for this class. I hope that in the future there will be similar
> frameworks
> > for other databases (as the SQLLite people are the first to admit that
> it is
> > not suitable for multiple client use).
> > perhaps ooSQLLiteConst.cls would do as a name or you might think of
> > something more suitable.
>
> This can be changed of course. the ooSQL.cls is also needed when using
> the functional interface. I named it this because the SQLite constants are
> all named:
>
> SQLITE_const
>
> and .ooSQL~ is the same number of letters as the SQLITE_ prefix so that
> you have:
>
> SQLITE_NOMEM
> .ooSQL~NOMEM
>
> Which I thought would be easy to explain to the "I won't touch objects"
> group, or really anyone, by saying exactly replace SQLITE_ with .ooSQL~
>
> Of course, I also was hoping I could convince people to read the SQLite
> documentation rather than write a new document that just repeats what the
> SQLite documentation says.
>
> The ooSQL class is not currently a mixin class, but originally it was
> going to be. This could be changed to:
>
> ::class 'ooSQLiteConstants public mixin
>
> and if people wanted to shorten things they could inherit the class in
> their programs:
>
> ::class 'C' inherit ooSQLiteConstants
>
> say .C~NOMEM
>
> which would produce 7.
>
> > I have made use of two ooRexx designed SQL frameworks, both wrappers for
> > rexxSQL
>
> Following what I said in the other post about ooSQLite, currently, not
> really being a framework and not having a higher level 'DataBase' object.
>
> > Firstly oRexxSQL which provided two approaches, one allowing a query to
> > emulate a stream, and the other allowing cursoring through a result set.
> >
> > I found it quite confusing with it's miriad methods, and after I found a
> way
> > to use it to return data settled down to use a couple of classes and
> > methods.
>
> ooSQLite does / will also have a myriad of methods because I would like to
> be able access all of SQLite's APIs from my Rexx program.
>
> But, 90% of them would not normally be used. You could un-confuse
> yourself here by only using the few needed.
>
> Plus, if a higher level abstraction is added as a 'DataBase' object, the
> myriad of methods would not be exposed in that object.
>
> > I was happy with that until I saw Lee Peedin's approach, which returned a
> > dataset as an array of directory objects. I have since been using an
> > adaptation of that and as a user I am very satisfied.
>
> Right now data returned from a SELECT is returned as an array of arrays.
>
> Originally, before I wrote any code, I was thinking there would be a
> ResultSet object. I toyed with that. And I tried a 2-dimensional array
> and an array of directory objects. (I have used an array of directory
> objects in my CSV "databases.")
>
> For what I've coded so far, I found an array of arrays to be the best.
> This uses the convention that the first item in the array is an array of
> the column names. Array[2] through Array[array~items + 1] are the rows in
> the result set.
>
> I'm thinking now that it would be easy enough to add a way for the user to
> specify how the results of queries should be returned. An array of arrays
> or an array of directory objects, or even possibly a stem of stems.
>
> SQLite doesn't have cursors. But, it does use a lot of callbacks and
> actually has a number of different ways of getting the result of a SELECT.
>
> Here again, ooSQLite supports all of those ways. With my thinking that it
> is better to let the user pick the method most suitable for them, rather
> than providing one method and let the user live with it.
>
> SQLite has a 'prepared statement' which you can use to step through each
> row of data from a select. That is actually the method I like the best.
> Something like this:
>
> stmt = .ooSQLiteStmt~new(dbConn, 'SELECT * FROM foods ORDER BY name;')
> stepRC = stmt~step
> if stepRC == .ooSQL~row then do
> colCount = stmt~columnCount
> header = ''
> do i = 1 to colCount
> header ||= stmt~columnName(i)~left(20)
> end
> say header
> say '='~copies(80)
> do while stepRC == .ooSQL~row
> row = ''
> do i = 1 to colCount
> row ||= stmt~columnText(i)~left(20)
> end
> say row
>
> stepRC = stmt~step
> end
> end
> Which produces output similar to:
>
> id type_id name
> ======================================================
> 86 4 A1 Sauce
> 213 9 All Day Sucker
> 212 9 Almond Joy
> 189 8 Apple
> 129 7 Apple Cider
> 190 8 Apple Pie
> ...
>
> As I mentioned in the other post, this is all a rather low-level
> abstraction.
>
> A convenience method is exec() which will return an array of arrays.
> Something like:
>
> dbName = 'ooFoods.rdbx'
>
> dbConn = .ooSQLiteDB~new(dbName, .ooSQL~OPEN_READWRITE)
>
> resultSet = dbConn~exec('SELECT * FROM foods ORDER BY name;', .true)
>
> z = printResultSet(resultSet)
>
> ret = dbConn~close
>
> return ret
>
> In the above, resultSet is an array of arrays and printResultSet() is just
> code to deal with the data.
>
> > I made a rather odd design decision in my adaptation which I have never
> been
> > sure whether or not I regretted, the DML methods all return true if
> there is
> > an error and false if not. This has saved me hundreds of lines of code,
> but
> > I am unsure that it reads well.
> >
> > The main methods It provides are connect, disconnect, query (ie select
> > queries), execute (ie the other DML queries and DDL queries), commit,
> > rollback, and describe. Where a dataset is returned it is at the rows
> > attribute. Other main attributes are database, rowcount, details (the
> > contents of the status stem passed back by rexxSQL, last_insert_id.
>
> If you've followed what I said in the other post, currently the classes in
> ooSQLite are low-level abstractions. There is no higher-level 'Database'
> abstraction. I'm thinking of adding a default implementation of a
> 'DataBase.' This would be along the lines of your adaption above where the
> lower-level work is done for you.
>
> --
> Mark Miesfeld
>
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Oorexx-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>
>
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Oorexx-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/oorexx-devel
