Newbie - Free SQL builder and SQL dialect questions?

[JGKWORK]

Hi, hope someone can help?

Just started a new job with a company who are using Informix v7.3 on Linux.

I have never used Informix or Linux before (mostly GUI products) so as you can imagine it is a big leap for me to use a terminal and pure SQL.

What I would like to know is are there any (preferably FREE) programs which can allow me to do pretty much what access query builder allows me to do i.e. build select, udpdate, delete, insert etc queries using a GUI but that WORK with Informix 7.3?

I have tried Flyspeed SQL but this only supports from Informix 8 onwards, Win SQL but this doesn't allow me to perform outer joins for some reason and doesn't allow updating for more than one row at a time(that I can figure out), Active Query builder, free query builder, razor sql 4 and SQL Leonardo I can't get too work with Infx 7.3.



This brings me on to the question of the SQl dialect of Informix - I am using the MERANT 3.50 ODBC driver to connect to Informix for the above programs so does this driver use ISQL to talk to the database for queries?

The strange thing is the WinSQL program doesn't allow outer joins through this ODBC driver but Excel using the same driver does.

Can someone please explain to me how the driver and SQL dialect works between the program and the db?

Sorry I know there's a lot there but I'm really struggling to get my head round this! Many thanks.

 

[mberni]

Hi,

have a look at :

http://squirrel-sql.sourceforge.net/

 

[JGKWORK]

Many thanks for the link.

I can't seem to get hold of a JDBC driver to get this to work, why is Informix so hard to work with?!!

I signed up to the IMB site and downloaded what I thought was the correct driver JDBC.3.00.JC3.tar but don't know what to do with a tar file - isn't that a Unix file?!

Please help.....anyone!

Thanks.

 

[mberni]

tar is an abbreviation for tape archiver and is simply an archive file, pretty much like .zip

Do you have winrar or winzip? You should be able to open it with that. There are also tar-implementations for windows available on the net.

Calm down ... if these things are new to you ... read ...

Maybe this is of help too:

http://en.wikipedia.org/wiki/Tar_(file_format)

 

[JGKWORK]

Cheers for the help - I know I need to calm it but I've been trying to find a program and configure it every day for over a week and I'm used to being able to install a driver (without first hunting it down, signing up to the website and then having an unknown file type presented to me) and have it work without too much trouble.

I have tried to find out about how it all works but it is very difficult to find explanations in plain English.

Right I'll give an extractor a go, cheers.

 

[PHV]

Just started a new job with a company who are using Informix v7.3 on Linux.
I have never used Informix or Linux before
Either take some courses or search for a more appropriate job.

 

[JGKWORK]

Ok, managed to extract that and installed the JDBC driver but can't get it too work on Squirrel-SQL do you know what I should be doing here?

I'm calm and ready to work to solve the problem......(!)

Thanks

 

[JGKWORK]

Managed to get it working after some trial and error.

Many thanks for your help on this mberni.

PHV - I am actually due to go on courses for this but can't believe you actually went out of your way to post this and you don't even know me? You had to start some where and now so do I and this is not the main part of my job I was only asked to help out anyway so please don't post unhelpful stuff like this again it makes you sound very pompous.

 

[JGKWORK]

One last issue.

I have managed now to setup every "GUI" program I have downloaded including each that use JDBC drivers and the ones which use ODBC connections.

The problem I have is that no matter what I do when I contsruct a query which has a join (with the wizard) in each program it fails with an sql syntax error.

Can someone explain what is going on here and how I might fix this particular problem as when I setup a query through excel using the same ODBC driver it works fine?

Many thanks.

 

[mberni]

ok, so SQL itself is a problem too ?

Squirrel is not a tool like msquery where you drag and drop your joins, but it is a very good graphical SQL editor.

Maybe if you are used to MS Access .... you can create external database links there and work with them as if they were native MS-access tables. Then you could drag and drop your joins again.

But when it comes to more complex queries you will not get around sql or a powerful reporting tool.

concerning syntax-questions : post the statements (and errorstatements if any), otherwise its difficult to help

 

[JGKWORK]

Will post up the errors on Monday if that's ok (left work for the weekend - just in time for it to get cloudy for the 1st time in over a week!).

I get sysntax errors even in Access when doing anything more than a simple join - Monday I'll post up the SQL access produces and the syntax error.

Both access and all other downloaded programs are producing this error but NOT excel.

Thanks again.

 

[JGKWORK]

Hi,

Spent some time going through different queries to pin point what's causing the SQL problem and it seems to be down to when a COUNT is used with any type of join.

So the following both work fine:

1. SELECT Count(bield_tenant.tenant) AS Payer
FROM bield_tenant;

2. SELECT bield_tenant.tenant AS Payer
FROM bield_rent INNER JOIN bield_tenant ON bield_rent.tenant = bield_tenant.tenant;



However the below SQL causes: "[Merant][ODBC Informix driver][Informix] A syntax error has occured (#-201)":

SELECT Count(bield_tenant.tenant) AS Payer
FROM bield_rent INNER JOIN bield_tenant ON bield_rent.tenant = bield_tenant.tenant;


Any ideas?

Many thanks.

 

[mjldba]

I don't think Informix supports inner joins, only outer joins and nested joins so "INNER JOIN" will not work.

Also replace "ON" with a "WHERE" to set conditions.


This may not deliver the result set you're looking for but the syntax should point you in the right direction, try something like:

SELECT Count(bield_tenant.tenant) AS Payer
FROM bield_tenant, outer bield_rent
where bield_rent.tenant = bield_tenant.tenant;

 

[mberni]

@mjldba: informix does

an inner join is a join, in which all records are selected which meet the join criteria, so your example IS an inner join

the syntax jgk is using, is the ANSI-compliant form, so you could write something like (taken from the sql-syntax manual):

SELECT c.catalog_num, c.stock_num, s.description, s.unit_price,
s.unit_descr, c.manu_code, i.quantity
FROM (catalog c INNER JOIN stock s
ON c.stock_num = s.stock_num
AND c.manu_code = s.manu_code)

 

[JGKWORK]

Thank you both.

This brings me back to my original problem, the SQL on my last post was generated by Access but I have the same problem with EVERY SQL "wizard" program I have downloaded i.e. they generate SQL that Informix doesn't understand.

Why does this happen and is there anything I can do about it?

Thanks again and sorry for my lack if understanding around this.

 

[mjldba]

sorry to lead you astray, wasn't having a good day that day & situation has not improved.

my best

 

[JGKWORK]

mjldba - no probs! Just hope someone can explain my last post?

Cheers.

 

[mberni]

did you try the native odbc-driver from informix? if you want to give it a try you can download it from ibm, you alread know that procedure ;-) it is contained within the csdk or informix-connect package.

 

[JGKWORK]

Hi mberni - I'm trying to get this going but its causing some problems with the Setnet32 file which I'm working on now, will get back to you. Thanks again.

 

[JGKWORK]

Ok finally found the program I've been looking for all this time and everything works perfectly (which is a wonder why all the other programs I tried didn't as I'm using the same driver?).

Anyway the name of it is OpenOffice by Sun Microsystems and it works great.

Many thanks mberni for all your help.