MySQL Forums - Informix

To Cluster Option in MySQL (no replies)

Sandeep Pant — Tue, 09 Jun 2009 12:32:56 +0000

Hi,

I am using following sql statement in informix to cluster the indexes.
ALTER INDEX ix_cust TO CLUSTER;

What is the sql command for the same in MySQL?

Please help.

Regards,

-Sandeep Pant

How familiar is MySQL to an old Informix guy? (no replies)

Rorie Devine — Thu, 16 Apr 2009 20:31:36 +0000

Hi

Way back in the 90's I used to an Informix DBA - even writing 4GL!

I'm thinking of moving http://www.buycake.co.uk to MySQL, does Informix experiene help?

Cheers

Rorie

Error Load Data Use '|' (no replies)

Marcelo Aguirre — Wed, 25 Mar 2009 20:37:21 +0000

EXample File .txt

1|30/04/1999|5051000002|0.0|5.0|0.0|51.0|1.0|-144.21|-144.21|5.74|1.0|migracion|0.0|50.0|50.0|AXN|15:38:27|03/05/1999|

2|30/041999|5051000004|0.0|5.0|0.0|51.0|1.0|-270.24|-270.24|5.74|1.0|MIGRACION|0.0|50.0|50.0|AXN|15:38:28|03/05/1999|

How import from load data command ?

Marcelo

Excalibur text search (no replies)

rambo rambo — Fri, 14 Nov 2008 01:06:22 +0000

Hi all,

Anyone here using Excalibur text search for informix database ? If yes, hows the search performance ?

Full-text search in informix db (1 reply)

rambo rambo — Fri, 31 Oct 2008 22:48:44 +0000

Im looking for any full-text search engine that can be use with informix db ? I know sphinx but unfortunately sphinx can't talk to informix. Anyone know?

Informix to MySQL (2 replies)

Ali Mdidech — Wed, 18 Mar 2009 19:10:35 +0000

Hi,

Here is a small script to convert Informix dbexport files to MySQL format.

http://ifx2mysql.moua7.com/ifx2mysql.tgz

Cheers,

--
Ali

like query (1 reply)

Naresh Kakkad — Tue, 09 Jun 2009 10:07:09 +0000

want to fetch data from three table to have the relation but one of the table as field owner_name and another as first name and last name how to solve this type of query

select si.*,co.firstname as cofname , co.lastname as colname , co.city as cocity ,sr.firstname as srfname, sr.lastname as srlname from sales_info as si,company_owner as co, sales_rep as sr where si.user_id=sr.user_id and sr.owner_name like '%co.firstname%lastname%' and sr.owner_name IN ( SELECT co.firstname FROM company_owner ) and si.type='ipod' and co.distributor_name='Ingram Micro' order by si.date, si.type ASC

please reply ASAP

Thanks,
Naresh

Porting procedures - SYSTEM function (no replies)

Thomas Downing — Thu, 27 Mar 2008 18:16:50 +0000

In porting some stored procedures from Informix to MySQL
it appears that MySQL does not have an equivalent function
to the Informix SYSTEM function. Is this correct? Or is
there some other way to invoke an external executable from
inside a MySQL procedure?

Thanks

td

MYSQL process infinitely hanging on an INSERT (no replies)

Maodo Toure — Sat, 20 Oct 2007 23:08:41 +0000

Hi,

I posted this problem since 5 days, desperately waiting for answer but did recieve nothing. May be informations I gave were not clear enough. I do another post with more clear information.

This problem is clearly process hanging infinitely on an INSERT statement. May be the process is waiting for a lock. But, why it does not give up if it does not obtain the lock after having wait for lock wait timeout value ???

Below is innodb parameteres in my.cnf :

innodb_data_home_dir = /opt/mysql/data/
innodb_data_file_path = ibdata_metacads1:1M;ibdata_metacads2:1000M:autoextend
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /opt/mysql/log
innodb_log_files_in_group = 2
innodb_log_file_size=64M
innodb_log_buffer_size=1M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=10
innodb_thread_concurrency=4
innodb_commit_concurrency=0
innodb_concurrency_tickets=500

Help again !

Testing (no replies)

Dups à une soirée de débats — Mon, 01 Oct 2007 10:44:04 +0000

Testing.. please ignore

Informix C API conversion to MySQL C API (1 reply)

Antony Suresh — Sat, 07 Jun 2008 19:24:48 +0000

Is there any easy way/tool to convert Informix C Api functions to MySQL c API functions?

Informix JDBC setup - RH 6.2 (no replies)

Dan Kelly — Tue, 16 Jan 2007 17:25:02 +0000

Can anyone advise how to setup an Informix JDBC on a redhat 6.2 server?

Is it at all possible - or is the OS too old to support the JDBC?

Any tips / suggestions

Dynamic SQL is not allowed (no replies)

Aftab Khan — Mon, 13 Nov 2006 12:04:12 +0000

I have to choose the table name on fly in the trigger and then pass dynamic sql to
Stored procedure which make use of PREPARE and EXECUTE statements.I know that triggers can't have PREPARE and EXECUTE statement, but STORED PROCEDURES can make use of PREPARE AND EXECUTE. So thats why I had put PREPARE AND EXECUTE statements in Stored procedures, which I call from trigger, I recieve the following error.

\\\\\\\\\\\\\
Dynamic SQL support is not enable in triggers and functions
\\\\\\\\\\\

Can any body help me...

CREATE TRIGGER trigger1 after INSERT ON cdr
FOR EACH ROW

BEGIN

DECLARE id BIGINT;
DECLARE vname VARCHAR(200);
DECLARE destination VARCHAR(200) DEFAULT 'null';
DECLARE rows1 BIGINT DEFAULT 0;
DECLARE sql1 VARCHAR(2000);

SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve
WHERE
substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1;

SET @@sql_mode='ansi';
SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');

IF (id >0) THEN
call testing(sql1);
ELSE
SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');
call testing(sql1);
END IF;

END

/////////////

CREATE PROCEDURE testing (IN param1 VARCHAR(3000))
BEGIN
SET @s =param1;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
END

Dynamic SQL in Stored Procedure (no replies)

Aftab Khan — Mon, 13 Nov 2006 10:59:13 +0000

When I insert new row in `cdr` table, I get following error. I am using MYSQL Server version: 5.0.24a-community-nt, and that version support dynamic sql (prepare and execute statments) in Stored Procedures; Can any body help

#1336 - Dynamic SQL is not allowed in stored function or trigger

////////////////////////////////////////////////////////////////

CREATE TRIGGER trigger1 after INSERT ON cdr
FOR EACH ROW
BEGIN

DECLARE id BIGINT;
DECLARE vname VARCHAR(200);
DECLARE destination VARCHAR(200) DEFAULT 'null';
DECLARE rows1 BIGINT DEFAULT 0;
DECLARE sql1 VARCHAR(200);
DECLARE sql2 VARCHAR(200);

SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve
WHERE
substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1;

set sql1='insert into `'|| CONCAT(vname,id) || '` values('|| id || ',\"' || vname || '\",\"' || NEW.Field5 || '\",\"' || NEW.Field35 || '\",\"' || NEW.Field12 || '\",\"' || NEW.Field8 || '\",\"'|| NEW.Field0 || '\")';
set sql2='insert into unknown values('|| id || ',\"' || vname || '\",\"' || NEW.Field5 || '\",\"' || NEW.Field35 || '\",\"' || NEW.Field12 || '\",\"' || NEW.Field8 || '\",\"'|| NEW.Field0 || '\")';

IF (id >0) THEN
call testing(sql1);
ELSE
call testing(sql2);
END IF;

END

Help with informix and mysql (no replies)

Gerardo Polo — Sun, 05 Nov 2006 03:53:02 +0000

I know maybe some of you know already the answer but in this new thing we are newbie,, we need to migrate a database from informix to mysql, can that be done directly from mysql? can it be done with php? does anyone have a example code plz.

thanks

Sequential mysql reads (1 reply)

Christopher Rothery — Sat, 16 Sep 2006 14:41:32 +0000

Hi there, hopefully someone can help me!

The company I work for has an application that uses Informix C-Isam as it's database. We don't do anything too spectacular with it, we read, read next, update, add etc. If we want anything that isn't in key order we hunt for the answers in the most appropriate key and filter out the chaff. All of our access is through our own library functions that pretty much map to C-Isam funtions/options and the code of the application is what I'd call 'large'! (> 4 million lines).

For the last couple of weeks I've been trying to take baby steps towards moving to mysql as our database by mimicing what our current functions do on a dump of the C-Isam database. The logic is that if we can mimic what those functions do accurately and without too much of a performance hit, we can live like that and newer parts of the application can be 'mysql aware' and start to take advantage of the non-sequential reads, table joins etc that mysql gives us.

I've been reasonably successful in the mimicing but not the performance. Because this has to be backwardly compatible, I'm basically issuing a mysql select for each read we do (not worrying about updates yet). I've experimented with caches (ask for one record, get it and the next 10 back so that as we loop through we can use the cache rather than read afresh). Within reason this would be ok (we'd choose when it's safe to use the cache and when not).

What's troubling me (apart from the overall dread from the whole project) is the performance of these mysql reads. It's complicated but best described with a bit of pseudo code:

existing logic:

initialise record key
if (can read a record based on initial key) {
do {
print record details
} while (can read next record)
}

With our existing code, this runs through my data set in about 3 seconds.
If I augment that and try and read the parallel mysql record for the cisam record we have it jumps to 18 seconds (basically just adding 13000 mysql_query + associated consumption of the results).

To read the mysql record, I am opening a connection to the db outside of the loop, making a select statement inside the loop (timings for this alone are tiny), calling mysql_query (haven't mentioned it but we're using c and linking the mysql library to our program), and then fetching/freeing the results.

As the record I'm reading is changing each time, the statement is changing each time (although the layout of the statement is the same, the data parts of the 'where' change). I'm going to try and experiment with creating a paramaterised procedure that can accept the key field values to see if this will help at all.

Long intro, now the questions:

- Has anyone tried anything vaguely similar?
- Is what I'm trying feasable (make a relational database pretend to be a sequential one with only within-reason performance loss)
- Could I do what I'm trying to do better or shall I just give up?

I know this was long and if you made it this far I salute you. If you manage to come up with any answers/queries I'll be even happier.

Thanks

Chris Rothery
Cognition Solutions plc.
Cheltenham
UK

interval data type (1 reply)

Mircea LUTIC — Thu, 26 Jun 2008 07:32:08 +0000

I suggest adding the interval datatype to mysql as it is in Informix.
There already is the interval syntax in date/time functions.
It should be natural and easy to add the interval datatype to the available column datatypes.

Thank you,
Mircea.

To translate judgment (1 reply)

Raul Gallego — Mon, 16 Apr 2007 10:00:55 +0000

hello!!

The judgment select in formix "select rowid from customer", like serious in mysql?

Thank you.

Informix talking to MySQL (5 replies)

E Sh — Thu, 31 May 2007 19:00:42 +0000

Hello,

I would appreciate if anyone on this forum can provide me with any pointers to the following problem: we are trying to have a copy of data located in Informix replicated in MySQL, that is changes in data that take place in Informix have to be propagated to the corresponding MySQL set of data. We've acquired a tool from IBM that promised to make this happen but turns out it does not work, with such an acknowledgement from IBM.

Anyone aware of any tools or techniques out there that will help us solve this issue?

Thank you for any pointers in the right direction.

Emilia

Informix unload command returning null i.e || (no replies)

Navin Patel — Sun, 30 Apr 2006 09:55:15 +0000

Hi,

We have problem when unloading a column containg empty string(space's) from an Informix database table, i.e. the unloaded column looks like || rather than | |.
So when we load this data into the MySQL database is stored as a NULL value rather than a space.
Is there any way of getting round this problem,other than post processing the data.

Thank's

Navin

connecting to source db (no replies)

Doug Hall — Thu, 16 Feb 2006 11:28:43 +0000

Hi,
I am trying a migration from informix to mysql using the migration tool - I have fallen at the first hurdle...can anyone give me a heads up on the following error:

Connecting to source database and retrieve schemata names.
The list of schema names could not be retrieved (error: 0).
ReverseEngineeringGeneric.getSchemata :C:\Program Files\IBM\Informix_JDBC_Driver\lib\ifxjdbc/jar;C:\Program Files\IBM\Informix_JDBC_Driver\lib\ifxsqlj/jar
Details:
java.lang.Class.forName0(Native Method)
java.lang.Class.forName(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.getSchemata(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

I am using the jdbc v3.00.JC3 and j2sdk1.4.2, informix 7.31. I can connect to the informix db through server studio no probs. Any ideas gratefully rec'd

Migrate informix programming language to MSQL? (3 replies)

Fri, 13 Oct 2006 00:27:27 +0000

Hello,

glad i found a forum with msql & informix issues.
I'm not a DB geek, so i'd be grateful to get some help...

our SMB plans to upgrade our old unix system to linux on the one hand, and our old informix 4.10 DB to a newer (& open source-) one, as MYSQL. Now, even with the old informix system, it's possible to create a MYSQL DB as a copy which can be questionned by ODBC afterwards, but the problem is the Informix programming language which - obviously - is proprietary. Our programmer claims if we change everything to mysql, all the programms he wrote must be rewritten, which we want to avoid.

So here is my question:

if we migrate from informix (even from a more recent version) to mysql, is it possible not only to export the DB, but also to convert informix programming language to mysql?

thanks for any help on this issue!!!

View Problem with joins (no replies)

Marco Marco — Thu, 19 Jan 2006 16:18:07 +0000

Hy, I have this problem.

Informix

select *
from
outer(ref x0 ) ,en x1 ,enc x2
where (((x1.p = x0.c ) AND (x1.n = x2.n) ) AND (x2.c = x0.c ) ) ;

And in mysql, I'm doing

from enc x2, en x1 left join (ref x0)
on ((x1.p = x0.c) AND (x1.n = x2.n) AND (x2.c = x0.c)))

but this sentence give me this error:

Error Code : 1054
Unknown column 'x2.num' in 'on clause'
(10 ms taken)

How I can do this right.
Sorry, for my poor english.

How to transfer data b/w remote Informix NTS 2000 db server to local MySql (no replies)

Rajneesh Yadav — Thu, 19 Jan 2006 02:52:37 +0000

Hi friends,
Please help me to find a way to transfer data from a remote infomix database to local MySql database.Using dbacess tool i can make a flat file and later transfer it to my local pc and load data to database.But i need to make a programe which will run 24*7 and after every 1/2 hr load data to local pc.
How can i directly transfer or how to unload data to to flat file on that remote machine which i can get using FTP.When i send 'unload to abc.txt select * from .....' it always says syntax error.I seached on net and found that this statement is used by dbaccess to execute on database and is not a pure sql query.
Also i am not able to get resultset on that specific table because it is always in transaction and i get error 'system can not get information from this table' .It seems that table is locked due to transactions being taking place.
Can i access dbaccess using a java program,telnet or any way that i can get my work done.
Pleeeeeeaaaaaaaase help me out..........
Thanks in advance...........
Rajneesh.

Group by problems (1 reply)

Adrian Baceanu — Tue, 16 Dec 2008 04:33:00 +0000

In INFORMIX, an early version, I have a table created as follows:
create table "test".salvari
(
codc char(5),
codfisc char(20),
nrprezi integer,
nrprezf integer,
buc integer,
taxa float,
fel char(1),
numeex char(50),
adrex char(50),
locex char(30),
judex char(2),
data date,
timp datetime year to second,
nrord serial not null constraint "test".n308_129,
regl char(1)
);
revoke all on "test".salvari from "public";

create index "test".i_salvari1 on "test".salvari (codfisc,data);

create index "test".i_salvari2 on "test".salvari (data,codc);
create index "test".i_salvari3 on "test".salvari (codc);
create index "test".i_salvari4 on "test".salvari (codfisc);
create index "test".i_salvari5 on "test".salvari (nrprezi,nrprezf);

create index "test".salvari_data on "test".salvari (data);
create index "test".tr1 on "test".salvari (nrord);

and I have the following select statement: select * from salvari where data="11/06/2005" group by codfisc

Why is informix saying that codc needs to be in the group by statement, then, if I insert this column I get that nrprezi must be also in group by and so on. I just need a grouping by codfisc, because I need just one sum(taxa) for every distinct codfisc. Please help, 'cause I'm novice.

Porting Informix 4GL code to MySql c api (1 reply)

Navin Patel — Fri, 18 May 2007 10:49:37 +0000

Hi,

Does anyone know if there's an Mysql c api equivalent of the Informix 4gl type definitions of tbale name's and column names i.e.

Informix 4gl definition of a table : p_table1 RECORD LIKE table1.*

I want to define a new table 'p_table1' to be exactly like 'table1', and then assign values to individual column's in the new table.

Informix 4gl definition of a column: p_customer LIKE customer_table.customer

i.e I want to define a variable 'p_customer' to be same as column customer in the database table customer_table.

Is there anything similar in Mysql c api?

Thanks

Navin.

ISQL like Query By Example front end for MySQL (2 replies)

Merv Graham — Wed, 30 May 2007 17:44:41 +0000

I have used, and still use, Informix's ISQL package for many years for several small databases. I depend heavily on the "query by example" nature of the forms and I like the quick and easy way forms and reports can be created.
I want to migrate to MySQL, but haven't yet found a similar front end of MySQL.
I have started to write a curses based package, but, of course, don't want to reinvent the wheel if the work has been done already.
So, my question is: Is there such a package avalable, or in development for MySQL?
I don't want this to be a long winded post, but if I have been too criptic, please contact via e-mail and I'll answer any questions I can.

Merv

Table conversion from Informix to MySQL 4.1 (4 replies)

Andreas Grote — Mon, 22 Aug 2005 08:17:37 +0000

Hey!
I'm doing some first steps in converting existing Informix tables to MySQL (4.1) and was positivley suprised how easy this seems to be. Can you have a look at the following example and give me a hint if there are pitfalls I didn't recognize?

Original Informix table:
create table example
(
number integer not null,
enroute char(30) default "",
object char(70),
attribute char(50),
value char(80),
lastupdate datetime year to second,
action char(1),
userid char(30),
commentlen smallint,
reserved char(18),
comments char(300),
reserved2 char(500)
);

MySQL 4.1 table syntax:
create table example
(
number integer not null, <- It seems that MySQL does automatically add a default 0?
enroute char(30) default "",
object char(70),
attribute char(50),
value char(80),
lastupdate datetime, <- Does this match "year-to-second" type?
action char(1),
userid char(30),
commentlen smallint,
reserved char(18),
comments text(300), <- Converted this to text as char was to small
reserved2 text(500) <- Converted this to text as char was to small
);
I think this is fine, any comments?

In Informix I sometimes saw statments like "extent size 16 next size 32" at the end of the table definition. Can I just forget about them?

Converting indices also seems to be quite easy, e.g. a statement like
"create unique index exampleidx on example (object,attribute);"
is working 1:1 on MySQL :-)
Any pitfalls with create index known?

Thanks,
Andreas

What does columnname::datatype do in a where clause (1 reply)

Ross Rankin — Tue, 02 Aug 2005 15:35:13 +0000

I have a SP I'm trying to convert, the WHERE clause looks like this:
WHERE
Reseler_Profile.Status_ID = Reseller_Status.Status_ID AND
(param_Reseller_Id IS NULL OR Reseler_Profile.Reseller_Id::CHAR(4) LIKE param_Reseller_Id)

Anyone know what it does and what the MySQL Equivalent is?

Ross

MySQL Migration Toolkit (no replies)

Edwin DeSouza — Wed, 13 Jul 2005 17:45:40 +0000

MySQL Migration Toolkit simplifies migration from third-party databases.

The Toolkit has support for:
1) "Generic migration" from Any Database that has a JDBC Driver.
2) "Optimized migration" from MS Access, MS SQL Server and Oracle.

Docs:
http://dev.mysql.com/doc/migration-toolkit/en/index.html

Forum:
http://forums.mysql.com/list.php?104