sql

Isolation level and DIRT READ SQL SERVER 2005 (advanced question)

Hi all! I will describe my problem for an easier explanation: I have a table and my soft is accessing it (update, insert) using transaction. The problem is that I want to enable DIRT READ in this table. But I cant use with (nolock) in my sql statements because I cant change the soft source. So I was thinking in enable dirty read in th...

How to display MySQL Select statement results in PHP

I have the following code and it should return just one value (id) from mysql table. The following code doesnt work. How can I output it without creating arrays and all this stuff, just a simple output of one value. $query = "SELECT id FROM users_entity WHERE username = 'Admin' "; $result = map_query($query); echo $result; ...

How to optimize an SQL query with many thousands of WHERE clauses

I have a series of queries against a very mega large database, and I have hundreds-of-thousands of ORs in WHERE clauses. What is the best and easiest way to optimize such SQL queries? I found some articles about creating temporary tables and using joins, but I am unsure. I'm new to serious SQL, and have been cutting and pasting result...

MySQL LEFT JOIN error

Hello, I've got some SQL that used to work with an older MySQL version, but after upgrading to a newer MySQL 5 version, I'm getting an error. Here's the SQL: SELECT portfolio.*, projects.*, types.* FROM projects, types LEFT JOIN portfolio ON portfolio.pfProjectID = projects.projectID WHERE projects.projectType = types.typeID AND types.t...

Php function within SQL statement syntax

I have the following code. I would like username to take the value of the getUserName function however I am fighting with syntax. Can anybody tell me what should be the correct one? $query = "SELECT user FROM users_entity WHERE username = getUserName()"; ...

PHP calling PostgreSQL function - type issue?

I have a function in PostgreSQL / plpgsql with the following signature: CREATE OR REPLACE FUNCTION user_login(TEXT, TEXT) RETURNS SETOF _get_session AS $$ ... $$ Where _get_session is a view. The function works fine when calling it from phpPgAdmin, however whan I call it from PHP I get the following error: Warning: pg_query() [functi...

Getting DateTimeOffset value form SQL 2008 to C#

I have a SQL 2008 table with a field called RecDate of type DateTimeOffset. For a given record the value is '2010-04-01 17:19:23.62 -05:00' In C# I create a DataTable and fill it with the results of "SELECT RecDate FROM MyTable". I need to get the milliseconds, but if I do the following the milliseconds are always 0: DateTime...

How do I remove the first 8 characters from a Microsoft Access database field?

Hi, I've got an Access '07 database that imports data from text files. The problem is one of the fields that is taken in comes with a date value before a place value that I don't need. For example, the field comes in like so: 01012010DUBLIN whereas I need it to just display DUBLIN. I'm stumped on this and any help would be greatly appre...

SQL left join automatic aliasing?

I just realized that I'm going to have to start aliasing my database calls due to repeating column names in my join tables. Is there a way to automatically tell SQL to alias all my column names so that they are returned with a prefix of the table name? Otherwise it appears to be quite confusing when only some of them are aliased. Just ...

SQL View Creation

I have two tables Table FOO FooUniqueID| Year| Name| Worth| --------------------------- 1 2008 Bob 23.00 2 2009 Bob 40200 Table Bar BarUniqueID | Name | Value ----------------------- 1aBc Year 2009 I would like to create a view. That will select everything from FOO where the Year is equal ...

django: How to make copy of one of the objects in the db

Hi, I wonder if there is a way to make a copy of one db item? e.g. I have a model class Category(models.Model): slug = models.CharField(max_length=200) name = models.CharField(max_length = 200) So if I added one item, how can i clone it 100 times for testing? ...

How to get use text columns in a trigger

I am trying to use an update trigger in sql 2000 so that when an update occurs, I insert a row into a history table, so I retain all history on a table: CREATE Trigger trUpdate_MyTable ON MyTable FOR UPDATE AS INSERT INTO [MyTableHistory] ( [AuditType] ,[MyTable_ID] ,[Inserted] ...

MS Acess SQL - where clause to get year from date based on the year - data located in MS access form

OK, back again. I have a problem getting a drop down list to populate based on information in two fields. I have got the SQL correct as to Select just one year if I put DateValue('01/01/2001') in both places, but I am trying now to get it to grab the year data from the MS access form - another drop down named "cboYear". I'd hate to ha...

Updating rows using "in" operator in "where" clause

Hi. I stumbled upon SQL behavior I don't understand. I needed to update several rows in a table at once; started with just finding them: SELECT * FROM some_table WHERE field1 IN (SELECT ...) This returned a selection of about 60 rows. Now I was pretty confident I got the subquery right, so I modified the first part only: UPDATE so...

SQL update a table with a foreign key

I have a categories table, which one of the fields serves as the foreign key for a sub-categories table. One field that serves as part of the primary key for each table is the language id. I need to update these in both tables. Basically, wherever the language id = x in both tables, I need to set it to y. When I try to do an update...

Displaying zero valued months with SQL

The following query returns values for months with sales but not for months where sales are zero: SELECT DATE_FORMAT(O.OrderDate, "%b, %Y") AS MonthYear, SUM(O.Total * C.Rate) AS Sales FROM (Orders O INNER JOIN OrdersStates OS ON O.OrderID = OS.OrderID) INNER JOIN Users U ON U.UserID = O.UserID INNER JOIN UsersDescription UD ON U.UserI...

Using NHibernate to select entities based on activity of children entities

I'm having a case of the Mondays... I need to select blog posts based on recent activity in the post's comments collection (a Post has a List<Comment> property and likewise, a Comment has a Post property, establishing the relationship. I don't want to show the same post twice, and I only need a subset of the entities, not all of the pos...

Select from table and function

I have a function that returns a table. The returned table contains (among other things) a store_id. I can get the store_id for a particular transaction_id and city_id as follows: select store_id from table(user2.f_get_store(15, 12345)); --where 15 and 12345 are city_id and transation_id I have another table that contains a list of...

How to save a Stored Procedure ?

Hello, I've been playing today with stored procedures. I'm stoned that I picked the basic of it up so easily. (I'll also try triggers very soon). Now, I'd like to know how to save my SPROC under the stored procedure folder (Programmability -> Stored Procedure) so that I can access it by name (eventually from my C# application). So far,...

getting a combo box that has a row source equal to a query - and the query takes data from a form - to update

I have a combo box with a row source based on an SQL query about like SELECT DISTINCT Database_New.ASEC FROM Database_New WHERE Database_New.Date>= DateSerial([cboYear], 1, 1) And Database_New.Date<= DateSerial([cboYear], 12, 31); the trouble is that if I change the value of cboYear, the values in the drop down cboASEC do n...