sql

Increment counter or insert row in one statement, in SQLite

In SQLite, given this database schema CREATE TABLE observations ( src TEXT, dest TEXT, verb TEXT, occurrences INTEGER ); CREATE UNIQUE INDEX observations_index ON observations (src, dest, verb); whenever a new observation tuple (:src, :dest, :verb) comes in, I want to either increment the "occurrences" column for t...

SQL Compact Edition 3.5 - Access to the database file is not allowed

Windows 7, Visual Studio 2010, .NET 4 Client Profile I developed an application (100% local, no access to servers) using SQL CE 3.5, it works fine on my computer. However, when i deployed it on another computer, it shows this error: Access to the database file is not allowed. [ File name = data\BDApepucCE.sdf ] I deployed on a window...

What would it mean If I change the identifying relationship from this part of a database design to a non-identifying relationship?

Hi all, I have a question regarding with this database-design. I am a bit unsure of the difference between identifying and non-identifying relationships in a database leading me to some puzzles in my head. I have this database design: *(kind of like a movie rental stores. "friend" are those who borrow the movie. "studio" is the product...

Problem with ifelse in PHP script

I have a working pagination script, it displays the data with few issues. However, the issue I'm having is trying to get my data to enclose it in quotes if it's not null. This is the part of my pagination script: //This function shows the data public function display() { if(date('Y-m-d') == date('Y-m-d', $this->airdate)) $dateForm...

Is a join appropriate for accomplishing such functionality in LINQ to SQL?

I'm writing an ASP.NET MVC site where I'm using LINQ to SQL to access my SQL Server database. In my database, I have the following tables: Posts: PostID - int, PK, identity Text - nvarchar(MAX) PublishDate - datetime etc. PostTags: PostTagID - int, PK, identity PostID - FK to PK to Posts table TagID - FK to PK to Tags table Tags...

Using LINQ to count value frequency

I have a table ID|VALUE VALUE an integer field with possible values 0 - 4. How can I query the count of each value? Ideally the result should be an array with 6 elements, one for the count of each value and the last one is the total number of rows. ...

SQL Server 2005 Database Designer

Hello ! Does anyone know a good and free Database Designer able to export the design into SQL Server 2005 ? I have been using PowerAMC for 2 weeks but it was the trial. PowerAMC is the only program I know with a Conceptual Data Model Designer. Thank you. ...

Is denormalizing acceptable in this case?

I have the following locations table: ---------------------------------------------------------- | ID | zoneID | storeID | address | latitude | longitude | ---------------------------------------------------------- and the phones table: ----------------------- | locationID | number | ----------------------- Now, keep in mind that f...

Can this row-by-row T-SQL script be converted to a set-based script?

I have written this T-SQL script to roll up duplicate rows in a database that are created by a reconstruction process. To do this, it performs the following: Gets and stores the minimum target table primary key (ColTargetPK) per set of duplicate entries in a table variable (@minColTargetPKTable); determining duplicates by matching on t...

SQL Not Like Statement

How can I select table from sys.tables where table name does not containt special word (which pass in parameters). I want to select all tables where its contains word 'customer' but not those who ends with 'old' in name. TableName In DB customer1 customer2 customer3 customerold1 customerold2 Output Wanted customer1 customer2 c...

Selecting the top 5 unique rows, sorted randomly in SQL Server 2005?

I have a table in SQL Server 2005 that looks like 1 | bob | joined 2 | bob | left 3 | john | joined 4 | steve | joined 5 | andy | joined 6 | kyle | joined What I want is to give someone the ability to pull up the activity of 5 random users (showing their latest activity) ex: I want to return results 1, 3, 4, 5, 6 - or - 2, 3, 4, 5, ...

MySQL: how should I draft this database?

I'm designing a plugin for a high traffic forum. What I am doing is listing related threads using a search API, which has a maximum of 800 searches per day What I'm wanting to do is create a database to store the results, cache them for one day so it will be used instead of the API. Would a table layout such as this be optimal: Tabl...

how to return number of records efficiently in SQL?

Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. I am using the following code to return a part of data for a query to implement paging (i.e. page up/down to show a part of result at each page, like Google search result paging) on my web application (I use pageCount as number of results showed on ...

Combining Union results

I have the below SQL Query select Count(emailID) as ViewsThatMonth, Day(entry_date) as day, Month(entry_date) as month, Year(entry_date) as year from email_views where emailID = 110197 Group By Day(entry_date), Month(entry_date), Year(entry_date) UNION ALL select Count(emailID) as ViewsThatMon...

How to handle optional parameters in SQL query?

Hi, guys. Say I have a sample table: id_pk value ------------ 1 a 2 b 3 c And I have a sample PL/SQL block, which has a query that currently selects a single row into an array: declare type t_table is table of myTable%rowtype; n_RequiredId myTable.id_pk%type := 1; t_Output t_table := t_table(); b...

What indices should be created to optimize sql query with multiple OR conditions

I have a query like this: SELECT * FROM T WHERE A = @A AND (B=@B OR C=@C OR D=@D OR @E=E) ORDER BY F What indices should I add to improve query performance? Also I'll need to implement paging so this query will be more complex. My guess is that four indices should be created: (A, B, F), (A, C, F), (A, D, F) (A, E, F), but I'm not su...

Redirecting from one CGI page to another

I am a nembie to CGI with Perl, please do help me out and please do correct me wherever I have committed a mistake. This is the code. The problem is that after the password is validated and found as correct, instead of redirecting to the next page it is giving this message: Status: 302 Found Location: http://localhost/cgi-bin/Main.cgi ...

Converting flat data from sql to List<Item>

First of all I'm sorry if you feel this question has been raised before, but I can't seem to wrap my mind around this one, although it's rly not the hardest thing to do.. Basically I have a query result from sql which holds several rows, existing out of : id, parentid, name, description, level level is the depth of the item viewed as...

How to use transaction and select together, with sql2008

Hi, we have a ASP.NET site with a SQL2008 DB. I'm trying to implement transactions in our current system. We have a method that is inserting a new row to a table (Table_A), and it works fine with transaction. If an exception is throwed, it will do the rollback. My problem is, that another user can't do anything that invole with Table...

Exposing SQL fired when LINQ executes

Morning all. Just a quick one for you - where can I find the SQL that is executed when a LINQ statement fires? I have the following code that works a treat, var r = (from p in getproductweightsbuyer.tblWeights where p.MemberId == memberid && p.LocationId == locationid ...