Database

9-Mar-09 6:43

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.

Luc Pattyn9-Mar-09 7:11

9-Mar-09 7:11

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.

Smile | :)

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

Kschuler9-Mar-09 7:19

9-Mar-09 7:19

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.

Luc Pattyn9-Mar-09 7:34

9-Mar-09 7:34

Kschuler wrote:
try LIKE 'myemail%hotmail.com'

did you at least try it, to make sure no other problem exists?

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Kschuler9-Mar-09 7:38

9-Mar-09 7:38

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.

Luc Pattyn9-Mar-09 7:48

9-Mar-09 7:48

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 " _ ".

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Kschuler9-Mar-09 8:05

Re: DB2 - SQL where selects field that contains @

9-Mar-09 8:05

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.

Kschuler9-Mar-09 9:08

Re: DB2 - SQL where selects field that contains @

9-Mar-09 9:08

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.

Luc Pattyn9-Mar-09 10:01

how to use stored procedure argument with like operator

9-Mar-09 10:01

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... Big Grin | :-D

Luc Pattyn [Forum Guidelines] [My Articles]

Meax7-Mar-09 8:03

Meax

7-Mar-09 8:03

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?

Re: how to use stored procedure argument with like operator

Meax7-Mar-09 9:31

Meax

7-Mar-09 9:31

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 />

Re: how to use stored procedure argument with like operator

Colin Angus Mackay7-Mar-09 12:23

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

7-Mar-09 12:23

That can't work. You have too many + operators. The + between LIKE and @SenderName should be removed.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

Re: how to use stored procedure argument with like operator

Rami Said Abd Alhalim9-Mar-09 21:43

Rami Said Abd Alhalim

9-Mar-09 21:43

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

Re: how to use stored procedure argument with like operator

Colin Angus Mackay10-Mar-09 13:17

*Developer Day Scotland - Free community conference Delegate Registration Open

10-Mar-09 13:17

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.

SQL search all columns [modified]

Expert Coming7-Mar-09 4:43

Expert Coming

7-Mar-09 4:43

I am trying to do a search on all the data in a specific table.

How do I search across all columns? And how do I search period? The LIKE command isn't working how I'd like it to, or I am not using it right.

EXAMPLE:

Player Name:
John Doe
Johnny Hopkins
Night Hawk

I would like to be able to search 'john' and get the first two records return, or 'doe' for the first, or 't h' and get the third ect.

The LIKE command seems to be too picky although I may be wrong...

Thanks for your time.

The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo

modified on Saturday, March 7, 2009 10:51 AM

Re: SQL search all columns

Eddy Vluggen7-Mar-09 4:47

Eddy Vluggen

7-Mar-09 4:47

That sounds an awfull lot like a Full Text Search. Someone made an example here[^] Smile | :)

I are troll Smile | :)

Re: SQL search all columns

Kschuler9-Mar-09 8:10

Check if SQL is doing some king of process

9-Mar-09 8:10

What does your SQL statement look like? It should look something like this:

SELECT PlayerName FROM Table WHERE UPPER(FirstName) LIKE '%JOHN%' OR UPPER(LastName) LIKE '%JOHN%'

But if you really have to search ALL columns you will have to add a LIKE for each column name in the WHERE portion of you statement.

Hope this helps.

cdpace6-Mar-09 22:17

cdpace

6-Mar-09 22:17

Hello guys, i think i have a stupid question but worth a try. I have an application where it is uploading a file to a remote server via a webservice, after doing this it will unzip the data and merge that data with the database in the remote server. Now I was thinking what if 2 people where uploading at the same time and try to merge the data at the same time wouldnt that resort in some kind of error. Doest SQL server handle this problem its self or is there somehow i can handle it trough c# code?

Thank you for your time in advance and sorry if it's a stupid question.

Re: Check if SQL is doing some king of process

Expert Coming7-Mar-09 4:24

Expert Coming

7-Mar-09 4:24

What do you mean 'merge' the data?

The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo

Re: Check if SQL is doing some king of process

Colin Angus Mackay7-Mar-09 12:20

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

7-Mar-09 12:20

It depends on what you mean by merge. But at a most basic level SQL Server is designed to be used by multiple users simultaneously.

I suppose if the uploaded data is meant to be uploaded in a specific sequence and the second set of data starts being "merged" before the first then you have a problem. If the data doesn't have that sort of constrant then I don't see there being a problem.

Will the two sets of data being uploaded overlap in anyway? e.g. they both cause the same row to be updated. If that is the case who wins? In other words which update gets retained, and which is overwritten or thrown away? If there is no over lap then there is no problem.

If the two sets of data pertain to only new information that is being added to the database then I don't see any real problem at all.

But, it really all depends on what you mean by "merge"

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

Server 08 Execute SQL

enipla6-Mar-09 9:45

enipla

6-Mar-09 9:45

In SQL Server 08, the 'Execute SQL' ability is grayed out in the Query designer.

I don't see how it could be permisions because if I right click on the table and choose 'Edit top 1000 rows' I get the thousand rows.

Thanks

Re: Server 08 Execute SQL

Colin Angus Mackay7-Mar-09 12:14

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

7-Mar-09 12:14

I'm not being silly, but does the query window have the current focus? The only time it greys out for me is when the Query window loses focus.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

Re: Server 08 Execute SQL

enipla10-Mar-09 3:14

enipla

10-Mar-09 3:14

Yep it has focus. I open the query designer, add a table to it and right click in the designer.

The Execute SQL option is grayed out.

Is this how you operated it?

Thanks

Re: Server 08 Execute SQL

Colin Angus Mackay10-Mar-09 13:29