Hi all, Im working on a project using subsonic 2.1. For a simple search query i've the following code: DAL.ItemCollection coll = new DAL.ItemCollection(); SubSonic.Select s = new SubSonic.Select(); s.From(DAL.Item.Schema); s.Where("Title").Like("%" + q + "%").Or("Tags").Like("%" + q + "%").And("IsAct...
I'm trying to find a way to do a conditional DELETE on an InnoDB table which contains millions of records, without locking it (thus not bringing the website down). I've tried to find information on mysql.com, but to no avail. Any tips on how to proceed? ...
I've got a pretty simple need for a graph, or at least I thought it was. We have a number of time based products and I'd like to have a chart that goes: <1 Hour 45 Mins 30 mins 15min OnTime But not all products have files arriving in every timeframe, so may appear as: 45mins 15mins As you can imaging this makes reporti...
I have a List<MyObject> with a million elements. (It is actually a SubSonic Collection but it is not loaded from the database). I'm currently using SqlBulkCopy as follows: private string FastInsertCollection(string tableName, DataTable tableData) { string sqlConn = ConfigurationManager.ConnectionStrings[SubSonicConfig.DefaultDataP...
I have this table (simplified): CREATE TABLE `my_table` ( `id` INT NOT NULL AUTO_INCREMENT , `item_name` VARCHAR(45) NULL , `price` DECIMAL(10,0) NULL , PRIMARY KEY (`id`) ) I need to select all items from the table, ordered this way: 1. items with price > 0.00 first, ordered by price ASC 2. items with price = 0.00 last, orde...
The problem is that the dates are stored in the SQL database as nvarchar() and the times are stored in a separate column. I only have read access to this database so I can't change the format. Besides, it would probably void our support if the manufacture found out. As I see I need to first combine the dates and times into one cell as a...
Right, so I have a set of dropdowns on my page. Depending on whether a value is selected, I want to add it to an SQL query string in PHP. Example: select1: options("*" "op1", "op2) select2: options("*" "op1", "op2) select3: options("*" "op1", "op2) '*' refers to anything. i.e the data should not be filtered by that query option. Now, ...
I created a user on the SQL Server but using that login I get this message: Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SqlException (0x80131904): Login failed for user ''. The user is not associated with a trusted SQL Server connection.] Here is my connect string: <connectionStrings> ...
I have a bunch of date data in a mysql table like this: 2010-02-13 1:00:00, "soma data" 2010-02-13 1:25:00, "soma data" 2010-02-13 1:37:00, "soma data" 2010-02-13 2:12:00, "soma data" I want to select a report which shows the data grouped by hour, for example: On Feb 13, during the hour from 1:00 pm to 1:59 pm, there were 3 data poin...
I have next approximate tables structure: accounts: ID INT, owner_id INT, currency_id TINYINT related to clients: ID INT and currency_types: ID TINYINT, name NVARCHAR(25) I need to write a stored procedure to check existence of accounts with specific currency and all others, i.e. client can have accounts in specific currency, so...
Hi I have a currency(Rec_ID, currency) table which is having M-M relationship with it self it results in CurrencyExchangeRate table (Rec_ID, Currency_FK1 AS 'FROM', Currency_FK2 AS 'TO, Exchange_Rate) Now I need a query to do all combination of currency rec_id (repetition is not allowed), along with the exchange rate or null. if there ...
I realise different solutions will have different variations of what "Working Days" means but in my case I mean Monday to Friday inclusive. Basically I have Created a function to do the calculation for me and my current solution works. My concern (and reason for asking this question) is that I am worried that this is a bad way of achiev...
Possible Duplicates: Surrogate Vs. Natural/Business Keys When not to use surrogate primary keys? So, I would prefer having an INT as a primary key with AI. But, here is the reason why I am considering making a unique string as a primary key: I don't have to query related tables to fetch the primary key since I will already k...
Hello. I have made this code for giving out +1 point, but it doesn't work properly. mysql_query("UPDATE member_profile SET points= ' ".$points." ' + 1 WHERE user_id = '".$userid."'"); the $points variable is the user´s points right now.. I want it to plus one to it.. so example if he had like 5 points, it should be 5+1 = 6.. but it do...
What is the best database engine independent way to load a huge amount of data. While I'm using SQL Server I use SqlBulkCopy but want to untie from SQL Server ...
I was wondering if SQL Server has an equivalent to Oracle PL/SQL Package? It is really nice to build your sprocs, functions, etc into a Package. ...
Hi I'm new to filemaker - liking it, but taking a while to get used to it I'm creating a solution where customers checkin with their ID card. The first time they do this, I need to link their card to their user record. I have a user table, and want to click a button on the layout for this table to set the ID of a card. The ID of the...
I'm having some trouble getting a search by zip code proximity query working. I've search and searched google but everything I find is either way too slow or I can't get working. Here's the issue: I have a database with a table with all the US Zip Codes (~70,500 of them), and I have a table of several thousand stores (~10,000+), which...
What I am hoping to achieve is the ability to generate 'teams' of users. I will have x amount of men, weighted (decimal skill weight, like 75.23) and y amount of women (also with a skill weight value). Given that list of users, I would then take for input the number of teams to make (let us say, 6 teams). Then, I go through the list o...
Suppose I have this query SELECT * FROM ( SELECT * FROM table_a WHERE id > 10 ) AS a_results LEFT JOIN (SELECT * from table_b WHERE id IN (SElECT id FROM a_results) ON (a_results.id = b_results.id) I would get the error "a_results is not a table". Anywhere I could use the re-use the results of the subquer...