[Squirrel-sql-users] Informix unload/load command

[Jacobo Balbuena]

First post here so I have no clue about if I'm doing things right, feel free to flame and show me the right way.

My question: In dbaccess and other Informix command, I'm allowed to do the
unload to 'file' command. You can see the api of the Informix command here:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.mig.doc/mig192.htm

like
unload to 'c:\unloads\tmp.unl' select * from systables;

This doesn't seem to work in squirrel, some knows if I need a special pluggin or something for this? I already have the Informix pluggin installed.

Another option could be a Squirrel specific syntax that I can put in a script to tell next select will be downloaded into a file.

Thanks a lot for help.

[John Hardin]

Post by Jacobo Balbuena
My question: In dbaccess and other Informix command, I'm allowed to do the
unload to 'file' command.
like
unload to 'c:\unloads\tmp.unl' select * from systables;
This doesn't seem to work in squirrel, some knows if I need a special
pluggin or something for this? I already have the Informix pluggin
installed.

Use the SQLScripts plugin.

http://stackoverflow.com/questions/2314029/how-can-i-store-the-result-of-a-sql-query-in-a-csv-file-using-squirrel

I haven't tried this, though, so I don't know whether the delimiter is
under your control so you can get vertical-bar-delimited data like
dbaccess provides.

[Jacobo Balbuena]

Thanks a lot for the answer.
This is a start, I managed to download a plain fail separated by pipes | with this tool, I also saw the sql2ins to do the loads.
 
Now, to make it viable in my work, I'd need a way to invoke this automaticaly in a big sql script where I only want to load/download some of the queries, and in diferent files.
 
If the load/unload Informix command worked in squirrel, it would be easily done with the  command using it in any part of the script where you want to download the result in a file.
 
Someone knows if this is doable?
Thanks a lot.

--- El mié, 6/3/13, John Hardin <***@impsec.org> escribió:


De: John Hardin <***@impsec.org>
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Para: "Jacobo Balbuena" <***@yahoo.es>
CC: squirrel-sql-***@lists.sourceforge.net
Fecha: miércoles, 6 de marzo, 2013 21:49

Post by Jacobo Balbuena
My question: In dbaccess and other Informix command, I'm allowed to do the
unload to 'file' command.
like
unload to 'c:\unloads\tmp.unl' select * from systables;
This doesn't seem to work in squirrel, some knows if I need a special pluggin or something for this? I already have the Informix pluggin installed.

Use the SQLScripts plugin.

http://stackoverflow.com/questions/2314029/how-can-i-store-the-result-of-a-sql-query-in-a-csv-file-using-squirrel

I haven't tried this, though, so I don't know whether the delimiter is under your control so you can get vertical-bar-delimited data like dbaccess provides.

--  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
***@impsec.org    FALaholic #11174     pgpk -a ***@impsec.org
key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
  Failure to plan ahead on someone else's part does not constitute
  an emergency on my part.                 -- David W. Barts in a.s.r
-----------------------------------------------------------------------
4 days until Daylight Saving Time begins in U.S. - Spring Forward

[John Hardin]

If the load/unload Informix command worked in squirrel, it would be
easily done with the command using it in any part of the script where
you want to download the result in a file.
 
Someone knows if this is doable?

No. "LOAD FROM" and "UNLOAD TO" are extensions to the basic SQL grammar
that are provided internally by dbaccess. You have to be running your
queries via dbaccess to use them.

I don't know whether there is a similar scriptable delimited unload
facility for Squirrel.

Is there some reason you don't want to use dbaccess to run these queries?
Squirrel is a wonderful cross-platform interactive query tool. In my
opinion, trying to make it into a batch query tool is trying to force it
into being something it was never intended to be.

[Jacobo Balbuena]

No, dbaccess sucks bad, I just used it as an example of where the 'load' and 'unload' works. Other IFX platforms are able to use the thoes ifx commands, like Sqleditor.
 
The thing is sqleditor is not longer supported by IBM, dbaccess is like 'vi', it does things well if you dont cut your veins using it.
 
We are migrating to Windows 7 and sqleditor will not work, so I'm looking for a new sql platform that may adapt to our needs.
 
Where I work we do LOTS of updates-deletes everyday, usually with sqleditor we download as backup the rows we are changing in the same script. Most of those scripts we must execute them in 14 or more databases, and with the load and unload command, once I check the script is ok, I dont need to pause the script to download what I want or be asked the file name and accept.
 
I'm liking squirrel a lot, and I know now what I miss but I bet one day you will do it:
 
Those functions like sql2file need to be able to be called automatically from script like
sql2file(select * from blablabla, 'c:\unloads\kk.unl')
 
Once your comunity manage to do this, you will get as customers about 150 people that work with me.
 
Thanks a lot for your time, I'd like to have more spare time to help you building this great proyect :(
 
--- El jue, 7/3/13, John Hardin <***@impsec.org> escribió:


De: John Hardin <***@impsec.org>
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Para: "Jacobo Balbuena" <***@yahoo.es>
CC: squirrel-sql-***@lists.sourceforge.net
Fecha: jueves, 7 de marzo, 2013 23:17

If the load/unload Informix command worked in squirrel, it would be easily done with the command using it in any part of the script where you want to download the result in a file.
 
Someone knows if this is doable?

No. "LOAD FROM" and "UNLOAD TO" are extensions to the basic SQL grammar that are provided internally by dbaccess. You have to be running your queries via dbaccess to use them.

I don't know whether there is a similar scriptable delimited unload facility for Squirrel.

Is there some reason you don't want to use dbaccess to run these queries? Squirrel is a wonderful cross-platform interactive query tool. In my opinion, trying to make it into a batch query tool is trying to force it into being something it was never intended to be.

--  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
***@impsec.org    FALaholic #11174     pgpk -a ***@impsec.org
key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
  Failure to plan ahead on someone else's part does not constitute
  an emergency on my part.                 -- David W. Barts in a.s.r
-----------------------------------------------------------------------
3 days until Daylight Saving Time begins in U.S. - Spring Forward

[John Hardin]

Post by Jacobo Balbuena
No, dbaccess sucks bad,

...

Post by Jacobo Balbuena
dbaccess is like 'vi', it does things well if you dont cut your veins
using it.

Again, what does this matter if you're only using it to run SQL scripts in
batch mode?
 

Post by Jacobo Balbuena
 Where I work we do LOTS of updates-deletes everyday, usually with
sqleditor we download as backup the rows we are changing in the same
script. Most of those scripts we must execute them in 14 or more
databases, and with the load and unload command, once I check the script
is ok, I dont need to pause the script to download what I want or be
asked the file name and accept.

So develop the script on a test database in Squirrel, edit it to add the
"UNLOAD TO" part in front of the backup SELECT statements, and run it in
batch mode against your 14+ production databases using dbaccess (which is
available under Windows, IIRC).

Post by Jacobo Balbuena
Those functions like sql2file need to be able to be called automatically from script like
sql2file(select * from blablabla, 'c:\unloads\kk.unl')
 
Once your comunity manage to do this, you will get as customers about 150 people that work with me.
 
Thanks a lot for your time, I'd like to have more spare time to help you building this great proyect :(
 
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Fecha: jueves, 7 de marzo, 2013 23:17

If the load/unload Informix command worked in squirrel, it would be easily done with the command using it in any part of the script where you want to download the result in a file.
 
Someone knows if this is doable?

No. "LOAD FROM" and "UNLOAD TO" are extensions to the basic SQL grammar that are provided internally by dbaccess. You have to be running your queries via dbaccess to use them.
I don't know whether there is a similar scriptable delimited unload facility for Squirrel.
Is there some reason you don't want to use dbaccess to run these queries? Squirrel is a wonderful cross-platform interactive query tool. In my opinion, trying to make it into a batch query tool is trying to force it into being something it was never intended to be.

[Jacobo Balbuena]

Because to use dbaccess you must connect to each machine where the database is. And you must have a profile in unix that takes you to the prompt.
In most servers we have user (needed to use informix) but your user can log in properly to use the dbaccess.
That's why we are using sqleditor now. But that won't do when we pass to windows 7 :(

--- El vie, 8/3/13, John Hardin <***@impsec.org> escribió:


De: John Hardin <***@impsec.org>
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Para: "Jacobo Balbuena" <***@yahoo.es>
CC: squirrel-sql-***@lists.sourceforge.net
Fecha: viernes, 8 de marzo, 2013 19:31

Post by Jacobo Balbuena
No, dbaccess sucks bad,

...

Post by Jacobo Balbuena
dbaccess is like 'vi', it does things well if you dont cut your veins using it.

Again, what does this matter if you're only using it to run SQL scripts in batch mode?
 

Post by Jacobo Balbuena
 Where I work we do LOTS of updates-deletes everyday, usually with sqleditor we download as backup the rows we are changing in the same script. Most of those scripts we must execute them in 14 or more databases, and with the load and unload command, once I check the script is ok, I dont need to pause the script to download what I want or be asked the file name and accept.

So develop the script on a test database in Squirrel, edit it to add the "UNLOAD TO" part in front of the backup SELECT statements, and run it in batch mode against your 14+ production databases using dbaccess (which is available under Windows, IIRC).

Post by Jacobo Balbuena
Those functions like sql2file need to be able to be called automatically from script like
sql2file(select * from blablabla, 'c:\unloads\kk.unl')
 
Once your comunity manage to do this, you will get as customers about 150 people that work with me.
 
Thanks a lot for your time, I'd like to have more spare time to help you building this great proyect :(
 
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Fecha: jueves, 7 de marzo, 2013 23:17

If the load/unload Informix command worked in squirrel, it would be easily done with the command using it in any part of the script where you want to download the result in a file.
 
Someone knows if this is doable?

No. "LOAD FROM" and "UNLOAD TO" are extensions to the basic SQL grammar that are provided internally by dbaccess. You have to be running your queries via dbaccess to use them.
I don't know whether there is a similar scriptable delimited unload facility for Squirrel.
Is there some reason you don't want to use dbaccess to run these queries? Squirrel is a wonderful cross-platform interactive query tool. In my opinion, trying to make it into a batch query tool is trying to force it into being something it was never intended to be.

--  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
***@impsec.org    FALaholic #11174     pgpk -a ***@impsec.org
key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
  Failure to plan ahead on someone else's part does not constitute
  an emergency on my part.                 -- David W. Barts in a.s.r
-----------------------------------------------------------------------
2 days until Daylight Saving Time begins in U.S. - Spring Forward

[John Hardin]

Post by Jacobo Balbuena
Because to use dbaccess you must connect to each machine where the database is.

That can easily be scripted to occur from a single account on a single
box through setting environment variables.

export INFORMIXDIR=/opt/informix
export PATH=$PATH:$INFORMIXDIR/bin

for SRV in $list_of_ifx_server_names
do
# $SRV must appear in $INFORMIXDIR/etc/sqlhosts
export INFORMIXSERVER=$SRV
dbaccess -e $SRV $sql_script_name >> $SRV.log 2>&1
done

...but this is drifting further OT for Squirrel. Feel feee to contact me
offlist if you wish to continue this discussion.

Post by Jacobo Balbuena
And you must have a profile in unix that takes you to the prompt.
In most servers we have user (needed to use informix) but your user can log in properly to use the dbaccess.
That's why we are using sqleditor now. But that won't do when we pass to windows 7 :(
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Fecha: viernes, 8 de marzo, 2013 19:31

Post by Jacobo Balbuena
No, dbaccess sucks bad,

...

Post by Jacobo Balbuena
dbaccess is like 'vi', it does things well if you dont cut your veins using it.

Again, what does this matter if you're only using it to run SQL scripts in batch mode?
 

Post by Jacobo Balbuena
 Where I work we do LOTS of updates-deletes everyday, usually with sqleditor we download as backup the rows we are changing in the same script. Most of those scripts we must execute them in 14 or more databases, and with the load and unload command, once I check the script is ok, I dont need to pause the script to download what I want or be asked the file name and accept.

So develop the script on a test database in Squirrel, edit it to add the "UNLOAD TO" part in front of the backup SELECT statements, and run it in batch mode against your 14+ production databases using dbaccess (which is available under Windows, IIRC).

Post by Jacobo Balbuena
Those functions like sql2file need to be able to be called automatically from script like
sql2file(select * from blablabla, 'c:\unloads\kk.unl')
 
Once your comunity manage to do this, you will get as customers about 150 people that work with me.
 
Thanks a lot for your time, I'd like to have more spare time to help you building this great proyect :(
 
Asunto: Re: [Squirrel-sql-users] Informix unload/load command
Fecha: jueves, 7 de marzo, 2013 23:17

If the load/unload Informix command worked in squirrel, it would be easily done with the command using it in any part of the script where you want to download the result in a file.
 
Someone knows if this is doable?

No. "LOAD FROM" and "UNLOAD TO" are extensions to the basic SQL grammar that are provided internally by dbaccess. You have to be running your queries via dbaccess to use them.
I don't know whether there is a similar scriptable delimited unload facility for Squirrel.
Is there some reason you don't want to use dbaccess to run these queries? Squirrel is a wonderful cross-platform interactive query tool. In my opinion, trying to make it into a batch query tool is trying to force it into being something it was never intended to be.

--
John Hardin KA7OHZ http://www.impsec.org/~jhardin/
***@impsec.org FALaholic #11174 pgpk -a ***@impsec.org
key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
Our government should bear in mind the fact that the American
Revolution was touched off by the then-current government
attempting to confiscate firearms from the people.
-----------------------------------------------------------------------
3 days until Albert Einstein's 134th Birthday