When programming a large transaction (lots of inserts, deletes, updates) and thereby violating a constraint in Informix (v10, but should apply to other versions too) I get a not very helpful message saying, for example, I violated constraint r190_710. How can I find out which table(s) and key(s) are covered by a certain constraint I know...
Hey.. I'm trying to concatenate 3 [char(32)] fields:title1title2title3 into one field, but the catch is that I'm using an older version of SQL and it DOES NOT support the CONCAT() subroutine or the + operatorfor example:CONCAT(title1, title2, title3)(title1 + title2 + title3) DON'T WORK!!!!Is there another way? ...
In Informix I can run SQL statement that use other database: INSERT INTO other_db:mytable ... I would like "unite" both databases, but I would like do it "step by step". At first I want to move all data from other_db to main database and make alias that other_db = main_database. This way I will have time to remove "other_db:" from al...
I have Informix database with timestamp field defined as YEAR TO SECOND. When I show this field using JDBC rs.getString(column) it uses format with miliseconds so this field looks like: 2008-12-18 13:58:14.0 I would like it to use only YEAR TO SECOND fields. I set environment variable: GL_DATETIME=%Y-%m-%D %H:%M:%S but even then I go...
Question: Does Informix have a construct equivalent to Oracle's "materialized view" or is there a better way to synchronize two tables (not DB's) accross a DB link? I could write a sync myself (was asked to) but that seems like re-inventing the wheel. Background: Recently we had to split (one part of DB one one server, the other part o...
Are there any nvl() equivalent functions in SQL? Or something close enough to be used in the same way in certain scenarios? UPDATE: no if statementsno case statementsno isnullno coalesce select nvl (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581; (expression) SODIUFOSDIUFSDOIFUDSF 1 row(s) retrieved. select isnul...
I have an Informix stored procedure that returns two columns and multiple rows. I can use "EXECUTE FUNCTION curr_sess(2009,'SP')" fine, but how do I get the results into a temp table. EDIT: We are on version 10.00.HC5 Testing Jonathan Leffler's idea didn't work. EXECUTE FUNCTION curr_sess(2009,'SP') works fine. Then I did CREATE...
I cannot get trailing spaces from varchar column in Informix database. I created test table, filled it with field with some trailing spaces, but they are not returned by SELECT while it seems they are stored in db. CREATE TABLE tmptable (txt varchar(240)); INSERT INTO tmptable (txt) VALUES ('123 '); SELECT txt, txt || '***', LENGTH(t...
I have problem when I try insert some data to Informix TEXT column via JDBC. In ODBC I can simply run SQL like this: INSERT INTO test_table (text_column) VALUES ('insert') but this do not work in JDBC and I got error: 617: A blob data type must be supplied within this context. I searched for such problem and found messages from 200...
Am looking for a single query that can convert the following information in the table name:time :state a :10:00 AM:login b :10:05 AM:login a :10:06 AM:chatting a :10:08 AM:Idle b :10:11 AM:chatting a :10:10 AM:Logout b :10:12 AM:Logout to something like this (given the time range 10 AM to 10:15 AM as the querying per...
Am looking for a query in Informix's SQL that will simulate MySQL's group_concat function. What MySQL's group_concat does is it creates an enumeration of all members in the group. So with the data as follows: orderid:itemName:price 1:Paper :10 1:Pen :5 2:Sugar :15 and the following query: select group_conc...
I have a temp table that has numeric integer values in one column. I want to either replace the integer values with character values based on some criteria or I want to add another column of character type that automatically inserts values into itself based on some criteria. If x <= 1, change to "SP" or make new column and store "SP" i...
I have an informix database consisting of a large number of tables. I know that there is a string "example" somewhere inside some table, but don't know which table it is or which column it is. (I know this is a very rare case) Because of the large number of tables, there is no way to look for it manually. How do i find this value insid...
I am working with Informix in C++ with the IBM CSDK client libraries. I use the multi-threaded version of the libraries and each thread uses its own ITConnection object. Still the application crashes if more than one thread is spawned. Has anybody come across a similar problem? ...
Hi I have an Informix database containing measured temperature values for quite a few different locations. The measurements are taken every 15 min for all locations and then loaded with a timestamp into the same table. Table looks like this: locId dtg temp aaa 2009-02-25 10:00 15 bbb 2009-02-25 10:00 20 cc...
We are using an Informix database, and are connecting to it from .NET sucessfully using ODBC. The connection string we are using is; DRIVER={IBM INFORMIX ODBC RIVER}; UID=username; PWD=password; DATABASE=our_database; HOST=devsrv01; SERVER=devsrv01_tcp; SERVICE=ids9tcp2; PROTOCOL=onsoctcp; CLIENT_LOCALE=en_US.CP1252; DB_LOCALE=en_US.819...
I tried to trace ODBC function calls from my program working on Linux. This program dynamically links ODBC manager and then connect to database and fetch some data. I can trace ODBC calls with unixODBC by adding to odbcinst.ini: [ODBC] Trace=yes TraceFile=/tmp/sql.log This method is documented by IBM: Collecting data for an ODBC Prob...
Using Informix, I've created a tempory table which I am trying to populate from a select statement. After this, I want to do an update, to populate more fields in the tempory table. So I'm doing something like; create temp table _results (group_ser int, item_ser int, restype char(4)); insert into _results (group_ser, item_ser) select ...
I am working on a system that runs with Informix and so I started learning more and more about Informix. I thinking on studying and getting the Informix certification. Do you think there is future on Informix out there? Or should I try learning more about Oracle or MySQL or some other popular database system? ...
We have Informix running on our Solaris server and under /usr/informix/bin there are several executables, such as 'dbaccess' and 'isql'. Each of them produce different version information when typing 'isql -V' for example. How do you identify the exact version of the SE running? Output: 'esql -V' INFORMIX-ESQL Version 6.00.UH1 'isql...