sam15
asked on
CompariingValues_inplsql
Does mod plsql add extra space to the data submitted from HTML form.
I noticed today that even when i do not change any data and hit submit I always get the "Data
Changed" message.
After I added TRIM and NVL (for nulls), it stoppped.
Is this how you would compare data inputed via form against record in database.
I know if a column already has "abc123" and another is NULL, this will not yield correct result.
open c_org(l_orgcode);
fetch c_org into l_rec;
if TRIM(NVL(p_name,'abc123'))
TRIM(NVL(p_addr1,'abc123')
TRIM(NVL(p_addr2,'abc123')
TRIM(NVL(p_city,'abc123'))
TRIM(NVL(p_state,'abc123')
TRIM(NVL(p_zip,'abc123')) = TRIM(NVL(p_zip,'abc123'))
then
htp.p('No change in data');
else
update...
htp.p('Data changed);
end if;
I noticed today that even when i do not change any data and hit submit I always get the "Data
Changed" message.
After I added TRIM and NVL (for nulls), it stoppped.
Is this how you would compare data inputed via form against record in database.
I know if a column already has "abc123" and another is NULL, this will not yield correct result.
open c_org(l_orgcode);
fetch c_org into l_rec;
if TRIM(NVL(p_name,'abc123'))
TRIM(NVL(p_addr1,'abc123')
TRIM(NVL(p_addr2,'abc123')
TRIM(NVL(p_city,'abc123'))
TRIM(NVL(p_state,'abc123')
TRIM(NVL(p_zip,'abc123')) = TRIM(NVL(p_zip,'abc123'))
then
htp.p('No change in data');
else
update...
htp.p('Data changed);
end if;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I used NVL because if both fields are null they are the SAME but the comparison fails. so i wanted to use some common value in case of NULL values.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can still use explicit cursor for one record fetch. I do not need a loop. I did it this way because there is a lot of variables. Doing it implicit like you I would need to declare 50 variables.
WIth cursor i can declare a variable based on cursor row type.
The parameters are like this
procedure handle_form
( p_name in varchar2 default null,
p_addr1 in varchar2 default null,
p_addr2 in varchar2 default null,
p_city in varchar2 default null )
then you have the above code comaprig input values to record value in DB.
I think there is a standard way for using NVL for NULLcomparisons but i cant find it. RIght now I used 'abc123' but if DB value was null and someone inputed abc123' it would return true even though they are different values.
WIth cursor i can declare a variable based on cursor row type.
The parameters are like this
procedure handle_form
( p_name in varchar2 default null,
p_addr1 in varchar2 default null,
p_addr2 in varchar2 default null,
p_city in varchar2 default null )
then you have the above code comaprig input values to record value in DB.
I think there is a standard way for using NVL for NULLcomparisons but i cant find it. RIght now I used 'abc123' but if DB value was null and someone inputed abc123' it would return true even though they are different values.
Not sure why my approach would need 50 variables. I'm thinking a single select with 50 different items in the where clause.
Same if structure you have but I moved them into the select.
As far as you NVL issue, you just need something that cannot be entered in the form or at least has a very slim chance. Like chr(0)
Try: ... nvl(p_name,chr(0)) ...
Same if structure you have but I moved them into the select.
As far as you NVL issue, you just need something that cannot be entered in the form or at least has a very slim chance. Like chr(0)
Try: ... nvl(p_name,chr(0)) ...
ASKER
you are doing a row count. why?
I need to do
select col1,col2, col50 into l_col1,l_col2,l_col3,...l_
from T1, T2, T3 where.
and then
if (p_col1 = l_col1 AND p_col2 = l_col2 and .....
I need to do
select col1,col2, col50 into l_col1,l_col2,l_col3,...l_
from T1, T2, T3 where.
and then
if (p_col1 = l_col1 AND p_col2 = l_col2 and .....
Same thing but without the variables and explicit cursor open/fetch.
You have:
Select col1, col2 into l_col1, l_col2 from tab1 where...
if p_col1=l_col1 and ...
I suggest:
Select count(*) from tab1
Where ...
And col1=p_col1 and ...;
If you get a 0 from my count that is the same as a 'false' as your if statement.
Tell you what: provide a simple test case with your way and I'll tweak it and show mine.
A simple two column table, some sample data and a quick procedure should work.
You have:
Select col1, col2 into l_col1, l_col2 from tab1 where...
if p_col1=l_col1 and ...
I suggest:
Select count(*) from tab1
Where ...
And col1=p_col1 and ...;
If you get a 0 from my count that is the same as a 'false' as your if statement.
Tell you what: provide a simple test case with your way and I'll tweak it and show mine.
A simple two column table, some sample data and a quick procedure should work.
ASKER
oh so you are propsing a different way using COUNT that does not require selecting the table row into variables and comparing it into the original parameters getting passed?
It is pretty smart actually the way you did it although I have never seen it in text books or websites. Most seem to di it the same way I wrote it.
I guess also your way woukld handle NULLS since if the table column is null and parameter is null the WHERE caluse returns TRUE. right?
My only concern is if MOD_PLSQL is passing extra spaces in the data. IT might be safer to do a TRIM on the parameters.
It is pretty smart actually the way you did it although I have never seen it in text books or websites. Most seem to di it the same way I wrote it.
I guess also your way woukld handle NULLS since if the table column is null and parameter is null the WHERE caluse returns TRUE. right?
My only concern is if MOD_PLSQL is passing extra spaces in the data. IT might be safer to do a TRIM on the parameters.
Pretty sure it isn't a unique way but yes, no need to fetch into a lot of variables when you don't need to.
You basicaly just want to see if a 'row' exists. That is a 'count'.
You will still need the nvl calls since null cannot '=' null. Feel free to keep the trim in there unless a 'space' is valid in the form and you want to preserve them.
You basicaly just want to see if a 'row' exists. That is a 'count'.
You will still need the nvl calls since null cannot '=' null. Feel free to keep the trim in there unless a 'space' is valid in the form and you want to preserve them.
ASKER
so basically it would look like this
select count(*) from ORG where name=NVL(TRIM(p_name,'123'
etc....
there must be some better than '123' because if one column has 123 and parameter passed is null it will return true. I cant remember what is a common practice using the NVL value.
select count(*) from ORG where name=NVL(TRIM(p_name,'123'
etc....
there must be some better than '123' because if one column has 123 and parameter passed is null it will return true. I cant remember what is a common practice using the NVL value.
>>there must be some better than '123'
I covered that in http:#a37086572
Just use a value that will never be passed in as a parameter (or so likely to never be passed in).
The chr(0) would be hard to pass in. If the field has 'edits' for something like all numbers then 'abc' would work since it cannot be passed in. Is there a max allowed number of characters? If say 3, make the compare string 4 long, '----'.
There are many ways around this.
I covered that in http:#a37086572
Just use a value that will never be passed in as a parameter (or so likely to never be passed in).
The chr(0) would be hard to pass in. If the field has 'edits' for something like all numbers then 'abc' would work since it cannot be passed in. Is there a max allowed number of characters? If say 3, make the compare string 4 long, '----'.
There are many ways around this.
ASKER
each column in the table i check for NULL is different. It would be cumbersome to check the length for each field abs base the NVL n that.
CHr(0) = 0 (ascii equivalent).
i think if a user enters a ZERO (0) and table has 0 then it wont be correct.
CHr(0) = 0 (ascii equivalent).
i think if a user enters a ZERO (0) and table has 0 then it wont be correct.
>>enters a ZERO
a '0' is NOT the same as chr(0). a '0' is chr(48)
>>It would be cumbersome to check the length for each field abs base the NVL n that.
pick the 'max' for ALL columns unless is is like a varcahr2(4000).
This does raise an interesting point: The columns for NVL need to be the same data type so if on of the fields is a 'date', then you will need a different check for that.
a '0' is NOT the same as chr(0). a '0' is chr(48)
>>It would be cumbersome to check the length for each field abs base the NVL n that.
pick the 'max' for ALL columns unless is is like a varcahr2(4000).
This does raise an interesting point: The columns for NVL need to be the same data type so if on of the fields is a 'date', then you will need a different check for that.
ASKER
I was thinking about your count method to compare each field.
It would work but in case there is a table of 100,000 it would take a while (slow) to compare all records instead of selecting one record (by primary key) to a cursor and then doing comparison unless it uses the primary key index only to find the record first.
do you agree.
It would work but in case there is a table of 100,000 it would take a while (slow) to compare all records instead of selecting one record (by primary key) to a cursor and then doing comparison unless it uses the primary key index only to find the record first.
do you agree.
>>do you agree.
Not really. You have a select to 'fetch' that one row currently. I'm just saying add the checks to the where clause you already have. You will still either get the 1 row or 0 rows.
A picture is worth 1000 words. Back in http:#a37086644 I asked for you to post a simple test using your approach. I'll then convert it to my suggestion so you can see it, run it, test it.
A single table with a few rows showing 'success' and 'failure' will do. It doesn't have to be complicated to do a proof of concept.
Not really. You have a select to 'fetch' that one row currently. I'm just saying add the checks to the where clause you already have. You will still either get the 1 row or 0 rows.
A picture is worth 1000 words. Back in http:#a37086644 I asked for you to post a simple test using your approach. I'll then convert it to my suggestion so you can see it, run it, test it.
A single table with a few rows showing 'success' and 'failure' will do. It doesn't have to be complicated to do a proof of concept.
<input type="text" name="p_name" value=" "> or <input type='text' name='p_name' value=' '>