squirrel-sql-users — A subscriber only discussion list for SQuirreL

Re: [Squirrel-sql-users] SQuirreL somehow interpreting vertical bar in varchar value?

[Gerd W. <ger...@t-...>]

Sorry my last redundant answer that John had already given.


Am 25.04.2017 um 21:34 schrieb KARR, DAVID:
> Now that I've turned off variable interpolation in my big sql import
> script, when I run it, it gets an odd error on a particular insert
> line.  It looks somewhat like this (mostly elided, except for the
> start of the values list):
>
> insert into blah (colname1,colname2,colname3,...) values
> (371,null,'433449 | 274027 - ...
>
> In the error message, it says the syntax error is near "433449", and
> when it shows the entire query, the end of the line is the "9" at the
> end of that number.  It seems like that vertical bar character is
> doing something.
>
> I don't know whether this is a mysql/mariadb issue, or SQuirreL.
This should be due to our MYSQL Plugin (MariaDB is based on MYSQL). See 
menu File -> Global preferences -> Tab MySQL -> The procedure separator 
is by default set to '|'. Change it for example to ';' and the problem 
should be gone.

Re: [Squirrel-sql-users] How to execute SQL ignoring parameters

[Gerd W. <ger...@t-...>]

Am 25.04.2017 um 20:16 schrieb KARR, DAVID:
> Somewhat related to my just earlier question about exporting and
> importing a database, it appears that when I execute a SQL script,
> SQuirreL looks for strings like ":abc", which it recognizes as a
> parameter.  How can I execute the SQL script without processing
> parameters at all?

Got to menu Plugins -> Summary and uncheck the "SQL Parametrization" 
Plugin. Then restart SQuirreL.

Re: [Squirrel-sql-users] Export mariadb database for import to another instance?

[Gerd W. <ger...@t-...>]

Am 25.04.2017 um 20:03 schrieb KARR, DAVID:
> I'm working with an existing mariadb database, and I need to export
> the contents to a new instance.  I didn't notice any obvious export
> facilities in SQuirreL.  Is there an easy way to do this?  I noticed
> that the changelog for the unreleased version appeared to have some
> changes related to this.

The only feature that comes close to this is to select tables in the 
Object tree right click and choose "Copy table" then right click the 
same schema of your target database and choose "Paste".

Hope this helps.

>
>
>
> ------------------------------------------------------------------------------
>
>
Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>
>
>
> _______________________________________________ Squirrel-sql-users
> mailing list Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>

[Squirrel-sql-users] MariaDB connections start failing after idle and won't reconnect

[KARR, D. <dk...@at...>]

I'm connecting to a MariaDB database.  The app running in Tomcat that uses the database is now setting "testWhileIdle" and "testOnReturn" to true, because the connections seem to time out after a while of being idle.  Those appear to mitigate the problem in the app itself.

In SQuirreL, if I leave a connection open for a while, I get similar errors like:
----------
	Error: (conn:121) Could not read packet: unexpected end of stream, read 0 bytes from 4
---------

It's not clear to me how to configure the connection automatically to mitigate these problems.

What's even worse is that when I try to "Reconnect", it just fails with "Error: (conn:121) Connection is close".  I actually have to completely close the connection and reopen it, and then it works until I go idle again.

What can I do to mitigate these problems in SQuirreL?

Re: [Squirrel-sql-users] SQuirreL somehow interpreting vertical bar in varchar value?

[KARR, D. <dk...@at...>]

> -----Original Message-----
> From: John Hardin [mailto:jh...@im...]
> Sent: Tuesday, April 25, 2017 2:57 PM
> To: KARR, DAVID <dk...@at...>
> Cc: squ...@li...
> Subject: Re: [Squirrel-sql-users] SQuirreL somehow interpreting vertical
> bar in varchar value?
> 
> On Tue, 25 Apr 2017, KARR, DAVID wrote:
> 
> > Now that I've turned off variable interpolation in my big sql import
> script, when I run it, it gets an odd error on a particular insert line.
> It looks somewhat like this (mostly elided, except for the start of the
> values list):
> >
> >    insert into blah (colname1,colname2,colname3,...) values
> (371,null,'433449 | 274027 - ...
> >
> > In the error message, it says the syntax error is near "433449", and
> > when it shows the entire query, the end of the line is the "9" at the
> > end of that number.  It seems like that vertical bar character is
> > doing something.
> >
> > I don't know whether this is a mysql/mariadb issue, or SQuirreL.
> 
> Take a look at Session -> Session Properties -> SQL tab -> Statement
> Separator, is that perhaps set to vertical bar?
> 
> It doesn't make any sense for that to be the case, but that is one thing
> that can cause Squirrel to break the parsing of SQL midway through.

It turns out the problem was in the "MySQL Script Settings", in the "Procedure/Function Separator" field.  It was "|", and I changed it to "|||" to fix this problem.

Re: [Squirrel-sql-users] SQuirreL somehow interpreting vertical bar in varchar value?

[John H. <jh...@im...>]

On Tue, 25 Apr 2017, KARR, DAVID wrote:

> Now that I've turned off variable interpolation in my big sql import script, when I run it, it gets an odd error on a particular insert line.  It looks somewhat like this (mostly elided, except for the start of the values list):
>
>    insert into blah (colname1,colname2,colname3,...) values (371,null,'433449 | 274027 - ...
>
> In the error message, it says the syntax error is near "433449", and 
> when it shows the entire query, the end of the line is the "9" at the 
> end of that number.  It seems like that vertical bar character is doing 
> something.
>
> I don't know whether this is a mysql/mariadb issue, or SQuirreL.

Take a look at Session -> Session Properties -> SQL tab -> Statement 
Separator, is that perhaps set to vertical bar?

It doesn't make any sense for that to be the case, but that is one thing 
that can cause Squirrel to break the parsing of SQL midway through.


-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   We should endeavour to teach our children to be gun-proof
   rather than trying to design our guns to be child-proof
-----------------------------------------------------------------------
  26 days since the first commercial re-flight of an orbital booster (SpaceX)

[Squirrel-sql-users] SQuirreL somehow interpreting vertical bar in varchar value?

[KARR, D. <dk...@at...>]

Now that I've turned off variable interpolation in my big sql import script, when I run it, it gets an odd error on a particular insert line.  It looks somewhat like this (mostly elided, except for the start of the values list):

    insert into blah (colname1,colname2,colname3,...) values (371,null,'433449 | 274027 - ...

In the error message, it says the syntax error is near "433449", and when it shows the entire query, the end of the line is the "9" at the end of that number.  It seems like that vertical bar character is doing something.

I don't know whether this is a mysql/mariadb issue, or SQuirreL.

Re: [Squirrel-sql-users] How to execute SQL ignoring parameters

[John H. <jh...@im...>]

On Tue, 25 Apr 2017, KARR, DAVID wrote:

> Somewhat related to my just earlier question about exporting and 
> importing a database, it appears that when I execute a SQL script, 
> SQuirreL looks for strings like ":abc", which it recognizes as a 
> parameter.  How can I execute the SQL script without processing 
> parameters at all?

Temporarily disable the parameters plugin?

I don't think there's a way to suppress that on a per-query basis.


-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   It is not the business of government to make men virtuous
   or religious, or to preserve the fool from the consequences
   of his own folly.                                   -- Henry George
-----------------------------------------------------------------------
  26 days since the first commercial re-flight of an orbital booster (SpaceX)

[Squirrel-sql-users] How to execute SQL ignoring parameters

[KARR, D. <dk...@at...>]

Somewhat related to my just earlier question about exporting and importing a database, it appears that when I execute a SQL script, SQuirreL looks for strings like ":abc", which it recognizes as a parameter.  How can I execute the SQL script without processing parameters at all?

[Squirrel-sql-users] Export mariadb database for import to another instance?

[KARR, D. <dk...@at...>]

I'm working with an existing mariadb database, and I need to export the contents to a new instance.  I didn't notice any obvious export facilities in SQuirreL.  Is there an easy way to do this?  I noticed that the changelog for the unreleased version appeared to have some changes related to this.

Re: [Squirrel-sql-users] Squirrel - Importing

[Michael C. <mjc...@ao...>]

On Sun, 19 Mar 2017 19:03:05 -0400
"Brooks Jealous" <dbj...@gm...> wrote:

> Hi
> 
>  
> 
> First time Squirrel user and I’m having a bit of trouble with the
> import function.
> 
>  
> 
> 1.       I get a pop-up verification that all records in the target
> table will be deleted when I import. Seems a bit odd. Is there any
> flag / parameter that enables non-delete imports?
> 
>  
> 
> 2.       Even though I don’t get an error, records are not
> successfully imported. The records are valid CSV and/or XLS files
> that have been previously tested with other SQL clients, so I’m
> confident that there are no formatting issues. Any thoughts /
> comments on the stability of the import function, or notes on common
> errors?
> 
>  
> 
> Many thanks in advance! 
> 
>  
> 
> Brooks 
> 
> 金布鲁克斯
> 
>  
> 
> +1 415.870.0962
> 
>  <mailto:dbj...@gm...> dbj...@gm...
> 
>  <http://www.linkedin.com/in/dbjdigitalconsultant>
> www.linkedin.com/in/dbjdigitalconsultant
> 
>  
> 
> 滴水穿石
> 
>  
> 

Hi Brooks, I use the import plug-in regularly and it seems to work
well, but it was difficult to get it to work the first time.  For the
suggest to work, I need to use ignore case.  When the import doesn't
work I get an error.  I have not seen it appear to work without actually
doing the import.  I use a temporary table to get the import and then
use an insert to move data from the temporary table into the working
table.  I am not aware of an option where you can import directly
without deleting the existing data in the table.  I have used
squirrel-sql for a few years but am not an expert by any means.
Perhaps others can offer other insights.  Mike

[Squirrel-sql-users] Squirrel - Object Maintenance

[Brooks J. <dbj...@gm...>]

Hi

 

Maybe I didn’t install Squirrel correctly, but I can’t find any function to add, drop or modify a view. Surely this function exists in Squirrel – could anyone point me to it?

 

Thanks!

 

Brooks

金布鲁克斯

 

+1 415.870.0962

 <mailto:dbj...@gm...> dbj...@gm...

 <http://www.linkedin.com/in/dbjdigitalconsultant> www.linkedin.com/in/dbjdigitalconsultant

 

滴水穿石

 

[Squirrel-sql-users] FW: Squirrel - Importing - Part 02

[Brooks J. <dbj...@gm...>]

Pls disregard this last question. 

 

From: Brooks Jealous [mailto:dbj...@gm...] 
Sent: Sunday, March 19, 2017 7:07 PM
To: 'squ...@li...' <squ...@li...>
Subject: Squirrel - Importing - Part 02

 

Sorry – forgot one other obvious question. The mapping process can be tedious for tables with numerous columns – even with Suggest Columns checked, Squirrel does not populate the mapping list. 

 

1.       Is there a fix for the mapping issue above?

 

2.       Is there a template function, so that I can create standard templates for recurring imports…?

 

Thanks again,

 

Brooks

金布鲁克斯

 

+1 415.870.0962

 <mailto:dbj...@gm...> dbj...@gm...

 <http://www.linkedin.com/in/dbjdigitalconsultant> www.linkedin.com/in/dbjdigitalconsultant

 

滴水穿石

 

[Squirrel-sql-users] Squirrel - Importing - Part 02

[Brooks J. <dbj...@gm...>]

Sorry – forgot one other obvious question. The mapping process can be tedious for tables with numerous columns – even with Suggest Columns checked, Squirrel does not populate the mapping list. 

 

1.       Is there a fix for the mapping issue above?

 

2.       Is there a template function, so that I can create standard templates for recurring imports…?

 

Thanks again,

 

Brooks

金布鲁克斯

 

+1 415.870.0962

 <mailto:dbj...@gm...> dbj...@gm...

 <http://www.linkedin.com/in/dbjdigitalconsultant> www.linkedin.com/in/dbjdigitalconsultant

 

滴水穿石

 

[Squirrel-sql-users] Squirrel - Importing

[Brooks J. <dbj...@gm...>]

Hi

 

First time Squirrel user and I’m having a bit of trouble with the import function.

 

1.       I get a pop-up verification that all records in the target table will be deleted when I import. Seems a bit odd. Is there any flag / parameter that enables non-delete imports?

 

2.       Even though I don’t get an error, records are not successfully imported. The records are valid CSV and/or XLS files that have been previously tested with other SQL clients, so I’m confident that there are no formatting issues. Any thoughts / comments on the stability of the import function, or notes on common errors?

 

Many thanks in advance! 

 

Brooks 

金布鲁克斯

 

+1 415.870.0962

 <mailto:dbj...@gm...> dbj...@gm...

 <http://www.linkedin.com/in/dbjdigitalconsultant> www.linkedin.com/in/dbjdigitalconsultant

 

滴水穿石

 

[Squirrel-sql-users] Running out of memory when genreating SQL Server table data creation script in v.3.6

[Alex M. <ale...@bl...>]

So far I increased the memory size on the start command in the bat file to 1024m but java does not allocate anywhere near that - I get the error message

The query results have exceeded the maximum amount of memory allocated to this application.
All result tabs have been closed to allow you to save your work and restart the application.

when the process memory size is around 719m in the task manager. Also, I am not able to allow Squirrel use more than approximately 1240m of memory, it simply crashes on startup when I specify 1260, 1400 etc. Is there any workaround?  Does the data script generator really need to keep all data in memory? I am thinking that if there was an option to output the generated script straight to disk, there would be no need for this much memory.

Thank you!
Alex

Re: [Squirrel-sql-users] Columns double when running a simple select table

[Wesley N. <We...@Ke...>]

It appears to have something to do with viewing and possible sorting on the table columns tab. Switching from one table to another with the columns tab open sometimes causes all the column data to be shown twice.

How this gets back into my Pervasive schema is something I have not been able to duplicate consistently. Sometimes it happens, but most times the schema is unchanged. I wish I could figure out a way to duplicate it consistently.

I restarted Squirrel and cannot get it to corrupt the tables. I am going to dig into it further on my end. We did just do a major software update and possibly they had something wrong in there update process.

Best Regards,

Wesley S. Newswanger<http://w3w.cm/today.astounding.equals>    IT & Database Administrator

Keystone Collections(tm)
Office:  866.881.6680    ext. 1028
Email:    we...@ke... <mailto:we...@ke...>

[cid:image001.gif@01D29411.916F96D0]<http://www.keystonecollections.com/>
From: Wesley Newswanger
Sent: Friday, March 03, 2017 9:47 AM
To: 'Squ...@li...' <Squ...@li...>
Subject: Columns double when running a simple select table

I am running into a weird problem with Squirrel snapshot 20170214_2214 causing my columns to double in my Pervasive database.

If I run a simple Select * from query on a table it adds a whole set of duplicate columns. The duplicate columns do not show in the query. After this happens I get the following error when attempting to link in MS Access - 'Cannot define field more than once'. Squirrel shows the column names twice.

Here is an example of table schema from Pervasive before and after running a query.

/* ==========================================
* TABLE: INVENTORY_COST
* ========================================== */
CREATE TABLE "INVENTORY_COST"(
"PART" CHAR(20),
"LOCN" CHAR(2),
"COST" NUMERIC(16,6),
"MATL" NUMERIC(16,6),
"LABOR" NUMERIC(16,6),
"OVHD" NUMERIC(16,6),
"OUTS" NUMERIC(16,6),
"FREIGHT" NUMERIC(16,6),
"OTH" NUMERIC(16,6),
"CHG_DATE" DATE,
"CHG_TIME" TIME,
"CHG_BY" CHAR(8),
"CHG_PGM" CHAR(8),
"FILLER" CHAR(30));

CREATE UNIQUE NOT MODIFIABLE INDEX "INVENTORY_COSTK00" USING 0 ON "INVENTORY_COST"("PART", "LOCN");


/* ==========================================
* TABLE: INVENTORY_COST
* ========================================== */
CREATE TABLE "INVENTORY_COST"(
"PART" CHAR(20),
"LOCN" CHAR(2),
"COST" NUMERIC(16,6),
"MATL" NUMERIC(16,6),
"LABOR" NUMERIC(16,6),
"OVHD" NUMERIC(16,6),
"OUTS" NUMERIC(16,6),
"FREIGHT" NUMERIC(16,6),
"OTH" NUMERIC(16,6),
"CHG_DATE" DATE,
"CHG_TIME" TIME,
"CHG_BY" CHAR(8),
"CHG_PGM" CHAR(8),
"FILLER" CHAR(30),
"column0" CHAR(20),
"column1" CHAR(2),
"column2" NUMERIC(16,6),
"column3" NUMERIC(16,6),
"column4" NUMERIC(16,6),
"column5" NUMERIC(16,6),
"column6" NUMERIC(16,6),
"column7" NUMERIC(16,6),
"column8" NUMERIC(16,6),
"column9" DATE,
"column10" TIME,
"column11" CHAR(8),
"column12" CHAR(8),
"column13" CHAR(30));

CREATE UNIQUE NOT MODIFIABLE INDEX "INVENTORY_COSTK00" USING 0 ON "INVENTORY_COST"("PART", "LOCN");

Best Regards,

Wesley S. Newswanger<http://w3w.cm/today.astounding.equals>    IT & Database Administrator

Keystone Collections(tm)
Office:  866.881.6680    ext. 1028
Email:    we...@ke... <mailto:we...@ke...>

[cid:image001.gif@01D29411.916F96D0]<http://www.keystonecollections.com/>

Re: [Squirrel-sql-users] Columns double when running a simple select table

[John H. <jh...@im...>]

On Fri, 3 Mar 2017, Wesley Newswanger wrote:

> If I run a simple Select * from query on a table it adds a whole set of duplicate columns.

> Here is an example of table schema from Pervasive before and after running a query.

{snip}

Just to be clear about what you're saying: running a SELECT query in 
Squirrel causes the table schema in the database to be modified?


-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Taking my gun away because I *might* shoot someone is like cutting
   my tongue out because I *might* yell "Fire!" in a crowded theater.
                                                   -- Peter Venetoklis
-----------------------------------------------------------------------
  10 days until Albert Einstein's 138th Birthday

[Squirrel-sql-users] Columns double when running a simple select table

[Wesley N. <We...@Ke...>]

I am running into a weird problem with Squirrel snapshot 20170214_2214 causing my columns to double in my Pervasive database.

If I run a simple Select * from query on a table it adds a whole set of duplicate columns. The duplicate columns do not show in the query. After this happens I get the following error when attempting to link in MS Access - 'Cannot define field more than once'. Squirrel shows the column names twice.

Here is an example of table schema from Pervasive before and after running a query.

/* ==========================================
* TABLE: INVENTORY_COST
* ========================================== */
CREATE TABLE "INVENTORY_COST"(
"PART" CHAR(20),
"LOCN" CHAR(2),
"COST" NUMERIC(16,6),
"MATL" NUMERIC(16,6),
"LABOR" NUMERIC(16,6),
"OVHD" NUMERIC(16,6),
"OUTS" NUMERIC(16,6),
"FREIGHT" NUMERIC(16,6),
"OTH" NUMERIC(16,6),
"CHG_DATE" DATE,
"CHG_TIME" TIME,
"CHG_BY" CHAR(8),
"CHG_PGM" CHAR(8),
"FILLER" CHAR(30));

CREATE UNIQUE NOT MODIFIABLE INDEX "INVENTORY_COSTK00" USING 0 ON "INVENTORY_COST"("PART", "LOCN");


/* ==========================================
* TABLE: INVENTORY_COST
* ========================================== */
CREATE TABLE "INVENTORY_COST"(
"PART" CHAR(20),
"LOCN" CHAR(2),
"COST" NUMERIC(16,6),
"MATL" NUMERIC(16,6),
"LABOR" NUMERIC(16,6),
"OVHD" NUMERIC(16,6),
"OUTS" NUMERIC(16,6),
"FREIGHT" NUMERIC(16,6),
"OTH" NUMERIC(16,6),
"CHG_DATE" DATE,
"CHG_TIME" TIME,
"CHG_BY" CHAR(8),
"CHG_PGM" CHAR(8),
"FILLER" CHAR(30),
"column0" CHAR(20),
"column1" CHAR(2),
"column2" NUMERIC(16,6),
"column3" NUMERIC(16,6),
"column4" NUMERIC(16,6),
"column5" NUMERIC(16,6),
"column6" NUMERIC(16,6),
"column7" NUMERIC(16,6),
"column8" NUMERIC(16,6),
"column9" DATE,
"column10" TIME,
"column11" CHAR(8),
"column12" CHAR(8),
"column13" CHAR(30));

CREATE UNIQUE NOT MODIFIABLE INDEX "INVENTORY_COSTK00" USING 0 ON "INVENTORY_COST"("PART", "LOCN");

Best Regards,

Wesley S. Newswanger<http://w3w.cm/today.astounding.equals>    IT & Database Administrator

Keystone Collections(tm)
Office:  866.881.6680    ext. 1028
Email:    we...@ke... <mailto:we...@ke...>

[cid:image001.gif@01D29401.8C65B6C0]<http://www.keystonecollections.com/>

Re: [Squirrel-sql-users] feed a CSV file into a IN statement

[John H. <jh...@im...>]

On Sat, 25 Feb 2017, John Hardin wrote:

> (2) write some SQL to create a work table (e.g. t_pasta) with one column of 
>
> SELECT pasta FROM menu WHERE pasta IN (SELECT pasta FROM t_pasta_file);

Oop - apologies for the editing error there, it should of course be:

   SELECT pasta FROM menu WHERE pasta IN (SELECT pasta FROM t_pasta);


-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Insofar as the police deter by their presence, they are very, very
   good. Criminals take great pains not to commit a crime in front of
   them.                                             -- Jeffrey Snyder
-----------------------------------------------------------------------
  432 days since the first successful real return to launch site (SpaceX)

Re: [Squirrel-sql-users] feed a CSV file into a IN statement

[John H. <jh...@im...>]

On Fri, 24 Feb 2017, Federico Bruni wrote:

> I wonder if I can tell Squirrel to take a list of items from a CSV file.
> I'd like to have a query which works like this (pseudocode):
>
> SELECT pasta FROM menu WHERE pasta IN :csvfile
>
> and the CSV file may contain the items I want to use to filter the
> query, such as:
>
> pesto;tomato;butter
>
> Perhaps there's a plugin for this purpose?
>
> I don't want to type each item interactively, as I could do with
> sqlparam plugin.
> And I don't want to edit my saved query (under version control) each
> time I want to run a new query.
>
> Thanks in advance
> Federico

There is a data import plugin but it's not scriptable (as far as I know - 
I don't use it myself).

Is it acceptable to pop a UI and type in the filename and some other 
details every time you want to do that?

If that's acceptable then how about this:

(1) create your data file with one value per line rather than multiple 
delimited values on one line.

(2) write some SQL to create a work table (e.g. t_pasta) with one column 
of the correct datatype. A session-temporary table (e.g. MSSQL #t_pasta) 
may or may not work depending on JDBC connection pooling, so you might 
need to use a "permanent" temporary table (i.e. one you have to explicitly 
drop when you're finished with it).

(3) run the data import plugin to populate that table with your data file.

(4) write your query to do this:

SELECT pasta FROM menu WHERE pasta IN (SELECT pasta FROM t_pasta_file);

(5) remember to drop t_pasta when you're finished with it if it's not 
a true session-temporary table.

You wouldn't have to type in the data (you do have to type in the filename 
and some other info to load the data, but that shouldn't change from 
session to session assuming whatever is generating the data file puts it 
in the same place every time), and your SQL script can be read-only.


You might want to go to the Squirrel-SQL bug tracker at SourceForge and 
create a feature request for adding scriptability to the data import 
plugin so that the interactive part of the above could be eliminated.



-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Insofar as the police deter by their presence, they are very, very
   good. Criminals take great pains not to commit a crime in front of
   them.                                             -- Jeffrey Snyder
-----------------------------------------------------------------------
  432 days since the first successful real return to launch site (SpaceX)

[Squirrel-sql-users] feed a CSV file into a IN statement

[Federico B. <fe...@in...>]

Hi all

I wonder if I can tell Squirrel to take a list of items from a CSV file.
I'd like to have a query which works like this (pseudocode):

SELECT pasta FROM menu WHERE pasta IN :csvfile

and the CSV file may contain the items I want to use to filter the 
query, such as:

pesto;tomato;butter

Perhaps there's a plugin for this purpose?

I don't want to type each item interactively, as I could do with 
sqlparam plugin.
And I don't want to edit my saved query (under version control) each 
time I want to run a new query.

Thanks in advance
Federico

Re: [Squirrel-sql-users] UDB databases using security mechanisms

[John H. <jh...@im...>]

On Thu, 16 Feb 2017, Laffrey Lynne Marie (FCA) wrote:

> With the semicolon at the end I get the following error message.
>
> java.util.concurrent.ExecutionException: java.lang.RuntimeException: 
> com.ibm.db2.jcc.am.SqlException: [jcc][1071][10615][3.67.26] Caught 
> java.security.InvalidAlgorithmParameterException while initializing 
> EncryptionManager.  See attached Throwable for details. ERRORCODE=-4223, 
> SQLSTATE=null

...which is not a syntax error, so progress is being made.

Wild guess: is your local Java runtime up-to-date? "Security Mechanism 13" 
might specify a recent crypto algorithm.

See also:

   https://support.dbvis.com/support/discussions/topics/1000079566

You may need to download your Java runtime from IBM...

This:

   http://stackoverflow.com/questions/10649009/java-security-invalidalgorithmparameterexception-while-initializing-encryptionma

...suggests that you might need some crypto initialization parameters in 
the JDBC connect string. I have no idea precisely how to do that, though, 
sorry.

This has apparently been an issue for a while:

   https://sourceforge.net/p/squirrel-sql/bugs/998/

Here, do some digging:

   http://www.google.com/search?q=java.security.InvalidAlgorithmParameterException+while+initializing+EncryptionManager+db2


> From: Alex Malmyguine [mailto:ale...@bl...]
> Sent: Thursday, February 16, 2017 5:45 PM
> To: squ...@li...
> Subject: Re: [Squirrel-sql-users] UDB databases using security mechanisms
>
> John Hardin just posted a few min ago that the driver requires the closing semicolon after =13;
>
>
> From: Laffrey Lynne Marie (FCA) [mailto:lyn...@fc...]
> Sent: Thursday, February 16, 2017 5:40 PM
> To: Alex Malmyguine; squ...@li...<mailto:squ...@li...>
> Subject: RE: UDB databases using security mechanisms
>
> Hi Alex,
>
> I have tried both a colon and a semi-colon and receive the same error.  When I look up the syntax, it only stops at database with nothing after it.  This driver and url works for all my other UDB databases.  This new one has added security to it and I can't connect.
>
> Sincerely,
> Lynne Marie
>
> From: Alex Malmyguine [mailto:ale...@bl...]
> Sent: Thursday, February 16, 2017 3:40 PM
> To: squ...@li...<mailto:squ...@li...>
> Subject: Re: [Squirrel-sql-users] UDB databases using security mechanisms
>
> Should it be a colon or semicolon before securityMechanism, or perhaps a question mark?
>
>
> From: Laffrey Lynne Marie (FCA) [mailto:lyn...@fc...]
> Sent: Thursday, February 16, 2017 2:21 PM
> To: squ...@li...<mailto:squ...@li...>
> Subject: [Squirrel-sql-users] UDB databases using security mechanisms
>
> Hi,
>
>                I need to connect to a UDB v10.5.2 AIX database using Squirrel.  I'm using the IBM DB2 Universal type 4 driver.  I get this error:
>
> java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][201][11237][3.67.26] Connection authorization failure occurred.  Reason: Security mechanism not supported. ERRORCODE=-4214, SQLSTATE=28000
>
> I was told by the DBA that I would need to use securityMechanism=13 in my JDBC command, but when I try it in squirrel, I get this error:
>
> java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10051][3.67.26] Invalid database URL syntax: jdbc:db2://151.92.117.35:60000/DAPG201L:securityMechanism=13. ERRORCODE=-4461, SQLSTATE=42815
>
> How do I pass the security mechanism parameter when using Squirrel?
>
> Sincerely,
>
> [FCA_Pantone_email]
> _________________________________
>
> Lynne Marie Laffrey
> Data Engineer
>
> ICT Business Analytics
> 1075 West Entrance Drive
> Auburn Hills, MI   48326
> CIMS: 489-03-02
>
> Phone: 248-838-4895
> Email: Lyn...@fc...<mailto:Lyn...@fc...>
>
> Be social follow, Business Intelligence and Analytics
>
> [http://files.softicons.com/download/system-icons/windows-8-metro-icons-by-dakirby309/png/512x512/Office%20Apps/SharePoint.png]<https://partners.chrysler.com/sites/smbusintl/SitePages/Home.aspx>
>
> The information contained in the e-mail message is intended only for the personal and confidential use of the recipient(s) named above.  If the reader of this message is not the intended recipient or an agent responsible for delibering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited.  If you have received this communication in error, please notify us immediately by e-mail, and delete the original message.
>
>
>

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   USMC Rules of Gunfighting #7: In ten years nobody will remember the
   details of caliber, stance, or tactics. They will only remember who
   lived.
-----------------------------------------------------------------------
  6 days until George Washington's 285th Birthday

Re: [Squirrel-sql-users] UDB databases using security mechanisms

[Laffrey L. M. (FCA) <lyn...@fc...>]

With the semicolon at the end I get the following error message.


java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlException: [jcc][1071][10615][3.67.26] Caught java.security.InvalidAlgorithmParameterException while initializing EncryptionManager.  See attached Throwable for details. ERRORCODE=-4223, SQLSTATE=null

From: Alex Malmyguine [mailto:ale...@bl...]
Sent: Thursday, February 16, 2017 5:45 PM
To: squ...@li...
Subject: Re: [Squirrel-sql-users] UDB databases using security mechanisms

John Hardin just posted a few min ago that the driver requires the closing semicolon after =13;


From: Laffrey Lynne Marie (FCA) [mailto:lyn...@fc...]
Sent: Thursday, February 16, 2017 5:40 PM
To: Alex Malmyguine; squ...@li...<mailto:squ...@li...>
Subject: RE: UDB databases using security mechanisms

Hi Alex,

I have tried both a colon and a semi-colon and receive the same error.  When I look up the syntax, it only stops at database with nothing after it.  This driver and url works for all my other UDB databases.  This new one has added security to it and I can't connect.

Sincerely,
Lynne Marie

From: Alex Malmyguine [mailto:ale...@bl...]
Sent: Thursday, February 16, 2017 3:40 PM
To: squ...@li...<mailto:squ...@li...>
Subject: Re: [Squirrel-sql-users] UDB databases using security mechanisms

Should it be a colon or semicolon before securityMechanism, or perhaps a question mark?


From: Laffrey Lynne Marie (FCA) [mailto:lyn...@fc...]
Sent: Thursday, February 16, 2017 2:21 PM
To: squ...@li...<mailto:squ...@li...>
Subject: [Squirrel-sql-users] UDB databases using security mechanisms

Hi,

                I need to connect to a UDB v10.5.2 AIX database using Squirrel.  I'm using the IBM DB2 Universal type 4 driver.  I get this error:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][201][11237][3.67.26] Connection authorization failure occurred.  Reason: Security mechanism not supported. ERRORCODE=-4214, SQLSTATE=28000

I was told by the DBA that I would need to use securityMechanism=13 in my JDBC command, but when I try it in squirrel, I get this error:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10051][3.67.26] Invalid database URL syntax: jdbc:db2://151.92.117.35:60000/DAPG201L:securityMechanism=13. ERRORCODE=-4461, SQLSTATE=42815

How do I pass the security mechanism parameter when using Squirrel?

Sincerely,

[FCA_Pantone_email]
_________________________________

Lynne Marie Laffrey
Data Engineer

ICT Business Analytics
1075 West Entrance Drive
Auburn Hills, MI   48326
CIMS: 489-03-02

Phone: 248-838-4895
Email: Lyn...@fc...<mailto:Lyn...@fc...>

Be social follow, Business Intelligence and Analytics

[http://files.softicons.com/download/system-icons/windows-8-metro-icons-by-dakirby309/png/512x512/Office%20Apps/SharePoint.png]<https://partners.chrysler.com/sites/smbusintl/SitePages/Home.aspx>

The information contained in the e-mail message is intended only for the personal and confidential use of the recipient(s) named above.  If the reader of this message is not the intended recipient or an agent responsible for delibering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited.  If you have received this communication in error, please notify us immediately by e-mail, and delete the original message.

Re: [Squirrel-sql-users] UDB databases using security mechanisms

[Alex M. <ale...@bl...>]

John Hardin just posted a few min ago that the driver requires the closing semicolon after =13;


From: Laffrey Lynne Marie (FCA) [mailto:lyn...@fc...]
Sent: Thursday, February 16, 2017 5:40 PM
To: Alex Malmyguine; squ...@li...
Subject: RE: UDB databases using security mechanisms

Hi Alex,

I have tried both a colon and a semi-colon and receive the same error.  When I look up the syntax, it only stops at database with nothing after it.  This driver and url works for all my other UDB databases.  This new one has added security to it and I can't connect.

Sincerely,
Lynne Marie

From: Alex Malmyguine [mailto:ale...@bl...]
Sent: Thursday, February 16, 2017 3:40 PM
To: squ...@li...<mailto:squ...@li...>
Subject: Re: [Squirrel-sql-users] UDB databases using security mechanisms

Should it be a colon or semicolon before securityMechanism, or perhaps a question mark?


From: Laffrey Lynne Marie (FCA) [mailto:lyn...@fc...]
Sent: Thursday, February 16, 2017 2:21 PM
To: squ...@li...<mailto:squ...@li...>
Subject: [Squirrel-sql-users] UDB databases using security mechanisms

Hi,

                I need to connect to a UDB v10.5.2 AIX database using Squirrel.  I'm using the IBM DB2 Universal type 4 driver.  I get this error:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][201][11237][3.67.26] Connection authorization failure occurred.  Reason: Security mechanism not supported. ERRORCODE=-4214, SQLSTATE=28000

I was told by the DBA that I would need to use securityMechanism=13 in my JDBC command, but when I try it in squirrel, I get this error:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10051][3.67.26] Invalid database URL syntax: jdbc:db2://151.92.117.35:60000/DAPG201L:securityMechanism=13. ERRORCODE=-4461, SQLSTATE=42815

How do I pass the security mechanism parameter when using Squirrel?

Sincerely,

[FCA_Pantone_email]
_________________________________

Lynne Marie Laffrey
Data Engineer

ICT Business Analytics
1075 West Entrance Drive
Auburn Hills, MI   48326
CIMS: 489-03-02

Phone: 248-838-4895
Email: Lyn...@fc...<mailto:Lyn...@fc...>

Be social follow, Business Intelligence and Analytics

[http://files.softicons.com/download/system-icons/windows-8-metro-icons-by-dakirby309/png/512x512/Office%20Apps/SharePoint.png]<https://partners.chrysler.com/sites/smbusintl/SitePages/Home.aspx>

The information contained in the e-mail message is intended only for the personal and confidential use of the recipient(s) named above.  If the reader of this message is not the intended recipient or an agent responsible for delibering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited.  If you have received this communication in error, please notify us immediately by e-mail, and delete the original message.