How do I stop MSSQL truncating text when accessed from php?
When I try to retrieve the contents of a Text field from mssql in php I only get back part of the contents. I think its about the first 4000 characters. How do I stop this. ...
sql
LINQ connecting Many - Many relationships
Hello all, I am using c#.net and LINQ Within my LINQ model I have three database tables: The code below is how I access the data: public IQueryable<tblAvailability> GetAvailabilitiesBySet(int id) { return (from a in dc.tblAvailabilities where a.asID == id select a).DefaultIfEmpty(); ...
primary key declaration error while creating table
CreateL() { _LIT(KSQLCountry, "CREATE TABLE Country(CountryID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,CountryName VARCHAR(45) NOT NULL,CountryCode VARCHAR(10) NOT NULL)"); User::LeaveIfError(iDatabase.Execute(KSQLCountry)); } while creating table i want to declare for primary key and foreign key which showing run time error (it cr...
Parsing SQL with Python
I want to create a SQL interface on top of a non-relational data store. Non-relational data store, but it makes sense to access the data in a relational manner. I am looking into using ANTLR to produce an AST that represents the SQL as a relational algebra expression. Then return data by evaluating/walking the tree. I have never implem...
LINQ to SQL - How to add a where clause to a left join?
This LINQ query expression emits a left join and works: from p in Prices join ip in ItemPrices on new { p.PriceId, ItemId = 7 } equals new { ip.PriceId, ip.ItemId } into priceItemPrice from pip in priceItemPrice.DefaultIfEmpty() select new { pricesPriceId = p.PriceId, z = (int?)pip.PriceId, p.Content, p.Pri...
Use of MD5(URL) instead of URL in DB for WHERE.
I have a big MySQL InnoDB table (about 1 milion records, increase by 300K weekly) let's say with blog posts. This table has an url field with index. By adding new records in it I'm checking for existent records with the same url. Here is how query looks like: SELECT COUNT(*) FROM `tablename` WHERE url='http://www.google.com/'; Curren...
reverse engneering a query
I have the following query: SELECT a.catalogID, d.catalogFileID, e.catalogFileID FROM catalog_topics a LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID LEFT JOIN catalog_files d ON d.catalogFileID = c.catalogFileID LEFT JOIN catalog_files e ON e.catalogFileID ...
Which is better: Bookmark/Key Lookup or Index Scan
I know that an index seek is better than an index scan, but which is preferable in SQL Server explain plans: Index seek or Key Lookup (Bookmark in SQL Server 2000)? Please tell me they didn't change the name again for SQL Server 2008... ...
Best practice on users/roles on SQL Server for a web application
I searched online a bit and couldn't find anything that really nailed the spot or covered the bases how to go about setting up users/roles on a database. Basically, there would be a user that would be used to access the database from the application (web application in this case) that will need access to database for the regular databas...
Can layered subqueries spanning multiple tables reference a parent query?
I have three tables setup: POSTS, DISCUSSIONS, and COMMENTS. The layout is as follows: POSTS id | user_id | title | body | created DISCUSSIONS id | post_id | user_id | title | body | created COMMENTS id | post_id | discussion_id | user_id | title | body | created A post hasMany DISCUSSIONS, which then hasMany COMMENTS. What I need ...
Return temp table of continuous dates
I need to create a function that returns a table of continuous dates. I would pass in a min & max date. I expect it to be able to be called like this: SELECT * FROM GetDates('01/01/2009', '12/31/2009') I currently have a stored proc that does this, but requirements changed and now I need to do include the returned data from within a ...
Merge SQL Server databases
I'm attempting to merge multiple SQL Server databases into one. The problem is that one of the tables has an identity field as the primary key. The table also has two uniqueid fields. Something like: datatable: id pk identity link1 uniqueid link2 uniqueid My initial thought was to run the following script: declare @maxId as int set @...
Why does 'HASH JOIN' or 'LOOP JOIN' improve this stored proc?
I have a basic query that goes from 6 seconds to 1 second just by changing one join from LEFT JOIN to LEFT HASH JOIN or 'LEFT LOOP JOIN'. Can anyone explain why this would cause such a large increase in performance and why SQL's optimizer isn't figuring it out on it's own? Here is roughly what the SQL looks like: SELECT a.[ID] FROM...
SQL statement to insert record into table that has identity column?
Hello SO: I am attempting to insert a row manually into my SQL Server data table. This table has 5 columns, one identity and four data columns. I looked at this post, but when I run the selected answer's query (after replacing 'GroupTable' with my table name of course), my SQL Server 2005 management studio crashes. I have tried variati...
How do I create a login system for an iPhone application.
Hi guys, I'm trying to make a login system for my cocoa app. How would I do this? I know it involves SQL, but I know nothing of SQL. I want the user to register or login. It would be easier if apple had a source code for this kind of thing, but I don't think they do. Best Regards, Kevin ...
Query most recent TWO entries per widget
I have two tables. One (Widgets) has a list of widgets (ID, widget_name, color, etc...) and data about them. The other one (Tests) has a list of tests run on the widgets (ID, date, info1, info2, etc...). What I want to do is display the most recent TWO tests. I dont think i really need to use the table Widgets for this but i described ...
How to create a view that explodes a csv field into multiple rows?
I have a table structured as: create table a ( a bigint primary key, csv varchar(255) ) I would like to be able to query a view ("b") such that: select * from b; Yields something like: a | b ------ 1 | A 1 | B 1 | C For the case where the initial table has one row of data (1, 'A,B,C'). Is this possible with a postgres vie...
Preg_replace solution for prepared statements
Hi Guys, I have a command class that abstracts almost all specific database functions (We have the exactly same application running on Mssql 2005 (using ODBC and the native mssql library), MySQL and Oracle. But sometimes we had some problems with our prepare method that, when executed, replaces all placeholders with their respective val...
Best way to calculate Max/Min of N columns in SQL Server
Ok, firstly I've seen this thread. But none of the solutions are very satisfactory. The nominated answer looks like NULLs would break it, and the highest-rated answer looks nasty to maintain. So I was wondering about something like the following : CREATE FUNCTION GetMaxDates ( @dte1 datetime, @dte2 datetime, @dte3 datetime, ...
MySQL Select the Number of Entries that Occur "N" times
Hello All: I've collected a number of entries in a table for a sweepstakes. I've been asked by the client to provide: The number of unique entries The number of entries that occur twice. The number of entries that occur three times. I'm able to determine the number of unique entries, but not sure how to pull out the number of entri...