|
I've got an issue that I'd like to find a better solution to,
I'm inserting a new row into a table, which fires of a trigger that does a set of inserts to replace something that used to be done manually. Previously I had been using @@Identity to retrieve the ID of the new row I inserted, however since I added the trigger I was getting the ID of the last item the trigger inserted.
I've found SELECT IDENT_CURRENT('tbl'), which works, but leaves me concerned with potential concurrency issues.
Any better methods out there or should I consider killing off the trigger to ensure the correct ID will always be returned?
|
|
|
|
|
Read up on Scope_Identity() in BOL. This is a classic error when using triggers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I have 2 admin users - say Admin1 and Admin2 - on a XP home edition. I installed my application on a machine using Admin1 login. The application runs smoothly.
Now, i logged off and logged in to the same machine using Admin2 credentials and try to launch the application. But the application could not be launched as the database connection could not be established. The application uses SQL express as the client side database. I have verified that the SQL express service is running on the client.
I tried to reproduce the same case on XP professional (SP2 and SP3 both) by creating 2 admin users. But could not reproduce the issue. The application works wonderfully with both the logins.
Is there anything with SQL Express that is clashing with XP Home???
Do I need to do some special settings on XP Home for SQL Express to work for all admin users ???
Any help would be highly appreciated.
TIA
~Shrikant
|
|
|
|
|
Hi,
Just a little advice needed here. During university I studied databases extensively which included the use of composite primary keys. Now, since leaving university a year or so ago and working in the industry, I haven't come across the use of a composite primary key anywhere. And what's more, I have come across databases where I believe that a composite primary key, using existing data, is the right choice for the table, a single unique field has been added to be used as the primary key.
My question is this, are composite primary keys a good or bad idea? I always thought that they were a good idea but noticing the lack of them in real world applications I beginning to think twice!
|
|
|
|
|
Well, it's debated. The down side of a composite key is when it comes to joins and the like, there more fiddly and take longer. By joining on just a single field your queries are quicker. Some people always have a single field primary key, and the the composite key which you're thinking of as a unique index constraint on the table.
Personally, I don't see anything wrong with a composite primary key. It makes a lot of sense logically and saves a column worths of data.
Perhaps just a question of choice or style.
Regards,
Rob Philpott.
|
|
|
|
|
Ah OK, thanks for the reply.
Having not worked in the industry for too long I haven't really found 'my own style' yet but I do like the idea of using natural data to use as the key rather than adding an extra field, even if it means using a composite key and dealing with it's drawbacks. But then, maybe I'll change my mind once I put it into practice!
Thanks again.
|
|
|
|
|
Theoretically writing, I think composite keys are the way to go because they suggest to the consumer of the database that these are the pieces of data that define a unique row in the table.
Practically writing, if you or programmers that use the database want to use one of the many Object-Relational Mappers out there, quite quickly you'll/they'll start complaining because composite keys make them really hard to use. Quite often you end up with the integral primary key and the unique constraint that Rob Philpot describes above.
"we must lose precision to make significant statements about complex systems."
-deKorvin on uncertainty
|
|
|
|
|
As someone who has been designing data structures for many years there are some things that I avoid, composite primary keys are one of them. Triggers are another, both make the system more difficult to support. However that is my "style" as you noted, you will fall into your own style with experience.
Curtis point about using ORM tolls is the driving decision maker behind my preference. I use a my own custom written ORM and composite keys are a bitch.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Example:
Create table myTable(studentID int,
firstName nvarchar(25),
lastName nvharchar(23)
)
I want the studentID field to be auto generated automatically, like 1,2,3 or 0,1,2 etc each time i insert a record. How do I do that inside a query (not in design mode).
|
|
|
|
|
Hi,
this is the SQL statement PHPMyAdmin comes up with when creating a table with two fields, one of them auto-incrementing:
CREATE TABLE `db1`.`test3` (
`name` VARCHAR( 12 ) NOT NULL ,
`ID` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY ( `ID` )
) ENGINE = MYISAM
Hope this helps.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
If you mean in SQLServer you must use IDENTITY
IDENTITY is the auto generate mode of an number field
|
|
|
|
|
I need to query a file using DB2's flavor of SQL. My query is selecting rows based on email address and I'm having trouble because the email address contains the @ symbol. Does anyone know of an escape character or something I can use to specify that I'm actually looking for the @ symbol instead of it thinking I'm loading a parmeter or something?
My basic statement is something like this:
SELECT * FROM MyLib.MyFile WHERE email='myemail@hotmail.com'
The statement doesn't error out. It just returns nothing, even when I know that the specified email address is in the file. And it's not an issue with lower/upper case letters, I've looked into that as well.
If anyone has any ideas for me, I'd really appreciate any help.
|
|
|
|
|
Kschuler wrote: any ideas
1. if your table really called 'MyLib.MyFile'?
2. try LIKE 'myemail%hotmail.com' to check there are some matches. It circumvents your @ problem (if that is what it is), but may match more than you anticipate.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Luc Pattyn wrote: 1. if your table really called 'MyLib.MyFile'?
No, my table is not really called that.
Luc Pattyn wrote: 2. try LIKE 'myemail%hotmail.com'
I've thought of this one, but ran into the issue with getting more than I wanted. Please let me know if you think of any other solutions.
|
|
|
|
|
Kschuler wrote: try LIKE 'myemail%hotmail.com'
did you at least try it, to make sure no other problem exists?
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Yes, using
LIKE 'myemail%hotmail.com'
works, but it will return not just records with email of
myemail@hotmail.com
but also returns recordswith emails like:
myemail773@hotmail.com
myemailaddress@hotmail.com
I was hoping to find a way to specify the @ symbol to avoid problems like the one specified above.
|
|
|
|
|
Hi,
OK I suggest you check your database responds to SQL parameters, either parameters by name as in WHERE email=@email or by sequence as in WHERE email=?. So check the documentation on SQL parameters.
If it does not, you could go for "_" instead of "%" since the former should match exactly one character, which is bound to be a "@" in an e-mail address.
However, parameterized queries are the better option, since they protect you against SQL injection attacks, and don't force you to escape special characters such as " ' " and " _ ".
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Unfortunately I cannot use parameters. The whole point of the program that I'm working on is that it lets users type an sql statement to create their own queries to the data. (Program is only used by fellow developers) So I guess I will have to use your suggestion and replace any @ symbols with _ or just let my users know that they need to do that themselves when typing the SQL statement and querying by email address.
Thanks for your input.
|
|
|
|
|
Okay....so this question was a total wash. I just found some code in the program that will replace an @ symbol with an empty string because it was causing a different problem. So....this was the reason it wasn't behaving the way I wanted and yeah. Sorry to waste your time.
|
|
|
|
|
Kschuler wrote: I just found some code in the program that will replace an @ symbol with an empty string
I trust that was while investigating why "_" did not work for you... 
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
i have this stored procedure in database.
<br />
ALTER PROCEDURE dbo.SearchForASpecificDocumentByFromBeginsWith<br />
(<br />
@SenderName nvarchar(50)<br />
)<br />
<br />
AS<br />
<br />
Select * from MyTable<br />
<br />
Where (SenderName like 'SenderName%')<br />
<br />
RETURN<br />
how can i use the argument (with the like operator) i am passing into this?
|
|
|
|
|
i have found the solution. here it is.
<br />
ALTER PROCEDURE dbo.SearchForASpecificDocumentByFromBeginsWith<br />
(<br />
@SenderName nvarchar(50)<br />
)<br />
<br />
AS<br />
<br />
Select * from MyTable<br />
<br />
Where (SenderName LIKE + @SenderName + '%')<br />
<br />
RETURN<br />
|
|
|
|
|
That can't work. You have too many + operators. The + between LIKE and @SenderName should be removed.
|
|
|
|
|
if you need too many operators you must use dynamic variable
for example:
-- this stored procedure used to search
create PROCEDURE [dbo].[SP__Find_Country]
(
@chv_Country_name varchar(150)=null,
@Calling_no varchar(5)=null,
@Short_Name varchar(3)=null
)
AS
set nocount on
Declare @Condition varchar(8000)
Set @condition =''
IF @chv_Country_name is not null
set @Condition=' co_name like ''' +@chv_Country_name + '%'''
IF @Calling_no is Not Null and @Condition =''
set @Condition=' co_Calling_code like ''' +@Calling_no + '%'''
else If @Calling_no is not Null
set @Condition=@Condition +' and co_Calling_code like ''' +@Calling_no + '%'''
IF @Short_Name is not null and @Condition =''
Set @Condition=' co_Short_Name like ''' +@Short_Name + '%'''
else IF @Short_Name is not null
Set @Condition=@Condition+' and co_Short_Name like ''' +@Short_Name + '%'''
if @Condition <> ''
Set @Condition=@Condition+' and co_deleted=0'
print @condition
if @condition =''
select * from Country
where co_deleted=0 -- Updated by Nabeel Adnan
Order By Country.co_name -- Updated By Mahmoud Khalil
else
begin
Exec ('select * from Country where '+ @condition + ' Order By co_name ')
print 'select * from Country where '+ @condition + ' Order By co_name '
end
|
|
|
|
|
Rami Said Abd Alhalim wrote: if you need too many operators you must use dynamic variable
If I need too many operators. Why would I need too many? Why don't I just need the right number?
Rami Said Abd Alhalim wrote: create PROCEDURE [dbo].[SP__Find_Country]
+10 Points for using stored procedures.
Rami Said Abd Alhalim wrote: set @Condition=' co_name like ''' +@chv_Country_name + '%'''
And take twice as many away for writing code that is vulnerable to a SQL Injection attack.
You should really look into sp_executesql and see how to pass parameters into dynamically built SQL.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
