database

Parameter Binding: What happens under the hood?

.NET, Java and other high level database API's in various language often provide techniques known as prepared statements and parameter binding as opposed to sending plain text commands to the Database server. What I would like to know is what happens when you execute a statement like this: SqlCommand cmd = new SqlCommand("GetMemberByID"...

Creating test data in a database

I'm aware of some of the test data generators out there, but most seem to just fill name and address style databases [feel free to correct me]. We have a large integrated and normalised application - e.g. invoices have part numbers linked to stocking tables, customer numbers linked to customer tables, change logs linked to audit informa...

Should DB layer members be static or instance?

I've seen projects where the classes in the DB layer have just static functions in them and other projects where those classes need to be instantiated to get access to the member functions. Which is "better" and why? ...

Restore database backup over the network.

How do you restore a database backup using SQL Server 2005 over the network? I recall doing this before but there was something odd about the way you had to do it. ...

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server MySQL PostgreSQL SQLite Oracle I am currently doing something like the following in SQL Se...

How do you deal with transport-level errors in SqlConnection?

Every now and then in a high volume .NET application, you might see this exception when you try to execute a query: System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. According to my research, this is something that "just happens" and not much can be done to prevent it. It...

SQL query to compare product sales by month

I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this: Category | Revenue | Yearh | Month Bikes 10 000 2008 1 Bikes 12 000 2008 2 Bikes 12 000 2008 3 Bikes 15 000 2008 1 Bikes 11 000 2007 ...

Migrating from MySQL to PostgreSQL

We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons. Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improveme...

RDF storage

Has someone experiment RDF storage solution like sesame ? I'm looking for performance review of this kind of solution compared to the traditional database solution. ...

How to encrypt connection string in WinForms 1.1 app.config?

Just looking for the first step basic solution here that keeps the honest people out. Thanks, Mike ...

What is a good book on MySql for someone experienced with SQL Server?

I am about to migrate a project from SQL Server to MySQL. I am looking for a good book that will quickly get me up to speed with MySQL (that assumes I already have a good knowledge of relational databases). Any recommendations? ...

Best way to learn SQL Server

So I'm getting a new job working with databases (Microsoft SQL Server to be precise). I know nothing about SQL much less SQL Server. They said they'd train me, but I want to take some initiative to learn about it on my own to be ahead. Where's the best place to start (tutorials, books, etc)? I want to learn more about the SQL languag...

Why do we need entity objects?

Ok, I realize I might be downvoted into oblivion for this question, especially given my stance on the matter, but I really need to see some honest, thoughtful debate on the merits of the currently accepted enterprise application design paradigm. I am not convinced that entity objects should exist. By entity objects I mean the typical t...

Are foreign keys really necessary in a database design?

As far as I know, foreign keys (FK) are used to aid the programmer to manipulate data in the correct way. Suppose a programmer is actually doing this in the right manner already, then do we really need the concept of foreign keys? Are there any other uses for foreign keys? Am I missing something here? ...

SQL - What are your favorite performance tricks?

When you have a query or stored procedure that needs performance tuning, what are some of the first things you try? ...

SQL2005: Linking a table to multiple tables and retaining Ref Integrity?

Howdy, Here is a simplification of my database: Table: Property Fields: ID, Address Table: Quote Fields: ID, PropertyID, BespokeQuoteFields... Table: Job Fields: ID, PropertyID, BespokeJobFields... Then we have other tables that relate to the Quote and Job tables individually. I now need to add a Message table where users can recor...

Version track, automate DB schema changes with django

I'm currently looking at the Python framework Django for future db-based web apps as well as for a port of some apps currently written in PHP. One of the nastier issues during my last years was keeping track of database schema changes and deploying these changes to productive systems. I haven't dared asking for being able to undo them to...

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read). The task in question will be doing over 1 million database...

Is there a Profiler equivalent for MySql?

"Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services." I find using SQL Server Profiler extremely useful during development, testing and when I am debugging database application problems. Does anybody know if there is an equivalent program for My...

Automate Syncing Oracle Tables With MySQL Tables

The university I work at uses Oracle for the database system. We currently have programs we run at night to download what we need into some local Access tables for our testing needs. Access is getting to small for this now and we need something bigger. Also, the nightly jobs require constant maintance to keep working (because of network ...