Open Object Rexx

Oops, looks like I jumped the gun on the ErrorHandler
comments...didn't scroll down far enough to see that this was a
locally defined class.

Rick

On Sat, Jun 2, 2012 at 8:28 AM, Rick McGuire <object.rexx@...> wrote:
> Mark,
>
> Found some time to start looking at ooSQLLite implementation.  I'm
> guessing you don't have a draft of the APIs yet, which is really what
> I was interested in looking at right now.  However, I found the
> samples pretty useful for getting a flavor of what you have.
>
> There's one thing I picked up as a potential concern looking at the
> samples, and that's the ErrorHandler class.  This seems like a pretty
> generic name with a lot of potential for name collisions.  It might be
> better prefixed with ooSQL, although there might be other approaches
> you might want to consider:
>
> 1)  implement the error handler methods as part of a main package
> class.  In other words, rather than call ErrorHandler~stmtError, you'd
> call ooSQLite~stmtError.  Since these methods clearly indicate the
> operation being performed, having a separate .ErrorHandler class
> doesn't make this any clearer.
>
> 2)  Since you have different types of errors, consider adding error
> methods to the objects involved in the errors.  For example, rather
> than coding
>
>  .ErrorHandler~stmtError(stmt, db, 'INIT', 'contacts')
>
> code
>
>  stmt~error('INIT', 'contacts)
>
> This assumes the DB is obtainable from the stmt object (which is
> probably a good thing since it was created from the DB).
>
> In a similar fashion, rather than directly instantiate the statement using
>
>  stmt = .ooSQLiteStmt~new(db, sql)
>
> it would be better from an oo design standpoint to ask the DB object
> for a statement:
>
>  stmt = db~stmt(sql)
>
> you might want to create an SQLStmt class that defines the methods of
> the Stmt class, and then the DB object would return an instance of the
> ooSQLiteStmt class that implements the methods.  This allows the DB
> object to have a bit of flexibility for determining what class to use
> for a particular statement.
>
> Rick

On Sat, Jun 2, 2012 at 5:28 AM, Rick McGuire <object.rexx@...> wrote:

> Found some time to start looking at ooSQLLite implementation.

Great, thanks Rick.  I was hoping to get some feedback on the
implementation, which really is completely flexible at this point.

> I'm
> guessing you don't have a draft of the APIs yet, which is really what
> I was interested in looking at right now.

Yeah, I've only started a draft in DocBook, it doesn't even compile
yet.  You could look at ooSQLite.cls which contains all the classes
and methods.  It is almost entirely implemented in native code so the
file is mostly just a listing of the methods of each class.

This is my original approach:

SQLite provides a large number, (I think around 200,) C APIs.
However, only a small subset of them are really needed to effectively
create and use databases.  I would like ooSQLite to eventually be
complete, that is to have access to all the APIs, except probably for
some where it just doesn't make sense.

I wanted to wrap up the APIs in classes and to also provide a more
'classic Rexx' interface for those people who shy away from objects.

The 'classic Rexx' interface is pretty easy to describe and implement,
it is basically a one to one mapping of ooSQLite ::routines to SQLite
C APIs.  This will excluding any deprecated SQLite C APIs and only
provide the recommended API for the deprecated API.

The object orientated interface is the one I'm more interested in and
hoping to get comments on.  I would like to get a good design that the
committers are happy with before a first release.  The only reason for
what I'm calling the "Preview" release is to have something concrete
to look at, for discussion.

Here is an outline of the classes:

*  In SQLite, 95% of what any one would use are is done though APIs
that either require a database connection or require a prepared
statement.  So the 2 main objects in ooSQLite are:

Database connection:

::class 'ooSQLiteDB' public  (in SQLite -> struct sqlite3)

Prepared statement:

::class 'ooSQLiteStmt' public   (in SQLite -> struct sqlite3_stmt)


*  Then there are a number SQLite process wide settings for tuning its
operation and querying its operation.  Settings like changing the
process soft heap limit and queries like getting the high water memory
used.  So I have the ooSQLite class:

::class 'ooSQLite' public

composed entirely of class methods.  None of the methods should
require any ooSQLite objects as arguments.  As I said they concern
things about the database engine itself, they are not database or
statement specific.

*  SQLite has a large number of numeric defines for return codes and
arguments.  I've put all of these in a separate class composed of
constants:

::class 'ooSQL' public

    Each constant is named the same as the SQLITE_x constant, minus the SQLITE_
    part and is intended to be used as:  .ooSQL~x  That is:

    .ooSQL~OK          ->  is the value of SQLITE_OK
    .ooSQL~ERROR  ->  is the value of SQLITE_ERROR
    .ooSQL~NOMEM  -> is the value of SQLITE_NOMEM

Since some of the defines are flags, there is one class method,
merge(), which basically does a binary or of any number of flags so
that the Rexx programmer doesn't need to try and do that in Rexx code.

I'm thinking about adding a method that converts the constant to a
string which is mostly only needed as a debugging aid so that in a
Rexx program you could print out "NOMEM" rather than having to know
that NOMEM is 7.  Not sure if that is feasible since many of the
defines are the same numeric value.

*  A mutex class is implemented, which I'm not sure if it is needed,
or even desirable.

::class 'ooSQLiteMutex' public (in SQLite -> struct sqlite3_mutex)

Its inclusion is based on my design goal of allowing access to the
complete SQLiter API, where it makes sense.

And what I saw in ooDialog.  Which was that there were a number of
places where only the simplest things could be done, but it would have
been just as easy to allow access to a full API.  Sure, if you didn't
understand Windows, you probably couldn't use the not so simple
feature.  But, if you did understand the API, there was no way to take
advantage of it from Rexx.

So the ooSQLiteMutex class might be something that only a very few
Rexx programmers would use, but if it is not included then it is not
available to those few.

*  There are 2 other classes not written yet.

One is a back up object, probably ooSQLiteBackUp.  SQLite has a struct
sqlite3_backup with APIs that use the struct to safely allow database
back up while the database is in use.

The other is some type of Blob object.  SQLite databases can store
blobs.  I'm hazy on how to interface this with Rexx code.

Lastly, I would like add classes and / or methods that make using
ooSQLite easier for the Rexx programmer.  But, I've never worked with
databases much at all, and I really have no idea what those classes or
methods would be.  Or if they even exist.


> There's one thing I picked up as a potential concern looking at the
> samples, and that's the ErrorHandler class.

As you saw later this was a local class.  As far as the examples go,
they can be completely redone.  Since my database experience is
limited, my examples may not be very good.

...

>  stmt~error('INIT', 'contacts)
>
> This assumes the DB is obtainable from the stmt object (which is
> probably a good thing since it was created from the DB).

It is:

ooSQLiteStmt::dbHandle()  returns the Rexx ooSQLiteDB object used in
the statement creation.

One thing I've done is stick mostly to the SQLite API names so that it
is easy to look up the details of the API in the SQLite documentation.
 In most places the method names make sense to me.  This is one case
where it might not.

The other thing is the class name ooSQLiteDB.  Technically this is a
*connection* to a specific database, not a database.  In general use
the 2 seem interchangeable to me.  But maybe the class name should be
more along the line of ooSQLiteConn or ooSQLiteConnection depending on
how much abbreviation you want to do.

SQLite allows multiple connections to the same database, from the same
thread or proces, and from mutltiple threads and processes.  So a Rexx
program can or could have any number of ooSQLiteDB objects.  The
compile options used for the database engine are said to produce a
completely thread safe engine.


I'm going to come back to the following in another e-mail, this one is
already pretty long.

> In a similar fashion, rather than directly instantiate the statement using
>
>  stmt = .ooSQLiteStmt~new(db, sql)
>
> it would be better from an oo design standpoint to ask the DB object
> for a statement:
>
>  stmt = db~stmt(sql)
>
> you might want to create an SQLStmt class that defines the methods of
> the Stmt class, and then the DB object would return an instance of the
> ooSQLiteStmt class that implements the methods.  This allows the DB
> object to have a bit of flexibility for determining what class to use
> for a particular statement.

--
Mark Miesfeld

Hi Mark,

I'm following this with interest.

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.

I have made use of two ooRexx designed SQL frameworks, both wrappers for
rexxSQL

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.

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.

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.

I mention this in case it is helpful.

keep up the good work,

Jon



On 2 June 2012 18:15, Mark Miesfeld <miesfeld@...> wrote:

> On Sat, Jun 2, 2012 at 5:28 AM, Rick McGuire <object.rexx@...>
> wrote:
>
> > Found some time to start looking at ooSQLLite implementation.
>
> Great, thanks Rick.  I was hoping to get some feedback on the
> implementation, which really is completely flexible at this point.
>
> > I'm
> > guessing you don't have a draft of the APIs yet, which is really what
> > I was interested in looking at right now.
>
> Yeah, I've only started a draft in DocBook, it doesn't even compile
> yet.  You could look at ooSQLite.cls which contains all the classes
> and methods.  It is almost entirely implemented in native code so the
> file is mostly just a listing of the methods of each class.
>
> This is my original approach:
>
> SQLite provides a large number, (I think around 200,) C APIs.
> However, only a small subset of them are really needed to effectively
> create and use databases.  I would like ooSQLite to eventually be
> complete, that is to have access to all the APIs, except probably for
> some where it just doesn't make sense.
>
> I wanted to wrap up the APIs in classes and to also provide a more
> 'classic Rexx' interface for those people who shy away from objects.
>
> The 'classic Rexx' interface is pretty easy to describe and implement,
> it is basically a one to one mapping of ooSQLite ::routines to SQLite
> C APIs.  This will excluding any deprecated SQLite C APIs and only
> provide the recommended API for the deprecated API.
>
> The object orientated interface is the one I'm more interested in and
> hoping to get comments on.  I would like to get a good design that the
> committers are happy with before a first release.  The only reason for
> what I'm calling the "Preview" release is to have something concrete
> to look at, for discussion.
>
> Here is an outline of the classes:
>
> *  In SQLite, 95% of what any one would use are is done though APIs
> that either require a database connection or require a prepared
> statement.  So the 2 main objects in ooSQLite are:
>
> Database connection:
>
> ::class 'ooSQLiteDB' public  (in SQLite -> struct sqlite3)
>
> Prepared statement:
>
> ::class 'ooSQLiteStmt' public   (in SQLite -> struct sqlite3_stmt)
>
>
> *  Then there are a number SQLite process wide settings for tuning its
> operation and querying its operation.  Settings like changing the
> process soft heap limit and queries like getting the high water memory
> used.  So I have the ooSQLite class:
>
> ::class 'ooSQLite' public
>
> composed entirely of class methods.  None of the methods should
> require any ooSQLite objects as arguments.  As I said they concern
> things about the database engine itself, they are not database or
> statement specific.
>
> *  SQLite has a large number of numeric defines for return codes and
> arguments.  I've put all of these in a separate class composed of
> constants:
>
> ::class 'ooSQL' public
>
>    Each constant is named the same as the SQLITE_x constant, minus the
> SQLITE_
>    part and is intended to be used as:  .ooSQL~x  That is:
>
>    .ooSQL~OK          ->  is the value of SQLITE_OK
>    .ooSQL~ERROR  ->  is the value of SQLITE_ERROR
>    .ooSQL~NOMEM  -> is the value of SQLITE_NOMEM
>
> Since some of the defines are flags, there is one class method,
> merge(), which basically does a binary or of any number of flags so
> that the Rexx programmer doesn't need to try and do that in Rexx code.
>
> I'm thinking about adding a method that converts the constant to a
> string which is mostly only needed as a debugging aid so that in a
> Rexx program you could print out "NOMEM" rather than having to know
> that NOMEM is 7.  Not sure if that is feasible since many of the
> defines are the same numeric value.
>
> *  A mutex class is implemented, which I'm not sure if it is needed,
> or even desirable.
>
> ::class 'ooSQLiteMutex' public (in SQLite -> struct sqlite3_mutex)
>
> Its inclusion is based on my design goal of allowing access to the
> complete SQLiter API, where it makes sense.
>
> And what I saw in ooDialog.  Which was that there were a number of
> places where only the simplest things could be done, but it would have
> been just as easy to allow access to a full API.  Sure, if you didn't
> understand Windows, you probably couldn't use the not so simple
> feature.  But, if you did understand the API, there was no way to take
> advantage of it from Rexx.
>
> So the ooSQLiteMutex class might be something that only a very few
> Rexx programmers would use, but if it is not included then it is not
> available to those few.
>
> *  There are 2 other classes not written yet.
>
> One is a back up object, probably ooSQLiteBackUp.  SQLite has a struct
> sqlite3_backup with APIs that use the struct to safely allow database
> back up while the database is in use.
>
> The other is some type of Blob object.  SQLite databases can store
> blobs.  I'm hazy on how to interface this with Rexx code.
>
> Lastly, I would like add classes and / or methods that make using
> ooSQLite easier for the Rexx programmer.  But, I've never worked with
> databases much at all, and I really have no idea what those classes or
> methods would be.  Or if they even exist.
>
>
> > There's one thing I picked up as a potential concern looking at the
> > samples, and that's the ErrorHandler class.
>
> As you saw later this was a local class.  As far as the examples go,
> they can be completely redone.  Since my database experience is
> limited, my examples may not be very good.
>
> ...
>
> >  stmt~error('INIT', 'contacts)
> >
> > This assumes the DB is obtainable from the stmt object (which is
> > probably a good thing since it was created from the DB).
>
> It is:
>
> ooSQLiteStmt::dbHandle()  returns the Rexx ooSQLiteDB object used in
> the statement creation.
>
> One thing I've done is stick mostly to the SQLite API names so that it
> is easy to look up the details of the API in the SQLite documentation.
>  In most places the method names make sense to me.  This is one case
> where it might not.
>
> The other thing is the class name ooSQLiteDB.  Technically this is a
> *connection* to a specific database, not a database.  In general use
> the 2 seem interchangeable to me.  But maybe the class name should be
> more along the line of ooSQLiteConn or ooSQLiteConnection depending on
> how much abbreviation you want to do.
>
> SQLite allows multiple connections to the same database, from the same
> thread or proces, and from mutltiple threads and processes.  So a Rexx
> program can or could have any number of ooSQLiteDB objects.  The
> compile options used for the database engine are said to produce a
> completely thread safe engine.
>
>
> I'm going to come back to the following in another e-mail, this one is
> already pretty long.
>
> > In a similar fashion, rather than directly instantiate the statement
> using
> >
> >  stmt = .ooSQLiteStmt~new(db, sql)
> >
> > it would be better from an oo design standpoint to ask the DB object
> > for a statement:
> >
> >  stmt = db~stmt(sql)
> >
> > you might want to create an SQLStmt class that defines the methods of
> > the Stmt class, and then the DB object would return an instance of the
> > ooSQLiteStmt class that implements the methods.  This allows the DB
> > object to have a bit of flexibility for determining what class to use
> > for a particular statement.
>
> --
> 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
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>

On Sat, Jun 2, 2012 at 1:25 PM, Sahananda (Jon) Wolfers <
sahananda@...> 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

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 <miesfeld@...> wrote:

> On Sat, Jun 2, 2012 at 1:25 PM, Sahananda (Jon) Wolfers <
> sahananda@...> 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
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>
>

On Sun, Jun 3, 2012 at 2:33 PM, Sahananda (Jon) Wolfers <
sahananda@...> wrote:

> 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
>
>

Well, I think the above is close to what I have now:

results = osql~exec(myQuery)

do row over results
  if row[2] = 'Smith' then do ... end
end

What I was saying was that it wouldn't be too hard at this point to let the
user specify if the returned result set was an array of arrays or an array
of directory objects.

--
Mark Miesfeld

Mark, lets assume your table structure is something like:
firstname
lastname
address
city
state
zip

Now lets assume your sql query was simply:
select * from table

Both your and Jon's query would work fine UNTIL the point where it's determined that we now need a 
middleinitial column between firstname and lastname.  The beauty of SQL is that column can be 
inserted and the integrity of the data is not harmed.

HOWEVER, if your case of using an array of arrays, every statement in every script that expected the 
lastname to be the 2nd column to be returned would have to now be changed to the 3rd column.

But, hey, what do I know.

Lee

On 6/3/2012 11:24 PM, Mark Miesfeld wrote:
> On Sun, Jun 3, 2012 at 2:33 PM, Sahananda (Jon) Wolfers <sahananda@...
> <mailto:sahananda@...>> wrote:
>
>     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
>
> Well, I think the above is close to what I have now:
> results = osql~exec(myQuery)
> do row over results
>    if row[2] = 'Smith' then do ... end
> end
> What I was saying was that it wouldn't be too hard at this point to let the user specify if the
> returned result set was an array of arrays or an array of directory objects.
> --
> 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/
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7192 (20120603) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>
> _______________________________________________
> Oorexx-devel mailing list
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7192 (20120603) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>

Hi Lee,

good to hear from you again.  I hope your are well!

I have been thinking about this some more, and I think that the low level
structure Mark is aiming for where you have an array where the first
element is metadata and the rest is data will make any high level wrapper
around it difficult to code and probably rather inefficient (assuming I
have understood correctly what he is aiming for).

To dig a single column out of a dataset would then require something like
this:

do row over rows~section(2) -- skip the metadata
   say row[rows[1]~index('fieldname')]
end

Which feels very awkward to me

There is a Rexx precedent in the way that stems are used to return results
from external functions where stem.0 holds the metadata (number of results)
and the rest of the stem the data.

I never like using that, and my opinion fwiw is that it would be better to
create a structure where the data and metadata are kept separate even at
the low level design.

just my two more cents worth

Jon


On 4 June 2012 12:47, Lee Peedin <lee@...> wrote:

> Mark, lets assume your table structure is something like:
> firstname
> lastname
> address
> city
> state
> zip
>
> Now lets assume your sql query was simply:
> select * from table
>
> Both your and Jon's query would work fine UNTIL the point where it's
> determined that we now need a
> middleinitial column between firstname and lastname.  The beauty of SQL is
> that column can be
> inserted and the integrity of the data is not harmed.
>
> HOWEVER, if your case of using an array of arrays, every statement in
> every script that expected the
> lastname to be the 2nd column to be returned would have to now be changed
> to the 3rd column.
>
> But, hey, what do I know.
>
> Lee
>
> On 6/3/2012 11:24 PM, Mark Miesfeld wrote:
> > On Sun, Jun 3, 2012 at 2:33 PM, Sahananda (Jon) Wolfers <
> sahananda@...
> > <mailto:sahananda@...>> wrote:
> >
> >     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
> >
> > Well, I think the above is close to what I have now:
> > results = osql~exec(myQuery)
> > do row over results
> >    if row[2] = 'Smith' then do ... end
> > end
> > What I was saying was that it wouldn't be too hard at this point to let
> the user specify if the
> > returned result set was an array of arrays or an array of directory
> objects.
> > --
> > 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/
> >
> >
> > __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 7192 (20120603) __________
> >
> > The message was checked by ESET NOD32 Antivirus.
> >
> > http://www.eset.com
> >
> >
> >
> >
> > _______________________________________________
> > Oorexx-devel mailing list
> > Oorexx-devel@...
> > https://lists.sourceforge.net/lists/listinfo/oorexx-devel
> >
> >
> >
> > __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 7192 (20120603) __________
> >
> > The message was checked by ESET NOD32 Antivirus.
> >
> > http://www.eset.com
> >
>
>
> ------------------------------------------------------------------------------
> 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
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>

On Mon, Jun 4, 2012 at 4:47 AM, Lee Peedin <lee@...> wrote:

> Mark, lets assume your table structure is something like:
> firstname
> lastname
> address
> city
> state
> zip
>
> Now lets assume your sql query was simply:
> select * from table
>
> Both your and Jon's query would work fine UNTIL the point where it's determined that we now need a
> middleinitial column between firstname and lastname.  The beauty of SQL is that column can be
> inserted and the integrity of the data is not harmed.
>
> HOWEVER, if your case of using an array of arrays, every statement in every script that expected the
> lastname to be the 2nd column to be returned would have to now be changed to the 3rd column.

That's a good point Lee.

I'm not arguing against using an array of directories.  I'm just
saying that it this point, (since the array of arrays is already
coded,) it would be just as easy to allow the user to specify how he
wanted the result set returned.  It could be an attribute of the
database.

After your and Jon's arguments, I would make the default an array of
directories.

Yours is a good point, I'm not saying it isn't.  But, if your
application was already coded, why wouldn't you add the new column at
the end, column 6?

> But, hey, what do I know.

Probably a lot more than I do when it comes to databases.

--
Mark Miesfeld

On 6/4/2012 9:11 AM, Mark Miesfeld wrote:
> On Mon, Jun 4, 2012 at 4:47 AM, Lee Peedin<lee@...>  wrote:
>
>> Mark, lets assume your table structure is something like:
>> firstname
>> lastname
>> address
>> city
>> state
>> zip
>>
>> Now lets assume your sql query was simply:
>> select * from table
>>
>> Both your and Jon's query would work fine UNTIL the point where it's determined that we now need a
>> middleinitial column between firstname and lastname.  The beauty of SQL is that column can be
>> inserted and the integrity of the data is not harmed.
>>
>> HOWEVER, if your case of using an array of arrays, every statement in every script that expected the
>> lastname to be the 2nd column to be returned would have to now be changed to the 3rd column.
>
> That's a good point Lee.
>
> I'm not arguing against using an array of directories.  I'm just
> saying that it this point, (since the array of arrays is already
> coded,) it would be just as easy to allow the user to specify how he
> wanted the result set returned.  It could be an attribute of the
> database.
>
> After your and Jon's arguments, I would make the default an array of
> directories.
>
> Yours is a good point, I'm not saying it isn't.  But, if your
> application was already coded, why wouldn't you add the new column at
> the end, column 6?
>

Several reasons - first and foremost: "my application may not be the only one using the database/table".

Trust me, no matter how much you plan ahead and try to map out your database requirements, sooner or 
later a need to add/change/delete will raise it's nasty head.  I've had to work for years on 
database tables where the only "practical" way of adding columns to a table was to do it at the end. 
  It's just not a burden that should have to be born anymore when "Structured" data is an option. :-)

>> But, hey, what do I know.
>
> Probably a lot more than I do when it comes to databases.
>
> --
> 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
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7193 (20120604) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>

One more thing. :-)  I believe it way Rick who mentioned "sql injection" early on in this 
conversation.  David Ruggles developed a method in our "wrapper class" call "sanitize" which became 
a "must use" in our office. No data is to be inserted in changed in a table without sanitizing it 
first (wrapping it in quotes).  Might be a nice enhancement to add to the SQLLite class at some point.

Lee


On 6/4/2012 9:33 AM, Lee Peedin wrote:
>
>
> On 6/4/2012 9:11 AM, Mark Miesfeld wrote:
>> On Mon, Jun 4, 2012 at 4:47 AM, Lee Peedin<lee@...>   wrote:
>>
>>> Mark, lets assume your table structure is something like:
>>> firstname
>>> lastname
>>> address
>>> city
>>> state
>>> zip
>>>
>>> Now lets assume your sql query was simply:
>>> select * from table
>>>
>>> Both your and Jon's query would work fine UNTIL the point where it's determined that we now need a
>>> middleinitial column between firstname and lastname.  The beauty of SQL is that column can be
>>> inserted and the integrity of the data is not harmed.
>>>
>>> HOWEVER, if your case of using an array of arrays, every statement in every script that expected the
>>> lastname to be the 2nd column to be returned would have to now be changed to the 3rd column.
>>
>> That's a good point Lee.
>>
>> I'm not arguing against using an array of directories.  I'm just
>> saying that it this point, (since the array of arrays is already
>> coded,) it would be just as easy to allow the user to specify how he
>> wanted the result set returned.  It could be an attribute of the
>> database.
>>
>> After your and Jon's arguments, I would make the default an array of
>> directories.
>>
>> Yours is a good point, I'm not saying it isn't.  But, if your
>> application was already coded, why wouldn't you add the new column at
>> the end, column 6?
>>
>
> Several reasons - first and foremost: "my application may not be the only one using the database/table".
>
> Trust me, no matter how much you plan ahead and try to map out your database requirements, sooner or
> later a need to add/change/delete will raise it's nasty head.  I've had to work for years on
> database tables where the only "practical" way of adding columns to a table was to do it at the end.
>    It's just not a burden that should have to be born anymore when "Structured" data is an option. :-)
>
>>> But, hey, what do I know.
>>
>> Probably a lot more than I do when it comes to databases.
>>
>> --
>> 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
>> Oorexx-devel@...
>> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7193 (20120604) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>>
>
> ------------------------------------------------------------------------------
> 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
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7194 (20120604) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>

On 6/4/2012 9:00 AM, Sahananda (Jon) Wolfers wrote:
> Hi Lee,
>
> good to hear from you again.  I hope your are well!
>

Thanks Jon, doing real good for an old man with reworked plumbing. :-)

Lee

> I have been thinking about this some more, and I think that the low level structure Mark is aiming
> for where you have an array where the first element is metadata and the rest is data will make any
> high level wrapper around it difficult to code and probably rather inefficient (assuming I have
> understood correctly what he is aiming for).
>
> To dig a single column out of a dataset would then require something like this:
>
> do row over rows~section(2) -- skip the metadata
>     say row[rows[1]~index('fieldname')]
> end
>
> Which feels very awkward to me
>
> There is a Rexx precedent in the way that stems are used to return results from external functions
> where stem.0 holds the metadata (number of results) and the rest of the stem the data.
>
> I never like using that, and my opinion fwiw is that it would be better to create a structure where
> the data and metadata are kept separate even at the low level design.
>
> just my two more cents worth
>
> Jon
>
>
> On 4 June 2012 12:47, Lee Peedin <lee@... <mailto:lee@...>> wrote:
>
>     Mark, lets assume your table structure is something like:
>     firstname
>     lastname
>     address
>     city
>     state
>     zip
>
>     Now lets assume your sql query was simply:
>     select * from table
>
>     Both your and Jon's query would work fine UNTIL the point where it's determined that we now need a
>     middleinitial column between firstname and lastname.  The beauty of SQL is that column can be
>     inserted and the integrity of the data is not harmed.
>
>     HOWEVER, if your case of using an array of arrays, every statement in every script that expected the
>     lastname to be the 2nd column to be returned would have to now be changed to the 3rd column.
>
>     But, hey, what do I know.
>
>     Lee
>
>     On 6/3/2012 11:24 PM, Mark Miesfeld wrote:
>      > On Sun, Jun 3, 2012 at 2:33 PM, Sahananda (Jon) Wolfers <sahananda@...
>     <mailto:sahananda@...>
>      > <mailto:sahananda@... <mailto:sahananda@...>>> wrote:
>      >
>      >     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
>      >
>      > Well, I think the above is close to what I have now:
>      > results = osql~exec(myQuery)
>      > do row over results
>      >    if row[2] = 'Smith' then do ... end
>      > end
>      > What I was saying was that it wouldn't be too hard at this point to let the user specify if the
>      > returned result set was an array of arrays or an array of directory objects.
>      > --
>      > 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/
>      >
>      >
>      > __________ Information from ESET NOD32 Antivirus, version of virus signature database 7192
>     (20120603) __________
>      >
>      > The message was checked by ESET NOD32 Antivirus.
>      >
>      > http://www.eset.com
>      >
>      >
>      >
>      >
>      > _______________________________________________
>      > Oorexx-devel mailing list
>      > Oorexx-devel@... <mailto:Oorexx-devel@...>
>      > https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>      >
>      >
>      >
>      > __________ Information from ESET NOD32 Antivirus, version of virus signature database 7192
>     (20120603) __________
>      >
>      > The message was checked by ESET NOD32 Antivirus.
>      >
>      > http://www.eset.com
>      >
>
>     ------------------------------------------------------------------------------
>     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
>     Oorexx-devel@... <mailto:Oorexx-devel@...>
>     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/
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7193 (20120604) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>
> _______________________________________________
> Oorexx-devel mailing list
> Oorexx-devel@...
> https://lists.sourceforge.net/lists/listinfo/oorexx-devel
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 7193 (20120604) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>

I, too, have been watching this thread with interest;  I recently used
SQLite on a project where a small-footprint DB manager was needed and it
outperformed everyone's expectations.  It seems to me that a rexx-ish
solution would be to use a stem to return queries, whether single rows or
cursors.  The syntax

Sqldb.<column name> with sqldb.0 for the row count seems natural to me, and
does not require ooRexx support, although ooRexx could use it.  

My experience has been with in-core databases, so I can't guess how fast a
DASD query might run, but if one retrieved the column names, etc. for the
query prior to retrieving the data the tail name of the stem items could be
determined with no hassle.  

Mark, if you wish to release some sandbox versions of ooSQLite I will
certainly exercise them, although I might not be able to build them.     

-----Original Message-----
From: Mark Miesfeld [mailto:miesfeld@...] 
Sent: Monday, June 04, 2012 07:11
To: Open Object Rexx Developer Mailing List
Subject: Re: [Oorexx-devel] Some comments on the ooSQLLite extension

On Mon, Jun 4, 2012 at 4:47 AM, Lee Peedin <lee@...> wrote:

> Mark, lets assume your table structure is something like:
> firstname
> lastname
> address
> city
> state
> zip
>
> Now lets assume your sql query was simply:
> select * from table
>
> Both your and Jon's query would work fine UNTIL the point where it's 
> determined that we now need a middleinitial column between firstname 
> and lastname.  The beauty of SQL is that column can be inserted and the
integrity of the data is not harmed.
>
> HOWEVER, if your case of using an array of arrays, every statement in 
> every script that expected the lastname to be the 2nd column to be
returned would have to now be changed to the 3rd column.

That's a good point Lee.

I'm not arguing against using an array of directories.  I'm just saying that
it this point, (since the array of arrays is already
coded,) it would be just as easy to allow the user to specify how he wanted
the result set returned.  It could be an attribute of the database.

After your and Jon's arguments, I would make the default an array of
directories.

Yours is a good point, I'm not saying it isn't.  But, if your application
was already coded, why wouldn't you add the new column at the end, column 6?

> But, hey, what do I know.

Probably a lot more than I do when it comes to databases.

--
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
Oorexx-devel@...
https://lists.sourceforge.net/lists/listinfo/oorexx-devel

On Mon, Jun 4, 2012 at 6:34 AM, Dan Carter <gwcarter@...> wrote:


> Mark, if you wish to release some sandbox versions of ooSQLite I will
> certainly exercise them, although I might not be able to build them.


There is a sandbox, I'm calling it a 'preview,' version on SourceForge
to make it easy for people to download and join in the discussion(s).
 It is already built. The release notes tell you how to set it up.
There is little to no documentation yet, and few examples.

https://sourceforge.net/projects/oorexx/files/ooSQLite/

Read through the ReleaseNotes that displays on the SourceForge page.

Even though the doc for ooSQLite is minimum at this poing, if you look
at the classes and methods in ooSQLite.cls and read the SQLite doc
starting at this page:

http://www.sqlite.org/c3ref/funclist.html

it shouldn't be too hard to play with.  Especially if you have already
done something with SQLite.

In addition, in the ooSQLite directory there is an excel spreadsheet
that lists every SQLite C API and notes whether it is implemented in
ooSQLite or not.

So, SQLite has the API:  sqlite3_data_count which uses a sqlite3_stmt.
 The spreadsheet notes that API is implemented.

If you are using the OO interface in ooSQLite and look at the methods
of the ooSQLiteStmt class you will see the dataCount() method.   That
method does what the SQLite doc for the sqlite3_data_count API says it
does.

In a similar fashion.  If you look at the ooSQLite class: ooSQLiteStmt
class and see it has a method: bindText() if you look at the SQLite
doc, look for sqlite_bind_text and you will have the documentation for
the bindText() method.

If instead you want to use the functional interface:

the routine in ooSQLite: oosqlDataCount() would correspond to sqlite3_data_count

the routine in ooSQLite: oosqlBindText() would correspond to sqlite3_bind_text


> It seems to me that a rexx-ish
> solution would be to use a stem to return queries, whether single rows or
> cursors.

Again, I'm not arguing that an array of arrays is the best solution.
I'm saying it wouldn't be hard to let the user of ooSQLite pick the
format of a result set they wanted to use.

--
Mark Miesfeld

Thanks, Mark.  I will download and start work with it this afternoon.  As it
happens, I have use for such a thing through ooRexx (the oo part) right now.
I will let the list know.  

BTW, huge thanks and congrats for your  hard work and forward thinking.  We
all benefit from it.  
 

-----Original Message-----
From: Mark Miesfeld [mailto:miesfeld@...] 
Sent: Monday, June 04, 2012 08:52
To: Open Object Rexx Developer Mailing List
Subject: Re: [Oorexx-devel] Some comments on the ooSQLLite extension

On Mon, Jun 4, 2012 at 6:34 AM, Dan Carter <gwcarter@...> wrote:


> Mark, if you wish to release some sandbox versions of ooSQLite I will 
> certainly exercise them, although I might not be able to build them.


There is a sandbox, I'm calling it a 'preview,' version on SourceForge to
make it easy for people to download and join in the discussion(s).
 It is already built. The release notes tell you how to set it up.
There is little to no documentation yet, and few examples.

https://sourceforge.net/projects/oorexx/files/ooSQLite/

Read through the ReleaseNotes that displays on the SourceForge page.

Even though the doc for ooSQLite is minimum at this poing, if you look at
the classes and methods in ooSQLite.cls and read the SQLite doc starting at
this page:

http://www.sqlite.org/c3ref/funclist.html

it shouldn't be too hard to play with.  Especially if you have already done
something with SQLite.

In addition, in the ooSQLite directory there is an excel spreadsheet that
lists every SQLite C API and notes whether it is implemented in ooSQLite or
not.

So, SQLite has the API:  sqlite3_data_count which uses a sqlite3_stmt.
 The spreadsheet notes that API is implemented.

If you are using the OO interface in ooSQLite and look at the methods
of the ooSQLiteStmt class you will see the dataCount() method.   That
method does what the SQLite doc for the sqlite3_data_count API says it does.

In a similar fashion.  If you look at the ooSQLite class: ooSQLiteStmt class
and see it has a method: bindText() if you look at the SQLite doc, look for
sqlite_bind_text and you will have the documentation for the bindText()
method.

If instead you want to use the functional interface:

the routine in ooSQLite: oosqlDataCount() would correspond to
sqlite3_data_count

the routine in ooSQLite: oosqlBindText() would correspond to
sqlite3_bind_text


> It seems to me that a rexx-ish
> solution would be to use a stem to return queries, whether single rows 
> or cursors.

Again, I'm not arguing that an array of arrays is the best solution.
I'm saying it wouldn't be hard to let the user of ooSQLite pick the format
of a result set they wanted to use.

--
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
Oorexx-devel@...
https://lists.sourceforge.net/lists/listinfo/oorexx-devel

Mark Miesfeld <miesfeld@...> wrote:

> Here is an outline of the classes:
>
> *  In SQLite, 95% of what any one would use are is done though APIs that
> either require a database connection or require a prepared statement.  So
> the 2 main objects in ooSQLite are:
>
> Database connection:
>
>::class 'ooSQLiteDB' public  (in SQLite -> struct sqlite3)
>
>Prepared statement:
>
>::class 'ooSQLiteStmt' public   (in SQLite -> struct sqlite3_stmt)

Something that catches my eye here isn't really a SQLite issue as such, but
it seems to me that the implication of SQLite C structs having names which
include a "3" is that future versions may be different.  

Do you have a mechanism in mind to allow future versions of ooSQLLite to use
V4/5/6... structs and APIs?  How would a programmer dictate which set were
to be used for a particular db?  Would it not be better to embed a "3" in
the name of the class, whatever DLL (or whatever) it uses, and so on?


-- 
Jeremy Nicoll - my opinions are my own

On Fri, Jun 15, 2012 at 7:33 AM, Jeremy Nicoll - ml sourceforge
<jn.ml.sfrg.25@...> wrote:
> Mark Miesfeld <miesfeld@...> wrote:
>
>> Here is an outline of the classes:
>> ...
>
> Something that catches my eye here isn't really a SQLite issue as such, but
> it seems to me that the implication of SQLite C structs having names which
> include a "3" is that future versions may be different.
>
> Do you have a mechanism in mind to allow future versions of ooSQLLite to use
> V4/5/6... structs and APIs?  How would a programmer dictate which set were
> to be used for a particular db?  Would it not be better to embed a "3" in
> the name of the class, whatever DLL (or whatever) it uses, and so on?

The ooSQLite package has entire SQLite database engine embedded inside
it.  So the programmer can not dictate which set of structs and APIs
it works with.  It works with the set it was built with.

If you had a database written / used with ooSQLite version, say:

ooSQLite: ooSQLite Version 1.0.0.7897 (64 bit)
          Built Jun 14 2012 16:47:20
          Copyright (c) RexxLA 2012-2012.
          All Rights Reserved.

Rexx:     Open Object Rexx Version 4.1.1

SQLite:   SQLite Library Version 3.7.13
          2012-06-11 02:05:22

and SQLite moved on to say, version 5.8.9, that was no longer
compatible with the database file format, then Rexx programmers would
have to decide whether they wanted to move on to ooSQLite 3.55.10,
which has SQLite version 5.8.9 in it, or not.

The Rexx programmer would be faced with the same choices as any other
SQLite user would be faced with.

The name of the structs in SQLite is of no consequence to the Rexx
programmer.  So, I don't really see a problem here.

--
Mark Miesfeld

Mark Miesfeld <miesfeld@...> wrote:

>On Fri, Jun 15, 2012 at 7:33 AM, Jeremy Nicoll - ml sourceforge
><jn.ml.sfrg.25@...> wrote:

> > Do you have a mechanism in mind to allow future versions of ooSQLLite to
> > use V4/5/6... structs and APIs?  How would a programmer dictate which
> > set were to be used for a particular db?  Would it not be better to
> > embed a "3" in the name of the class, whatever DLL (or whatever) it
> > uses, and so on?
>
> The ooSQLite package has entire SQLite database engine embedded inside it.
> So the programmer can not dictate which set of structs and APIs it works
> with.  It works with the set it was built with.

I know; I haven't explained well enough...  The problem is that "the set it
was built with" isn't flexible enough once there are multiple versions of
the engines. 


>If you had a database written / used with ooSQLite version, say:
>
>ooSQLite: ooSQLite Version 1.0.0.7897 (64 bit)
>          Built Jun 14 2012 16:47:20
>          Copyright (c) RexxLA 2012-2012.
>          All Rights Reserved.
>
>Rexx:     Open Object Rexx Version 4.1.1
>
>SQLite:   SQLite Library Version 3.7.13
>          2012-06-11 02:05:22
>
> and SQLite moved on to say, version 5.8.9, that was no longer compatible
> with the database file format, then Rexx programmers would have to decide
> whether they wanted to move on to ooSQLite 3.55.10, which has SQLite
> version 5.8.9 in it, or not.

On a home system that mightn't be a problem but suppose someone was using
ooRexx on a commercial/ production system.  They might be happy to use
SQLite 5.8.9 for new apps / development, but need to keep running the
version of ooSQLite that's based on 3.7.13 for legacy apps, and it's
possible they might even have apps that accessed several dbs, some needing
one version and some the other, eg while migrating specific dbs from one
version of SQLite to another.

Assuming that they'd only install ooRexx once, they'd need a way of making
the older apps use one version of ooSQLite while other apps (using the same
ooRexx) could use a newer version.  

Maybe this would only mean that they'd need to rename the supplied 
'ooSQLite.cls' files to, say, 'ooSQLite3-7-13.cls' and 'ooSQLite5-8-9.cls'
and ::require one or both of the specified files in their app.  Obviously
the binaries would need to be able to coexist too.

But if the classnames do not include at least the "3" (and "5") would it
actually be possible to make sure that the methods etc invoked by an object
would come from the right .cls and binary files?
 

>The Rexx programmer would be faced with the same choices as any other
>SQLite user would be faced with.

But in eg C you could surely compile an app with code using one or both of
the sqlite3 and sqlite5 structs, depending on which was needed for which db,
and then call the version-specific entry-point?


>The name of the structs in SQLite is of no consequence to the Rexx
>programmer.  So, I don't really see a problem here.

The name is only significant because the "3" in it gives a clue that the
version of the engine used with a particular db may matter, and therefore
that any user of SQLite may need to be able to choose which engine to use
for a particular db or project.  

I was just suggesting that such a thing needs designed into the interface at
the start, so that any business user using SQLite and ooRexx can work around
that problem.    

-- 
Jeremy Nicoll - my opinions are my own