Search Results

Search found 63752 results on 2551 pages for 'create table'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 540/2551 | < Previous Page | 536 537 538 539 540 541 542 543 544 545 546 547  | Next Page >

  • Hibernate Auto-Increment Setup

    - by dharga
    How do I define an entity for the following table. I've got something that isn't working and I just want to see what I'm supposed to do. USE [BAMPI_TP_dev] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MemberSelectedOptions]( [OptionId] [int] NOT NULL, [SeqNo] [smallint] IDENTITY(1,1) NOT NULL, [OptionStatusCd] [char](1) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF This is what I have already that isn't working. @Entity @Table(schema="dbo", name="MemberSelectedOptions") public class MemberSelectedOption extends BampiEntity implements Serializable { @Embeddable public static class MSOPK implements Serializable { private static final long serialVersionUID = 1L; @Column(name="OptionId") int optionId; @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="SeqNo", unique=true, nullable=false) BigDecimal seqNo; //Getters and setters here... } private static final long serialVersionUID = 1L; @EmbeddedId MSOPK pk = new MSOPK(); @Column(name="OptionStatusCd") String optionStatusCd; //More Getters and setters here... } I get the following ST. [5/25/10 15:49:40:221 EDT] 0000003d JDBCException E org.slf4j.impl.JCLLoggerAdapter error Cannot insert explicit value for identity column in table 'MemberSelectedOptions' when IDENTITY_INSERT is set to OFF. [5/25/10 15:49:40:221 EDT] 0000003d AbstractFlush E org.slf4j.impl.JCLLoggerAdapter error Could not synchronize database state with session org.hibernate.exception.SQLGrammarException: could not insert: [com.bob.proj.ws.model.MemberSelectedOption] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2285) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2678) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028) at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:366) at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137) at com.bcbst.bamp.ws.dao.MemberSelectedOptionDAOImpl.saveMemberSelectedOption(MemberSelectedOptionDAOImpl.java:143) at com.bcbst.bamp.ws.common.AlertReminder.saveMemberSelectedOptions(AlertReminder.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

    Read the article

  • Integer Surrogate Key?

    - by CitadelCSAlum
    I need something real simple, that for some reason I am unable to accomplish to this point. I have also been unable to Google the answer surprisingly. I need to number the entries in different tables uniquely. I am aware of AUTO INCREMENT in MySQL and that it will be involved. My situation would be as follows If I had a table like Table EXAMPLE ID - INTEGER FIRST_NAME - VARCHAR(45) LAST_NAME - VARCHAR(45) PHONE - VARCHAR(45) CITY - VARCHAR(45) STATE - VARCHAR(45) ZIP - VARCHAR(45) This would be the setup for the table where ID is an integer that is auto-incremented every time an entry is inserted into the table. The thing I need is that I do not want to have to account for this field when inserting data into the database. From my understanding this would be a surrogate key, that I can tell the database to automatically increment and I do not have to include it in the INSERT STATEMENT so instead of INSERT INTO EXAMPLE VALUES (2,'JOHN','SMITH',333-333-3333,'NORTH POLE'.... I can leave out the first ID column and just write something like INSERT INTO EXAMPLE VALUES ('JOHN','SMITH'.....etc) Notice I Wouldnt have to define the ID column... I know this is a very common task to do, but for some reason I cant get to the bottom of it. I am using MySQL, just to clarify. Thanks alot

    Read the article

  • Mysql - Help me alter this search query to get desired results

    - by sandeepan-nath
    Following is a dump of the tables and data needed to answer understand the system:- The system consists of tutors and classes. The data in the table All_Tag_Relations stores tag relations for each tutor registered and each class created by a tutor. The tag relations are used for searching classes. CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`), KEY `tag_4` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Tags` (`id_tag`, `tag`) VALUES (1, 'Sandeepan'), (2, 'Nath'), (3, 'first'), (4, 'class'), (5, 'new'), (6, 'Bob'), (7, 'Cratchit'); CREATE TABLE IF NOT EXISTS `All_Tag_Relations` ( `id_tag` int(10) unsigned NOT NULL default '0', `id_tutor` int(10) default NULL, `id_wc` int(10) unsigned default NULL, KEY `All_Tag_Relations_FKIndex1` (`id_tag`), KEY `id_wc` (`id_wc`), KEY `id_tag` (`id_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `All_Tag_Relations` (`id_tag`, `id_tutor`, `id_wc`) VALUES (1, 1, NULL), (2, 1, NULL), (3, 1, 1), (4, 1, 1), (6, 2, NULL), (7, 2, NULL), (5, 2, 2), (4, 2, 2); Following is my query:- This query searches for "first class" (tag for first = 3 and for class = 4, in Tags table) and returns all those classes such that both the terms first and class are present in the class name. SELECT wtagrels.id_wc,SUM(DISTINCT( wtagrels.id_tag =3)) AS key_1_total_matches, SUM(DISTINCT( wtagrels.id_tag =4)) AS key_2_total_matches FROM all_tag_relations AS wtagrels WHERE ( wtagrels.id_tag =3 OR wtagrels.id_tag =4 ) GROUP BY wtagrels.id_wc HAVING key_1_total_matches = 1 AND key_2_total_matches = 1 LIMIT 0, 20 And it returns the class with id_wc = 1. But, I want the search to show all those classes such that all the search terms are present in the class name or its tutor name So that searching "Sandeepan class" (wtagrels.id_tag = 1,4) or "Sandeepan Nath" also returns the class with id_wc=1. And Searching. Searching "Bob First" should not return any classes. Please modify the above query or suggest a new query, if possible using MyIsam - fulltext search, but somehow help me get the result.

    Read the article

  • Dependecy Injection with Massive ORM: dynamic trouble

    - by Sergi Papaseit
    I've started working on an MVC 3 project that needs data from an enormous existing database. My first idea was to go ahead and use EF 4.1 and create a bunch of POCO's to represent the tables I need, but I'm starting to think the mapping will get overly complicated as I only need some of the columns in some of the tables. (thanks to Steven for the clarification in the comments. So I thought I'd give Massive ORM a try. I normally use a Unit of Work implementation so I can keep everything nicely decoupled and can use Dependency Injection. This is part of what I have for Massive: public interface ISession { DynamicModel CreateTable<T>() where T : DynamicModel, new(); dynamic Single<T>(string where, params object[] args) where T : DynamicModel, new(); dynamic Single<T>(object key, string columns = "*") where T : DynamicModel, new(); // Some more methods supported by Massive here } And here's my implementation of the above interface: public class MassiveSession : ISession { public DynamicModel CreateTable<T>() where T : DynamicModel, new() { return new T(); } public dynamic Single<T>(string where, params object[] args) where T: DynamicModel, new() { var table = CreateTable<T>(); return table.Single(where, args); } public dynamic Single<T>(object key, string columns = "*") where T: DynamicModel, new() { var table = CreateTable<T>(); return table.Single(key, columns); } } The problem comes with the First(), Last() and FindBy() methods. Massive is based around a dynamic object called DynamicModel and doesn't define any of the above method; it handles them through a TryInvokeMethod() implementation overriden from DynamicObject instead: public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result) { } I'm at a loss on how to "interface" those methods in my ISession. How could my ISession provide support for First(), Last() and FindBy()? Put it another way, how can I use all of Massive's capabilities and still be able to decouple my classes from data access?

    Read the article

  • sybase - fails to use index unless string is hard-coded

    - by Garrett
    I'm using Sybase 12.5.3 (ASE); I'm new to Sybase though I've worked with MSSQL pretty extensively. I'm running into a scenario where a stored procedure is really very slow. I've traced the issue to a single SELECT stmt for a relatively large table. Modifying that statement dramatically improves the performance of the procedure (and reverting it drastically slows it down; i.e., the SELECT stmt is definitely the culprit). -- Sybase optimizes and uses multi-column index... fast!<br> SELECT ID,status,dateTime FROM myTable WHERE status in ('NEW','SENT') ORDER BY ID -- Sybase does not use index and does very slow table scan<br> SELECT ID,status,dateTime FROM myTable WHERE status in (select status from allowableStatusValues) ORDER BY ID The code above is an adapted/simplified version of the actual code. Note that I've already tried recompiling the procedure, updating statistics, etc. I have no idea why Sybase ASE would choose an index only when strings are hard-coded and choose a table scan when choosing from another table. Someone please give me a clue, and thank you in advance.

    Read the article

  • C# Array of Dictionaries

    - by Nave
    I want to create an array of Dictionaries. But the array size is unknown. For integer, i used List to obtain the integer array of unknown size. But in the case of Dictionary, am not able to create a list of Dictionary. Is thr any wayz by which this can be done? Dictionary(int, String) paramList=null;I am want to create the array of paramList(above). I am using C Sharp.

    Read the article

  • How do I use accepts_nested_attributes_for? I cannot use the .build method (!)

    - by Angela
    Editing my question for conciseness and to update what I've done: How do I model having multiple Addresses for a Company and assign a single Address to a Contact, and be able to assign them when creating or editing a Contact? Here is my model for Contacts: class Contact < ActiveRecord::Base attr_accessible :first_name, :last_name, :title, :phone, :fax, :email, :company, :date_entered, :campaign_id, :company_name, :address_id, :address_attributes belongs_to :company belongs_to :address accepts_nested_attributes_for :address end Here is my model for Address: class Address < ActiveRecord::Base attr_accessible :street1, :street2, :city, :state, :zip has_many :contacts end I would like, when creating an new contact, access all the Addresses that belong to the other Contacts that belong to the Company. So here is how I represent Company: class Company < ActiveRecord::Base attr_accessible :name, :phone, :addresses has_many :contacts has_many :addresses, :through => :contacts end Here is how I am trying to create a field in the View for _form for Contact so that, when someone creates a new Contact, they pass the address to the Address model and associate that address to the Contact: <% f.fields_for :address, @contact.address do |builder| %> <p> <%= builder.label :street1, "Street 1" %> </br> <%= builder.text_field :street1 %> <p> <% end %> When I try to Edit, the field for Street 1 is blank. And I don't know how to display the value from show.html.erb. At the bottom is my error console -- can't seem to create values in the address table: My Contacts controller is as follows: def new @contact = Contact.new @contact.address.build # I GET AN ERROR HERE: says NIL CLASS @contact.date_entered = Date.today @campaigns = Campaign.find(:all, :order => "name") if params[:campaign_id].blank? else @campaign = Campaign.find(params[:campaign_id]) @contact.campaign_id = @campaign.id end if params[:company_id].blank? else @company = Company.find(params[:company_id]) @contact.company_name = @company.name end end def create @contact = Contact.new(params[:contact]) if @contact.save flash[:notice] = "Successfully created contact." redirect_to @contact else render :action => 'new' end end def edit @contact = Contact.find(params[:id]) @campaigns = Campaign.find(:all, :order => "name") end Here is a snippet of my error console: I am POSTING the attribute, but it is not CREATING in the Address table.... Processing ContactsController#create (for 127.0.0.1 at 2010-05-12 21:16:17) [POST] Parameters: {"commit"="Submit", "authenticity_token"="d8/gx0zy0Vgg6ghfcbAYL0YtGjYIUC2b1aG+dDKjuSs=", "contact"={"company_name"="Allyforce", "title"="", "campaign_id"="2", "address_attributes"={"street1"="abc"}, "fax"="", "phone"="", "last_name"="", "date_entered"="2010-05-12", "email"="", "first_name"="abc"}} Company Load (0.0ms)[0m [0mSELECT * FROM "companies" WHERE ("companies"."name" = 'Allyforce') LIMIT 1[0m Address Create (16.0ms)[0m [0;1mINSERT INTO "addresses" ("city", "zip", "created_at", "street1", "updated_at", "street2", "state") VALUES(NULL, NULL, '2010-05-13 04:16:18', NULL, '2010-05-13 04:16:18', NULL, NULL)[0m Contact Create (0.0ms)[0m [0mINSERT INTO "contacts" ("company", "created_at", "title", "updated_at", "campaign_id", "address_id", "last_name", "phone", "fax", "company_id", "date_entered", "first_name", "email") VALUES(NULL, '2010-05-13 04:16:18', '', '2010-05-13 04:16:18', 2, 2, '', '', '', 5, '2010-05-12', 'abc', '')[0m

    Read the article

  • How to turn off auto_increment in Rails Active Record

    - by Darth
    Is it possible to create primary key without auto_increment flag in ActiveRecord? I can't do create table :blah, :id => false because I want to have primary key index on the column. I looked up documentation but didn't find anything useful. Is it possible to create primary key without auto_increment?

    Read the article

  • MySQL ORDER BY DESC is fast but ASC is very slow

    - by Pepper
    Hello, I'm completely stumped on this one. For some reason when I sort this query by DESC it's super fast, but if sorted by ASC it's extremely slow. This takes about 150 milliseconds: SELECT posts.id FROM posts USE INDEX (published) WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 ) ORDER BY posts.published DESC LIMIT 0, 50; This takes about 32 seconds: SELECT posts.id FROM posts USE INDEX (published) WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 ) ORDER BY posts.published ASC LIMIT 0, 50; The EXPLAIN is the same for both queries. id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE posts index NULL published 5 NULL 50 Using where I've tracked it down to "USE INDEX (published)". If I take that out it's the same performance both ways. But the EXPLAIN shows the query is less efficient overall. id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE posts range feed_id feed_id 4 \N 759 Using where; Using filesort And here's the table. CREATE TABLE `posts` ( `id` int(20) NOT NULL AUTO_INCREMENT, `feed_id` int(11) NOT NULL, `post_url` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `content` blob, `author` varchar(255) DEFAULT NULL, `published` int(12) DEFAULT NULL, `updated` datetime NOT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `post_url` (`post_url`,`feed_id`), KEY `feed_id` (`feed_id`), KEY `published` (`published`) ) ENGINE=InnoDB AUTO_INCREMENT=196530 DEFAULT CHARSET=latin1; Is there a fix for this? Thanks!

    Read the article

  • mySQL Efficiency Issue - How to find the right balance of normalization...?

    - by Foo
    I'm fairly new to working with relational databases, but have read a few books and know the basics of good design. I'm facing a design decision, and I'm not sure how to continue. Here's a very over simplified version of what I'm building: People can rate photos 1-5, and I need to display the average votes on the picture while keeping track of the individual votes. For example, 12 people voted 1, 7 people voted 2, etc. etc. The normalization freak of me initially designed the table structure like this: Table pictures id* | picture | userID | Table ratings id* | pictureID | userID | rating With all the foreign key constraints and everything set as they shoudl be. Every time someone rates a picture, I just insert a new record into ratings and be done with it. To find the average rating of a picture, I'd just run something like this: SELECT AVG(rating) FROM ratings WHERE pictureID = '5' GROUP by pictureID Having it setup this way lets me run my fancy statistics to. I can easily find who rated a certain picture a 3, and what not. Now I'm thinking if there's a crapload of ratings (which is very possible in what I'm really designing), finding the average will became very expensive and painful. Using a non-normalized version would seem to be more efficient. e.g.: Table picture id | picture | userID | ratingOne | ratingTwo | ratingThree | ratingFour | ratingFive To calculate the average, I'd just have to select a single row. It seems so much more efficient, but so much more uglier. Can someone point me in the right direction of what to do? My initial research shows that I have to "find the right balance", but how do I go about finding that balance? Any articles or additional reading information would be appreciated as well. Thanks.

    Read the article

  • Optimizing MySql query to avoid using "Using filesort"

    - by usef_ksa
    I need your help to optimize the query to avoid using "Using filesort".The job of the query is to select all the articles that belongs to specific tag. The query is: "select title from tag,article where tag='Riyad' AND tag.article_id=article.id order by tag.article_id". the tables structure are the following: Tag table CREATE TABLE `tag` ( `tag` VARCHAR( 30 ) NOT NULL , `article_id` INT NOT NULL , INDEX ( `tag` ) ) ENGINE = MYISAM ; Article table CREATE TABLE `article` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 60 ) NOT NULL ) ENGINE = MYISAM Sample data INSERT INTO `article` VALUES (1, 'About Riyad'); INSERT INTO `article` VALUES (2, 'About Newyork'); INSERT INTO `article` VALUES (3, 'About Paris'); INSERT INTO `article` VALUES (4, 'About London'); INSERT INTO `tag` VALUES ('Riyad', 1); INSERT INTO `tag` VALUES ('Saudia', 1); INSERT INTO `tag` VALUES ('Newyork', 2); INSERT INTO `tag` VALUES ('USA', 2); INSERT INTO `tag` VALUES ('Paris', 3); INSERT INTO `tag` VALUES ('France', 3);

    Read the article

  • SQL Server: Output an XML field as tabular data using a stored procedure

    - by Pawan
    I am using a table with an XML data field to store the audit trails of all other tables in the database. That means the same XML field has various XML information. For example my table has two records with XML data like this: 1st record: <client> <name>xyz</name> <ssn>432-54-4231</ssn> </client> 2nd record: <emp> <name>abc</name> <sal>5000</sal> </emp> These are the two sample formats and just two records. The table actually has many more XML formats in the same field and many records in each format. Now my problem is that upon query I need these XML formats to be converted into tabular result sets. What are the options for me? It would be a regular task to query this table and generate reports from it. I want to create a stored procedure to which I can pass that I need to query "<emp>" or "<client>", then my stored procedure should return tabular data.

    Read the article

  • PostgreSQL insert on primary key failing with contention, even at serializable level

    - by Steven Schlansker
    I'm trying to insert or update data in a PostgreSQL db. The simplest case is a key-value pairing (the actual data is more complicated, but this is the smallest clear example) When you set a value, I'd like it to insert if the key is not there, otherwise update. Sadly Postgres does not have an insert or update statement, so I have to emulate it myself. I've been working with the idea of basically SELECTing whether the key exists, and then running the appropriate INSERT or UPDATE. Now clearly this needs to be be in a transaction or all manner of bad things could happen. However, this is not working exactly how I'd like it to - I understand that there are limitations to serializable transactions, but I'm not sure how to work around this one. Here's the situation - ab: => set transaction isolation level serializable; a: => select count(1) from table where id=1; --> 0 b: => select count(1) from table where id=1; --> 0 a: => insert into table values(1); --> 1 b: => insert into table values(1); --> ERROR: duplicate key value violates unique constraint "serial_test_pkey" Now I would expect it to throw the usual "couldn't commit due to concurrent update" but I'm guessing since the inserts are different "rows" this does not happen. Is there an easy way to work around this?

    Read the article

  • Can I combine two functions into one using Javascript?

    - by Melissa
    I have the following code that I would like to simplify. With javascript and jQuery is there an easy way that I could combine these two functions? Most of the code is the same but I am not sure how I could create a single function that works differently depending on what is clicked. $(document).ready(function () { $('#ListBooks').click(ListBooks); $('#Create').click(Create); }); function Create() { var dataSourceID = $('#DataSourceID').val(); var subjectID = $('#SubjectID').val(); var contentID = $('#ContentID').val(); if (dataSourceID && dataSourceID != '00' && subjectID && subjectID != "00" && contentID && contentID != "00") { var e = encodeURIComponent, arr = ["dataSourceID=" + e(dataSourceID), "subjectID=" + e(subjectID), "contentID=" + e(contentID)]; window.location.href = '/Administration/Books/Create?' + arr.join("&"); } else { alert('Datasource, Subject and Content must be selected.'); } return false; } function ListBooks() { var dataSourceID = $('#DataSourceID').val(); var subjectID = $('#SubjectID').val(); var contentID = $('#ContentID').val(); if (dataSourceID && dataSourceID != '00' && subjectID && subjectID != "00" && contentID && contentID != "00") { var e = encodeURIComponent, arr = ["dataSourceID=" + e(dataSourceID), "subjectID=" + e(subjectID), "contentID=" + e(contentID)]; window.location.href = '/Administration/Books/ListBooks?' + arr.join("&"); } else { alert('Datasource, Subject and Content must be selected.'); } return false; }

    Read the article

  • GridView: How can I get rid of extra space from my GirdView object?

    - by Lajos Arpad
    Hello, I'm writing an application for Android phones for Human vs. Human chess play over the internet. I was looking at some tutorials, to learn how to develop Android applications and found a very nice example of making galleries (it was a GridView usage example for making a gallery about dogs) and the idea came to draw the chess table using a GridView, because the example project also handled the point & click event and I intended to use the same event in the same way, but for a different purpose. The game works well (currently it's a hotseat version), however, I'm really frustrated by the fact that whenever I rotate the screen of the phone, my GridView gets hysterical and puts some empty space in my chess table between the columns. I realized that the cause of this is that the GridView's width is the same as its parent's and the GridView tries to fill its parent in with, but there should (and probably is) be a simple solution to get rid of this problem. However, after a full day of researching, I haven't found any clue to help me to make a perfect drawing about my chess table without a negative side effect in functionality. The chess table looks fine if the phone is in Portrait mode, but in Landscape mode it's far from nice. This is how I can decide whether we are in Portrait or Landscape mode: ((((MainActivity)mContext).getWindow().getWindowManager().getDefaultDisplay().getWidth()) < ((MainActivity)mContext).getWindow().getWindowManager().getDefaultDisplay().getHeight()) In the main.xml file the GridView is defined in the following way: <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" > <GridView xmlns:android="http://schemas.android.com/apk/res/android" android:id="@+id/gridview" android:layout_width="wrap_content" android:layout_height="wrap_content" android:numColumns="8" android:verticalSpacing="0dp" android:horizontalSpacing="0dp" android:stretchMode="columnWidth" android:gravity="center" > </GridView> ... </LinearLayout> I appreciate any help with the problem and thank you for reading this.

    Read the article

  • SQL Server Index cost

    - by yellowstar
    I have read that one of the tradeoffs for adding table indexes in SQL Server is the increased cost of insert/update/delete queries to benefit the performance of select queries. I can conceptually understand what happens in the case of an insert because SQL Server has to write entries into each index matching the new rows, but update and delete are a little more murky to me because I can't quite wrap my head around what the database engine has to do. Let's take DELETE as an example and assume I have the following schema (pardon the pseudo-SQL) TABLE Foo col1 int ,col2 int ,col3 int ,col4 int PRIMARY KEY (col1,col2) INDEX IX_1 col3 INCLUDE col4 Now, if I issue the statement DELETE FROM Foo WHERE col1=12 AND col2 > 34 I understand what the engine must do to update the table (or clustered index if you prefer). The index is set up to make it easy to find the range of rows to be removed and do so. However, at this point it also needs to update IX_1 and the query that I gave it gives no obvious efficient way for the database engine to find the rows to update. Is it forced to do a full index scan at this point? Does the engine read the rows from the clustered index first and generate a smarter internal delete against the index? It might help me to wrap my head around this if I understood better what is going on under the hood, but I guess my real question is this. I have a database that is spending a significant amount of time in delete and I'm trying to figure out what I can do about it. When I display the execution plan for the deletion, it just shows an entry for "Clustered Index Delete" on table Foo which lists in the details section the other indices that need to be updated but I don't get any indication of the relative cost of these other indices. Are they all equal in this case? Is there some way that I can estimate the impact of removing one or more of these indices without having to actually try it?

    Read the article

  • Model login constraints based on time

    - by DaDaDom
    Good morning, for an existing web application I need to implement "time based login constraints". It means that for each user, later maybe each group, I can define timeslots when they are (not) allowed to log in into the system. As all data for the application is stored in database tables, I need to somehow create a way to model this idea in that way. My first approach, I will try to explain it here: Create a tree of login constraints (called "timeslots") with the main "categories", like "workday", "weekend", "public holiday", etc. on the top level, which are in a "sorted" order (meaning "public holiday" has a higher priority than "weekday") for each top level node create subnodes, which have a finer timespan, like "monday", "tuesday", ... below that, create an "hour" level: 0, 1, 2, ..., 23. No further details are necessary. set every member to "allowed" by default For every member of the system create a 1:n relationship member:timeslots which defines constraints, e.g. a member A may have A:monday-forbidden and A:tuesday-forbidden Do a depth-first search at every login and check if the member has a constraint. Why a depth first search? Well, I thought that it may be that a member has the rules: A:monday->forbidden, A:monday-10->allowed, A:mondey-11->allowed So a login on monday at 12:30 would fail, but one at 10:30 succeed. For performance reasons I could break the relational database paradigm and set a flag for every entry in the member-to-timeslots-table which is set to true if the member has information set for "finer" timeslots, but that's a second step. Is this model in principle a good idea? Are there existing models? Thanks.

    Read the article

  • nested insert exec work around

    - by stackoverflowuser
    i have 2 stored procedures usp_SP1 and usp_SP2. Both of them make use of insert into #tt exec sp_somesp. I wanted to created a 3rd stored procedure which will decide which stored proc to call. something like create proc usp_Decision ( @value int ) as begin if (@value = 1) exec usp_SP1 -- this proc already has insert into #tt exec usp_somestoredproc else exec usp_SP2 -- this proc too has insert into #tt exec usp_somestoredproc end Later realized I needed some structure defined for the return value from usp_Decision so that i can populate the SSRS dataset field. So here is what i tried: within usp_Decision created a temp table and tried to do "insert into #tt exec usp_SP1". Didn't work out. error "insert exec cannot be nested" within usp_Decision tried passing table variable to each of the stored proc and update the table within the stored procs and do "select * from ". That didnt work out as well. Table variable passed as parameter cannot be modified within the stored proc. Pls. suggest what can de done?

    Read the article

  • How can I extend this SQL query to find the k nearest neighbors?

    - by Smigs
    I have a database full of two-dimensional data - points on a map. Each record has a field of the geometry type. What I need to be able to do is pass a point to a stored procedure which returns the k nearest points (k would also be passed to the sproc, but that's easy). I've found a query at http://blogs.msdn.com/isaac/archive/2008/10/23/nearest-neighbors.aspx which gets the single nearest neighbour, but I can't figure how to extend it to find the k nearest neighbours. This is the current query - T is the table, g is the geometry field, @x is the point to search around, Numbers is a table with integers 1 to n: DECLARE @start FLOAT = 1000; WITH NearestPoints AS ( SELECT TOP(1) WITH TIES *, T.g.STDistance(@x) AS dist FROM Numbers JOIN T WITH(INDEX(spatial_index)) ON T.g.STDistance(@x) < @start*POWER(2,Numbers.n) ORDER BY n ) SELECT TOP(1) * FROM NearestPoints ORDER BY n, dist The inner query selects the nearest non-empty region and the outer query then selects the top result from that region; the outer query can easily be changed to (e.g.) SELECT TOP(20), but if the nearest region only contains one result, you're stuck with that. I figure I probably need to recursively search for the first region containing k records, but without using a table variable (which would cause maintenance problems as you have to create the table structure and it's liable to change - there're lots of fields), I can't see how.

    Read the article

  • Full Text Index type column is empty

    - by RemotecUk
    I am trying to create an index on a VarBinary(max) field in my SQL Server 2008 database. The steps I am taking are as follows: Table: dbo.Records Right click on table and select "Full Text Index" Then select "Define Index..." I choose the primary key which is the PK of my table (field name Id, type UniqueIndentifier). I then get the screen with the options Available Columns, Language for Word Breaker and Type Column I select my VarBinary(max) field called Chart as the Available Column by ticking the box. I select "English" as the Language for Word Breaker field. Then... I try to select the Type Column but there are no entries in here. I cannot proceed by clicking "Next" until this column is populated. Why are there no entries in this column for selection and what should be in there? Note 1: The VarBinary(max) field is linked to a file group if that makes any difference. Note 2: Also noticed that in the table designer I cannot set the full text option on that same field to "Yes" - its permanently stuck on "No". Thanks.

    Read the article

  • Why does Sql Server recommends creating an index when it already exist?

    - by Pierre-Alain Vigeant
    I ran a very basic query against one of our table and I noticed that the execution plan query processor is recommending that we create an index on a column The query is SELECT SUM(DATALENGTH(Data)) FROM Item WHERE Namespace = 'http://some_url/some_namespace/' After running, I get the following message // The Query Processor estimates that implementing the following index could improve the query cost by 96.7211%. CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Item] ([Namespace]) My problem is that I already have such index on that column: CREATE NONCLUSTERED INDEX [IX_ItemNamespace] ON [dbo].[Item] ( [Namespace] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] Why is Sql Server recommending me to create such index when it already exist?

    Read the article

  • using gsub to modify output of xtable command

    - by stevejb
    Hello, my.mat <- cbind(1:5, rnorm(5), 6:10, rnorm(5)) colnames(my.mat) <- c("Turn", "Draw","Turn", "Draw") print(xtable(my.mat)) yields \begin{table}[ht] \begin{center} \begin{tabular}{rrrrr} \hline & Turn & Draw & Turn & Draw \\ \hline 1 & 1.00 & -0.72 & 6.00 & 0.91 \\ 2 & 2.00 & 0.57 & 7.00 & 0.56 \\ 3 & 3.00 & 1.08 & 8.00 & 0.55 \\ 4 & 4.00 & 0.95 & 9.00 & 0.46 \\ 5 & 5.00 & 1.94 & 10.00 & 1.06 \\ \hline \end{tabular} \end{center} \end{table} I want to filter out the \begin{table} and \end{table} lines. I can do this using gsub, but how to I get the results of print(xtable(... into a variable? Thanks for the help Stack Overflow R community!

    Read the article

  • Phonegap/Cordova geolocation not working on Android

    - by Kreeki
    I'm having a trouble to get Geolocation working on Android in both emulator (even when I geo fix over telnet) and on device. Works on iOS, WP8 and in the browser. When I ask device for location using the following code, I always get an error (in my case custom Retrieving your position failed for unknown reason. with null both error code and error message). Related code: successHandler = (position) -> resolve App.Location.create lat: position.coords.latitude lng: position.coords.longitude errorHandler = (error) -> error = switch error.code when 1 App.LocationError.create message: 'You haven\'t shared your location.' when 2 App.LocationError.create message: 'Couldn\'t detect your current location.' when 3 App.LocationError.create message: 'Retrieving your position timeouted.' else App.LocationError.create message: 'Retrieving your position failed for unknown reason. Error code: ' + error.code + '. Error message: ' + error.message reject(error) options = maximumAge: Infinity # I also tried with 0 timeout: 60000 enableHighAccuracy: true navigator.geolocation.getCurrentPosition(successHandler, errorHandler, options) platforms/android/AndroidManifest.xml <uses-permission android:name="android.permission.ACCESS_COARSE_LOCATION" /> <uses-permission android:name="android.permission.ACCESS_FINE_LOCATION" /> <uses-permission android:name="android.permission.ACCESS_LOCATION_EXTRA_COMMANDS" /> www/config.xml (just in case) <feature name="Geolocation"> <param name="android-package" value="org.apache.cordova.GeoBroker" /> </feature> Using Cordova 3.1.0. Testing on Android 4.2. Plugin installed. Cordova.js included in index.html (other plugins like InAppBrowser are working fine). $ cordova plugins ls [ 'org.apache.cordova.console', 'org.apache.cordova.device', 'org.apache.cordova.dialogs', 'org.apache.cordova.geolocation', 'org.apache.cordova.inappbrowser', 'org.apache.cordova.vibration' ] I'm clueless. Am I missing something?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 536 537 538 539 540 541 542 543 544 545 546 547  | Next Page >