Convert timestamp to a readable date during query
How can I tell MySQL to format a timestamp as a readable date before outputting a query result in a MySQL client/console? ...
sql
what is wrong with this sqlite3 statement
Hi, I have written this sqlite statement and I am getting syntax error: update List SET number = (CASE WHEN number>=3 then number++ WHEN number=1 then 3 ELSE number END) WHERE listKey=3; I keep on getting : SQL error: near "WHEN": syntax error I tried various versions, adding braces at places and all, but can't figure out the erro...
sql result set to Output JSP page
I have a sql query fired in my model class which returns a table of user data. Now i need to display the result on my view page (JSP). I don't want to fire the sql from JSP page. How do i pass of the result set to JSP? do i need to construct a collection set and pass it on to the JSP or is there any better way to do it? Thanks, -Vijay ...
Problems with checking particular columns of uploaded data against the existing database
I have got an upload button on my webportal through which the customers can upload a csv file. After uploading the .csv file the csv data will be displayed on datagrid view on the screen, After uploading, the customer is suppose to verify data by clicking on verify button which basically checks the uploaded data have got the right data i...
PHP & SQL: Best way to protect this query from SQL injections NOT using PDO
Hi, I have a query which is run against a mssql database and I'm not using PDO drivers. Is there something like prepared statement i can use? Here is the query: $tsql = "INSERT INTO cplinktable (liferayid, bmsid, autotaskid, waspdb, cpid) VALUES ($liferayid, $bmsid, $autotaskid, '$waspdb', $cpid)"; thanks, Jonesy ...
How to have PHPUnit do something if an assertion fails
I'm testing that a function properly adds data to a db, but I want the test data removed after the test is finished. If the test fails, it quits at the failure and never gets the chance to delete the test rows. It's the only test that hits the db, so I don't really want to do anything in the tearDown() method. I'm testing an $obj->sav...
How to insert into tempoary table twice
I've picked up some SQL similar to the following: IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name Like N'#tmp%' and id=object_id('tempdb..#tmp')) DROP TABLE #tmp into #tmp select * from permTable I need to add more data to #tmp before continuing processing: insert into #tmp select * from permTable2 But this gives errors ...
A good way to encrypt database fields? - Django
Hi folks, I've been asked to encrypt various db fields within the db. Problem is that these fields need be decrypted after being read. I'm using Django and SQL Server 2005. Any good ideas? ...
writing a sql query in MySQL with subquery on the same table
I have a table svn1: id | date | startdate 23 2002-12-04 2000-11-11 23 2004-08-19 2005-09-10 23 2002-09-09 2004-08-23 select id,startdate from svn1 where startdate>=(select max(date) from svn1 where id=svn1.id); Now the problem is how do I let know the subquery to match id with the id in the outer query. Obviously id=svn...
Django convert Model to sql code
Hi, My plan is to be able to dynamically generated sql tables. Since I know django can create sql tables via Models, they must have a function for this. The problem is I can not find this function in the django folder, or maybe I simply don't recognize it. thnx ...
variable decimal places in sql
How do you make it so that all calculations in the DB compute to a pre-specified # of decimal places? Say I have three tables with the following fields Table1 A int B decimal(18, 3) Table2 A int B decimal (18, 2) C decimal (18, 3) Table3 A int Precision int Now I need to change it so that all my calculations are based on w...
SQL Server 2005: T-SQL INSERT INTO and OUTPUT timestamp to a variable
Example: IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1; GO CREATE TABLE T1 (id int PRIMARY KEY, timestamp); GO INSERT INTO T1(id) VALUES (1); GO declare @v timestamp; INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10); select @v How can I get the inserted.timestamp into variable @v? ...
Mysql Query question
Hiya, I'm trying to work out the following query: SELECT did_numbers.did_dialstring, netareas.netarea_name, did_numbers.did_size, CASE WHEN (reseller_id < 1) IS NULL THEN 'F' ELSE 'T' END as reseller, COUNT(*) AS aantal FROM did_numbers ...
How can I find all columns that are filled with 100% nulls in my SQL Server database schema?
Is there a SQL way to find out which columns in my schema are completely full of null values? There are some fields in a couple of tables that I know are not used by the application and will be dropped, but I wanted to see if there was an automated way/script to find this out across the whole database to find candidates for code review/...
Update database from model - Entity Framework
Is it possible in any way to just update the database schema from entity model..? So that all the information in the database stays in the database? When you generate database from model the information will get lost.. I work against SQL Azure, and I have not found any tool to manage the tables and realations in the SQL Azure database in...
sql server conditional select into temp table
I'm working in SQL Server 2008, and I'm trying to select into a temp table based on a certain condition...for a report, I need up to 18% of the records to be of a certain product type. if ((@totalRecords * .18) > @productTypeCount) select * into #tmpLP_REIT from myTable where productType = @productType else select top 18 per...
return records in sql server which has data in a particular format
Hi Guys, I have a SQL database table column which has data in this format "0000-00-0000" for ex: "1234-98-2010" For it has data in other formats as well. I need to pick out all the records where the format is "0000-00-0000" irrespective of the data. Its the format which i am after Please, can some one advise me on how i can pick th...
update sql column with same data but with a little modifications to it
I have a sql DB table columns which carries data in "0000-00-0000" format. For ex: "8753-11-2010" Now i need to change this value from "8753-11-2010" to "008753-0011-2010" i.e. i need to pad "00" in front of "8753" and "11" i mean the first two strings u can call it. please advise how i can achieve this in sql server 2005. I need to ...
SQL subquery matches hard-coded IN criteria, but not subquery
I have a group of people who have taken a test. I can select their IDs with this query: SELECT person_id FROM tests WHERE test_code = 1234 I'd like to pull these individuals' records from a demographics table, so I tried this subquery to do so: SELECT * FROM demographics d WHERE d.person_id IN ( SELECT t.person_id FROM tests...
How to compare two tables column by column in oracle
I have two similar tables in oracle in two different databases. For example : my table name is EMPLOYEE and primary key is employee id. The same table with same columns(say 50 columns are is avlbl in two databases and two databases are linked. I want to compare these two tables column by column and find out which records are not matchi...