Re: use of ' in sql calls
Thanks for this info... I'll check this out.
-ivan-
Axton wrote:
**
Ivan, inquire from support about the following defect:
SW00256669 Server not escaping single quotes in Direct SQL
This is from the 7.0.1 p001 release notes. I would have imagined the
same were addressed in 7.0p002 since the release times were close.
Axton Grams
On 2/7/07, *Heider, Stephen* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Ivan,
Apostrophes are something that will need to planned for anytime
you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the Remedy
REPLACE() function. Store the result in a tmp field. Then use the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG <mailto:arslist@ARSLIST.ORG>] On
Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG <mailto:arslist@ARSLIST.ORG>
Subject: use of ' in sql calls
Situation: We have a field containing department names (called
UnitName
in the sql call below). Some of these names have an apostrophe
(') in
the name.
We have an Active link that does an sql call
SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND
'$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library
change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do
(a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
<http://www.arslist.org> ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
<http://www.arslist.org> ARSlist:"Where the Answers Are"
__20060125___This posting was submitted with HTML
in it___
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
Re: use of ' in sql calls
This may be a [welcome] change to 7.0. My redesign project for 7.0 is
on hold for other projects I haven't done much dev work in 7.x yet.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Richard Brooks
Sent: Wednesday, February 07, 2007 11:54 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
This was a change of behavior in Version 7. We had to correct code in
6.3 to get it to work under 7.
Richard
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W
Sent: Wednesday, February 07, 2007 10:51 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
Stephen,
Is this a change for ARS 7? I'm on 6.3 and the system automatically
replaces the single ' with 2 singles if it is in a field inside if
single quotes (like in his example). The only time I have to do the
Replace myself is if my single is in hard coded text of the statement.
i.e. Select SchemaID from ARSchema Where Name = '$NAME$'
If my $NAME$ field contains "My Account's" the sql log on 6.3 shows
Select SchemaID from ARSchema Where Name = 'My Account''s'
If I had hard coded a string into the SQL like
Select SchemaID from ARSchema Where Name like '%Maint''s%'
then I have to do the 2 quotes myself.
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen
Sent: Wednesday, February 07, 2007 10:32 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
Ivan,
Apostrophes are something that will need to planned for anytime you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the Remedy
REPLACE() function. Store the result in a tmp field. Then use the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG
Subject: use of ' in sql calls
Situation: We have a field containing department names (called UnitName
in the sql call below). Some of these names have an apostrophe (') in
the name.
We have an Active link that does an sql call SELECT MAX(zDepCount) FROM
FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: use of ' in sql calls
This was a change of behavior in Version 7. We had to correct code in
6.3 to get it to work under 7.
Richard
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W
Sent: Wednesday, February 07, 2007 10:51 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
Stephen,
Is this a change for ARS 7? I'm on 6.3 and the system automatically
replaces the single ' with 2 singles if it is in a field inside if
single quotes (like in his example). The only time I have to do the
Replace myself is if my single is in hard coded text of the statement.
i.e. Select SchemaID from ARSchema Where Name = '$NAME$'
If my $NAME$ field contains "My Account's" the sql log on 6.3 shows
Select SchemaID from ARSchema Where Name = 'My Account''s'
If I had hard coded a string into the SQL like
Select SchemaID from ARSchema Where Name like '%Maint''s%'
then I have to do the 2 quotes myself.
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen
Sent: Wednesday, February 07, 2007 10:32 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
Ivan,
Apostrophes are something that will need to planned for anytime you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the Remedy
REPLACE() function. Store the result in a tmp field. Then use the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG
Subject: use of ' in sql calls
Situation: We have a field containing department names (called UnitName
in the sql call below). Some of these names have an apostrophe (') in
the name.
We have an Active link that does an sql call SELECT MAX(zDepCount) FROM
FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: use of ' in sql calls
Axton... I got this just after I had hit send ... figures
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Axton
Sent: Wednesday, February 07, 2007 10:49 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
**
Ivan, inquire from support about the following defect:
SW00256669 Server not escaping single quotes in Direct SQL
This is from the 7.0.1 p001 release notes. I would have imagined the
same were addressed in 7.0p002 since the release times were close.
Axton Grams
On 2/7/07, Heider, Stephen <[EMAIL PROTECTED]> wrote:
Ivan,
Apostrophes are something that will need to planned for anytime
you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the
Remedy
REPLACE() function. Store the result in a tmp field. Then use
the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG
Subject: use of ' in sql calls
Situation: We have a field containing department names (called
UnitName
in the sql call below). Some of these names have an apostrophe
(') in
the name.
We have an Active link that does an sql call
SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE
'$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND
'$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an
Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to
an
Oracle 9i database (access via Oracle 10 client libraries), and
we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database
ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe,
and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library
change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to
do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
ARSlist:"Where the Answers Are"
__20060125___This posting was submitted with HTML in
it___
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: use of ' in sql calls
Stephen,
Is this a change for ARS 7? I'm on 6.3 and the system automatically
replaces the single ' with 2 singles if it is in a field inside if
single quotes (like in his example). The only time I have to do the
Replace myself is if my single is in hard coded text of the statement.
i.e. Select SchemaID from ARSchema Where Name = '$NAME$'
If my $NAME$ field contains "My Account's" the sql log on 6.3 shows
Select SchemaID from ARSchema Where Name = 'My Account''s'
If I had hard coded a string into the SQL like
Select SchemaID from ARSchema Where Name like '%Maint''s%'
then I have to do the 2 quotes myself.
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen
Sent: Wednesday, February 07, 2007 10:32 AM
To: arslist@ARSLIST.ORG
Subject: Re: use of ' in sql calls
Ivan,
Apostrophes are something that will need to planned for anytime you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the Remedy
REPLACE() function. Store the result in a tmp field. Then use the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG
Subject: use of ' in sql calls
Situation: We have a field containing department names (called UnitName
in the sql call below). Some of these names have an apostrophe (') in
the name.
We have an Active link that does an sql call SELECT MAX(zDepCount) FROM
FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: use of ' in sql calls
Ivan, inquire from support about the following defect:
SW00256669 Server not escaping single quotes in Direct SQL
This is from the 7.0.1 p001 release notes. I would have imagined the same
were addressed in 7.0p002 since the release times were close.
Axton Grams
On 2/7/07, Heider, Stephen <[EMAIL PROTECTED]> wrote:
Ivan,
Apostrophes are something that will need to planned for anytime you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the Remedy
REPLACE() function. Store the result in a tmp field. Then use the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG
Subject: use of ' in sql calls
Situation: We have a field containing department names (called UnitName
in the sql call below). Some of these names have an apostrophe (') in
the name.
We have an Active link that does an sql call
SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
Re: use of ' in sql calls
Ivan,
Apostrophes are something that will need to planned for anytime you make
calls to a SQL database directly. But, it's quick to do.
In a Set Fields, replace the single ' with two '' using the Remedy
REPLACE() function. Store the result in a tmp field. Then use the tmp
field in your SQL statement.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions
Sent: Wednesday, February 07, 2007 11:28 AM
To: arslist@ARSLIST.ORG
Subject: use of ' in sql calls
Situation: We have a field containing department names (called UnitName
in the sql call below). Some of these names have an apostrophe (') in
the name.
We have an Active link that does an sql call
SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: use of ' in sql calls
On a related note (and perhaps this should be a new subject line), but
in ARS 6.3 we use the Dev_AR_Apps_Basic_English and
Dev_AR_Apps_Adv_English pdf's all the time.
In ARS 7.0 I do not see the logical upgraded replacement for these
documents (so I'm still going to the 6.3 ones).
Am I missing finding these somewhere?
tia
-ivan-
Ivan Runions wrote:
Situation: We have a field containing department names (called
UnitName in the sql call below). Some of these names have an
apostrophe (') in the name.
We have an Active link that does an sql call
SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND
'$RequestID$' != RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library
change) that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
use of ' in sql calls
Situation: We have a field containing department names (called UnitName
in the sql call below). Some of these names have an apostrophe (') in
the name.
We have an Active link that does an sql call
SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE '$UnitName$'
= UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$'
!= RequestID
This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i
database (accessed via Oracle 9 client libraries).
We have just upgraded our server to ARS 7.0.00 patch 2, going to an
Oracle 9i database (access via Oracle 10 client libraries), and we now
get errored out on these calls with the error:
Arerr [552] Failure during SQL Operation to the database ORA-01756
Quoting string not properly terminated.
The error only occurs when the Unitname contains an apostrophe, and
obviously, it is mucking up the sql statement.
Does anyone have any thoughts/pointers, as
a) how to work around this
b) whether it's the ARS upgrade or the Oracle 10 client library change)
that might be causing this
Actually, I only need help with (b) if I can't determine how to do (a).
thanks
-ivan-
--
Ivan Runions
Information Technologies
University of Calgary
Calgary Alberta Canada
(403) 220-4437
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
