Re: Driver matching with SDriver.driverName

Hi Anthony,

I read the code and I think this will require the flush method to work a
differently for generated columns. It currently passes the NULL values for
generated columns as parameters in a prepared statement. This doesn't work
with Derby because Derby expects the keyword DEFAULT to be passed instead.
The keyword DEFAULT can not be passed as a parameter to a prepared
statement. Derby can handle the follow three syntax in a prepared
statement...

(*** got this from
http://objectmix.com/apache/646246-can-derby-have-sequence-column.html ***)

"""
The DEFAULT keyword <http://objectmix.com/#> is part of
SQL<http://objectmix.com/#>,
not ij. These SQL statements should
all work in JDBC
<http://objectmix.com/#> as a PreparedStatement

INSERT INTO GROUPS(ID, NAME, DESCRIPTION) VALUES (DEFAULT, ?, ?)

INSERT INTO GROUPS(NAME, DESCRIPTION) VALUES (?, ?)

INSERT INTO GROUPS VALUES (DEFAULT, ?, ?)
"""

So I think I'm going to have to change code a little earlier in the flush
method, from line 186 to line 236, so it doesn't handle generated columns as
parameters in the prepared statement, but rather as literals in the query
itself. For Derby the literal would be DEFAULT and for MSSQL and HSQL it
would be NULL - that decision being delegated to the driver.

Another option I considered was simply not specifying a value for generated
columns as you can see in the second syntax example above, but if I'm
thinking that does not work in MSSQL. Not 100% sure though.

Before I go forward, what are your thoughts? Is this the correct approach
(changing defaults to literals instead of prepared statement parameters) in
your design or do you have a better suggestion?

Thanks,
Ryan


On Mon, Sep 28, 2009 at 3:11 AM, <[email protected]> wrote:

>
>
> Hello Ryan,
>
> Could do. But for now just create subclasses for the different variants. I
> suggest just use a static class for these trivial bits. There may even be
> some real differences later.
>
> (The bigger problem is that SDrivers represent both the instance and the
> factory. Should be a small static factory class for the factory. Just has
> never got to the top of the list to fix.)
>
> It would be very good to support Deryb/JavaDB. (I'm not clear about the
> difference?). This is long overdue. And it should build and run Derby out of
> the JDK 1.6 directly, with their jars. Maybe even make Derby the default
> test DB instead of HSQL?
>
> The normal way to handle database inconsistencies is to delegate them to
> some method in the SDriver hierarchy. Null works for MSSQL and HSQL.
>
> In SSessionJdbcHelper.flush you would have to change
> if (value == null && fieldMeta.isPrimary() && theGenerator==null)
> throw new SException.Error("Null primary key not set (after
> createWithNullKey) " + instance);
> fieldMeta.writeFieldValue(ps, jx, instance.getRawArrayValue(fieldMeta));
>
> into something like
>
> if (value == null && fieldMeta.isPrimary())
> if (theGenerator==null)
> throw new SException.Error("Null primary key not set (after
> createWithNullKey) " + instance);
> else
> session.getDriver().setEmptyGeneratedKey() // Or maybe just do nothing
> here?
> else
> fieldMeta.writeFieldValue(ps, jx, instance.getRawArrayValue(fieldMeta));
>
> Make sure that whatever you do continues to work for at least HSQL, the
> default test case.
>
> Send me any changed files and I'll diff and check them in. You are welcome
> to become a committer if you make serious contributions.
>
> There is no magic in the code. You are not dependent on us to change
> things, unlike more complex systems.
>
> Regards,
>
> Anthony
>
> >I'm trying to get identity generation on insert working with Derby. I have
> the driver set up so it creates the table with GENERATED BY DEFAULT AS
> IDENTITY for my identity field and it works fine. The problem occurs when I
> call session.ses.createWithGeneratedKey and then try to commit the new
> record. SimpleORM tries to call insert passing null for the identity field.
> Derby accepts an insert statement that either 1) doesn't specify a value for
> the identity column or 2) passes the value "default" for the identity
> column. Derby throws an error when SimpleORM tries to pass null for the
> generated column.
> >
> >How should I handle this? If there's no way then I'll have to change
> SimpleORM code to pass "default" instead of "null" but I'd prefer not to do
> that.
>
>
> At 01:00 PM 27/09/2009, you wrote:
> >
> >
> >Hi.
> >
> >Have you considered allowing 1 SimpleORM driver to handle multiple JDBC
> drivers? Instead of using driverName and comparing the returned string to
> choose a SimpleORM driver, you could pass the actual driver name into a
> boolean method of an SDriver subclass and let the driver itself decide
> whether it's a match. For example, I'm working with an embedded Derby
> database and the driver name is "Apache Derby Embedded JDBC Driver" but the
> driver name for the network client would be "Apache Derby Network Client
> JDBC Driver". The same SimpleORM driver would probably work for both.
> >
> >Just curious,
> >--
> >Ryan Boder
> >1 614 598 6339
> >
>
> Dr Anthony Berglas, [email protected] <anthony%40berglas.org> Mobile:
> +61 4 4838 8874
> Just because it is possible to push twigs along the ground with ones nose
> does not necessarily mean that is the best way to collect firewood.
>  
>



-- 
Ryan Boder
1 614 598 6339