sql

How do I create an Emacs SQL buffer?

I can connect manually by doing: M-x sql-postgres and type the User, Database, Host, Server I'd like to do something like: (connect-foo (user "me")(database "bar")(host "earth")) Solution so far: I grabbed some code from sql.el and changed it so I can use it non-interactively. (defun sql-create-buffer (profile) "derived from sql...

Getting sourcecontrol on stored procedures

First off the technical stuff: We're using VS 2008 pro and running a MS SQL 2008 server. For sourcecontrol we use Subversion. We'd really like to keep our stored procedures in subversion, so we can tell what was changed, when and so forth. However in order for this to work, it has to be seamless, otherwise the developers are just goin...

sql Database to save different contact details for a message sending site

I am working for a project to create a database for saving different persons contact details in SQL. For example, X person saves 10 contacts, Y persons save 15 contacts, z persons save 20 contacts and so on. I cant create separate tables to save contacts of x,y,z and so on. But i just want to know the alternative method to do that. Is th...

Find earliest and latest dates of specified records from a table using SQL

Hi I have a table (in MS SQL 2005) with a selection of dates. I want to be able to apply a WHERE statement to return a group of them and then return which date is the earliest from one column and which one is the latest from another column. Here is an example table: ID StartDate EndDate Person 1 01/03/2010 03/03/2010 Paul 2 12/05...

T-SQL: Using OVER and PARTITION BY

Hello, I have the following data | Item | Value | Date | ------------------------------ | 1 | 10 | 01.01.2010 | 1 | 20 | 02.01.2010 | 1 | 30 | 03.01.2010 | 1 | 40 | 04.01.2010 | 1 | 50 | 05.01.2010 | 1 | 80 | 10.01.2010 | 2 | 30 | 04.01.2010 | 2 | 60 | 06...

Autoincrementing hierarchical IDs on SQL Server

Consider this table on SQL Server wordID aliasID value =========================== 0 0 'cat' 1 0 'dog' 2 0 'argh' 2 1 'ugh' WordID is a id of a word which possibly has aliases. AliasID determines a specific alias to a word. So above 'argh' and 'ugh' are aliases...

The current transaction cannot be committed and cannot support operations that write to the log file

I am getting an error "The current transaction cannot be committed and cannot support operations that write to the log file" in my production environment. When I detach and attach the same database in my staging environment then it works abosultely fine. I have no clue what's going on, please help as it's affecting lot of things. ...

Querying nHibernate using ICriteria with GROUP BY and HAVING clauses

Hi everyone, I'm struggling to find a simple example of using ICriteria (or the new QueryOver API) in nHibernate to write a query like this: SELECT TOP (10) [t0].* FROM [dbo].[ContentView] AS [t0] INNER JOIN [dbo].[ContentTag] AS [t1] ON [t0].[UniqueID] = [t1].[ContentID] WHERE [t0].[TypeName]='Book' AND [t1].[Name] IN ('aspnet', 'sqls...

Problem with sql query

Hi, I've got query: INSERT INTO [Tasks] ([LoginName] ,[Type] ,[Filter] ,[Dictionary] ,[Description]) Select N'Anonymous',4,'SomeTable.targetcode in (select Code from cities where countrycode in ('TN')) and SomeTable.SomeValue in ('13','15')',3,N'Cities from tunis' Union All ... [...

How to search multiple columns in MySQL?

I'm trying to make a search feature that will search multiple columns to find a keyword based match. This query: SELECT title FROM pages LIKE %$query%; works only for searching one column, I noticed separating column names with commas results in an error. So is it possible to search multiple columns in mysql? ...

Need help in tuning a sql-query

Hello, i need some help to boost this SQL-Statement. The execution time is around 125ms. During the runtime of my program this sql (better: equally structured sqls for different tables) will be called 300.000 times. The average row count in the tables lies around 10.000.000 rows and new rows (updates/inserts) will be added with a time...

Value Comparison with a multivalued column in SQL Database Table

Hi All, Suppose there is a table A which has a column AccessRights which is multivalued( Eg of values in it in this format STOLI,HELP,BRANCH(comma separated string) Now a stored procedure is written against this table to fetch records based on a AccessRight parameter sent to the SP. Let that parameter be @AccessRights, this is also a c...

How to make an entity out of a join table without primary key

I'm trying to generate JPA entities out of an existing database having an "interesting" design. Database has a table called UserSet, which can have links to several other UserSets. There is a one to many relation between UserSets and LinkedUserSets. LinkedUserSets also has one to one relation to UserSets. I tried to generate a JPA Enti...

microsoft sql equivalent of mysql REGEXP

I am trying to reconstruct a database query I created for mysql in Microsoft sql. I am looking for a term in Microsoft sql, which acts like REGEXP here is an example of how am using the term select * from musicdetails WHERE artistname REGEXP '^".mysql_escape_string($_GET['search'])."$' any help would be much appreciated ...

which one is a faster/better sql practice?

Suppose I have a 2 column table (id, flag) and id is sequential. I expect this table to contain a lot of records. I want to periodically select the first row not flagged and update it. Some of the records on the way may have already been flagged, so I want to skip them. Does it make more sense if I store the last id I flagged and use ...

SqlDataReader / DbDataReader implementation question

Does anyone know how DbDataReaders actually work. We can use SqlDataReader as an example. When you do the following cmd.CommandText = "SELECT * FROM Customers"; var rdr = cmd.ExecuteReader(); while(rdr.Read()) { //Do something } Does the data reader have all of the rows in memory, or does it just grab one, and then when Read is c...

SQL Server CONTAINS with digits gives no results

Hi, I have a database table which is full-text indexed and i use the CONTAINS-function to perform a search-query on it. When I do: SELECT * FROM Plants WHERE CONTAINS(Plants.Description, '"Plant*" AND "one*"'); I get back all correct results matching a description with the words "Plant" and "one". Some plant are named like "Plant 1...

Selecting rows without a value for Date/Time columns

I'm running this query: SELECT TOP 1 [DVD Copy].[Stock No] FROM [DVD Copy] WHERE [DVD Copy].[Catalogue No] =[Forms]![New Rental]![Catalogue No] And [Issue Date] = Null; Which works fine without the null check for Issue Date. I'm trying to select rows without a Date in the Issue Date column. Is Null the wrong kind of value to use for h...

Make a range in postgres

Hi, how can I make a range in a select in PostgreSQL ? I'd like to have this return: num --- 1 2 3 4 5 6 In a query like: SELECT range(1,6) AS num; ...

Non-latin-characters ordering in database with "order by"

I just found some strange behavior of database's "order by" clause. In string comparison, I expected some characters such as '[' and '_' are greater than latin characters/digits such as 'I' or '2' considering their orders in the ASCII table. However, the sorting results from database's "order by" clause is different with my expectation. ...