sql

Converting a php array to SQL array

Hey, I want something like the following: $arrayOfValues = array(1,2,3,4); $sqlArray = mysql_convertToSqlArray($arrayOfValues); which then will return what in SQL would be: (1,2,3,4) but in php would be the string "(1,2,3,4)" ...

With SQL, how do I calculate what percentage of rows have a certain value?

I'm using PHP/MySQL to make a website that displays race results. I want to display a statistic of the 10 hardest races ie the races that most people DNF. I'm doing it like this: select raceid,race.name,race.location,race.date,count(result.raceid) as dnfs from result inner join race on result.raceid=race.id where result.place=0 grou...

How To Return All Instances Within ## Mile Radius of a List of Zipcodes

SQL Server 2008 has added some cool new Spacial types and given SQL Developers a lot more powerful ways of manipulating spacial data but it still eludes me how to efficently return, for example, only the locations that are within ## mile radius of a long list of zips (20 to maybe 15 thousand distinct zipcodes). Is there a simple way to ...

Is Java.sql's getRow() thread safe?

Lets say I have a large number of threads inserting into a mysql database using Java.sql. After i perform an insert i would like to know the primary key of the record i just inserted. I could use getRow() on the ResultSet returned by the insert query. However, is this thread safe? Or should I fire off another select statement to f...

How to accurately set up a foreing key scenario in MySQL to accomplish my addressbook

I have been making a database and learning along the way. I recently got into using InnoDB and using foreign keys to connect tables together. But in all honestly I'm probably making my foreign keys blindly. What is the correct set and check list that I need to use when making a foreign key. My understanding with foreign keys is that I ...

Trouble turning a Simple MsSQL select into a monitor

I have a SQL Statement that works as I want. select COUNT(*), MIN(emailed_to)from email.email_archive group by emailed_to order by COUNT(*) desc The output looks like this. 13 [email protected]; 8 [email protected]; 4 [email protected]; ... ... ... 1 deadlockFF17uyt...

Why is SQLServerCE query performing a table scan and is it an issue?

I'm trying to optimise a query for a SQLServerCE database running on a Windows Mobile device. The query is used to identify rows to delete in one table based on no longer being referenced from a different table. The original query used a DISTINCT to identify matching rows: SELECT TestGroupId, TestNameId, ServiceTypeId FROM ServiceTypeIn...

The best way to compare a database row with a submitted form data

Hi guys. I have a table with 8 fields and a corresponding form. When the form is submitted, I have the id of the record. What is the best way to check if data in any one of the fields has changed for that row? Should use the SQL route WHERE field1=$field1 AND field2=$field2 ..... AND ID=$ID or pull the row and compare each field w...

Stored procedure that deletes records by looping through Id's

So I am really confused on how to write this proc. Here is what needs to be done. I have 3 tables laid out as follows: tblObject {ObjectId, MasterId} tblAnotherObject {ObjectId} tblFurtherObject {ObjectId} I need a proc that can delete the rows in 'tblAnotherObject' and 'tblFurtherObject' that contain an 'ObjectId' that does NOT hav...

Subsonic 2.0.3: Save() causes problem due to identity field constraint

Here's a strange one. I'm using SubSonic 2.0.3 to insert a new row into a given table. The table includes an int identity field that is set up properly in the database (Identity seed = 1, Identity increment = 1). Obviously I do not explicitly set this value before calling .Save(). Ever since I rebuilt my development DB (copying fro...

How do I use OData Expand like a SQL join?

I'm trying to figure out how to accomplish the equivalent of: select * from Users u inner join Comments c on c.UserId = u.Id where Id = 1569 (table aliases for better sql readability) ...on the StackOverflow OData endpoint. How would this url be constructed? I'm looking at the documentation for Expand at OData.org and I would have th...

How to reverse a string after tokenizing it in SQL

Hi, I need to tokenize a string and reverse it in SQL. For example if the string is, 'L3:L2:L1:L0', i need to reverse it as 'L0:L1:L2:L3'. The tokenizing could be done using a delimiter ':' and then reverse it. Please suggest a Function in SQL for the same. Thanks in advance, Geetha ...

Optimal image storing?

Possible Duplicate: manage uploaded files in database or file system? Hi! I'm currently developing a website I just want to know of it is more optimal to store images in a table(MySQL) or to store the reference to an image in the table ? In the latter, the image is stored in a directory in a server instead of SQL-database M...

Best way to show the SQL trace of a LINQ query to Entity Framework 3.5

Best way to show the SQL trace of a LINQ query to Entity Framework 3.5? I am using ASP.net and EF 3.5. Dim dbo As Web.Portal.RBMEntities = New Web.Portal.RBMEntities Dim Query = From RoleAllocations In dbo.RoleAllocations Where RoleAllocations.user_id = _ID And RoleAllocations.expire_date > Today Select RoleAllocations ' Console writ...

Is there any Query to find the SP which contains errors?

I am working in Sql Server 2008 and there is a change of table in my database. So I need to know which all the queries I am using that table or I need to know which all queries will throw errors.I has changed some SP to work fine.But there is a lot of queries which may cause errors.So If there is possible to find which all queries will r...

How to bulk copy sql (export) tables to a csv or tsv files in .Net?

Are there any .Net libraries that provide a way of exporting sql database data to plain text files (i.e. csv\tsv)? SqlBulkCopy only applies to the import part of the solution and I prefer not to be calling any Process.Start calls to open up command prompts to call bcp.exe. ...

Find duplicates or more in Mysql, delete them except the first one inputtet.

I have a table with rows like id, length, time and some of them are dublicates, where length and time is the same in some rows. I want to delete all copys of the first row submitted. id | length | time 01 | 255232 | 1242 02 | 255232 | 1242 <- Delete that one I have this to show all duplicates in table. SELECT idgarmin_track, length ...

Fill Grid from 2 datatable or adding column on runtime

i have 3 tables enquiryDetail CourseMast and FeeDetail i have to show the information of student with coursefee and remaining and deposite fee i m using the query select Name,MobileNo,CourseName,CourseFees from [AsahEnquiry].[dbo].EnquiryDetail e,[AsahEnquiry].[dbo].CourseMast c where e.CourseId=c.CourseId AND Admitted='Yes' which ...

MySQL Error? Where the syntax mistake?

Tried thus query with "SELECT *" instead of "DELETE FROM" and it worked perfectly. DELETE FROM `80dage_garmin_track` t1 WHERE EXISTS ( SELECT 1 FROM `80dage_garmin_track` t2 WHERE t1.Length = t2.Length AND t1.Time = t2.Time AND t1.idgarmin_track > t2.idgarmin_track ) MySQL Error: .#1064 - You have an error in your SQL syntax; chec...

Sql server To use nvarchar or bigint

This might be a stupid question. I need to have a Id field in a table. This Id is not a key (primary or whatever). The requirement says that user can input 10 digits in this field from the UI. This Id has no relation as of now with any otehr object in the system. Do you think If one can use nvarchar or bigint for this field. Does bigint ...