mixed pk generation types in db scheme

sherpa
2001-10-19
2001-11-04
  • sherpa
    sherpa
    2001-10-19

    Hi,

    could someone explain me in about 5 bytes how I have to use JGPL for a db scheme that has 2 types of primary keys ...
    1/ autonumber type (Integer)
    2/ varchar type (String) where the value can not be "generated" but rather comes from input from another process

    app. this means that the databaseIdentifier field should not be skipped (case 2/) in the INSERT statement but filled in instead

    now what's the most elegant way for doing this ?
    1/ have 2 brokers, one for each scenario (& what's the drawback on this, because there's a default broker set to BrokerFactory & SQLQuery, does this influence anything ?)
    2/ it seems to me that PrimaryKeyStrategy is only for auto generated pk series (db side of serverlogic side), am I wrong ?
    3/ something I missed ...

    thanx for 'any' hint
    [F]

     
    • David Hoag
      David Hoag
      2001-10-19

      Multiple primary key support was recently added to the code base. I'm not sure that's its a part of the official 2.1 release. I think it came after that.

      The way it works is not much different than declaring 2 attribute definitions, and marking them as primary keys. So, the types can be anything. The down side with the current multiple PKey support is the lack of support for auto number. Additionally you'll have to explicitly set all primary key values.

      Does the second column really need to be a 'primary' key (keep in mind that a JGrinder primary key does not have to have any relationship to a real database primary key)? The only reason it would need to be a JGPL primary key would be for building proxies in related persistent objects.

      The trick is getting this to join properly with related objects. When you specify a foreign key relation, you'll need to specify the list of join columns (since it takes more than 1 column to resolve to the proper row).

      Now, if you're not talking about multiple primary keys in a database table - that's a different topic. Each persistent object can have it's own primary key field. You don't have to use the primary key from DomainObject. When you define your persistent map, be sure to define one of the attributes as the primary key ( I believe setAsPrimaryKey() ). So, with one broker, you can easily mix primary key types. For the persistent object that has a pre set string value, you'll disable the JGPL primary key generation by setBrokerGeneratedPrimaryKey( false ) on the persistent adapter.

      PrimaryKeyStrategy allows users to customize the primary key generation. Default strategies use a server side table or a sequence to generate ID values. But, you're application might want to use time stamps or lunar phases. Implement the PrimaryKeyStrategy interface and set the strategy for the broker to be your implementation.

      Every persistent object has a persistent adapter. The persistent adapter exists to hold persistent related information about your persistent object ( like the broker generated primary key option). On attribute is the brokerName. This will identify what broker the persistent object is to use. In a multiple broker environment, you'll need to use this BrokerName attribute to get queries, deletes, and saves all working the way you would expect.
      DefaultBroker exists to handle those cases when the broker name is null or not found in the broker factory.

      Any other questions?

       
    • sherpa
      sherpa
      2001-10-19

      table1

      databaseIdentifier int serial
      ... other columns

      table2

      databaseIdentifier char(50) NOT NULL
      ... other columns

      now from a db point-of-view, databaseIdentifier == pk constraint
      so it's not really the composite key scenario (is the src in CVS much different then the one in the 2.1 release btw ? more xml support, composite pk, ... ?)

      for starters I tried to set RDBPersistentAdapter result.setBrokerGeneratedPrimaryKeys(false) ; in initializeObjectEditor() from my persistent object
      this stopped the calls to nextPrimaryKey() in AccessBroker (I know, it's not a database ... :)

      assuming I now disabled the pk generation, I just tried to insert a new object in table1,
      PObj obj = new PObj() ;
      obj.setxxx();
      obj.setPrimaryKeyField(127); // knowing int value isnt yet in db ...

      this doesnt work since my PreparedStatements app. dont include the pk field when doing a set, e.g. INSERT INTO Table1(col_xxx ) VALUES (? ) ;
      it just insert default value 0 for databaseIdentifier (1st time) and fails second time it does this (due to pk constraint)

      now when you say
      > The down side with the current multiple PKey support
      > is the lack of support for auto number.
      > Additionally you'll have to explicitly set all primary key values.

      this is kinda exactly what I need, but then in the case of a single primary key (btw, the problem is that the db table holding these char(50) pk already exists, holds data & interfaces are build on top of it, using these pk's, so I rather wouldnt redo all those things ...)

      once I get this one solved, I can try to extend DomainObject and switch between pk types, but I seem to be stuck before...

      I'm puzzled, are my binaries (JGPL2.1) outdated ?

      [F]

       
    • David Hoag
      David Hoag
      2001-10-19

      Sadly, access is the least tested database - that said - lets work through the problem.

      >PObj obj = new PObj() ;

      >obj.setxxx();

      >obj.setPrimaryKeyField(127); // knowing int value isnt yet in db ...

      >this doesnt work since my PreparedStatements app. dont include the pk field when doing a set, e.g. INSERT INTO >Table1(col_xxx ) VALUES (? ) ;

      >it just insert default value 0 for databaseIdentifier (1st time) and fails second time it does this (due to pk constraint).

      Well, this should work with BrokerGeneratedPrimaryKeys set to false. Unfortunately, the code for AccessBroker assumes the use of the autonumber column for the primary key. And it NEVER includes the primary key in the insert statement (as you've seen).

      If you are not going to use the autonumber feature, you're probably better off just using RDBBroker. The only feature in AccessBroker that's not related to the autonumber issue related to updates. For some reason, when I wrote AccessBroker, updates were not taking unless I executed the update statement twice. So, AccessBroker executes all updates twice.  This may not be a problem for you.

      RDBBroker, on the other hand, always includes the primary key in the insert statement. Can you try RDBBroker and see if it solves your problem?

      I'm going to update AccessBroker to include the primary key value(s) when useBrokerGeneratedPrimaryKey is set to false.

       
      • sherpa
        sherpa
        2001-11-02

        > For some reason, when I wrote AccessBroker, updates were not taking
        > unless I executed the update statement twice.
        > So, AccessBroker executes all updates twice.

        If you open a M$ Access db and go to Tools / Options / Advanced tab
        you'll see a field called "ODBC refresh interval" which you can bring down to a miniumum of 1 second
        this key is set on a per user basis HKEY_CURRENT_USER in the registry
        so in fact your updates are committed fine to access, but access refuses to display things instantly ;)
        a little drawback is:
        - query results in data1
        - update data1 -> data2
        - query can result in data1 | data2, depending on whether the JDBC-ODBC call made it already into access or not...

        after some testing, the only way to know 100% sure is to sleep the exact amount of seconds you set as "ODBC refresh interval", 1 sec as a minimum
        I think by doing the same update twice, you're waiting just long enough to see the result of the first update
        just give it a try with a sleep(1000) and things should work equal

        > I fixed a problem in the JGPL when inserting java.sql.Timestamp
        in SQLModifier.java I added
        case java.sql.Types.TIMESTAMP:
        {
          stmt.setTimestamp(index, (java.sql.Timestamp)value) ;
          break ;
        }

        because the default bindAsStringValue approach failed

        also note that inserting a timestamp WITHOUT pstmt looks like

        -> first, a Timestamp.toString() will look like 2001-11-02 10:56:46.923
        remove everything after the final . so you keep 2001-11-02 10:56:46

        -> INSERT INTO table(`col`) VALUES(# 2001-11-02 10:56:46 #)
        -> UPDATE table SET `col` = # 2001-11-02 10:56:46 #
        mind the backquotes AND the spaces before and after the #

        > RDBBroker, on the other hand, always includes the primary key in the insert statement.
        > Can you try RDBBroker and see if it solves your problem?

        next on my list ...

        [F]

         
        • David Hoag
          David Hoag
          2001-11-02

          Thanks! I'll get the changes into the CVS repository.

          To add a sleep call to the updates, you would either have to create an AccessRdbConnection class. You may be able to get away with modification of the saveObjects methods within AccessBroker itself ( inherited ).

           
          • sherpa
            sherpa
            2001-11-04

            ok,

            just to let you know I patched AccessBroker so one can now use it successfully on access tables with or without autonumber pk fields
            I also made some changes here and there to some other files
            Is there a place I can send them to so you can have a look @ it ?
            maybe some stuff can make it into CVS then ,,,

            the reason why I patched AccessBroker is that (I'm picking up our revious discussion here) I  need the autonumber pk for some db objects (no pk field in INSERT), while I want to insert the pk myself for others (pk field in INSERT), so using RDBBroker wasnt really what I was looking for

            anywayzz, let me know, you can reach me directly through sherpa@coresoft.be

            [F]

             
    • sherpa
      sherpa
      2001-10-19

      due to an ugly word (legacy that is) there is access in some cases, M$ @ its worst !
      I'll try RDBBroker & let you know if it works
      and if you just wait till somewhere next week (I wont have any time this weekend sorry :(
      I'll let you in on the dark 'n deep mysteries of the M$ access db ;)
      I happen to know why you have to call your update statement twice, but dont have the exact details here @ home
      also I fixed a problem in the JGPL when inserting java.sql.Timestamp using M$ Access 2000 on NT
      AND I'd like to have a short discussion with you on why you commented in the code that pstmt.setObject(i, obj) isnt working (I've seen this problem come and go on different versions of the Sun JDK) - more particular related to the JDBC-ODBC bridge from Sun/Merant (and yes, they say it shouldnt be used for production, but if I see the user forums, quite some people are using it anyway ....)
      oh, and please dont take me for a M$ programmer cause I'm using access, they forced me, so it's not my fault :)

      till next week ... [F]