sql

Is there any point in creating a second column optimized for FULLTEXT searches?

Hi, the project I'm working on has for each column that needs to be searched a second column called "ft[columnname]" which has a FULLTEXT index and only this one is searched against. This column contains an "optimized" text, that is automatically generated from the original column in the following way: The string is lowercased All acc...

Ungrouping effect?

I have a dynamic set of data X of the form: ---------------------------------- x.id | x.allocated | x.unallocated ---------------------------------- foo | 2 | 0 bar | 1 | 2 ---------------------------------- And I need to get to a result of Y (order is unimportant): ---------------------------------- y.id | y.st...

Consideration validation email in Data Base

Hi, I need to store in a Table messages which are sent using a classical web page CONTACT US. My question is: Would be better doing email validation on Business layer or better at Data Base level? Regarding email validation in BL I would like to know if using asp.net 4 there is some sort of automatic system for validating emails instea...

sql search on fields containing diacritics

Hi guys, I'm working at a web site search which uses Sql server 2008 express edition. I have 'Books' table which has a field named 'Title' where there are titles containing romanian (latin) characters. Well, if the user inserts a word like: 'casa' in the search field and i have a title in db like: 'test casă test' i want to show that ...

How do you get the max_length for a mysql field

MySQL is returning the current field value length in property max_length. I there a way to get the correct values ? ie Field part_code is a varchar(32) that returns 3 if it contains the value of "ABC" instead of the expected result of 32 EDIT original mysql //--------------------------------------------------------------------------...

Please can anyone why explain dropping and recreating stored procedures in SQL Server 2005 causes much more of an initial slow down than expected?

My first post here, please be gentle. =) I work for a company that inherited the maintenance of a bespoke system used by one of our customers. The previous developer (no longer with us) encrypted all the database objects (WITH ENCRYPTION). The system has been plagued with various timeout issues well before we took ownership of it, and ...

Data object storage - Can table JOIN's do what single table SELECT's cannot?

Now that "NOSQL" or "object only" storage systems like MongoDB or memcached are really picking up steam in the world. I was wondering if there are any requests that cannot be performed on them that can be performed using multiple object joins (in SQL that is JOIN "table"). In other words, are there any multi-table queries that cannot be ...

create a pricing matrix - sql or array of constants?

I am planning to create a pricing matrix for a project in Rails. It's supposed to be a table of destinations and departure locations, and prices depending on where you came and are planning to go. I am kinda undecided on how to better do this: either making a table in the db for this matrix, or making a mega array of constants. Problem ...

Can not create Delete trigger because table has a FOREIGN KEY with cascading DELETE

Hi, I am trying to create a Delete trigger. I have this navigation table which is called Nemanet_Navigation. The table has a foreign key to itself but when selcting relationship for the table I have in INSERT and UPDATE Specification selected - Delete Rule NO Action. So I do not select Cascading. Then I have this trigger: CREATE TRIGGER...

How to: Manage multiple overlapping indexes in SQL Server 2005

For the sake of example, I have a table with columns A B C D E F G H. I have created two indexes on the table that correspond to the most used queries. The first is on columns B C D and E. The second is on B C D E and F. The queries that use these columns are called the same number of times and they are each optimized with respect to...

SQL: Filtering groups based on aggregate function

Using MySQL So i'm trying to improve upon a query I've written. My current query works, but i feel like i could be more efficient Essentially, I have a table that lists 'who talks to who and how much'. The records look like this: email name status count prod_ref (I'll post an example set with example output at the end of the...

Build SQL queries in Python

Are there any python packages that help generating SQL queries from variables and classes? For example, instead of writing create query manually, the developer will create a create table (as an object maybe), with desired columns in a list for instance. Then the object will return a string that will be used as a query. It would be a plu...

SQL: Finding duplicate values in a field, but using SubString()

Here's a question for all those SQL SERVER 2000 experts: I have only 1 table... I can already find if any of the values in a certain field, also appears in another record. I.E.: Does any record have "ABCDEFGHI" in a field, and then "ABCDEFGHI" again in that same field... but in another record. But I run into trouble when I...

C# Adding an array to SQL CE database

This may be to general of a question, and I apologize if it is. I'm trying to add data from an array to a SQL CE database. The array is 30 columns by up to 360 rows. I've found samples of different ways to do it, but are all inefficient on an array of the size I'm working with as they require each element of the array to be added individ...

mysql: how do i start auto increment from a specific point?

CREATE TABLE `batchinfo` ( `rowid` int(11) NOT NULL AUTO_INCREMENT, `datapath` mediumtext, `analysistime` varchar(50) DEFAULT NULL, `reporttime` varchar(50) DEFAULT NULL, `lastcalib` varchar(50) DEFAULT NULL, `analystname` varchar(150) DEFAULT NULL, `reportname` varchar(150) DEFAULT NULL, `batchstate` varchar(150) DEFAULT...

Incorrect Syntax near Where for an Update query

Hello, Here is the update query which i am using to update a table. It throws me an exception "Incorrect Syntax near Where" Why is that exception for? i have no idea. public bool UpdateLocationCountintoMerchantPackage(int PackageID, long MerchantID,int LocationCount) { try { SqlParameter[] parameters...

Is this query injection proof?

I am using PDO to talk to my database, and I wonder if casting a type like this $dbh->query("SELECT * FROM recipes WHERE id=".(int)$id); is sufficient to prevent sql injection? In this case $id is always an integer. I also wonder what would be a good way to prevent an injection in this kind of statement if the variable was a string. ...

Select one row from a range of rows in SQL Server

Hi all, I've come across a very interesting problem.. quite lost actually. Suppose I have a table with range of values, like: id.........From........To 1..........0...........5 2..........6...........10 3..........11...........15 4..........16...........29 So now I have an integer say @MyInt = 8, I want to select that single row in w...

SQL View Conditional Selection

I am trying to create a view that pulls a particular record. The problem I am running into is that there are 2 records to choose from and a status flag that is either 1 or 2. It should pull the 1 record if it exists, and if not the 2 record. Is this possible from a view? ...

How do I run a .sql3 file that I received when pulling my database from Google App Engine?

The Google page on uploading and downloading datastores is pretty useless, but I've gotten this far. Now I need to get the data in the .sql3 into whatever database it was originally stored in. ...