DB2 - no primary key or identity column : error

[sterankin]

I am using the gem: activerecord-jdbc-adapter (1.3.16) in Jruby 9.1.17.0 I added a jar file to my application lib folder, I found the jar file on http://jt400.sourceforge.net/. It is called jt400-9.4.jar In application.rb, I load the jar file:

require_relative '../lib/jt400-9.4.jar' if RUBY_PLATFORM == "java"

My database.yml is: development: adapter: as400 url: "jdbc:as400://ipaddess:port" schema: schema_name username: username password: pw

I can open a console and connect and insert RAW SQL statements in ActiveRecord. Now I am starting to uses Active Record models.

However I am getting the following error:

irb(main):003:0> g.save!
   (465.3ms)  SELECT NEXT VALUE FOR  FROM sysibm.sysdummy1
ActiveRecord::JDBCError: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0104] Token . was not valid. Valid tokens: , FROM INTO.: SELECT NEXT VALUE FOR  FROM sysibm.sysdummy1
ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0104] Token . was not valid. Valid tokens: , FROM INTO.: SELECT NEXT VALUE FOR  FROM sysibm.sysdummy1

Looking at the code for the driver, it looks like it is trying to ‘pre-fetch’ the primary key since there is no IDENTITY column on the main table, but it returns nothing since there is no primary key, and the sql fails:

@override

--   | def prefetch_primary_key?(table_name = nil)   | # TRUE if the table has no identity column   | names = table_name.upcase.split(".")   | sql = "SELECT 1 FROM SYSIBM.SQLPRIMARYKEYS WHERE "   | sql << "TABLE_SCHEM = '#{names.first}' AND " if names.size == 2   | sql << "TABLE_NAME = '#{names.last}'"   | select_one(sql).nil?   | end

Is there anyway to prevent this code from executing, the tables already exist and I cannot add an identity column or a primary key.

[rsov]

Have you tried adding self.primary_key = :random_key to your model?

I believe if you manually specify a primary key inside your model rails won't try to look it up from SYS tables.

Any column should work, even if it's not unique. You just have to be VERY careful when updating/deleting records

[sterankin]

Thanks for the comment - I tried adding this but I still got the error when I chose a primary key in the model.

I guess the driver code is trying to get the SYSIBM.SQLPRIMARYKEYS if there is no identity column, I wonder is there a way to tell rails that a primary key is an identity column in order for the driver to not call def prefetch_primary_key?

Like this e.g.

self.primary_key = :random_key, identity: true

[rsov]

I have re-read your post few times. Actually it sounds like a problem that I've encountered before.

It's an issue with the way Rails and ActiveRecord work. They love to have a single column auto auto increment primary key.

Looks like you're trying to insert a new record. By default rails will fetch the next auto incremented ID value:

User.new(name: 'rsov').save!
=> SELECT 1 FROM SYSIBM.SQLPRIMARYKEYS WHERE TABLE_NAME = 'USERS' -- Fetches next ID
=> INSERT INTO USERS (ID, NAME) VALUES(2, 'rsov') --Uses the fetched ID

However if you specify the ID yourself, it should not fetch it from DB:

User.new(id: 999, name: 'rsov').save!
=> INSERT INTO USERS (ID, NAME) VALUES(999, 'rsov') --Uses the ID specified

One way to do this is to write before_create action to set the ID yourself. For example:

class User < ActiveRecord::Base
  before_create :set_id

  def set_id
    self.id = (User.maximum(:id) + 1) || 1
  end 
end

[sterankin]

Thanks for the reply, that looks like a potential solution, however when I manually run the SQL:

SELECT 1 FROM SYSIBM.SQLPRIMARYKEYS WHERE TABLE_NAME = 'my_table_name';

It returns 0 rows. Does this mean the primary key is not set correctly on the table>?

[rsov]

@sterankin That is a good theory.

Try checking with if the table has constraints set. I think QSYS2.PRIMARY_KEYS or QSYS2.COLUMNS to see if the table has an identity set