What alternatives do I have to implement a union query using hibernate? I know hibernate does not support union queries at the moment, right now the only way I see to make a union is to use a view table. The other option is to use plain jdbc, but this way I would loose all my example/criteria queries goodies, as well as the hibernate ma...
Hi, I try to read a binary field in database ( Project Server 2007; dbo.MSP_CALENDAR ). MS don't implemante any fonction into PSI. Everybody have a suggestion? Thanks in advance SPo In Database Piblished, in table dbo.MSP_CALENDAR, i found a field named CAL_DATA, this field is BINARY. I try to CAST or CONVERT this field into s...
I've recently been posting quite a few questions about database design and I've got some great answers. However, lots of the things that have been mentioned I've never heard of! These things include triggers on databases, constraints (using what looks like a SQL CHECK keyword) and various other things like that. I guess this is to be ex...
I like the flexibility of Dynamic SQL and I like the security + improved performance of Prepared Statements. So what I really want is Dynamic Prepared Statements, which is troublesome to make because bind_param and bind_result accept "fixed" number of arguments. So I made use of an eval() statement to get around this problem. But I ge...
I've been working on database-driven web applications for a few years now and recently took on a project involving a CMS that is XML-capable. This has got me to thinking about the usage of XML/XSLT in general and in what situations it would be more useful than the approach I've always used, which is storing all of my data in a (My)SQL da...
On large tables in MSSQL; selecting specific columns results in greater speed of the query. Does the same apply to Linq to SQL? Would this: var person = from p in [DataContextObject].Persons where p.PersonsID == 1 select new { p.PersonsID, p.PersonsAdress, p.PersonsZipcode }; be faster than this: var person...
I have an SQL Server DB with a table with these fields: A bit with the default value 1, NOT NULL. A smalldatetime with the default value gettime(), NOT NULL. An int with no default value, IDENTITY, NOT NULL. When I generate Linq to SQL for this table, the following happens: The bit is given no special treatment. The smalldatetime i...
I have a SQL Server 2000 database instance that is rarely updated. I also have a database table which has no columns holding each row's created date or modified date. Is there any way that I can determine the last time an update or insert was performed on the database as a whole, so that I can at least put a bound on when the specifi...
I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet. These 2 extra columns are the result of conversions on a field in the table which is a Datetime field. The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S' The 2 additional fields which should be i...
I'm looking for a way to sequentially number rows in a result set (not a table). In essence, I'm starting with a query like the following: SELECT id, name FROM people WHERE name = 'Spiewak' The ids are obviously not a true sequence (e.g. 1, 2, 3, 4). What I need is another column in the result set which contains these auto-numbering...
I am building a C#/ASP.NET app with an SQL backend. I am on deadline and finishing up my pages, out of left field one of my designers incorporated a full text search on one of my pages. My "searches" up until this point have been filters, being able to narrow a result set by certain factors and column values. Being that I'm on deadline...
This is what I currently have: CREATE OR REPLACE TRIGGER MYTRIGGER AFTER INSERT ON SOMETABLE FOR EACH ROW DECLARE v_emplid varchar2(10); BEGIN SELECT personnum into v_emplid FROM PERSON WHERE PERSONID = :new.EMPLOYEEID; dbms_output.put(v_emplid); /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values...
I have some hierarchical data - each entry has an id and a (nullable) parent entry id. I want to retrieve all entries in the tree under a given entry. This is in a SQL Server 2005 database. I am querying it with LINQ to SQL in C# 3.5. LINQ to SQL does not support Common Table Expressions directly. My choices are to assemble the data in ...
I am using sql server 2005. I just want to know is there something we can get the week number for the particular month. Example Date = '2008-10-16' Is there something we can have SELECT WEEK(Date). Or any better suggestion. ...
I've ran some "ALTER" scripts on the database [SQL Server Server 2005], and overwrote some sprocs. Is there any way to undo changes and get my old sprocs back? Is there a way to get the scripts that were executed out of the .LDf file? That way i can re-run my initial "create" script for my sprocs. I don't have a backup file with old sp...
I have a table of items, each of which has a date associated with it. If I have the date associated with one item, how do I query the database with SQL to get the 'previous' and 'subsequent' items in the table? It is not possible to simply add (or subtract) a value, as the dates do not have a regular gap between them. One possible app...
I have a table in my database which stores a tree structure. Here are the relevant fields: mytree (id, parentid, otherfields...) I want to find all the leaf nodes (that is, any record whose id is not another record's parentid) I've tried this: SELECT * FROM mytree WHERE `id` NOT IN (SELECT DISTINCT `parentid` FROM `mytree`) But th...
What is the equivalent of the extended procedure 'xp_dirscan' in SQL Server 2005? ...
I've seen the question (and answer) when posed for MS SQL Server, though I don't yet know of one for Oracle and PL/SQL. Are there xUnit style testing frameworks for Oracle's PL/SQL? What are they? ...
I've got two models, joined by a Has and Belongs To Many join table. Lets call these models User and Event. The majority of Users have 0 events, while few have one or more. I want to do something like: User.find(:all, :joins => :events, :conditions => ["'Something that count the events' > ?"], 0) The problem is, I'm not sure how to s...