As I have limited knowledge in SQL, I "know" the following:
The NULL value in a SQL statement is just NULL, no quotes and that is the hard
part when building a resulting string in rexx using rexx variables. That's why
I use string~changestr('NULL', NULL) as my uninitialized variables will be set
to this value ('NULL') if they don't exist in the current record built for
insert.
There is no apparently overhead in doing the changestr clause, I now inserts
~500 000 records spread over 5 tables(~7 columns/table) + 2 indexes in about
120-180 seconds, using a memory database and then copy it to disc.
So I can accept doing the changestr in my code.
It's mearly a matter of convinence about the NULL, and maybe the size of the
database( i believe NULL take less space than 1 space or more) and the fact you
can't query the database for "if not NULL" of " if NULL"
using INSERT INTO table (col1,col2,col3) value( 'value1 ' , NULL , ' value3 ');
Note! the absent quote surrounding NULL. This works. But is hard to achieve
when building strings with variables in rexx
using INSERT INTO table (col1,col2,col3) value( 'value1 ' , 'NULL' , ' value3
'); Note! Quotes around NULL, will be a literal string NULL in the database,
not a SQL NULL
In my case, of initial loading of the database (which is merely used for
analyzing the data, so no updates.) I create an array of commands for different
tables and just insert them all at one time in order after the parse of the
infiles is done, due to dependency, the keys in one table must exist before the
dependent table has it's inserts , so using parameter substitution is as I
believe much slower and more coding to do.
/hex
----- Ursprungligt Meddelande -----
Från: Mark Miesfeld <[email protected]>
Till:<[email protected]> , Open Object Rexx Developer Mailing List
<[email protected]>
Kopia:
Datum: torsdag, 21 juni 2012 19:44
Ämne: Re: [Oorexx-devel] ooSQLite NULL value ?
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
