informix check if table exists and then read the value

Question

I have a table in informix (Version 11.50.UC4) called NextRecordID with just one column called id and it will have one row. What I want to do is copy this value into another table. But don't want my query to fail if this table does not exist. Something like

if table NextRecordID exists
    then insert into sometable values ('NextRecordID', (select id from NextRecordID))
    else insert into sometable values ('NextRecordID', 1)

Answer 1

You should be able to do this by checking the systables table.

Answer 2

Thank you for including server version information - it makes answering your question easier.

You've not indicated which language(s) you are using.

Normally, though, you design a program to expect a certain schema (certain tables to be present), and then fail - preferably under control - if those tables are not present. Also, it is not clear whether you would get into problems because of repeated execution of the second INSERT statement. Nor is it clear when the NextRecordID table is updated - presumably, once the value has been used, it must be updated.

You should look at SERIAL (BIGSERIAL) and see whether that is appropriate for you.

You should also look at whether a SEQUENCE would be appropriate to use here - it certainly looks rather like it might be applicable.

As Adam Hughes points out, if you want to check whether the NextRecordID table is present in the database, you would look in the systables table. Be aware, though, that your search will need to be against an all lower-case name (nextrecordid).

Also, MODE ANSI databases complicate life - you have to worry about the table's owner (because there could be multiple tables called nextrecordid in a MODE ANSI database). Most likely, you don't have to worry about that - any more than you are likely to have to worry about delimited identifiers for table "someone"."NextRecordID" (which is a different table from someone.NextRecordID).

Answer 3

I ended up using the below SQL query. Its not ANSI SQL but works the informix server I am using.

insert into sometable values ('NextRecordID', 
    select case (select 1 from systables where tabname='nextrecordid')
    when 1 then (select nextid from nextrecordid) 
    else (select 1 from systables where tabname='systables') end 
    from systables where tabname='systables');

What is happening here is within insert query I get the value to be inserted by using select query. Now that select query is interesting. It uses case statement of Informix. I have written a select query to check if the table nextrecordid exists in systables and return 1 if it exists. If this query returns 1, I query the table nextrecordid for the value or else I wrote a query to return the default value 1. This work for me.