Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #38408 > unrolled thread

Is there a SQL hashmap?

Back to article view | Back to comp.lang.java.programmer

Contents

  Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-26 09:03 -0700
    Re: Is there a SQL hashmap? Arne Vajhøj <arne@vajhoej.dk> - 2018-06-26 12:23 -0400
    Re: Is there a SQL hashmap? Robert Klemme <shortcutter@googlemail.com> - 2018-06-26 22:36 +0200
      Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-26 14:02 -0700
        Re: Is there a SQL hashmap? Arne Vajhøj <arne@vajhoej.dk> - 2018-06-27 20:22 -0400
          Re: Is there a SQL hashmap? firstsql@gmail.com - 2018-06-27 20:53 -0700
          Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-28 05:03 -0700
            Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-28 06:49 -0700
              Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-28 07:38 -0700
                Re: Is there a SQL hashmap? Andreas Leitgeb <avl@logic.at> - 2018-06-28 15:52 +0000
                  Re: Is there a SQL hashmap? Martin Gregorie <martin@mydomain.invalid> - 2018-06-28 19:47 +0000
                    Re: Is there a SQL hashmap? Arne Vajhøj <arne@vajhoej.dk> - 2018-06-28 18:34 -0400
                      Re: Is there a SQL hashmap? Martin Gregorie <martin@mydomain.invalid> - 2018-06-28 23:20 +0000
                        Re: Is there a SQL hashmap? Arne Vajhøj <arne@vajhoej.dk> - 2018-06-28 20:07 -0400
                        Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-29 05:11 -0700
                          Re: Is there a SQL hashmap? Martin Gregorie <martin@mydomain.invalid> - 2018-06-29 12:30 +0000
                            Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-06-29 06:11 -0700
                              Re: Is there a SQL hashmap? Martin Gregorie <martin@mydomain.invalid> - 2018-06-29 13:43 +0000
                                Re: Is there a SQL hashmap? Robert Klemme <shortcutter@googlemail.com> - 2018-06-29 23:20 +0200
                                  Re: Is there a SQL hashmap? Martin Gregorie <martin@mydomain.invalid> - 2018-06-29 22:34 +0000
                                    Re: Is there a SQL hashmap? Arne Vajhøj <arne@vajhoej.dk> - 2018-06-30 16:36 -0400
                                      Re: Is there a SQL hashmap? Martin Gregorie <martin@mydomain.invalid> - 2018-06-30 23:00 +0000
                Re: Is there a SQL hashmap? Robert Klemme <shortcutter@googlemail.com> - 2018-06-29 23:34 +0200
              Re: Is there a SQL hashmap? Arne Vajhøj <arne@vajhoej.dk> - 2018-06-30 17:51 -0400
                Re: Is there a SQL hashmap? Eric Douglas <e.d.programmer@gmail.com> - 2018-07-03 07:13 -0700

Page 1 of 2  [1] 2  Next page →

#38408 — Is there a SQL hashmap?

I'm using JDBC with java.sql.PreparedStatement on SQL Server 2012.

I'm trying to write a translate that says "select <map value> (where table1.field = <map key>) from table1"

Getting a bit tired of writing it as "select iif(table1.field = 'first key','first value',iif(table1.field = 'second key','second value','default')) from table1"

Just a better syntax to do such a thing within the SQL, or a way to pass the values as a parameter?  Ideally I would just create a table and write the values there and join that table, but I don't want to alter that database which was created by our third party application.

I was hoping to avoid the other option, modifying the code that gets the results of the query to translate that result field, since this section of the code is generic - used to send the results of multiple queries to a spreadsheet.

[toc] | [next] | [standalone]

#38409

On 6/26/2018 12:03 PM, Eric Douglas wrote:
> I'm using JDBC with java.sql.PreparedStatement on SQL Server 2012.
> 
> I'm trying to write a translate that says "select <map value> (where
> table1.field = <map key>) from table1"
> 
> Getting a bit tired of writing it as "select iif(table1.field =
> 'first key','first value',iif(table1.field = 'second key','second
> value','default')) from table1"
> 
> Just a better syntax to do such a thing within the SQL, or a way to
> pass the values as a parameter?  Ideally I would just create a table
> and write the values there and join that table, but I don't want to
> alter that database which was created by our third party
> application.
> 
> I was hoping to avoid the other option, modifying the code that gets
> the results of the query to translate that result field, since this
> section of the code is generic - used to send the results of multiple
> queries to a spreadsheet.


I think you already considered the two most obvious options.

1) Database table and JOIN.
2) Translate in application code.

Note that #2 is a lot more elegant if using JPA than plain JDBC.

String mappedfield1 = valmap.get(rs.getString("field1"));

vs:

@Column
private String field1;
...
public String getField1() {
     return field1;
}
public String getMappedField1() {
     return valmap.get(field1);
}

Arne

[toc] | [prev] | [next] | [standalone]

#38420

On 26.06.2018 18:03, Eric Douglas wrote:
> I'm using JDBC with java.sql.PreparedStatement on SQL Server 2012.
> 
> I'm trying to write a translate that says "select <map value> (where
> table1.field = <map key>) from table1"
> 
> Getting a bit tired of writing it as "select iif(table1.field =
> 'first key','first value',iif(table1.field = 'second key','second
> value','default')) from table1"

Why do you use SQL Server's non standard IFF which requires uuhgly 
nesting when there is CASE?

SELECT CASE field
   WHEN 'first key' THEN 'first value'
   WHEN 'second key' THEN 'second value'
   ELSE 'default'
   END AS mapped
FROM table1

I guess a separate table with index and joining is more efficient if the 
number of mappings gets large.

> Just a better syntax to do such a thing within the SQL, or a way to
> pass the values as a parameter?  Ideally I would just create a table
> and write the values there and join that table, but I don't want to
> alter that database which was created by our third party
> application.

Another option might be to create another database on the same server 
with the new table and join cross databases if SQL Server can do that.

> I was hoping to avoid the other option, modifying the code that gets
> the results of the query to translate that result field, since this
> section of the code is generic - used to send the results of multiple
> queries to a spreadsheet.

Oh, lots of constraints.

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[toc] | [prev] | [next] | [standalone]

#38421

On Tuesday, June 26, 2018 at 4:36:44 PM UTC-4, Robert Klemme wrote:
> On 26.06.2018 18:03, Eric Douglas wrote:
> > I'm using JDBC with java.sql.PreparedStatement on SQL Server 2012.
> > 
> > I'm trying to write a translate that says "select <map value> (where
> > table1.field = <map key>) from table1"
> > 
> > Getting a bit tired of writing it as "select iif(table1.field =
> > 'first key','first value',iif(table1.field = 'second key','second
> > value','default')) from table1"
> 
> Why do you use SQL Server's non standard IFF which requires uuhgly 
> nesting when there is CASE?
> 
> SELECT CASE field
>    WHEN 'first key' THEN 'first value'
>    WHEN 'second key' THEN 'second value'
>    ELSE 'default'
>    END AS mapped
> FROM table1
> 
> I guess a separate table with index and joining is more efficient if the 
> number of mappings gets large.
> 
> > Just a better syntax to do such a thing within the SQL, or a way to
> > pass the values as a parameter?  Ideally I would just create a table
> > and write the values there and join that table, but I don't want to
> > alter that database which was created by our third party
> > application.
> 
> Another option might be to create another database on the same server 
> with the new table and join cross databases if SQL Server can do that.
> 
> > I was hoping to avoid the other option, modifying the code that gets
> > the results of the query to translate that result field, since this
> > section of the code is generic - used to send the results of multiple
> > queries to a spreadsheet.
> 
> Oh, lots of constraints.
> 
> Cheers
> 
> 	robert

Right.  I am familiar with IFF, not with CASE, sounds good I'll check that out.
I don't think the SQL can join tables from 2 different databases but would be nice if it can, needs researched.
I hard coded all the values it currently needs translated but it certainly should be in something end users can maintain.

[toc] | [prev] | [next] | [standalone]

#38441

On 6/26/2018 5:02 PM, Eric Douglas wrote:
> On Tuesday, June 26, 2018 at 4:36:44 PM UTC-4, Robert Klemme wrote:
>> On 26.06.2018 18:03, Eric Douglas wrote:
>>> Just a better syntax to do such a thing within the SQL, or a way to
>>> pass the values as a parameter?  Ideally I would just create a table
>>> and write the values there and join that table, but I don't want to
>>> alter that database which was created by our third party
>>> application.
>>
>> Another option might be to create another database on the same server
>> with the new table and join cross databases if SQL Server can do that.

> Right.  I am familiar with IFF, not with CASE, sounds good I'll check that out.

> I don't think the SQL can join tables from 2 different databases but would be nice if it can, needs researched.

Generally you can't.

Usually you can.

You can in MS SQLServer

Just refer to the tables as somedatabasename.dbo.sometablename and it 
should work.

Arne

[toc] | [prev] | [next] | [standalone]

#38443

On Wednesday, June 27, 2018 at 5:22:23 PM UTC-7, Arne Vajhøj wrote:
> On 6/26/2018 5:02 PM, Eric Douglas wrote:
> > On Tuesday, June 26, 2018 at 4:36:44 PM UTC-4, Robert Klemme wrote:
> >> On 26.06.2018 18:03, Eric Douglas wrote:
> >>> Just a better syntax to do such a thing within the SQL, or a way to
> >>> pass the values as a parameter?  Ideally I would just create a table
> >>> and write the values there and join that table, but I don't want to
> >>> alter that database which was created by our third party
> >>> application.
> >>
> >> Another option might be to create another database on the same server
> >> with the new table and join cross databases if SQL Server can do that.
> 
> > Right.  I am familiar with IFF, not with CASE, sounds good I'll check that out.
> 
> > I don't think the SQL can join tables from 2 different databases but would be nice if it can, needs researched.
> 
> Generally you can't.
> 
> Usually you can.
> 
> You can in MS SQLServer
> 
> Just refer to the tables as somedatabasename.dbo.sometablename and it 
> should work.
> 
> Arne

The general name is Federated Database or Federated SQL.

Sounds like a great idea, but optimization is severely crippled.

Lee Fesperman, FirstSQL Software

[toc] | [prev] | [next] | [standalone]

#38444

On Wednesday, June 27, 2018 at 8:22:23 PM UTC-4, Arne Vajhøj wrote:
> > I don't think the SQL can join tables from 2 different databases but would be nice if it can, needs researched.
> 
> Generally you can't.
> 
> Usually you can.
> 
> You can in MS SQLServer
> 
> Just refer to the tables as somedatabasename.dbo.sometablename and it 
> should work.
> 
> Arne

but I am using java.sql.DriverManager.getConnection() which requires a connect string which includes the database name - ?

[toc] | [prev] | [next] | [standalone]

#38445

On Thursday, June 28, 2018 at 8:03:41 AM UTC-4, Eric Douglas wrote:
> On Wednesday, June 27, 2018 at 8:22:23 PM UTC-4, Arne Vajhøj wrote:
> > > I don't think the SQL can join tables from 2 different databases but would be nice if it can, needs researched.
> > 
> > Generally you can't.
> > 
> > Usually you can.
> > 
> > You can in MS SQLServer
> > 
> > Just refer to the tables as somedatabasename.dbo.sometablename and it 
> > should work.
> > 
> > Arne
> 
> but I am using java.sql.DriverManager.getConnection() which requires a connect string which includes the database name - ?

I see, that works only on other level...
Currently we're using connect string = jdbc:sqlserver://servername:port;databasename
Then we can do select * from tablename

The other option is to update every reference:
connect string = jdbc:sqlserver://servername:port
Then select * from databasename.dbo.tablename

This second format is only required if you need to connect tables from multiple databases and looks uglier the more tables you join.  To switch from one to the other gets really ugly if you're changing the connect string for a common connection method that already has a bunch of sql statements which aren't referencing the database name.

[toc] | [prev] | [next] | [standalone]

#38446

On Thursday, June 28, 2018 at 9:49:47 AM UTC-4, Eric Douglas wrote:
> > > You can in MS SQLServer
> > > 
> > > Just refer to the tables as somedatabasename.dbo.sometablename and it 
> > > should work.
> > > 
> > > Arne
> > 
> > but I am using java.sql.DriverManager.getConnection() which requires a connect string which includes the database name - ?
> 
> I see, that works only on other level...
> Currently we're using connect string = jdbc:sqlserver://servername:port;databasename
> Then we can do select * from tablename
> 
> The other option is to update every reference:
> connect string = jdbc:sqlserver://servername:port
> Then select * from databasename.dbo.tablename
> 
> This second format is only required if you need to connect tables from multiple databases and looks uglier the more tables you join.  To switch from one to the other gets really ugly if you're changing the connect string for a common connection method that already has a bunch of sql statements which aren't referencing the database name.

I don't know if this third party software would have issue with adding a database to the same instance.  The database part is optional in the connect string, but the second database we do have running on that server is in a totally different instance, different port, different credentials, one database running sql server 2012 one running 2014.  So JDBC can access both but as far as I know cannot join both in the same select statement.  Even if I can setup matching credentials, surely only one port at a time.  We have another server running mysql, probably the best place to store the lookup values, but would have to process in a separate JDBC instance after getting the results.

[toc] | [prev] | [next] | [standalone]

#38447

Eric Douglas <e.d.programmer@gmail.com> wrote:
> So JDBC can access both but as far as I know cannot join both in the
> same select statement.

Further approaches:

- adding another table to a database shouldn't really interfere much
    with the 3rd party software accessing it.

- maybe the database already has a simple table of two string columns,
    where you could add the key-value pairs as extra rows, maybe aug-
    menting the keys to not interfere with other entries of that table

- maybe it is possible to create temporary tables and fill them with
    your mappings just when you need it...

[toc] | [prev] | [next] | [standalone]

#38449

On Thu, 28 Jun 2018 15:52:54 +0000, Andreas Leitgeb wrote:

> Eric Douglas <e.d.programmer@gmail.com> wrote:
>> So JDBC can access both but as far as I know cannot join both in the
>> same select statement.
> 
> Further approaches:
> 
> - adding another table to a database shouldn't really interfere much
>     with the 3rd party software accessing it.
> 
> - maybe the database already has a simple table of two string columns,
>     where you could add the key-value pairs as extra rows, maybe aug-
>     menting the keys to not interfere with other entries of that table
> 
> - maybe it is possible to create temporary tables and fill them with
>     your mappings just when you need it...

...or use a view (if SQL Server supports views)


-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

#38451

On 6/28/2018 3:47 PM, Martin Gregorie wrote:
> On Thu, 28 Jun 2018 15:52:54 +0000, Andreas Leitgeb wrote:
>> Eric Douglas <e.d.programmer@gmail.com> wrote:
>>> So JDBC can access both but as far as I know cannot join both in the
>>> same select statement.
>>
>> Further approaches:
>>
>> - adding another table to a database shouldn't really interfere much
>>      with the 3rd party software accessing it.
>>
>> - maybe the database already has a simple table of two string columns,
>>      where you could add the key-value pairs as extra rows, maybe aug-
>>      menting the keys to not interfere with other entries of that table
>>
>> - maybe it is possible to create temporary tables and fill them with
>>      your mappings just when you need it...
> 
> ...or use a view (if SQL Server supports views)

It does.

But the view need to be on something.

Arne

[toc] | [prev] | [next] | [standalone]

#38453

On Thu, 28 Jun 2018 18:34:53 -0400, Arne Vajhøj wrote:

> On 6/28/2018 3:47 PM, Martin Gregorie wrote:
>> On Thu, 28 Jun 2018 15:52:54 +0000, Andreas Leitgeb wrote:
>>> Eric Douglas <e.d.programmer@gmail.com> wrote:
>>>> So JDBC can access both but as far as I know cannot join both in the
>>>> same select statement.
>>>
>>> Further approaches:
>>>
>>> - adding another table to a database shouldn't really interfere much
>>>      with the 3rd party software accessing it.
>>>
>>> - maybe the database already has a simple table of two string columns,
>>>      where you could add the key-value pairs as extra rows, maybe aug-
>>>      menting the keys to not interfere with other entries of that
>>>      table
>>>
>>> - maybe it is possible to create temporary tables and fill them with
>>>      your mappings just when you need it...
>> 
>> ...or use a view (if SQL Server supports views)
> 
> It does.
> 
> But the view need to be on something.
> 
I thought Eric was saying that the stuff he needed to join was in two or 
more parallel tables, in which case a view should solve his problem with 
little impact on the existing database.


-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

#38454

On 6/28/2018 7:20 PM, Martin Gregorie wrote:
> On Thu, 28 Jun 2018 18:34:53 -0400, Arne Vajhøj wrote:
>> On 6/28/2018 3:47 PM, Martin Gregorie wrote:
>>> ...or use a view (if SQL Server supports views)
>>
>> It does.
>>
>> But the view need to be on something.
>
> I thought Eric was saying that the stuff he needed to join was in two or
> more parallel tables, in which case a view should solve his problem with
> little impact on the existing database.

In that case I missed something.

Arne
\

[toc] | [prev] | [next] | [standalone]

#38455

On Thursday, June 28, 2018 at 7:20:20 PM UTC-4, Martin Gregorie wrote:
> >> ...or use a view (if SQL Server supports views)
> > 
> > It does.
> > 
> > But the view need to be on something.
> > 
> I thought Eric was saying that the stuff he needed to join was in two or 
> more parallel tables, in which case a view should solve his problem with 
> little impact on the existing database.
> 

Not sure what you mean by parallel, related tables - one to many relationship.
It looks like you can connect one JDBC to multiple instances as server:port,server:port in the connection string, apparently if both have the same credentials.  It only appears to allow one username, one password.

[toc] | [prev] | [next] | [standalone]

#38456

On Fri, 29 Jun 2018 05:11:45 -0700, Eric Douglas wrote:

> On Thursday, June 28, 2018 at 7:20:20 PM UTC-4, Martin Gregorie wrote:
>> >> ...or use a view (if SQL Server supports views)
>> > 
>> > It does.
>> > 
>> > But the view need to be on something.
>> > 
>> I thought Eric was saying that the stuff he needed to join was in two
>> or more parallel tables, in which case a view should solve his problem
>> with little impact on the existing database.
>> 
>> 
> Not sure what you mean by parallel, related tables - one to many
> relationship.
> It looks like you can connect one JDBC to multiple instances as
> server:port,server:port in the connection string, apparently if both
> have the same credentials.  It only appears to allow one username, one
> password.

Yes, I'm not sure why I said 'parallel'. What I meant was that, if I 
understood you correctly, you could do what you need by building a new 
table and populating it by joining existing tables. All I was suggesting 
that, rather than creating the new table, you use a view instead. For 
your purpose it can be treated as another table. However, it does not 
carry any maintenance overheads because its best thought of as being a 
virtual table that only exists when its queried.   


-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

#38457

On Friday, June 29, 2018 at 8:30:27 AM UTC-4, Martin Gregorie wrote:
> Yes, I'm not sure why I said 'parallel'. What I meant was that, if I 
> understood you correctly, you could do what you need by building a new 
> table and populating it by joining existing tables. All I was suggesting 
> that, rather than creating the new table, you use a view instead. For 
> your purpose it can be treated as another table. However, it does not 
> carry any maintenance overheads because its best thought of as being a 
> virtual table that only exists when its queried.   
> 

as in you can create a 'table' in a database as a view to a table in a different database?

[toc] | [prev] | [next] | [standalone]

#38458

On Fri, 29 Jun 2018 06:11:02 -0700, Eric Douglas wrote:

> On Friday, June 29, 2018 at 8:30:27 AM UTC-4, Martin Gregorie wrote:
>> Yes, I'm not sure why I said 'parallel'. What I meant was that, if I
>> understood you correctly, you could do what you need by building a new
>> table and populating it by joining existing tables. All I was
>> suggesting that, rather than creating the new table, you use a view
>> instead. For your purpose it can be treated as another table. However,
>> it does not carry any maintenance overheads because its best thought of
>> as being a virtual table that only exists when its queried.
>> 
>> 
> as in you can create a 'table' in a database as a view to a table in a
> different database?

As in 'Add a view to the existing database'. I've just checked a Mogwai-
generated ERD for my PostgreSQL-based mail archive database: this uses a 
view to determine whether I've sent mail to an the sender of a newly 
received e-mail - if I have the sender gets whitelisted. The ERD shows 
the view as unconnected to the tables it accesses, so its effectively a 
recipe for building a table with a selective join that can be queried as 
though its another table. IOW there are two ways of understanding what a 
view is (choose the one you prefer):

- the view  describes a virtual table with no physical existence
  that is only populated with data when its queried.

- the view is a predefined chunk of SQL that gets incorporated in any
  query that accesses it. The combined query uses the tables listed in
  the view after combining the view's 'where' clause with the
  'where' clause in the query accessing it.


-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

#38459

On 29.06.2018 15:43, Martin Gregorie wrote:
> On Fri, 29 Jun 2018 06:11:02 -0700, Eric Douglas wrote:
> 
>> On Friday, June 29, 2018 at 8:30:27 AM UTC-4, Martin Gregorie wrote:
>>> Yes, I'm not sure why I said 'parallel'. What I meant was that, if I
>>> understood you correctly, you could do what you need by building a new
>>> table and populating it by joining existing tables. All I was
>>> suggesting that, rather than creating the new table, you use a view
>>> instead. For your purpose it can be treated as another table. However,
>>> it does not carry any maintenance overheads because its best thought of
>>> as being a virtual table that only exists when its queried.
>>>
>>>
>> as in you can create a 'table' in a database as a view to a table in a
>> different database?
> 
> As in 'Add a view to the existing database'.

As far as I understood Eric he does not want to modify the existing 
database in _any_ way.  If you can create a view there then you can also 
add a table with the mapping against which you can join.

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[toc] | [prev] | [next] | [standalone]

#38461

On Fri, 29 Jun 2018 23:20:10 +0200, Robert Klemme wrote:

> On 29.06.2018 15:43, Martin Gregorie wrote:
>> On Fri, 29 Jun 2018 06:11:02 -0700, Eric Douglas wrote:
>> 
>>> On Friday, June 29, 2018 at 8:30:27 AM UTC-4, Martin Gregorie wrote:
>>>> Yes, I'm not sure why I said 'parallel'. What I meant was that, if I
>>>> understood you correctly, you could do what you need by building a
>>>> new table and populating it by joining existing tables. All I was
>>>> suggesting that, rather than creating the new table, you use a view
>>>> instead. For your purpose it can be treated as another table.
>>>> However, it does not carry any maintenance overheads because its best
>>>> thought of as being a virtual table that only exists when its
>>>> queried.
>>>>
>>>>
>>> as in you can create a 'table' in a database as a view to a table in a
>>> different database?
>> 
>> As in 'Add a view to the existing database'.
> 
> As far as I understood Eric he does not want to modify the existing
> database in _any_ way.  If you can create a view there then you can also
> add a table with the mapping against which you can join.
>
I know, but I think its worthwhile letting him know what a view is, its 
likely impact and how I understand it to work on at least one DB 
(PosgreSQL). At least it gives him more information to work with.

Although I haven't used SQLServer, I did do a fair bit of work 
programming and doing DBA stuff with Sybase, though I don't recall using 
views on it. SQL Server was forked from the Sybase RDBMS.


-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.java.programmer

csiph-web