Search Results

Search found 27530 results on 1102 pages for 'sql truncate'.

Page 650/1102 | < Previous Page | 646 647 648 649 650 651 652 653 654 655 656 657  | Next Page >

  • Access is re-writing - and breaking - my query!

    - by FrustratedWithFormsDesigner
    I have a query in MS Access (2003) that makes use of a subquery. The subquery part looks like this: ...FROM (SELECT id, dt, details FROM all_recs WHERE def_cd="ABC-00123") AS q1,... And when I switch to Table View to verify the results, all is OK. Then, I wanted the result of this query to be printed on the page header for a report (the query returns a single row that is page-header stuff). I get an error because the query is suddenly re-written as: ...FROM [SELECT id, dt, details FROM all_recs WHERE def_cd="ABC-00123"; ] AS q1,... So it's Ok that the round brackets are automatically replaced by square brackets, Access feels it needs to do that, fine! But why is it adding the ; into the subquery, which causes it to fail? I suppose I could just create new query objects for these subqueries, but it seems a little silly that I should have to do that.

    Read the article

  • How to create a better tables Structure.

    - by user160820
    For my website i have tables Category :: id | name Product :: id | name | categoryid Now each category may have different sizes, for that I have also created a table Size :: id | name | categoryid | price Now the problem is that each category has also different ingredients that customer can choose to add to his purchased product. And these ingredients have different prices for different sizes. For that I also have a table like Ingredient :: id | name | sizeid | categoryid | price I am not sure if this Structure really normalized is. Can someone please help me to optimize this structure and which indexed do i need for this Structure?

    Read the article

  • PHP Security checklist (injection, sessions etc)

    - by NoviceCoding
    So what kind of things should a person using PHP and MySql be focused on to maximize security. Things I have done: -mysql_real_escape_string all inputs -validate all inputs after escaping em -Placed random alpha numerics before my table names -50character salt + Ripemd passwords Heres where I think I am slacking: -I know know nothing about sessions and securing them. How unsafe/safe is it if all you are doing is: session_start(); $_SESSION['login']= $login; and checking it with: session_start(); if(isset($_SESSION['login'])){ -I heard something about other forms of injection like cross site injection and what not... -And probably many other things I dont know about. Is there a "checklist"/Quicktut on making php secure? I dont even know what I should be worried about.I kinda regret now not building off cakephp since I am not a pro.

    Read the article

  • Having different database sorting order (default_scope) for two different views

    - by Juniper747
    In my model (pins.rb), I have two sorting orders: default_scope order: 'pins.featured DESC' #for adding featured posts to the top of a list default_scope order: 'pins.created_at DESC' #for adding the remaining posts beneath the featured posts This sorting order (above) is how I want my 'pins view' (index.html.erb) to look. Which is just a list of ALL user posts. In my 'users view' (show.html.erb) I am using the same model (pins.rb) to list only current_user pins. HOWEVER, I want to sorting order to ignore the "featured" default scope and only use the second scope: default_scope order: 'pins.created_at DESC' How can I accomplish this? I tried doing something like this: default_scope order: 'pins.featured DESC', only: :index default_scope order: 'pins.created_at DESC' But that didn't fly... UPDATE I updated my model to define a scope: scope :featy, order: 'pins.featured DESC' default_scope order: 'pins.created_at DESC' And updated my pins view to: <%= render @pins.featy %> However, now when I open my pins view, I get the error: undefined method `featy' for #<Array:0x00000100ddbc78> UPDATE 2 User.rb class User < ActiveRecord::Base attr_accessible :name, :email, :username, :password, :password_confirmation, :avatar, :password_reset_token, :password_reset_sent_at has_secure_password has_many :pins, dependent: :destroy #destroys user posts when user is destroyed # has_many :featured_pins, order: 'featured DESC', class_name: "Pin", source: :pin has_attached_file :avatar, :styles => { :medium => "300x300#", :thumb => "120x120#" } before_save { |user| user.email = user.email.downcase } before_save { |user| user.username = user.username.downcase } before_save :create_remember_token before_save :capitalize_name validates :name, presence: true, length: { maximum: 50 } VALID_EMAIL_REGEX = /\A[\w+\-.]+@[a-z\d\-.]+\.[a-z]+\z/i VALID_USERNAME_REGEX = /^[A-Za-z0-9]+(?:[_][A-Za-z0-9]+)*$/ validates :email, presence: true, format: { with: VALID_EMAIL_REGEX }, uniqueness: { case_sensitive: false } validates :username, presence: true, format: { with: VALID_USERNAME_REGEX }, uniqueness: { case_sensitive: false } validates :password, length: { minimum: 6 }, on: :create #on create, because was causing erros on pw_reset Pin.rb class Pin < ActiveRecord::Base attr_accessible :content, :title, :privacy, :date, :dark, :bright, :fragmented, :hashtag, :emotion, :user_id, :imagesource, :imageowner, :featured belongs_to :user before_save :capitalize_title before_validation :generate_slug validates :content, presence: true, length: { maximum: 8000 } validates :title, presence: true, length: { maximum: 24 } validates :imagesource, presence: { message: "Please search and choose an image" }, length: { maximum: 255 } validates_inclusion_of :privacy, :in => [true, false] validates :slug, uniqueness: true, presence: true, exclusion: {in: %w[signup signin signout home info privacy]} # for sorting featured and newest posts first default_scope order: 'pins.created_at DESC' scope :featured_order, order: 'pins.featured DESC' def to_param slug # or "#{id}-#{name}".parameterize end def generate_slug # makes the url slug address bar freindly self.slug ||= loop do random_token = Digest::MD5.hexdigest(Time.zone.now.to_s + title)[0..9]+"-"+"#{title}".parameterize break random_token unless Pin.where(slug: random_token).exists? end end protected def capitalize_title self.title = title.split.map(&:capitalize).join(' ') end end users_controller.rb class UsersController < ApplicationController before_filter :signed_in_user, only: [:edit, :update, :show] before_filter :correct_user, only: [:edit, :update, :show] before_filter :admin_user, only: :destroy def index if !current_user.admin? redirect_to root_path end end def menu @user = current_user end def show @user = User.find(params[:id]) @pins = @user.pins current_user.touch(:last_log_in) #sets the last log in time if [email protected]? render 'pages/info/' end end def new @user = User.new end pins_controller.rb class PinsController < ApplicationController before_filter :signed_in_user, except: [:show] # GET /pins, GET /pins.json def index #Live Feed @pins = Pin.all @featured_pins = Pin.featured_order respond_to do |format| format.html # index.html.erb format.json { render json: @pins } end end # GET /pins, GET /pins.json def show #single Pin View @pin = Pin.find_by_slug!(params[:id]) require 'uri' #this gets the photo's id from the stored uri @image_id = URI(@pin.imagesource).path.split('/').second if @pin.privacy == true #check for private pins if signed_in? if @pin.user_id == current_user.id respond_to do |format| format.html # show.html.erb format.json { render json: @pin } end else redirect_to home_path, notice: "Prohibited 1" end else redirect_to home_path, notice: "Prohibited 2" end else respond_to do |format| format.html # show.html.erb format.json { render json: @pin } end end end # GET /pins, GET /pins.json def new @pin = current_user.pins.new respond_to do |format| format.html # new.html.erb format.json { render json: @pin } end end # GET /pins/1/edit def edit @pin = current_user.pins.find_by_slug!(params[:id]) end Finally, on my index.html.erb I have: <%= render @featured_pins %>

    Read the article

  • MySQL whats wrong with my foreign keys?

    - by Skiy
    Hello, what is wrong with the two foreign keys which I have marked with comments? create database db; use db; create table Flug( Flugbez varchar(20), FDatum Date, Ziel varchar(20), Flugzeit int, Entfernung int, Primary Key (Flugbez, FDatum)); create table Flugzeugtyp( Typ varchar(20), Hersteller varchar(20), SitzAnzahl int, Reisegeschw int, primary key (Typ) ); create table flugzeug( Typ varchar(20), SerienNr int, AnschDatum Date, FlugStd int, primary key(Typ,SerienNr), foreign key(Typ) references Flugzeugtyp(Typ)); create table Abflug( Flugbez varchar(20), FDatum Date, Typ varchar(20), Seriennr int, Kaptaen varchar(20), Primary key(Flugbez,FDatum,Typ,SerienNr), Foreign key(Flugbez) references Flug(Flugbez), -- Foreign key(FDatum) references Flug(FDatum), Foreign key(Typ) references Flugzeugtyp(Typ) -- ,Foreign key(SerienNr) references Flugzeug(SerienNr) ); When I uncomment these, I get: ERROR 1005 (HY000): Can't create table 'db.abflug' (errno: 150)

    Read the article

  • Removing certain characters in all rows that match a regex?

    - by user001
    I'd like to change {foo, {bar}, foobar} to {foo, bar, foobar} in all rows that match '{.*{'. I.e. remove all curly braces { and } except the outer most pair. So doing mysql -h $H -u $U -p$P $DB -B -e "SELECT id FROM t WHERE col REGEXP '{.*{'" > bad.txt selects all the rows that will need this substitution. How do I make this substitution very quickly? EDIT: Could I do it by update table set column = REPLACE(column,'{',''); Then restore the out most pair update table set column = REPLACE(column,'^','{'); update table set column = REPLACE(column,'$','}');

    Read the article

  • MySQL: Select pages that are not tagged?

    - by lauthiamkok
    Hi, I have a db with two tables like these below, page table pg_id title 1 a 2 b 3 c 4 d tagged table tagged_id pg_id 1 1 2 4 I want to select the pages which are tagged, I tried with this query below but doesn't work, SELECT * FROM root_pages LEFT JOIN root_tagged ON ( root_tagged.pg_id = root_pages.pg_id ) WHERE root_pages.pg_id != root_tagged.pg_id It returns zero - Showing rows 0 - 1 (2 total, Query took 0.0021 sec) But I want it to return pg_id title 2 b 3 c My query must have been wrong?? How can I return the pages which are not tagged correctly? Thanks.

    Read the article

  • How to limit select items with L2E/S?

    - by orlon
    This code is a no-go var errors = (from error in db.ELMAH_Error select new { error.Application, error.Host, error.Type, error.Source, error.Message, error.User, error.StatusCode, error.TimeUtc }).ToList(); return View(errors); as it results in a 'requires a model of type IEnumerable' error. The following code of course works fine, but selects all the columns, some of which I'm simply not interested in: var errors = (from error in db.ELMAH_Error select error).ToList(); return View(errors); I'm brand spanking new to MVC2 + L2E, so maybe I'm just not thinking in the right mindset yet, but this seems counter-intuitive. Is there an easy way to select a limited number of columns, or is this just part of using an ORM?

    Read the article

  • fastest way to copy a table in mysql

    - by Anon
    which one is the fastest way to completely copy a table on mysql ? CREATE TABLE copy LIKE original; INSERT INTO copy SELECT * FROM original; or CREATE TABLE copy SELECT * FROM original; ALTER TABLE copy ADD PRIMARY KEY (id); or theres another way ? PS. can't use command-line tools like mysqldump, must be on-the-fly.

    Read the article

  • MySQL: selecting totals as three fields from same table as one query?

    - by coderama
    I have a table with various orders in it: ID | Date | etc... 1 | 2013-01-01 | etc 2 | 2013-02-01 | etc 3 | 2013-03-01 | etc 4 | 2013-04-01 | etc 5 | 2013-05-01 | etc 6 | 2013-06-01 | etc 7 | 2013-06-01 | etc 8 | 2013-03-01 | etc 9 | 2013-04-01 | etc 10 | 2013-05-01 | etc I want a query that ends wit the result: overallTotal | totalThisMonth | totalLastMonth 10 | 2 | 1 But I want to do this in one query! I am trying to find a way to use subqueries to do this. SO far I have: SELECT * from ( SELECT count(*) as overallTotal from ORDERS ) How can I combine this with other subqueries so I can get the totals in one query?

    Read the article

  • Best way to update/insert into a table based on a remote table.

    - by martilyo
    I have two very large enterprise tables in an Oracle 10g database. One table keeps the historical information of the other table. The problem is, I'm getting to the point where the records are just too many that my insert update is taking too long and my session is getting killed by the governor. Here's a pseudocode of my update process: sqlsel := 'SELECT col1, col2, col3, sysdate FROM table2@remote_location dpi WHERE (col1, col2, col3) IN ( SELECT col1, col2, col3 FROM table2@remote_location MINUS SELECT DISTINCT col1, col2, col3 FROM table1 mpc WHERE facility = '''||load_facility||''' )'; EXECUTE IMMEDIATE sqlsel BULK COLLECT INTO table1; I've tried the MERGE statement: MERGE INTO table1 t1 USING ( SELECT col1, col2, col3 FROM table2@remote_location ) t2 ON ( t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 = t2.col3 ) WHEN NOT MATCHED THEN INSERT (t1.col1, t1.col2, t1.col3, t1.update_dttm ) VALUES (t2.col1, t2.col2, t2.col3, sysdate ) But there seems to be a confirmed bug on versions prior to Oracle 10.2.0.4 on the merge statement when doing a merge using a remote database. The chance of getting an enterprise upgrade is slim so is there a way to further optimize my first query or write it in another way to have it run best performance wise? Thanks.

    Read the article

  • Relation to multiple tables of different types for rating?

    - by Tronic
    i have a table structure like this Products Team Images and want to implement a rating/commenting-feature, where users can rate each entry of all tables. what's the best way to make a single rating table? e.g. a user votes a a product and a team entry, and it should be possible to get alle these entries from a single table. what kind of table-structure is best for this purpose? i hope, my questions is clear enough :/ thanks in advance!

    Read the article

  • Using a trigger to record audit information vs. stored procedure

    - by Germ
    Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving this? I know I could remove the trigger and have the delete stored procedure insert the audit entry prior to deleting but are there any other recommeded alternative? If a username was passed as a parameter to the delete stored procedure, is there anyway to get this value in the trigger that's excuted when the record is deleted? I'm just throwing this out there...

    Read the article

  • postgres subquery w/ derived column

    - by Wells
    The following query won't work, but it should be clear what I'm trying to do: split the value of 't' on space and use the last element in that array in the subquery (as it will match tl). Any ideas how to do this? Thanks! SELECT t, y, "type", regexp_split_to_array(t, ' ') as t_array, sum(dr), ( select uz from f.tfa where tl = t_array[-1] ) as uz, sc FROM padres.yd_fld WHERE y = 2010 AND pos <> 0 GROUP BY t, y, "type", sc;

    Read the article

  • Multiple user database design

    - by dieguitoweb
    I have to develop a basic social network for an academic purpose; but I need some tips for the users management.. The users are subdivided into 3 groups with different privilege: admins,analysts and standards users. For every user should be stored into the database the following information: name,lastname,e-mail,age,password. I'm not quite sure how I should design the database between theese two solutions: 1)one table called 'users' with the 'role' attribute that explain what a user can do and what can't do, and the permissions are managed via php 2)every application user is a database user created with the query 'CREATE ROLE' (It's a postgres database) and he has permissions on some tables granted with the 'GRANT' statement You should take into account that the project is for a database exam.. thanks

    Read the article

  • Can I concatenate multiple MySQL rows into one field?

    - by Dean
    Using MySQL, I can do something like select hobbies from peoples_hobbies where person_id = 5; and get: shopping fishing coding but instead I just want 1 row, 1 col: shopping, fishing, coding The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like. I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?

    Read the article

  • Indexing table with duplicates MySQL/MSSQL with millions of records

    - by Tesnep
    I need help in indexing in MySQL. I have a table in MySQL with following rows: ID Store_ID Feature_ID Order_ID Viewed_Date Deal_ID IsTrial The ID is auto generated. Store_ID goes from 1 - 8. Feature_ID from 1 - let's say 100. Viewed Date is Date and time on which the data is inserted. IsTrial is either 0 or 1. You can ignore Order_ID and Deal_ID from this discussion. There are millions of data in the table and we have a reporting backend that needs to view the number of views in a certain period or overall where trial is 0 for a particular store id and for a particular feature. The query takes the form of: select count(viewed_date) from theTable where viewed_date between '2009-12-01' and '2010-12-31' and store_id = '2' and feature_id = '12' and Istrial = 0 In MSSQL you can have a filtered index to use for Istrial. Is there anything similar to this in MySQL? Also, Store_ID and Feature_ID have a lot of duplicate data. I created an index using Store_ID and Feature_ID. Although this seems to have decreased the search period, I need better improvement than this. Right now I have more than 4 million rows. To search for a particular query like the one above, it looks at 3.5 million rows in order to give me the count of 500k rows. PS. I forgot to add view_date filter in the query. Now I have done this.

    Read the article

  • SQL Join query, getting ManagerName

    - by user279521
    I have an tblEmployeeProfile & a tblPersonnel. tblPersonnel is an HR table, that consists of all employees in the company; tblEmployeeProfile contains details about an employee's position. tblPersonnel.PersonnelID tblPersonnel.FirstName tblPersonnel.MiddleName tblPersonnel.LastName tblPersonnel.PhoneNumber tblPersonnel.Email tblEmployeeProfile.EmployeeID tblEmployeeProfile.ManagerID tblEmployeeProfile.DepartmentID tblEmployeeProfile.JobCategoryID tblEmployeeProfile.SalaryID I want to return a record with the following fields: EmployeeID, FirstName, MiddleName, LastName, Email, ManagerFullName where EmployeeID = @EmployeeID *tblEmployeeProfile.ManagerID = tblPersonnel.PersonnelID* I can't seem to get the query correct for getting the ManagerFullName

    Read the article

  • How to check if an entityset is populated

    - by TheQ
    How can i check if an entityset of a linq-object is populated or not? Example code below. My model have two methods, one joins data, and the other does not: public static Member GetMemberWithSettings(Guid memberId) { using (DataContext db = new DataContext()) { DataLoadOptions dataLoadOptions = new DataLoadOptions(); dataLoadOptions.LoadWith<Member>(x => x.Settings); db.LoadOptions = dataLoadOptions; var query = from x in db.Members where x.MemberId == memberId select x; return query.FirstOrDefault(); } } public static Member GetMember(Guid memberId) { using (DataContext db = new DataContext()) { var query = from x in db.Members where x.MemberId == memberId select x; return query.FirstOrDefault(); } } Then my control have the following code: Member member1 = Member.GetMemberWithSettings(memberId); Member member2 = Member.GetMember(memberId); Debug.WriteLine(member1.Settings.Count); Debug.WriteLine(member2.Settings.Count); The last line will generate a "Cannot access a disposed object" exception. I know that i can get rid of that exception just by not disposing the datacontext, but then the last line will generate a new query to the database, and i don't want that. What i would like is something like: Debug.WriteLine((member1.Settings.IsPopulated()) ? member1.Settings.Count : -1); Debug.WriteLine((member2.Settings.IsPopulated()) ? member2.Settings.Count : -1); Is it possible?

    Read the article

  • Load Empty Database table

    - by john White
    I am using SQLexpress and VS2008. I have a DB with a table named "A", which has an IdentitySpecification column named ID. The ID is auto-incremented. Even if the row is deleted, the ID still increases. After several data manipulation, the current ID has reached 15, for example. When I run the application if there's at least 1 row: if I add a new row, the new ID is 16. Everything is fine. If the table is empty (no row): if I add a new row, the new ID is 0, which is an error (I think). And further data manipulation (eg. delete or update) will result in an unhandled exception. Has anyone encountered this? PS. In my table definition, the ID has been selected as follow: Identity Increment = 1; Identity Seed =1; The DB load code is: dataSet = gcnew DataSet(); dataAdapter->Fill(dataSet,"A"); dataTable=dataSet->Tables["A"]; dbConnection->Open(); The Update button method dataAdapter->Update(dataSet,"tblInFlow"); dataSet->AcceptChanges(); dataTable=dataSet->Tables["tblInFlow"]; dataGrid->DataSource=dataTable; If I press Update: if there's at least a row: the datagrid view updates and shows the table correctly. if there's nothing in the table (no data row), the Add method will add a new row, but from ID 0. If I close the program and restart it again: the ID would be 16, which is correct. This is the add method row=dataTable->NewRow(); row["column1"]="something"; dataTable->Rows->Add(row); dataAdapter->Update(dataSet,"A"); dataSet->AcceptChanges(); dataTable=dataSet->Tables["A"];

    Read the article

  • Mysql query help needed

    - by Me-and-Coding
    Hi, i have two tables category and hotels where category.id should be equal to hotels.catid. Now how do i select 3 rows from each different category from the hotels table. I have this query: select h.* from hotels h inner join category c on h.catid = c.id order by h.catid, h.hid this selects all records, but i want to select three rows per different category so in all it should return 9 rows with 3 rows for each category. If this can not be done in mysql, you could also suggest php code please. Thanks

    Read the article

  • change postgres date format

    - by Jay
    Is there a way to change the default format of a date in Postgres? Normally when I query a Postgres database, dates come out as yyyy-mm-dd hh:mm:ss+tz, like 2011-02-21 11:30:00-05. But one particular program the dates come out yyyy-mm-dd hh:mm:ss.s, that is, there is no time zone and it shows tenths of a second. Apparently something is changing the default date format, but I don't know what or where. I don't think it's a server-side configuration parameter, because I can access the same database with a different program and I get the format with the timezone. I care because it appears to be ignoring my "set timezone" calls in addition to changing the format. All times come out EST. Additional info: If I write "select somedate from sometable" I get the "no timezone" format. But if I write "select to_char(somedate::timestamptz, 'yyyy-mm-dd hh24:mi:ss-tz')" then timezones work as I would expect. This really sounds to me like something is setting all timestamps to implicitly be "to_char(date::timestamp, 'yyyy-mm-dd hh24:mi:ss.m')". But I can't find anything in the documentation about how I would do this if I wanted to, nor can I find anything in the code that appears to do this. Though as I don't know what to look for, that doesn't prove much.

    Read the article

  • Multiple conditions with CASE statements

    - by Pavan Reddy
    I need to query some data. here is the query that i have constructed but which isn't workig fine for me. For this example I am using AdventureWorks database. SELECT * FROM [Purchasing].[Vendor] WHERE PurchasingWebServiceURL LIKE case // In this case I need all rows to be returned if @url is '' or 'ALL' or NULL when (@url IS null OR @url = '' OR @url = 'ALL') then ('''%'' AND PurchasingWebServiceURL IS NULL') //I need all records which are blank here including nulls when (@url = 'blank') then (''''' AND PurchasingWebServiceURL IS NULL' ) //n this condition I need all record which are not like a particular value when (@url = 'fail') then ('''%'' AND PurchasingWebServiceURL NOT LIKE ''%treyresearch%''' ) //Else Match the records which are `LIKE` the input value else '%' + @url + '%' end This is not working for me. How can I have multiple where condition clauses in the THEN of the the same CASE? How can I make this work?

    Read the article

< Previous Page | 646 647 648 649 650 651 652 653 654 655 656 657  | Next Page >