database

How to do an INNER JOIN on multiple columns

I'm working on a homework project and I'm supposed to perform a database query which finds flights either by the city name or the airport code, but the flights table only contains the airport codes so if I want to search by city I have to join on the airports table. The airports table has the following columns: code, city The flights ta...

database transaction rollback processing in PHP

try { $con->beginTransaction(); $this->doSave($con); $con->commit(); } catch (Exception $e) { $con->rollBack(); throw $e; } The code above is quite standard an approach to deal with transactions, but my question is:what if $con->rollBack() also fails? It may cause db lock,right?If so,what's the perfect way to go? ...

How to present features of aggregate functions (NULL)?

I'm looking for 'textbook' example of database to illustrate salient features of the aggregate functions (Max, Min, Sum, Avg and Count) when NULL values are involved. I must be able to discuss and illustrate/present the usage of these aggregates function in the presence of NULLs with example queries and their answers, using mentioned da...

Can table columns with a foreign key be null?

For example, I have a table which has several ID columns to other tables. I want a foreign key to force integrity only if I do put data in there. If I do an update at a later time to populate that column then it will still check the constraint (this is likely database server dependant, i'm using MySQL & InnoDB table type). I believe this...

connect to oracledb in java

Hey I work in a big company and need to connect to a oracle database. It is set up so that i can connect to it through DSN, but I would like to connect to the db directly. How do i do that, when the only information I have is the name of the db and the username and password. It works fine through dsn. ...

How to produce a read only iphone app like a book

I'm new here to stackoverflow, so bear with me. I have a book that I wrote, that I would like to have available as an iPhone app. All I want it to do is just show my content. I have done some work putting my book into excel, then into a csv format for importing into a database, so the content is all organized ok, and is in fairly accessi...

How should I index this MySQL db?

I am creating a site that allows users to share specific pages to the public. It is similar to how jsbin.com let's you create a public url of the script you're working on. The basic MySQL table I am working with now is: CREATE TABLE IF NOT EXISTS `lists` ( `id` int(10) NOT NULL AUTO_INCREMENT, `hash` varchar(6) NOT NULL, `created_...

Ruby on Rails: Is it better to validate in the model or the database?

Is it generally better practice (and why) to validate attributes in the model or in the database definition? For (a trivial) example: In the user model: validates_presence_of :name versus in the migration: t.string :name, :null => false On the one hand, including it in the database seems more of a guarantee against any type of b...

Handling database-related exceptions in Java/Spring

I'm reading the book Spring Recipes right now, and am puzzled by one thing: Any exceptions are thrown as a subclass of DataAccessException, a RuntimeException you're not meant to try/catch. What I'm worried about is problems that are more likely to happen, especially with inserts. If you're using something like SimpleJdbcTemplate each ...

Inner Joining two tables based on all "Key/Value" Pairs matching exactly

Lets say I have two tables - Person and Clothes and both of these tables have associated Key/Value tables which store attributes about a Person and an item of Clothing. A joined version of Person to Attributes might look like: PersonID | AttributeKey | AttributeValue 1 'Age' '20' 1 'Size' 'La...

asp.net MVC database driven navigation menu

Hello all, can anybody point me in the right direction as to how I would go about implementing a hierarchical navigation menu which is driven by database? I don't want to re-invent the wheel here, so I was wondering if there is something that's already been done. I'm aware of the Menu control in asp.net, but from what I figure it won'...

Will using the php function "insert into" create new columns if they aren't already defined, or return an error?

I'd like to use a foreach loop to insert a _POST associative array into a database using INSERT INTO. What I need to know, is if my _POST data contains keys that don't match the columns of my database, will INSERT INTO simply create these columns in the database? if not, will it return an error and not update my database at all? or wil...

Importing data from Access to SQL Server (with minor table changes)

I'm currently working on migrating an application from MS-Access to MS SQL Server. In the process, there are a few minor changes that I am making to the table layouts (I am splitting a few things up into more two tables), however, I would still like to keep all the data that is present in the database. What would be the best way to imp...

Help with LINQ query

I'm trying to retrieve columns based on their corresponding key. "Select * from diamond where p_Id="+p_Id I followed MSDN sample and they were doing it like this way: CurrencyManager cm = ((CurrencyManager)this.BindingContext[myDatabaseDataSet, "Items.ItemDiamond"]); IBindingList list = (IBindingList)cm.List; but lis...

High performance site

What technologies should I use when designing for a large social website (with a lot of transactions, like twitter)? using open source solutions - database - webserver - os ...

How to separate programming logic and data in MS SQL Server 2005?

I am developing a data driven website and quite a lot of programming logic resides in database stored procedures and database functions. I found myself changing the stored proc/functions quite a lot in order to fix bugs or add new functionality. The data (tables) have remained mostly untouched. The issue I am having is keeping track of...

Continuing a transaction after primary key violation error

I am doing a bulk insert of records into a database from a log file. Occasionally (~1 row out of every thousand) one of the rows violates the primary key and causes the transaction to fail. Currently, the user has to manually go through the file that caused the failure and remove the offending row before attempting to re-import. Given th...

SQL Server 2008 FILESTREAM Feature with VLDB

I have a bunch of xml files that is about 700 GB in size. I'm going to load the data within those files into a SQL Server 2008 database table(tabular data). In addition to the fields that will hold the data in a tabular format, the table will contain a field of SQL Server XML type that holds the xml data as a whole. I want to use the FIL...

Default database named postgres on Postgresql server

Apparently there is a database "postgres" that is created by default on each postgresql server installation. Can anyone tell me or point me to documentation what it is used for? ...

historically, what made relational databases popular?

EDIT I've just started skimming Codd's famous 1970 paper that started it all, that Oracle was based on (A Relational Model of Data for Large Shared Data Banks [pdf]), and was amazed to find that it seems it will answer this SO question. It talks about databases in the market at that time ("hierarchical" and "network" - like NoSQL?), the...