Get the type of a variable in MySQL
If I define a variable like set @a = "1";. How can I see that @a is a string? ...
sql
Duplicate Schema
I am creating and maintaining my SQL Server 2008 warehouse database using a Visual Studio 2008 project. When I try to deploy the project, I get an error as below Creating DataWarehouseReports... Company.HigherEducation.DataWarehouse.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 2 There is ...
VARCHARS: 2, 4, 8, 16, etc.? Or 1, 3, 7, 15, etc.?
I see VARCHAR(255) being used all the time instead of VARCHAR(256), but I also see VARCHAR(16) being used instead of VARCHAR(15). This seems inconsistent to me. If an extra byte is being used to store the VARCHAR's length, shouldn't this rule also apply to the smaller lengths like 2, 4, 8 to be 1, 3, 7, instead? Or am I totally missing ...
SQL server ignore case in a where expression
How do I construct a SQL query (MS SQL Server) where the "where" clause is case-insensitive? SELECT * FROM myTable WHERE myField = 'sOmeVal' I want the results to come back ignoring the case ...
WITH Common Table Expression vs. CREATE VIEW performance
I have several queries which use a WITH clause, or Common Table Expression, with a UNION ALL statement to recur through a table with a tree like structure in SQL server as described here. Would I see a difference in performance if I were to CREATE that same VIEW instead of including it with the WITH clause and having it generated every ...
How can I only execute an update command if a condition is true?
I'm working on a DB upgrade script that needs to drop a column (that part's easy). Before I can drop the column, I want to take the value and move it into another column that already exists. I'm trying to acheive idempotence with my script, which is where I seem to be failing. I have code like this... IF EXISTS (SELECT * FROM...
Need REGEXP Matching in MySQL
I want to match a String which looks like this: [lang_de]Hallo![/lang_de][lang_en]Hello![/lang_en]HeyHo[lang_es]Hola![/lang_es] I want the matching to return true if there is text which is not enclosed by lang_tags (in this example: HeyHo). It could also be positioned at the beginning or end of the string. Whitespace should NOT match...
dimensional and unit analysis in SQL database
Problem: A relational database (Postgres) storing timeseries data of various measurement values. Each measurement value can have a specific "measurement type" (e.g. temperature, dissolved oxygen, etc) and can have specific "measurement units" (e.g. Fahrenheit/Celsius/Kelvin, percent/milligrams per liter, etc). Question: Has anyone bui...
How to quote values for LuaSQL?
LuaSQL, which seems to be the canonical library for most SQL database systems in Lua, doesn't seem to have any facilities for quoting/escaping values in queries. I'm writing an application that uses SQLite as a backend, and I'd love to use an interface like the one specified by Python's DB-API: c.execute('select * from stocks where symb...
Conditional statement in WHERE clause
I need to write a conditional statement in my where clause that uses different operators based on the parameter passed into the procedure. I can't seem to find the syntax that will work. My example is as follows: @DateValue datetime select * from table where field1 = 'x' and field2 = 'y' and if @DateValue = '1/1/1900' then field...
SQL query and preg_match
Hi, guys! Need your help with sql query and php. I have to pieces of code here: 1. $sql = "SELECT SUBSTR(n.`title`, 1,1) FROM node n WHERE n.`type` = 'type1'"; $results = db_query($sql); while ($fields = db_fetch_array($results)) { foreach($fields as $key => $value) { echo $value; } } The code above returns first letters o...
Why does MySql allow "group by" queries WITHOUT aggregate functions?
Surprise -- this is a perfectly valid query in MySql: select X, Y from someTable group by X If you tried this query in Oracle or SQL Server, you’d get the natural error message: Column 'Y' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. So how does MySql determine w...
SQL - Two Outer Joins
Hello, I have a table that represents a list of countries. I have another table that represents a list of states. I have another table that represents a list of provinces. Because of poor data definition, some states are actually in the province table and vice-versa. Regardless, each province and state is associated with a country. I ...
Database as game data repository?
Hi, Has anyone had experience writing a game engine or demo engine using something like a SQL database to store game assets? Does it make sense to use in that context? What would be the advantages and disadvantages? Thanks! ...
what is this sql statement doing? (mysql)
SET @row := 0; SELECT * FROM ( SELECT @row := @row+1 AS rank, account_id, keyword, bid_amount ,timestamp FROM bids WHERE keyword='programmers' ORDER BY bid_amount DESC, timestamp ASC ) AS derived_table; i saw this somewhere and i was wondering what the set does along with the @ sign and the := thanks ...
MySQL 1443: what does it mean?
I'm trying to do an update, in MySQL 5.0, of the form: update mytable.myfield t set f = 'blah' where t.id in (select v.id from myview v where ...); MySQL tells me: ErrorNr. 1443 The definition of table 'v' prevents operation UPDATE on table 't'. The MySQL docs list this error, but (as usual) don't say what it means. Can anyone she...
could unique key ( not primary ) be a parent for declarative foreign key constraint
Naive question with the answer "No" , I believe, but still would like to ask. Table_parent pk_parent_surrogate parent_natural_unique_key Table_child pk_child_surrogate child_natural_NOT_unique Is that true that the only possible declarative relationship among main database vendors is pk_parent_surrogate ----------< pk_chi...
help with oracle sql rollup
Hi folks, I've written a query to collect some data to display in an auto-updating box & whisker graph in excel. I'd like to use rollup to create summary rows for each type of train_line (PF and MJ) to include in the excel chart. Can I do that using Rollup? I've tried to get my head around Rollup, but I'm not getting far. I've tried...
select query by range
Hi I want to select values from tables by passing two parameters like start and end ranges How can i get it? Ex: sid sname s001 name1 s002 name2 s003 name3 s004 name4 s005 name5 s006 name6 s007 name7 s008 name8 s009 name9 s0010 name10 here i want to pass two values like 3 and 5 so that my quer...
sql server 2008: bcp_sendrow, issue: invalid time format
Hi! I'm having a problem "Invalid time format" when I try to use bcp functions(bcp_sendrow/bind etc.) to insert into a table having a datetime column. so as per googled examples, I populated a DBDATETIME structure, for example: if I wanted to populate 3rd august 2009, exactly 8am localtime, I'd populate the structure members like so: d...