SQL - how to store and navigate hierarchies
What are the ways that you use to model and retrieve hierarchical info in a database? ...
sql
Generate field in MySQL SELECT
If I've got a table containing Field1 and Field2 can I generate a new field in the select statement? For example, a normal query would be: SELECT Field1, Field2 FROM Table And I want to also create Field3 and have that returned in the resultset... something along the lines of this would be ideal: SELECT Field1, Field2, Field3 = 'Valu...
group_concat query performance
i am having serious performance problems (queries up to 55 seconds) while running a mysql query since i added a group_concat clause. my query looks like: select ... group_concat(distinct category.name) .... from page where left outer join page_category on page.id = page_category.page_id left outer join category on page_category.cat...
"Similar Posts" like functionality using MS SQL Server?
I have lots of article store in MS SQL server 2005 database in a table called Articles- "Articles (ArticleID, ArticleTitle, ArticleContent)" Now I want some SP or SQL query which could return me similar Article against any user's input (very much like "Similar Posts" in blogs OR "Related Questions" in stackoverflow). The matching shou...
What generic techniques can be applied to optimize SQL queries?
What techniques can be applied effectively to improve the performance of SQL queries? Are there any general rules that apply? ...
Languages other than SQL in postgres
I've been using PostgreSQL a little bit lately, and one of the things that I think is cool is that you can use languages other than SQL for scripting functions and whatnot. But when is this actually useful? For example, the documentation says that the main use for PL/Perl is that it's pretty good at text manipulation. But isn't that m...
Best way to do multi-row insert in Oracle?
I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle. INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE) V...
Implementing a '20 Questions'-like Wizard in a Database
I am looking to implement a data-driven wizard with the question tree stored in an Oracle database. What is the best schema to use to make the database portion flexible (i.e. easy to add new paths of questions) without sacrificing too much in terms of performance? ...
How do I do multiple updates in a single SQL query?
Okay, here's the case: I have an SQL query like this: UPDATE foo SET flag=true WHERE id=? Now, I have a PHP array, which has a list of IDs. Is there a neat way of doing this, outside parsing something like: foreach($list as $item){ $querycondition = $querycondition . " OR " . $item; } and using that in the where clause? ...
Is it okay to have a lot of database views?
I infrequently (monthly/quarterly) generate hundreds of Crystal Reports reports using Microsoft SQL Server 2005 database views. Are those views wasting CPU cycles and RAM during all the time that I am not reading from them? Should I instead use stored procedures, temporary tables, or short-lived normal tables since I rarely read from m...
*= in Sybase SQL
I'm maintaining some code that uses a *= operator in a query to a Sybase database and I can't find documentation on it. Does anyone know what *= does? I assume that it is some sort of a join. select * from a, b where a.id *= b.id I can't figure out how this is different from: select * from a, b where a.id = b.id ...
vb.net object persisted in database
How can I go about storing a vb.net user defined object in a sql database. I am not trying to replicate the properties with columns. I mean something along the lines of converting or encoding my object to a byte array and then storing that in a field in the db. Like when you store an instance of an object in session, but I need the info ...
Microsoft T-SQL to Oracle PL/SQL translation
I've worked with T-SQL for years but i've just moved to an organisation that is going to require writing some Oracle stuff, probably just simple CRUD operations at least until I find my feet. I'm not going to be migrating databases from one to the other simply interacting with existing Oracle databases from an Application Development per...
SQL Number Formating
I'm looking to use SQL to format a number with commas in the thousands, but no decimal (so can't use Money) - any suggestions? I'm using SQL Server 2005, but feel free to answer for others as well (like MySQL) ...
How do you check your URL for SQL Injection Attacks?
I've seen a few attempted SQL injection attacks on one of my web sites. It comes in the form of a query string that includes the "cast" keyword and a bunch of hex characters which when "decoded" are an injection of banner adverts into the DB. My solution is to scan the full URL (and params) and search for the presence of "cast(0x" and i...
SQL Server Views, blessing or curse?
I once worked with an architect who banned the use of SQL views. His main reason was that views made it too easy for a thoughtless coder to needlessly involve joined tables which, if that coder tried harder, could be avoided altogether. Implicitly he was encouraging code reuse via copy-and-paste instead of encapsulation in views. The d...
Best way to get identity of inserted row?
What is the best way to get identity of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? ...
RDMS for C language newbie?
what database system should a beginner in c language to use? Can i use MySQL? Thanks ...
A python web application framework for tight DB/GUI coupling?
I'm a firm believer of the heretic thought of tight coupling between the backend and frontend: I want existing, implied knowledge about a backend to be automatically made use of when generating user interfaces. E.g., if a VARCHAR column has a maximum with of 20 characters, there GUIs should automatically constrain the user from typing mo...
SQL Profiler on SQL Server 2005 Professional Edition
I want to use SQL Profiler to trace the queries executed agains my database, track performance, etc. However it seems that the SQL Profiler is only available in the Enterprise edition of SQL Server 2005. Is this the case indeed, and can I do something about it? ...