Re: Driver matching with SDriver.driverName

Hi Anthony,

I'm working on a production level project on a deadline, not experimental.
It occurred to me that I can't afford to deal with Derby not being currently
supported since other problems may appear later.

I picked Derby because of JavaDB and because I read that HSQLDB could leave
you in an inconsistent state if the system crashes. Has H2 solved that
problem? What DB do you primarily develop SimpleORM with? If I want to use
SimpleORM for robust, easy to use Java persistence with no modifications
required, what DB should I use? (preferably a Java embedded DB)

Thanks,
Ryan


On Tue, Sep 29, 2009 at 6:12 AM, <[email protected]> wrote:

>
>
> Yes, you are right, more to do.
>
> Not important, but I think that I like to see the keyword DEFAULT generated
> -- makes it explicit.
>
> You could do what I suggested, but then also change SDriver.insertSql to
> call a new insertValue(SFieldScalar) which by default just creates ?. But
> then in Derby driver you override this to check for generated and output
> DEFAULT instead.
>
> It is a little bit fiddly. But either approach would be OK.
>
> You get the general drift though. Put hooks in the mainline code, and then
> override in your driver. Minimize per database complications in the mainline
> code.
>
> (A pain that the SQL does not standardize the most basic funtionality of
> key generation! It has lots of other escoterics, and SQL 3 for objects is
> bad. Also a pain that Sun chose Derby rather than Dafodil or maybe H2 as the
> Java database.)
>
> Regards,
>
> Anthony
>
>
> At 06:55 PM 29/09/2009, you wrote:
> >
> >
> >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>
> http://objectmix.com/apache/646246-can-derby-have-sequence-column.html***)
> >
> >"""
> >The DEFAULT <http://objectmix.com/#>keyword is part of <
> http://objectmix.com/#>SQL, not ij. These SQL statements should
> >all work in <http://objectmix.com/#>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, <<mailto:[email protected]<anthony%40berglas.org>
> >[email protected] <anthony%40berglas.org>> 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, <mailto:anthony%40berglas.org<anthony%2540berglas.org>
> >[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
> >
>
> 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