|
Giorgi Dalakishvili wrote: You can use ISNULL
yes, but coalesce is standard, so I prefer it to isnull which is not.
|
|
|
|
|
You can use coalesce function:
select siid as Sid,biid as Bid,coalesce ((select iname from Ol_items where iid=siid),'NO') as SIName,(select iname from Ol_items where iid=biid) as BIName from Ol_Wishlist
|
|
|
|
|
Hey guys
ive got a table in my DB, employee... this table has a column "EndOfEmployment" which is DATETIME and obviously nullable
yesterday while trying to write a query to count the number of "Active" employees i came across a strange issue... the query is as follows
SELECT count(Emp_EmployeeNumber)
FROM Employee
WHERE Emp_EmploymentStatus = 'A' OR (Emp_EmploymentStatus = 'T' AND (Emp_EndOfEmployment = NULL OR Emp_EndOfEmployment < @todaysDate))
i noticed that when the EndOfEmployment field does not get data in the insert statement, it DOES NOT get a value of NULL... so i tried giving it a value of NULL in the insert statement and that still didnt work.
then i tried Emp_EndOfEmployment = '' instead of Emp_EndOfEmployment = NULL in the WHERE clause but still no luck
Does anyone have any ideas?
Thanx
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
You can't normally compare anything to NULL with the = operator (the result isn't true or false, but NULL - anything you do on a NULL is NULL). You need to use IS NULL / IS NOT NULL or COALESCE.
If you KNOW that you're looking for NULL, do this:
... WHERE (thefield IS NULL) OR (thefield < @limit)
or:
... WHERE COALESCE(thefield, '1900-01-01') < @limit
That's assuming you have a suitable "NULL equivalent" value to use (as in 1900-01-01). Note that the YYYY-MM-DD layout is unambiguous and always converts automatically to datetime in SQL, regardless of regional settings. Any other format needs an explicit CONVERT with a style parameter to work internationally. Also - the datetime/smalldatetime are the only times where picking a good minimum value is problematic, because:
datetime: 1753-01-01 is the first valid date.
smalldatetime: 1900-01-01 is the first valid date.
Numeric and string columns are easier.
BONUS:
Sometimes you don't even know what you're looking for (such as in a parameterized query). This is a good one (assuming GroupId can be null but never 0 - as in a foreign key relation with an identity field):
... WHERE COALESCE(GroupId, 0) = COALESCE(@param1, 0)...
Figuring that out a) saved me a lot of IFs and CASEs, and b) made me feel pretty stupid about not having done it sooner...
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
How do I obtain the size of a particular database schema, not just a table, programatically using C# or VB.NET? Thanks.
|
|
|
|
|
I'm assuming that you use MS SQL Server,
Execute the sp_helpdb 'DBNAME' stored procedure. It will return 2 result sets, the first contains the db_size field which is the total size (data and log) of the database.
|
|
|
|
|
Hi all.
I have written a simple application in vb.net (2.0) that reads data from an excel sheet (previously stored in focus databases) and stores it into a sql server database. I am using typed datasets and table adapters to store the data. I have an "Import" button whose event handler does the following in turn.
1. connect to the excel sheet through an ole db conn
2. read the contents in a while loop using the executeReader() method
3. store the values in variables where necessary conversions and validations are performed.
4. call an insert method in the associated dataset tableAdapter that stores these processed values in the sql db.
This is all working as it should. Now I need to check for existing records before I import the values - Ignore completely if the value is found.
According to msdn, I should be able to use my tableAdapters update method by passing a dataSet/Table/Row(s). So this is how I tried to tackle it.
Legend:
lbf = my DataSet
lbf_COKEnCOLE = the Database table associated with lbf DataSet
GetRecordByIdDateLab = runs the following SQL Query on the data
SELECT *
FROM lbf_COKEnCOLE
WHERE (ORIGINATOR_ID = @ORIGINATOR_ID) AND (SMPL_DTE = @SMPL_DTE) AND (LAB_NUM = @LAB_NUM)
(The actual sql refers to all the columns by names instead of using *)
Dim tableadapter As New lbfTableAdapters.lbf_COKEnCOLETableAdapter
Dim dataTable As lbf.lbf_COKEnCOLEDataTable = Nothing
'check for existing record by using GetRecordByIdDateLab method
dataTable = tableadapter.GetRecordByIdDateLab(ORIGINATOR_ID, SMPL_DTE, LAB_NUM)
If Not dataTable Is Nothing Then
If dataTable.Rows.Count > 0 Then
If Not dataTable(0).ORIGINATOR_ID = Nothing Then
'Row = dataTable(0)
dataTable(0).TURN = TURN
dataTable(0).SMPL_ANALYS_HOUR = SMPL_ANALYS_HOUR
dataTable(0).SMPL_ANALYS_MIN = SMPL_ANALYS_MIN
dataTable(0).MOISTURE = MOISTURE
dataTable(0).S = S
dataTable(0).VM = VM
dataTable(0).ASH = ASH
dataTable(0).HARDNESS = HARDNESS
dataTable(0).STABILITY = STABILITY
dataTable(0).QRT_TUMBLE = TUMBLE_30M
dataTable(0).APTSPC_GRAV = APTSPC_GRAV
dataTable(0).FREE_SWL_I = FREE_SWL_I
dataTable(0).PULV8TH = PULV8TH
dataTable(0).QRT_PULV = QRT_PULV
dataTable(0).SCR_4 = SCR_4
dataTable(0).SCR_3 = SCR_3
dataTable(0).SCR_2 = SCR_2
dataTable(0).SCR_1NHALF = SCR_1NHALF
dataTable(0).SCR_1 = SCR_1
dataTable(0).SCR_3QRT = SCR_3QRT
dataTable(0).SCR_HALF = SCR_HALF
dataTable(0).SCR_38THS = SCR_38THS
dataTable(0).SCR_QRT = SCR_QRT
dataTable(0).QRT_PULV = QRT_PULV
dataTable(0).SCR_8TH = SCR_8TH
dataTable(0).SCR_20M = SCR_20M
dataTable(0).SCR_30M = SCR_30M
dataTable(0).SCR_50M = SCR_50M
dataTable(0).SCR_100M = SCR_100M
dataTable(0).SCR_PAN = SCR_PAN
dataTable(0).SCR_QRT_PLUS = SCR_QRT_PLUS
dataTable(0).SAMPL_LOC = SAMPL_LOC
tableadapter.Update(dataTable) '<<THIS IS WHERE IT CRASHES WITH "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll
End If
'The following tableAdapter works perfectly whenever the condition is true (for new entries that is)
ElseIf dataTable.Rows.Count = 0 Then
tableadapter.InsertQuery(counter, ORIGINATOR_ID, SMPL_DTE, LAB_NUM, TURN, SMPL_ANALYS_HOUR, SMPL_ANALYS_MIN, _
MOIS_GRAV, MOISTURE, S, VM, ASH, HARDNESS, STABILITY, QRT_TUMBLE, TUMBLE_30M, APTSPC_GRAV, _
FREE_SWL_I, PULV8TH, QRT_PULV, SCR_4, SCR_3, SCR_2, SCR_1NHALF, SCR_1, SCR_3QRT, SCR_HALF, _
SCR_38THS, SCR_QRT, SCR_8TH, SCR_20M, SCR_30M, SCR_50M, SCR_100M, SCR_PAN, SCR_QRT_PLUS, SAMPL_LOC)
End If
End If
****************************************
More details:
I have DataTable under "Watch" and the value for it shows up as "dataTable has not been declared". This ofcourse is misleading because it is, plus intellisense picks it up where I'm updating the column values by referencing them with dataTable(0).columnName
I have been wondering if
dataTable = tableadapter.GetRecordByIdDateLab(ORIGINATOR_ID, SMPL_DTE, LAB_NUM)
does what its supposed to do, ie. return a dataTable with the matching record. I tried to recieve the data in a row but it only allowed it to be saved in a datatable instantiated in this manner. Furthermore, when I read the values read into the datatable, each column value shows up only as lbf_COKEnCOLE.column (something to this effect) in curly braces. Almost everything else thats returned, like index values and what not that I suppose is used to keep the schema of the table has errors in it.
Any suggestions and help would be greatly appreciated!! I am trying to avoid suddenly using command objects when I'm doing everything else through a data Access layer. lastly, I have read and reread this msdn page
http://msdn.microsoft.com/en-us/library/ms233819(VS.80).aspx
and am trying to do everything as shown here, obviously to no avail 
All .net people out there, if you would help this awkward programmer, it would be greatly appreciated! Thanks
~Ghazanfar
|
|
|
|
|
Hi,
We have problem with our replication process which replicates notes data to SQL.
NoteSQL is used to create the ODBC connection between notes and SQL.
Initially the replication process went well and data is replicated in SQL.
COM object is created to do the replication process instead of Agents.
Problem is we are struck with this and we don't know where to look for the solution as the person created this is not available.
we are using Lotus Notes 6.5 and SQL server 2003,all I can see is an exe running in the processes and as long as that exe is running tables should be replciated in SQL(which it did initially),but tables are not replicated in SQL..
Thanks for any sort of help with this problem.
|
|
|
|
|
Dear All,
I need to set the value of a variable with the result of a stored procedure. I need something on these lines:
set @productCount = exec procGetCount
I did not manage to get this to work. I am using Microsoft Sql Server 7 and 2000.
Thanks a lot,
Chris
|
|
|
|
|
You can't (in those versions at least) that I know of.
Either you have to make it into a function or use output parameters.
|
|
|
|
|
in sql 2000 sp4(mabye in other versions of 2000 but i dont have any to test on)
EXEC @var = procGetCount
but you have to RETURN the value out of the stored procedure for it to work and sql 2000 can only return int values.
example procedure code
DECLARE @var INTEGER
SET @var = 3
RETURN @var
you can also put SELECT @var before the return if you need to as well.
|
|
|
|
|
I am using SQL Server 2005, I have some select and update statements in my query with WHERE clause
I want to prevent these queries from SQL injection attacks.
What are the steps and precautions to be taken for SQL Injection attacks?
Does anybody have suggestions?
Thanks in advance,
|
|
|
|
|
i'm not entirely sure of all of them but dont let the user add things to the where clause with a textbox..but sometimes thats unavoidable, in those cases you should be sure to scrub their input of "special" characters (hopefully you dotn need them to do what you're doing) "special" characters are things like ' " ; () etc.. non alpha numeric characters, i usually allow ' to let people use names like O'Malley but if you're adding that to a string you'lll have to go through and double them i.e. turn each ' into '' or sql server will throw an unterminted string literal or missing ' error. i dotn know if thats all you have to do but it should make it vastly more difficult for anyone trying a sql injection attack. I use RegEx's to do some of the validation on the users' input.
|
|
|
|
|
I have 2 queries,
say one for selecting records from a table based on some criteria say product and availability like this:
Select * from Stock where product = 'cooler' and availability = 'Available'
and another when the product is not available change the availability with update statement in my query like this:
UPDATE Stock SET availability = 'not available' WHERE StockId ='1' and product = 'cooler'
Now I want to prevent these queries from SQL injection attacks,
From the following links I came to know that if I use parameter collection in ASP.NET 2.0 then it is SQL Injection safe:
http://davidhayden.com/blog/dave/archive/2004/03/06/172.aspx[^]
http://forums.asp.net/p/1232362/2227463.aspx[^]
but do we have some other methods to prevent my above queries????
|
|
|
|
|
using a select * in code always a bad idea, it opens up the possibility of problems if column order changes or a new column is added, mostly this occurs with selecting * from views. A parameter list should help, but i dont like them. I just clean the user input, which is a good practice regardless of what method you use to construct the query to send to the database.
|
|
|
|
|
Besides being a bit off topic, then select * is not inherently bad. It most certainly have uses, and the main reason to *not* use SELECT * is to minimize network traffic. No need to move data you do not need.
|
|
|
|
|
unless you are selecting from a view which is used in several places, and an update to one place requires a change to the view, you could break anywhere where you used select *, but select col1, col2, col3 wont break as long as those columns are still present in the view. it not only needs to work well now it needs to work well in the future and not cause unnecessary problems for other people who may be doing updates, and not know all sections of a project like the person who first wrote them. Good practices will protect you from a lot including security threats, doubling tick marks ' to '', removing special characters, and validating your user's input will prevent more problems than i can count including sql injection, and it doesnt take much longer to write. sorry for getting off topic.
|
|
|
|
|
SELECT col1, col2, col3 will break just as easy as SELECT * if you remove the columns your query depends on.
It has nothing to do with SELECT * or not.
SELECT * is bad if you move (way) more data then you need. It is needless to do. But if you need all - or almost all - the data, it is fine.
|
|
|
|
|
Using parameterized stored procedures should cover you against the most direct forms of SQL Injection attack, unless you use string execution like:
EXEC ('SELECT * FROM MYTABLE WHERE ID = ' @INPUTSTRING)
In that case, validate input and replace ' with '' and do not allow the keywords which could do harm.
Generally - it is a bad thing to take user input and run it directly in a query, but as said - if you use parameterized SPROCs you should be pretty safe to begin with.
|
|
|
|
|
Actually I am trying to implementa function which can restrict or blacklist some database syntaxes like select, sys, insert etc. and do not allow them to fire i.e. to check input ...
But yet I m not entirely successful in that...
No idea how to implement that I just thought this thing yet...
|
|
|
|
|
Hi!
Let's say this is an old web site built in ASP with VBScript and it's just had an SQL Injection attack (as happened to me a week ago - I have hundreds of old sites running, and sometimes I or a colleague have had a bad day years ago - it tends to come back and bite you). You've stopped the web, cleaned the database (let's say it only appended script tags to some text fields, so it was a quick fix - this is common bot behaviour).
Quick and dirty fix to get it up:
Search for the word "WHERE" in the site source. Replace this:
Recs.Open "SELECT ... FROM ... WHERE x = '" & something & "' ..." ...
with:
Recs.Open "SELECT ... FROM ... WHERE x = '" & Replace(something, "'", "''") & "' ..." ...
and this:
Recs.Open "SELECT ... FROM ... WHERE someid = " & something & " ...", ...
with:
Recs.Open "SELECT ... FROM ... WHERE someid = " & CLng(something) & " ...", ...
'CLng will throw on invalid input!
This is a naive fix and only stops the most common attacks. So... when the site is up, you spend the next day rewriting further (from memory, possibly lots of syntax errors and remembering things wrong, but the concept is correct):
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = "SELECT ... FROM ... WHERE x = ? ..."
cmd.Parameters.Add cmd.CreateParameter("@p1", ...)
Recs.Open cmd, ...
And yes, you'd want to wrap that in a function, like so:
Recs.Open SqlCmd("SELECT...", Array(param1, param2)), ...
(the hideous Array() construct is because VBScript doesn't know about parameter arrays - also beware that you must analyze the values to figure out the proper types and other parameter metadata in the SqlCmd function)
If you're using .Net, look at the SqlCommand (or DbCommand) object. Same thing. There are several reasons to use parameterized queries:
- SQL Server can cache execution plans for similar queries - this improves performance A LOT if you do the same query over and over with different parameters.
- You're immune to SQL Injection attacks.
- You're code gets easier to read (provided you encapsulate the mechanism properly, otherwise it will bloat the code).
Then again, why don't we just round up all hackers and shoot them? I could even consider molesting them for a while first... 
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Currently i m using sql server 2000. when i directly modify the values inside the database the following TRIGGER fires correctly. But when pass values from front end (ASP.NET with C#, .net 2005, 2.0 framework) "update tbl_ORA_TBOps set Percentage_Compliant=((@comp/@tot_ex)*100) where AutoID=@id" this alone not working.. what problem.....? plz..
CREATE TRIGGER [calc] ON dbo.tbl_ORA_TBOps
AFTER INSERT, UPDATE
AS
begin
update tbl_ORA_TBOps set Total=(Compliant+Non_Compliant+InProgress+InputNotReceived+NA)
update tbl_ORA_TBOps set Total_Excl_NA=Total-NA
end
begin
declare @tot numeric
declare @na numeric
declare @tot_ex numeric
declare @comp numeric
declare @id numeric
select @tot=(Select total from inserted)
select @na=(Select NA from inserted)
select @tot_ex=(Select Total_Excl_NA from inserted)
select @comp=(Select Compliant from inserted)
select @id=(Select AutoID from inserted)
if (@tot=@na)
begin
update tbl_ORA_TBOps set Percentage_Compliant=100 where AutoID=@id
end
else
if (@tot_ex=0)
begin
update tbl_ORA_TBOps set Percentage_Compliant=0 where AutoID=@id
end
else
begin
update tbl_ORA_TBOps set Percentage_Compliant=((@comp/@tot_ex)*100) where AutoID=@id
end
end
in the above trigger, i m getting incorrect Percentage_Compliant value. how to solve this (i m very beginner to TRIGGERS). is the IF...ELSE loop correct? HELP ME! - KARAN
modified on Thursday, May 29, 2008 8:38 AM
|
|
|
|
|
Without looking in detail at your code, here is one critical heads-up about triggers: The inserted and deleted virtual tables contain more than one row. If you run one update statement that affects ten rows, they will contain ten rows each. The trigger gets called once per update statement, not once per row! Similar for all types of triggers.
So... your set @variable = (select ... from ...) would not do at all what you expect it to.
Rethink your approach accordingly.
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Thanks peter.... I m a beginner to TRIGGERS. so i will rectify my errors. but the thing is here... the ELSE part alone is not working.... other if loops working fine..... could u guide me to proceed this process? when i try the same in C#.NET the loop is working fine...
help me!
thanks for understanding....
|
|
|
|
|
I'm not sure I understand your question. When you're saying loop, what do you mean? You usually don't do work using loops in SQL, as SQL works on sets, not items. With sets, you specify a statement that says what should be done with the entire set. With items (as in C# or another procedural language) you loop over all the elements and do something to them one at a time. These are different ways of thinking altogether, and you can't mix the two.
So, instead of (abstract example, has nothing to do with your sample as I don't understand it, but the thinking should be similar):
-- The following wont work at all if inserted contains
-- more than one row (which it will if the statement
-- firing the trigger affects more than one row):
set @x = (select x from inserted)
if @x = 10 then set @y = 20 else set @y = 30
update target set y = @y
you do something like this:
update target set y = (case x when 10 then 20 else 30 end)
where target.id in (select id from inserted)
Note: There is an example that looks like yours in SQL Server Books Online, but like yours, it will fail when an update or insert statement affects more than one row.
I can't elaborate much on this without writing a small book, but what you have to realize is that with SQL you have to think quite differently than in procedural languages.
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
