I need to import a csv file into Firebird and I've spent a couple of hours trying out some tools and none fit my needs. The main problem is that all the tools I've been trying like EMS Data Import and Firebird Data Wizard expect that my csv file contains all the information needed by my Table. I need to write some custom SQL in the ins...
I'm doing some inter-database operational research... e.g. synchronizing Oracle, MySQL, etc. Are there any nice MySQL databases that I can download, so that I can test some importing on real-world cases? I'm thinking of some open project that might have a weekly data dump available for download. Of course, anything similar for Oracle,...
Scalar-valued functions can be called from .NET as follows: SqlCommand cmd = new SqlCommand("testFunction", sqlConn); //testFunction is scalar cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("retVal", SqlDbType.Int); cmd.Parameters["retVal"].Direction = ParameterDirection.ReturnValue; cmd.ExecuteScalar(); int aFuncti...
Suppose we have a table A: itemid mark 1 5 2 3 and table B: itemid mark 1 3 3 5 I want to join A*B on A.itemid=B.itemid both right and left ways. i.e. result: itemid A.mark B.mark 1 5 3 2 3 NULL 3 NULL 5 Is there a way to do it in one query in MySQL? ...
I'm looking for a pattern for performing a dynamic search on multiple tables. I have no control over the legacy (and poorly designed) database table structure. Consider a scenario similar to a resume search where a user may want to perform a search against any of the data in the resume and get back a list of resumes that match their se...
This is a question I asked on another forum which received some decent answers, but I wanted to see if anyone here has more insight. The problem is that you have one of your pages in a web application timing out when it gets to a stored procedure call, so you use Sql Profiler, or your application trace logs, to find the query and you pa...
Many applications have grids that display data from a database table one page at a time. Many of them also let the user pick the number of records per page, sort by any column, and navigate back and forth through the results. What's a good algorithm to implement this pattern without bringing the entire table to the client and then filte...
I'm doing a survey for the best SQL change script generators out there, specifically for MS SQL Server. I do know of Redgate SQL Compare, but I'd like to know what others use, and if there are free versions of such software that I could try. Any suggestions? ...
In your applications, what's a "long time" to keep a transaction open before committing or rolling back? Minutes? Seconds? Hours? and on which database? ...
For certain types of sql queries, an auxiliary table of numbers can be very useful. It may be created as a table with as many rows as you need for a particular task or as a user defined function that returns the number of rows required in each query. What is the optimal way to create such a function? ...
When I'm joining three or more tables together by a common column, I'd write my query like this: SELECT * FROM a, b, c WHERE a.id = b.id AND b.id = c.id a colleague recently asked my why I didn't do explicit Join Transitive Closure in my queries like this: SELECT * FROM a, b, c WHERE a.id = b.id AND b.id = c.id AND c.i...
What are the pros and cons of using table aliases in SQL? I personally try to avoid them, as I think they make the code less readable (especially when reading through large where/and statements), but I'd be interested in hearing any counter-points to this. When is it generally a good idea to use table aliases, and do you have any prefe...
I'm trying to figure out how big a certain database would be (it hasn't been created yet). I know how many rows and what the tables will be. Is there a feature in Oracle that will tell me the size of such a theoretical database? Is there a known math formula I can use? I know there is a feature to determine the size of an existing databa...
Recently I have been investigating the possibilities of caching in ASP.NET. I rolled my own "Cache", because I didn't know any better, it looked a bit like this: public class DataManager { private static DataManager s_instance; public static DataManager GetInstance() { } private Data[] m_myData; p...
I'm looking for a book/site/tutorial on best practices for relational database design, tuning for performance etc. It turns out this kind of resource is a bit difficult to find; there's a lot of "here's normalization, here's ER diagrams, have at it," but not much in the way of real examples. Anyone have any ideas? ...
We’ve found that the unit tests we’ve written for our C#/C++ code have really paid off. But we still have thousands of lines of business logic in stored procedures, which only really get tested in anger when our product is rolled out to a large number of users. What makes this worse is that some of these stored procedures end up being...
I've been using Emacs's sql interactive mode to talk to the MySQL db server and gotten to enjoy it. A developer has set up another db on a new non-default port number but I don't know how to access it using sql-mysql. How do I specify a port number when I'm trying to connect to a database? It would be even better if Emacs can prompt me...
So basically I'm building an app for my company and it NEEDS to be built using MS Access and it needs to be built on SQL Server. I've drawn up most of the plans but am having a hard time figuring out a way to handle the auditing system. Since it is being used internally only and you won't even be able to touch the db from outside the b...
I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this: update myTable set Col1=@col1, Col2=@col2 where ID=@ID if @@rowcount = 0 insert into myTable (Col1, Col2) values (@col1, @col2) My logic behind writing it in this way is that the update will perform an im...
I work on quite a few DotNetNuke sites, and occasionally (I haven't figured out the common factor yet), when I use the Database Publishing Wizard from Microsoft to create scripts for the site I've created on my Dev server, after running the scripts at the host (usually GoDaddy.com), and uploading the site files, I get an error... I'm 99...