On Thu, Jun 21, 2012 at 8:33 AM, hakan <[email protected]>
wrote:
> Is it possible to get some assistance from ooSQLite code regarding NULL
> values in SQL statments ?
Probably, maybe; there already is. I haven't worked out the details on
NULL database values completely yet. And currently the treatment is a
little inconsistent.
> Lets say you define a table like this (from ooSQLite examples more or
less)
> in a database
> sql = "CREATE TABLE addr_type (" || .endOfLine || -
> " id INTEGER PRIMARY KEY," || .endOfLine || -
> " somecol TEXT ," || .endOfLine || -
> " type TEXT NOT NULL DEFAULT 'Home'" || .endOfLine || -
> ");"
...
> But can it be possible to get support for .nil (NULL) in ooSQLite so the
> each sql statement is scanned (to remove the quotes) before handover to
> SQLite.
This would be expensive in terms of efficiency. Your database creation
example with the 90,000 rows would take a very long time again.
There are 2 things here.
1.) What is the pure string SQL statement for inserting a NULL? I don't
know enough SQL to answer that.
If you know the answer to this, then just use that string. (I'm not sure
if there is such a string, as I said my SQL knowledge is very meager.)
When you pass in a string as the sql argument to .ooSQLiteStmt~new() that
string is going to be used unchanged. I don't see a good alternative to
that at this point.
2.) I believe the SQLite / ooSQLite way to do this to use use parameters.
In your example you need to do something like this:
sql = "INSERT INTO addr_type (type, someCol) VALUES(?1, ?2);"
stmt = .ooSQLiteStmt~new(conn, sql)
stmt~bindText(1, "WORK")
stmt~bindNull(2)
stmt~step
No error checking above, but those are the basic steps to do it. Now, some
higher level abstractions will certainly be added, but the direction for
that is not yet determined.\
One might be something like an insert method, maybe:
db = .ooSQLiteConnection~new(...)
f = .array~of('type', 'someCol')
v = .array~of("WORK", .nil)
db~insert(f, v)
Where the details of the prepared statement are done for you. If v[i]
was .nil then bindNull() would be used. If v[i] is a number then bindInt()
would be used. The proper bindXX() would be used for v[i] depending on
what it was.
Then also maybe something like;
db = .ooSQLiteConnection~new(...)
f = .array~of('type', 'someCol')
r = .array~new
v1 = .array~of("WORK", .nil)
r~append(v1)
v1 = .array~of("HOME", '277-3467')
r~append(v1)
v1 = .array~of("STORE", '858-344-3997')
r~append(v1)
db~insertRows(f, r)
But, for now my thinking is to have some higher level object, for the sake
of discussion, say .RexxDatabse, that would have these methods in it. And
allow the user of that class to specify the low-level implementation class.
I might specify the low-level class to be .ooSQLite and Jon might specify
the low level class to be .ooMySQL.
The design of the higer level abstraction is difficult for me right now,
because I need to actually write programs to see what needs to be done.
And I'm hesitent to add a lot of high level abstractions to the low-level
ooSQLite package, because I intend to write an .ooMySQL package and would
like to have a .RexxDatabase package that could use .ooSQLite, or .ooMySQL,
or .orxsql, or etc..
Let me know if the bindXX() explanation is good enough for what you need.
There is some example of using it in the insertIntoDatabase.rex example.
Especially in the latest version.
--
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
