mardi 21 avril 2015

Unable to update value in database using AND operator in postgresql

I'm working on a Java swing application in which I'm trying to update postgresql database when user make any changes. There are four fields in the tables, If i try to update one column at a time it's working fine but when I use AND then It display following error message to me

Caught Exception:- org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type character varying

Given below is code that I'm using to update table.

Connection con = DbConnection.getConnect();
Statement stmt = con.createStatement();
String sql = "UPDATE webservice_config set product = ? AND product_cat = ? AND stock = ? where ID=?;";
PreparedStatement pstInsert = con.prepareStatement(sql);
pstInsert.setString(1, product);
pstInsert.setString(2, productCat);
pstInsert.setString(3, stock);
pstInsert.setString(4, "1");
pstInsert.executeUpdate();
con.close();

Any Help

SQL union of two tables with different columns

I want to get one result set of rows back from two tables with different column names, one line per line in each table.

Result should look like this, blanks can be null, team_id in 2nd half comes from coach_id:

-----------------------------------------
player_id | team_id | score | improvement
-----------------------------------------
11          20         5
11          21         4
12          22         2
12          23         2
11          20                   5
11          21                   6
12          21                   5
13          23                   10

Here's the schema:

CREATE TABLE coaches
    (`id` int, `team_id` int)
;

INSERT INTO coaches
    (`id`, `team_id`)
VALUES
    (1, 20),
    (2, 21),
    (3, 22),
    (4, 23)
;

CREATE TABLE players
 (`id` int, `player_id` int);


INSERT INTO players
(`id`, `player_id`)
VALUES
(1,11),
(2,12),
(3,13),
(4,14)
;
CREATE TABLE games
    (`id` int, `player_id` int, `team_id` int, `score` int)
;

INSERT INTO games
    (`id`, `player_id`, `team_id`, `score`)
VALUES
    (1, 11, 20, 5),
    (2, 11, 21, 4),
    (3, 12, 22, 2),
    (4, 12, 23, 2)
;

CREATE TABLE sessions
    (`id` int, `player_id` int, `coach_id` int, `improvement` int)
;

INSERT INTO sessions
      (`id`, `player_id`, `coach_id`, `improvement`)
VALUES
    (1, 11, 1, 5),
    (2, 11, 2, 6),
    (3, 12, 2, 5),
    (4, 13, 4, 10)
;

Tried this, but it wasn't really close:

SELECT tweets.player_id
      ,tweets.team_id
      ,follows.coach_id 
FROM tweets FULL OUTER JOIN follows ON (1 = 0);

NullReferenceError with linq to postgressql

I have proyect that use linq to postgresql, I have the library and Its three dependencies.

But when I try to connect to de DB I get this error:

First chance of Exception of type ' System.NullReferenceException ' in linq2db.dll. Additional information: Object reference not set to an instance of an object. (Translated).

Stack trace:

in LinqToDB.Data.DataConnection..ctor(String configurationString) in i:\linq2db\Source\Data\DataConnection.cs:line 41 in LinqToDB.Data.DataConnection..ctor() in i:\linq2db\Source\Data\DataConnection.cs:line 24 in modelo.BDGestion.bdgestionDB..ctor() in d:\Proyectos\Proyectos VisualStudio\TimeSheets\TimeSheets\modelo\BDGestion.PostgreSQL.generated.cs:línea 44 en TimeSheets.SeleccionPersonal..ctor() in d:\Proyectos\Proyectos VisualStudio\TimeSheets\TimeSheets\SeleccionPersonal.cs:line 23

public partial class SeleccionPersonal : Form
    {
        public SeleccionPersonal()
        {
            InitializeComponent();
            try
            {
                using (var db = new bdgestionDB())
                {

                }
            }catch(Exception ex){
                MessageBox.Show(ex.ToString());
            }
        }
    }

I generated the classes using the template, the connection String is well formed and properly added to de connectionStrings. Checked it by me in this solution and by a friend in other computer by its own. If you need any information just ask.

Generated class:

public partial class bdgestionDB : LinqToDB.Data.DataConnection
{
    tables...

    public bdgestionDB()
    {
        InitDataContext();
    }

    public bdgestionDB(string configuration)
        : base(configuration)
    {
        InitDataContext();
    }

    partial void InitDataContext();
}

The App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <connectionStrings>
    <add name="Postgres" connectionString="Server=thot-test;Port=5432;Database=bdgestion;UserId=test;Password=T15Pmc;Pooling=true;MinPoolSize=10;MaxPoolSize=100;Protocol=3;"/>
  </connectionStrings>
</configuration>

Which query plan is faster/better

I have two query plans for different queries that return the same results I'm wondering if someone can tell me which one is "better", and why.

SELECT * 
FROM bids order by (select ranking from users where users.id = runner_id) DESC limit 1


"Limit  (cost=17.62..17.62 rows=1 width=568)"
"  ->  Sort  (cost=17.62..17.62 rows=2 width=568)"
"        Sort Key: ((SubPlan 1))"
"        ->  Seq Scan on bids  (cost=0.00..17.61 rows=2 width=568)"
"              SubPlan 1"
"                ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=4)"
"                      Index Cond: (id = bids.runner_id)"

Second statement and plan:

SELECT  "bids".* 
FROM "bids" inner join users u on bids.runner_id=u.id  ORDER BY u.ranking DESC LIMIT 1

"Limit  (cost=17.64..17.64 rows=1 width=572)"
"  ->  Sort  (cost=17.64..17.64 rows=2 width=572)"
"        Sort Key: u.ranking"
"        ->  Nested Loop  (cost=0.28..17.63 rows=2 width=572)"
"              ->  Seq Scan on bids  (cost=0.00..1.02 rows=2 width=568)"
"              ->  Index Scan using users_pkey on users u  (cost=0.28..8.29 rows=1 width=8)"
"                    Index Cond: (id = bids.runner_id)"

lundi 20 avril 2015

Multiple Rails projects with different databases for each

I'm new to Rails and after going through the Heroku tutorial I've set up a project using PostgresQL.

A friend also learning Rails followed a different tutorial which used SQLite as the database, which I've cloned.

When starting the rails server with the SQLite project, I'm continually getting an error suggesting that the project is trying to use PostgresQL and telling me to add gem pg to my Gemfile.

All I can seem to find online are various posts about migrating to PostgresQL from MySQL/SQLite/etc, but I am not looking to migrate.

My database.yml is as follows:

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
#
default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

The error reported in RubyMine is:

/usr/bin/ruby -e $stdout.sync=true;$stderr.sync=true;load($0=ARGV.shift) /Users/Ty/Rails/TSCLunch/web/bin/rails server -b 0.0.0.0 -p 3000 -e development
=> Booting WEBrick
=> Rails 4.2.0 application starting in development on http://0.0.0.0:3000
=> Run `rails server -h` for more startup options
=> Ctrl-C to shutdown server
Exiting
/Users/Ty/.gem/ruby/2.0.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/connection_specification.rb:177:in `rescue in spec': Specified 'postgresql' for database adapter, but the gem is not loaded. Add `gem 'pg'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord). (Gem::LoadError)
    from /Users/Ty/.gem/ruby/2.0.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/connection_specification.rb:174:in `spec'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activerecord-4.2.0/lib/active_record/connection_handling.rb:50:in `establish_connection'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activerecord-4.2.0/lib/active_record/railtie.rb:120:in `block (2 levels) in <class:Railtie>'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/lazy_load_hooks.rb:38:in `instance_eval'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/lazy_load_hooks.rb:38:in `execute_hook'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/lazy_load_hooks.rb:28:in `block in on_load'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/lazy_load_hooks.rb:27:in `each'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/lazy_load_hooks.rb:27:in `on_load'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activerecord-4.2.0/lib/active_record/railtie.rb:116:in `block in <class:Railtie>'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/initializable.rb:30:in `instance_exec'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/initializable.rb:30:in `run'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/initializable.rb:55:in `block in run_initializers'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:150:in `block in tsort_each'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:183:in `block (2 levels) in each_strongly_connected_component'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:219:in `each_strongly_connected_component_from'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:182:in `block in each_strongly_connected_component'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:180:in `each'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:180:in `each_strongly_connected_component'
    from /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/tsort.rb:148:in `tsort_each'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/initializable.rb:54:in `run_initializers'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/application.rb:352:in `initialize!'
    from /Users/Ty/Rails/TSCLunch/web/config/environment.rb:5:in `<top (required)>'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:274:in `require'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:274:in `block in require'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:240:in `load_dependency'
    from /Users/Ty/.gem/ruby/2.0.0/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:274:in `require'
    from /Users/Ty/Rails/TSCLunch/web/config.ru:3:in `block in <main>'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/builder.rb:55:in `instance_eval'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/builder.rb:55:in `initialize'
    from /Users/Ty/Rails/TSCLunch/web/config.ru:in `new'
    from /Users/Ty/Rails/TSCLunch/web/config.ru:in `<main>'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/builder.rb:49:in `eval'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/builder.rb:49:in `new_from_string'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/builder.rb:40:in `parse_file'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/server.rb:299:in `build_app_and_options_from_config'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/server.rb:208:in `app'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/server.rb:61:in `app'
    from /Users/Ty/.gem/ruby/2.0.0/gems/rack-1.6.0/lib/rack/server.rb:336:in `wrapped_app'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/server.rb:139:in `log_to_stdout'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/server.rb:78:in `start'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/commands_tasks.rb:80:in `block in server'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/commands_tasks.rb:75:in `tap'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/commands_tasks.rb:75:in `server'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands/commands_tasks.rb:39:in `run_command!'
    from /Users/Ty/.gem/ruby/2.0.0/gems/railties-4.2.0/lib/rails/commands.rb:17:in `<top (required)>'
    from /Users/Ty/Rails/TSCLunch/web/bin/rails:4:in `require'
    from /Users/Ty/Rails/TSCLunch/web/bin/rails:4:in `<top (required)>'
    from -e:1:in `load'
    from -e:1:in `<main>'

Process finished with exit code 1

Order by foreign key in activerecord: without a join?

I want to expand this question. order by foreign key in activerecord

I'm trying to order a set of records based on a value in a really large table. When I use join, it brings all the "other" records data into the objects.. As join should..

#table users  30+ columns
#table bids  5 columns
record = Bid.find(:all,:joins=>:users, :order=>'users.ranking DESC' ).first

Now record holds 35 fields..

Is there a way to do this without the join?

Here's my thinking..
With the join I get this query

SELECT * FROM "bids" 
left join users on runner_id = users.id  
ORDER BY ranking LIMIT 1

Now I can add a select to the code so I don't get the full user table, but putting a select in a scope is dangerous IMHO.

When I write sql by hand.

SELECT * FROM bids 
order by (select users.ranking from users where users.id = runner_id) DESC
limit 1

I believe this is a faster query, based on the "explain" it seems simpler.
More important than speed though is that the second method doesn't have the 30 extra fields.
If I build in a custom select inside the scope, it could explode other searches on the object if they too have custom selects (there can be only one)

Solr AutoCommit not working with Postgresql

I am using Solr 4.10.0 with PostgreSql 9.3. I am able to configure my solr core properly using data-config.xml and search through the database different tables. However, I am not able to setup the autoCommit feature. Whenever any row gets added in the table, I expect them to start appearing in the results after the maxTime (1 minute) but that doesn't happen. I have to explicitly rebuild the index by doing a full data-import and then everything works fine.

My solrconfig.xml is:

  <updateHandler class="solr.DirectUpdateHandler2">

     <autoCommit>
       <maxTime>60000</maxTime>
       <openSearcher>true</openSearcher>
     </autoCommit>

     <autoSoftCommit>
       <maxTime>${solr.autoSoftCommit.maxTime:-1}</maxTime>
     </autoSoftCommit>

  </updateHandler>

Is there something extra needs to be done for using autoCommit here? I checked my log files as well but there is no error / exception. What am I missing?