Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SP Permissions-5054 error

11 posts in AEP Last posting was on 2009-12-16 20:03:30.0Z
Gene Lyman Posted on 2009-12-01 17:25:51.0Z
From: "Gene Lyman" <glyman@myactv.net>
Newsgroups: Advantage.AEP
Subject: SP Permissions-5054 error
Date: Tue, 1 Dec 2009 12:25:51 -0500
Lines: 14
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: 162.84.58.149
Message-ID: <4b155138@solutions.advantagedatabase.com>
X-Trace: 1 Dec 2009 10:24:08 -0700, 162.84.58.149
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.84.58.149
Xref: solutions.advantagedatabase.com Advantage.AEP:760
Article PK: 1104034

Hello,

I have create a stored SQL procedure in my data dictionary. The syntax
checks out fine and the SP name is saved in the dictionary. All users are
granted permission to "Create" procedures and "Execute" procedures.

The stored procedure is within in an AdsConnection tied to the data
dictionary. When I attempt to open the procedure, I get a 5054 error,
"Cannot create function object". I haven't figured out what the problem is
and would appreciate any suggestions.

Thanks,
Gene Lyman


Gene Lyman Posted on 2009-12-02 01:10:58.0Z
From: "Gene Lyman" <glyman@myactv.net>
Newsgroups: Advantage.AEP
References: <4b155138@solutions.advantagedatabase.com>
In-Reply-To: <4b155138@solutions.advantagedatabase.com>
Subject: Re: SP Permissions-5054 error
Date: Tue, 1 Dec 2009 20:10:58 -0500
Lines: 23
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: 162.84.58.149
Message-ID: <4b15be43@solutions.advantagedatabase.com>
X-Trace: 1 Dec 2009 18:09:23 -0700, 162.84.58.149
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.84.58.149
Xref: solutions.advantagedatabase.com Advantage.AEP:761
Article PK: 1104035

I seem to have progressed past the 5054 error and now get the 5132 error,
"an object of the same name already exists in the data dictionary". I guess
I don't understand how to activate the stored procedure from within the
application's code.
Gene Lyman

"Gene Lyman" <glyman@myactv.net> wrote in message
news:4b155138@solutions.advantagedatabase.com...
> Hello,
>
> I have create a stored SQL procedure in my data dictionary. The syntax
> checks out fine and the SP name is saved in the dictionary. All users
> are granted permission to "Create" procedures and "Execute" procedures.
>
> The stored procedure is within in an AdsConnection tied to the data
> dictionary. When I attempt to open the procedure, I get a 5054 error,
> "Cannot create function object". I haven't figured out what the problem
> is and would appreciate any suggestions.
>
> Thanks,
> Gene Lyman
>


Edgar Sherman Posted on 2009-12-02 23:01:03.0Z
Date: Wed, 02 Dec 2009 16:01:03 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: Advantage.AEP
Subject: Re: SP Permissions-5054 error
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com>
In-Reply-To: <4b15be43@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.164
Message-ID: <4b16f149@solutions.advantagedatabase.com>
X-Trace: 2 Dec 2009 15:59:21 -0700, 10.24.34.164
Lines: 30
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.164
Xref: solutions.advantagedatabase.com Advantage.AEP:762
Article PK: 1104042

You should be able to run the SQL "execute procedure <procedurename>
(param1, param2,...);"

Edgar

Gene Lyman wrote:
> I seem to have progressed past the 5054 error and now get the 5132
> error, "an object of the same name already exists in the data
> dictionary". I guess I don't understand how to activate the stored
> procedure from within the application's code.
> Gene Lyman
>
> "Gene Lyman" <glyman@myactv.net> wrote in message
> news:4b155138@solutions.advantagedatabase.com...
>> Hello,
>>
>> I have create a stored SQL procedure in my data dictionary. The
>> syntax checks out fine and the SP name is saved in the dictionary.
>> All users are granted permission to "Create" procedures and "Execute"
>> procedures.
>>
>> The stored procedure is within in an AdsConnection tied to the data
>> dictionary. When I attempt to open the procedure, I get a 5054
>> error, "Cannot create function object". I haven't figured out what
>> the problem is and would appreciate any suggestions.
>>
>> Thanks,
>> Gene Lyman
>>
>


Gene Lyman Posted on 2009-12-08 21:22:20.0Z
From: "Gene Lyman" <glyman@myactv.net>
Newsgroups: Advantage.AEP
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com>
In-Reply-To: <4b16f149@solutions.advantagedatabase.com>
Subject: Re: SP Permissions-5054 error
Date: Tue, 8 Dec 2009 16:22:20 -0500
Lines: 43
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: 162.84.58.149
Message-ID: <4b1ec315@solutions.advantagedatabase.com>
X-Trace: 8 Dec 2009 14:20:21 -0700, 162.84.58.149
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.84.58.149
Xref: solutions.advantagedatabase.com Advantage.AEP:763
Article PK: 1104039

I have successfully run the SQL procedure using ARC. The problem now is
that when I attempt to run the procedure through the application, I get a
7028 error. I have previously reported this problem in the SQL newsgroup,
and got the response that I have an incompatible LockingMode problem. All
my tables use the compatible mode, the SQL Stored procedure defaults to the
proprietary mode. I would seem there should be a place to change this
sstting, but I have failed to find it. Any suggestions.
Gene Lyman

"Edgar Sherman" <no@email.com> wrote in message
news:4b16f149@solutions.advantagedatabase.com...
> You should be able to run the SQL "execute procedure <procedurename>
> (param1, param2,...);"
>
> Edgar
>
> Gene Lyman wrote:
>> I seem to have progressed past the 5054 error and now get the 5132 error,
>> "an object of the same name already exists in the data dictionary". I
>> guess I don't understand how to activate the stored procedure from within
>> the application's code.
>> Gene Lyman
>>
>> "Gene Lyman" <glyman@myactv.net> wrote in message
>> news:4b155138@solutions.advantagedatabase.com...
>>> Hello,
>>>
>>> I have create a stored SQL procedure in my data dictionary. The syntax
>>> checks out fine and the SP name is saved in the dictionary. All users
>>> are granted permission to "Create" procedures and "Execute" procedures.
>>>
>>> The stored procedure is within in an AdsConnection tied to the data
>>> dictionary. When I attempt to open the procedure, I get a 5054 error,
>>> "Cannot create function object". I haven't figured out what the
>>> problem is and would appreciate any suggestions.
>>>
>>> Thanks,
>>> Gene Lyman
>>>
>>
>


Edgar Sherman Posted on 2009-12-08 23:58:12.0Z
Date: Tue, 08 Dec 2009 16:58:12 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: Advantage.AEP
Subject: Re: SP Permissions-5054 error
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com>
In-Reply-To: <4b1ec315@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.164
Message-ID: <4b1ee7ae@solutions.advantagedatabase.com>
X-Trace: 8 Dec 2009 16:56:30 -0700, 10.24.34.164
Lines: 65
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.164
Xref: solutions.advantagedatabase.com Advantage.AEP:764
Article PK: 1104040

The answer depends on the language you are using to write your application.

For Delphi, on the TAdsQuery Component expand the property
AdsTableOptions and then change "AdsLockType" to Compatible.

For .Net set LockMode=COMPATIBLE in the connection string.

If you are using the ODBC driver, change "Advantage Locking" to
Compatible in the ODBC DSN.

For JDBC set LockType=compatible in the connection string.

For VO you will want to call AX_AXSLocking( .F. )

For ACE you will want to call AdsStmtSetTableLockType and pass
ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)

Edgar

Gene Lyman wrote:
> I have successfully run the SQL procedure using ARC. The problem now
> is that when I attempt to run the procedure through the application, I
> get a 7028 error. I have previously reported this problem in the SQL
> newsgroup, and got the response that I have an incompatible LockingMode
> problem. All my tables use the compatible mode, the SQL Stored
> procedure defaults to the proprietary mode. I would seem there should
> be a place to change this sstting, but I have failed to find it. Any
> suggestions.
> Gene Lyman
>
>
> "Edgar Sherman" <no@email.com> wrote in message
> news:4b16f149@solutions.advantagedatabase.com...
>> You should be able to run the SQL "execute procedure <procedurename>
>> (param1, param2,...);"
>>
>> Edgar
>>
>> Gene Lyman wrote:
>>> I seem to have progressed past the 5054 error and now get the 5132
>>> error, "an object of the same name already exists in the data
>>> dictionary". I guess I don't understand how to activate the stored
>>> procedure from within the application's code.
>>> Gene Lyman
>>>
>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>> news:4b155138@solutions.advantagedatabase.com...
>>>> Hello,
>>>>
>>>> I have create a stored SQL procedure in my data dictionary. The
>>>> syntax checks out fine and the SP name is saved in the dictionary.
>>>> All users are granted permission to "Create" procedures and
>>>> "Execute" procedures.
>>>>
>>>> The stored procedure is within in an AdsConnection tied to the data
>>>> dictionary. When I attempt to open the procedure, I get a 5054
>>>> error, "Cannot create function object". I haven't figured out what
>>>> the problem is and would appreciate any suggestions.
>>>>
>>>> Thanks,
>>>> Gene Lyman
>>>>
>>>
>>
>


Gene Lyman Posted on 2009-12-09 15:16:35.0Z
From: "Gene Lyman" <glyman@myactv.net>
Newsgroups: Advantage.AEP
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com> <4b1ee7ae@solutions.advantagedatabase.com>
In-Reply-To: <4b1ee7ae@solutions.advantagedatabase.com>
Subject: Re: SP Permissions-5054 error
Date: Wed, 9 Dec 2009 10:16:35 -0500
Lines: 76
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: 162.84.58.149
Message-ID: <4b1fbedc@solutions.advantagedatabase.com>
X-Trace: 9 Dec 2009 08:14:36 -0700, 162.84.58.149
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.84.58.149
Xref: solutions.advantagedatabase.com Advantage.AEP:765
Article PK: 1104043

Thanks for the suggestions, but I must be missing something here. I am
attempting to access the SQL procedure through a TAdsStoredProc component.
I have attempted using various handles available to the component to setup
the AdsStmtSetTableLockType 1st parameter, hSQL. None have been successful,
and I'm not sure I'm on the right track.
Gene Lyman

"Edgar Sherman" <no@email.com> wrote in message
news:4b1ee7ae@solutions.advantagedatabase.com...
> The answer depends on the language you are using to write your
> application.
>
> For Delphi, on the TAdsQuery Component expand the property AdsTableOptions
> and then change "AdsLockType" to Compatible.
>
> For .Net set LockMode=COMPATIBLE in the connection string.
>
> If you are using the ODBC driver, change "Advantage Locking" to Compatible
> in the ODBC DSN.
>
> For JDBC set LockType=compatible in the connection string.
>
> For VO you will want to call AX_AXSLocking( .F. )
>
> For ACE you will want to call AdsStmtSetTableLockType and pass
> ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)
>
> Edgar
>
> Gene Lyman wrote:
>> I have successfully run the SQL procedure using ARC. The problem now is
>> that when I attempt to run the procedure through the application, I get a
>> 7028 error. I have previously reported this problem in the SQL
>> newsgroup, and got the response that I have an incompatible LockingMode
>> problem. All my tables use the compatible mode, the SQL Stored
>> procedure defaults to the proprietary mode. I would seem there should
>> be a place to change this sstting, but I have failed to find it. Any
>> suggestions.
>> Gene Lyman
>>
>>
>> "Edgar Sherman" <no@email.com> wrote in message
>> news:4b16f149@solutions.advantagedatabase.com...
>>> You should be able to run the SQL "execute procedure <procedurename>
>>> (param1, param2,...);"
>>>
>>> Edgar
>>>
>>> Gene Lyman wrote:
>>>> I seem to have progressed past the 5054 error and now get the 5132
>>>> error, "an object of the same name already exists in the data
>>>> dictionary". I guess I don't understand how to activate the stored
>>>> procedure from within the application's code.
>>>> Gene Lyman
>>>>
>>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>>> news:4b155138@solutions.advantagedatabase.com...
>>>>> Hello,
>>>>>
>>>>> I have create a stored SQL procedure in my data dictionary. The
>>>>> syntax checks out fine and the SP name is saved in the dictionary.
>>>>> All users are granted permission to "Create" procedures and "Execute"
>>>>> procedures.
>>>>>
>>>>> The stored procedure is within in an AdsConnection tied to the data
>>>>> dictionary. When I attempt to open the procedure, I get a 5054
>>>>> error, "Cannot create function object". I haven't figured out what
>>>>> the problem is and would appreciate any suggestions.
>>>>>
>>>>> Thanks,
>>>>> Gene Lyman
>>>>>
>>>>
>>>
>>


Edgar Sherman Posted on 2009-12-09 17:35:42.0Z
Date: Wed, 09 Dec 2009 10:35:42 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: Advantage.AEP
Subject: Re: SP Permissions-5054 error
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com> <4b1ee7ae@solutions.advantagedatabase.com> <4b1fbedc@solutions.advantagedatabase.com>
In-Reply-To: <4b1fbedc@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.164
Message-ID: <4b1fdf86@solutions.advantagedatabase.com>
X-Trace: 9 Dec 2009 10:33:58 -0700, 10.24.34.164
Lines: 87
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.164
Xref: solutions.advantagedatabase.com Advantage.AEP:766
Article PK: 1104044

Hi Gene,

It does not look like TAdsStoredProc has the option to change the lock
type. I suggest using a TAdsQuery, set AdsTableOptions.AdsLockType =
compatible, then set the TAdsQuery.SQL = "execute procedure
<procedurename> (param1, pram2....);" and finally TAdsQuery.execsql to
run the stored procedure.

Edgar

Gene Lyman wrote:
> Thanks for the suggestions, but I must be missing something here. I am
> attempting to access the SQL procedure through a TAdsStoredProc
> component. I have attempted using various handles available to the
> component to setup the AdsStmtSetTableLockType 1st parameter, hSQL.
> None have been successful, and I'm not sure I'm on the right track.
> Gene Lyman
>
> "Edgar Sherman" <no@email.com> wrote in message
> news:4b1ee7ae@solutions.advantagedatabase.com...
>> The answer depends on the language you are using to write your
>> application.
>>
>> For Delphi, on the TAdsQuery Component expand the property
>> AdsTableOptions and then change "AdsLockType" to Compatible.
>>
>> For .Net set LockMode=COMPATIBLE in the connection string.
>>
>> If you are using the ODBC driver, change "Advantage Locking" to
>> Compatible in the ODBC DSN.
>>
>> For JDBC set LockType=compatible in the connection string.
>>
>> For VO you will want to call AX_AXSLocking( .F. )
>>
>> For ACE you will want to call AdsStmtSetTableLockType and pass
>> ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)
>>
>> Edgar
>>
>> Gene Lyman wrote:
>>> I have successfully run the SQL procedure using ARC. The problem
>>> now is that when I attempt to run the procedure through the
>>> application, I get a 7028 error. I have previously reported this
>>> problem in the SQL newsgroup, and got the response that I have an
>>> incompatible LockingMode problem. All my tables use the compatible
>>> mode, the SQL Stored procedure defaults to the proprietary mode. I
>>> would seem there should be a place to change this sstting, but I have
>>> failed to find it. Any suggestions.
>>> Gene Lyman
>>>
>>>
>>> "Edgar Sherman" <no@email.com> wrote in message
>>> news:4b16f149@solutions.advantagedatabase.com...
>>>> You should be able to run the SQL "execute procedure <procedurename>
>>>> (param1, param2,...);"
>>>>
>>>> Edgar
>>>>
>>>> Gene Lyman wrote:
>>>>> I seem to have progressed past the 5054 error and now get the 5132
>>>>> error, "an object of the same name already exists in the data
>>>>> dictionary". I guess I don't understand how to activate the stored
>>>>> procedure from within the application's code.
>>>>> Gene Lyman
>>>>>
>>>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>>>> news:4b155138@solutions.advantagedatabase.com...
>>>>>> Hello,
>>>>>>
>>>>>> I have create a stored SQL procedure in my data dictionary. The
>>>>>> syntax checks out fine and the SP name is saved in the dictionary.
>>>>>> All users are granted permission to "Create" procedures and
>>>>>> "Execute" procedures.
>>>>>>
>>>>>> The stored procedure is within in an AdsConnection tied to the
>>>>>> data dictionary. When I attempt to open the procedure, I get a
>>>>>> 5054 error, "Cannot create function object". I haven't figured
>>>>>> out what the problem is and would appreciate any suggestions.
>>>>>>
>>>>>> Thanks,
>>>>>> Gene Lyman
>>>>>>
>>>>>
>>>>
>>>
>


Gene Lyman Posted on 2009-12-15 15:33:19.0Z
From: "Gene Lyman" <glyman@myactv.net>
Newsgroups: Advantage.AEP
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com> <4b1ee7ae@solutions.advantagedatabase.com> <4b1fbedc@solutions.advantagedatabase.com> <4b1fdf86@solutions.advantagedatabase.com>
In-Reply-To: <4b1fdf86@solutions.advantagedatabase.com>
Subject: Re: SP Permissions-5054 error
Date: Tue, 15 Dec 2009 10:33:19 -0500
Lines: 103
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: 162.83.12.117
Message-ID: <4b27abbe@solutions.advantagedatabase.com>
X-Trace: 15 Dec 2009 08:31:10 -0700, 162.83.12.117
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.83.12.117
Xref: solutions.advantagedatabase.com Advantage.AEP:769
Article PK: 1104046

Hi Edgar,

TAdsQuery doesn't offer the functionality of the stored procedure so it is
not a viable solution. In my specific instance I can use a stored procedure
because it was easy for me to convert one table to the ADT type. But this
is only one table of many in my application, and I would like to be able to
use the stored Sql procedure more widely. Will an update be made to the
stored procedure component soon, or can you provide me information I can use
to make a patch to the component source code?

Gene Lyman

"Edgar Sherman" <no@email.com> wrote in message
news:4b1fdf86@solutions.advantagedatabase.com...
> Hi Gene,
>
> It does not look like TAdsStoredProc has the option to change the lock
> type. I suggest using a TAdsQuery, set AdsTableOptions.AdsLockType =
> compatible, then set the TAdsQuery.SQL = "execute procedure
> <procedurename> (param1, pram2....);" and finally TAdsQuery.execsql to run
> the stored procedure.
>
> Edgar
>
> Gene Lyman wrote:
>> Thanks for the suggestions, but I must be missing something here. I am
>> attempting to access the SQL procedure through a TAdsStoredProc
>> component. I have attempted using various handles available to the
>> component to setup the AdsStmtSetTableLockType 1st parameter, hSQL. None
>> have been successful, and I'm not sure I'm on the right track.
>> Gene Lyman
>>
>> "Edgar Sherman" <no@email.com> wrote in message
>> news:4b1ee7ae@solutions.advantagedatabase.com...
>>> The answer depends on the language you are using to write your
>>> application.
>>>
>>> For Delphi, on the TAdsQuery Component expand the property
>>> AdsTableOptions and then change "AdsLockType" to Compatible.
>>>
>>> For .Net set LockMode=COMPATIBLE in the connection string.
>>>
>>> If you are using the ODBC driver, change "Advantage Locking" to
>>> Compatible in the ODBC DSN.
>>>
>>> For JDBC set LockType=compatible in the connection string.
>>>
>>> For VO you will want to call AX_AXSLocking( .F. )
>>>
>>> For ACE you will want to call AdsStmtSetTableLockType and pass
>>> ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)
>>>
>>> Edgar
>>>
>>> Gene Lyman wrote:
>>>> I have successfully run the SQL procedure using ARC. The problem now
>>>> is that when I attempt to run the procedure through the application, I
>>>> get a 7028 error. I have previously reported this problem in the SQL
>>>> newsgroup, and got the response that I have an incompatible LockingMode
>>>> problem. All my tables use the compatible mode, the SQL Stored
>>>> procedure defaults to the proprietary mode. I would seem there should
>>>> be a place to change this sstting, but I have failed to find it. Any
>>>> suggestions.
>>>> Gene Lyman
>>>>
>>>>
>>>> "Edgar Sherman" <no@email.com> wrote in message
>>>> news:4b16f149@solutions.advantagedatabase.com...
>>>>> You should be able to run the SQL "execute procedure <procedurename>
>>>>> (param1, param2,...);"
>>>>>
>>>>> Edgar
>>>>>
>>>>> Gene Lyman wrote:
>>>>>> I seem to have progressed past the 5054 error and now get the 5132
>>>>>> error, "an object of the same name already exists in the data
>>>>>> dictionary". I guess I don't understand how to activate the stored
>>>>>> procedure from within the application's code.
>>>>>> Gene Lyman
>>>>>>
>>>>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>>>>> news:4b155138@solutions.advantagedatabase.com...
>>>>>>> Hello,
>>>>>>>
>>>>>>> I have create a stored SQL procedure in my data dictionary. The
>>>>>>> syntax checks out fine and the SP name is saved in the dictionary.
>>>>>>> All users are granted permission to "Create" procedures and
>>>>>>> "Execute" procedures.
>>>>>>>
>>>>>>> The stored procedure is within in an AdsConnection tied to the data
>>>>>>> dictionary. When I attempt to open the procedure, I get a 5054
>>>>>>> error, "Cannot create function object". I haven't figured out what
>>>>>>> the problem is and would appreciate any suggestions.
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Gene Lyman
>>>>>>>
>>>>>>
>>>>>
>>>>
>>


Edgar Sherman Posted on 2009-12-15 16:49:14.0Z
Date: Tue, 15 Dec 2009 09:49:14 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: Advantage.AEP
Subject: Re: SP Permissions-5054 error
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com> <4b1ee7ae@solutions.advantagedatabase.com> <4b1fbedc@solutions.advantagedatabase.com> <4b1fdf86@solutions.advantagedatabase.com> <4b27abbe@solutions.advantagedatabase.com>
In-Reply-To: <4b27abbe@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.164
Message-ID: <4b27bda0@solutions.advantagedatabase.com>
X-Trace: 15 Dec 2009 09:47:28 -0700, 10.24.34.164
Lines: 112
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.164
Xref: solutions.advantagedatabase.com Advantage.AEP:770
Article PK: 1104047

I don't understand what extra functionality is missing from a TAdsQuery.
Running "EXECUTE PROCEDURE <procedurename>()" in a TAdsQuery should
return the same results as using a TAdsStoredProc. That is how you
execute it within ARC.

Edgar

Gene Lyman wrote:
> Hi Edgar,
>
> TAdsQuery doesn't offer the functionality of the stored procedure so it
> is not a viable solution. In my specific instance I can use a stored
> procedure because it was easy for me to convert one table to the ADT
> type. But this is only one table of many in my application, and I
> would like to be able to use the stored Sql procedure more widely. Will
> an update be made to the stored procedure component soon, or can you
> provide me information I can use to make a patch to the component source
> code?
>
> Gene Lyman
>
>
> "Edgar Sherman" <no@email.com> wrote in message
> news:4b1fdf86@solutions.advantagedatabase.com...
>> Hi Gene,
>>
>> It does not look like TAdsStoredProc has the option to change the lock
>> type. I suggest using a TAdsQuery, set AdsTableOptions.AdsLockType =
>> compatible, then set the TAdsQuery.SQL = "execute procedure
>> <procedurename> (param1, pram2....);" and finally TAdsQuery.execsql to
>> run the stored procedure.
>>
>> Edgar
>>
>> Gene Lyman wrote:
>>> Thanks for the suggestions, but I must be missing something here. I
>>> am attempting to access the SQL procedure through a TAdsStoredProc
>>> component. I have attempted using various handles available to the
>>> component to setup the AdsStmtSetTableLockType 1st parameter, hSQL.
>>> None have been successful, and I'm not sure I'm on the right track.
>>> Gene Lyman
>>>
>>> "Edgar Sherman" <no@email.com> wrote in message
>>> news:4b1ee7ae@solutions.advantagedatabase.com...
>>>> The answer depends on the language you are using to write your
>>>> application.
>>>>
>>>> For Delphi, on the TAdsQuery Component expand the property
>>>> AdsTableOptions and then change "AdsLockType" to Compatible.
>>>>
>>>> For .Net set LockMode=COMPATIBLE in the connection string.
>>>>
>>>> If you are using the ODBC driver, change "Advantage Locking" to
>>>> Compatible in the ODBC DSN.
>>>>
>>>> For JDBC set LockType=compatible in the connection string.
>>>>
>>>> For VO you will want to call AX_AXSLocking( .F. )
>>>>
>>>> For ACE you will want to call AdsStmtSetTableLockType and pass
>>>> ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)
>>>>
>>>> Edgar
>>>>
>>>> Gene Lyman wrote:
>>>>> I have successfully run the SQL procedure using ARC. The problem
>>>>> now is that when I attempt to run the procedure through the
>>>>> application, I get a 7028 error. I have previously reported this
>>>>> problem in the SQL newsgroup, and got the response that I have an
>>>>> incompatible LockingMode problem. All my tables use the
>>>>> compatible mode, the SQL Stored procedure defaults to the
>>>>> proprietary mode. I would seem there should be a place to change
>>>>> this sstting, but I have failed to find it. Any suggestions.
>>>>> Gene Lyman
>>>>>
>>>>>
>>>>> "Edgar Sherman" <no@email.com> wrote in message
>>>>> news:4b16f149@solutions.advantagedatabase.com...
>>>>>> You should be able to run the SQL "execute procedure
>>>>>> <procedurename> (param1, param2,...);"
>>>>>>
>>>>>> Edgar
>>>>>>
>>>>>> Gene Lyman wrote:
>>>>>>> I seem to have progressed past the 5054 error and now get the
>>>>>>> 5132 error, "an object of the same name already exists in the
>>>>>>> data dictionary". I guess I don't understand how to activate the
>>>>>>> stored procedure from within the application's code.
>>>>>>> Gene Lyman
>>>>>>>
>>>>>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>>>>>> news:4b155138@solutions.advantagedatabase.com...
>>>>>>>> Hello,
>>>>>>>>
>>>>>>>> I have create a stored SQL procedure in my data dictionary. The
>>>>>>>> syntax checks out fine and the SP name is saved in the
>>>>>>>> dictionary. All users are granted permission to "Create"
>>>>>>>> procedures and "Execute" procedures.
>>>>>>>>
>>>>>>>> The stored procedure is within in an AdsConnection tied to the
>>>>>>>> data dictionary. When I attempt to open the procedure, I get a
>>>>>>>> 5054 error, "Cannot create function object". I haven't figured
>>>>>>>> out what the problem is and would appreciate any suggestions.
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Gene Lyman
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>
>


Gene Lyman Posted on 2009-12-16 05:30:25.0Z
From: "Gene Lyman" <glyman@myactv.net>
Newsgroups: Advantage.AEP
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com> <4b1ee7ae@solutions.advantagedatabase.com> <4b1fbedc@solutions.advantagedatabase.com> <4b1fdf86@solutions.advantagedatabase.com> <4b27abbe@solutions.advantagedatabase.com> <4b27bda0@solutions.advantagedatabase.com>
In-Reply-To: <4b27bda0@solutions.advantagedatabase.com>
Subject: Re: SP Permissions-5054 error
Date: Wed, 16 Dec 2009 00:30:25 -0500
Lines: 124
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: 162.84.41.87
Message-ID: <4b286fee@solutions.advantagedatabase.com>
X-Trace: 15 Dec 2009 22:28:14 -0700, 162.84.41.87
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.84.41.87
Xref: solutions.advantagedatabase.com Advantage.AEP:771
Article PK: 1104050

How do you return the value from single field from the query? Or how can I
do the following in one transaction:
Update field to generate new value
Insert new record using new value (from above) and several other normal
input values
Still seems the best solution is to resolve the problem with the SQL stored
procedure.
procedure.
Gene Lyman

"Edgar Sherman" <no@email.com> wrote in message
news:4b27bda0@solutions.advantagedatabase.com...
>I don't understand what extra functionality is missing from a TAdsQuery.
>Running "EXECUTE PROCEDURE <procedurename>()" in a TAdsQuery should return
>the same results as using a TAdsStoredProc. That is how you execute it
>within ARC.
>
> Edgar
>
> Gene Lyman wrote:
>> Hi Edgar,
>>
>> TAdsQuery doesn't offer the functionality of the stored procedure so it
>> is not a viable solution. In my specific instance I can use a stored
>> procedure because it was easy for me to convert one table to the ADT
>> type. But this is only one table of many in my application, and I would
>> like to be able to use the stored Sql procedure more widely. Will an
>> update be made to the stored procedure component soon, or can you provide
>> me information I can use to make a patch to the component source code?
>>
>> Gene Lyman
>>
>>
>> "Edgar Sherman" <no@email.com> wrote in message
>> news:4b1fdf86@solutions.advantagedatabase.com...
>>> Hi Gene,
>>>
>>> It does not look like TAdsStoredProc has the option to change the lock
>>> type. I suggest using a TAdsQuery, set AdsTableOptions.AdsLockType =
>>> compatible, then set the TAdsQuery.SQL = "execute procedure
>>> <procedurename> (param1, pram2....);" and finally TAdsQuery.execsql to
>>> run the stored procedure.
>>>
>>> Edgar
>>>
>>> Gene Lyman wrote:
>>>> Thanks for the suggestions, but I must be missing something here. I am
>>>> attempting to access the SQL procedure through a TAdsStoredProc
>>>> component. I have attempted using various handles available to the
>>>> component to setup the AdsStmtSetTableLockType 1st parameter, hSQL.
>>>> None have been successful, and I'm not sure I'm on the right track.
>>>> Gene Lyman
>>>>
>>>> "Edgar Sherman" <no@email.com> wrote in message
>>>> news:4b1ee7ae@solutions.advantagedatabase.com...
>>>>> The answer depends on the language you are using to write your
>>>>> application.
>>>>>
>>>>> For Delphi, on the TAdsQuery Component expand the property
>>>>> AdsTableOptions and then change "AdsLockType" to Compatible.
>>>>>
>>>>> For .Net set LockMode=COMPATIBLE in the connection string.
>>>>>
>>>>> If you are using the ODBC driver, change "Advantage Locking" to
>>>>> Compatible in the ODBC DSN.
>>>>>
>>>>> For JDBC set LockType=compatible in the connection string.
>>>>>
>>>>> For VO you will want to call AX_AXSLocking( .F. )
>>>>>
>>>>> For ACE you will want to call AdsStmtSetTableLockType and pass
>>>>> ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)
>>>>>
>>>>> Edgar
>>>>>
>>>>> Gene Lyman wrote:
>>>>>> I have successfully run the SQL procedure using ARC. The problem
>>>>>> now is that when I attempt to run the procedure through the
>>>>>> application, I get a 7028 error. I have previously reported this
>>>>>> problem in the SQL newsgroup, and got the response that I have an
>>>>>> incompatible LockingMode problem. All my tables use the compatible
>>>>>> mode, the SQL Stored procedure defaults to the proprietary mode. I
>>>>>> would seem there should be a place to change this sstting, but I have
>>>>>> failed to find it. Any suggestions.
>>>>>> Gene Lyman
>>>>>>
>>>>>>
>>>>>> "Edgar Sherman" <no@email.com> wrote in message
>>>>>> news:4b16f149@solutions.advantagedatabase.com...
>>>>>>> You should be able to run the SQL "execute procedure <procedurename>
>>>>>>> (param1, param2,...);"
>>>>>>>
>>>>>>> Edgar
>>>>>>>
>>>>>>> Gene Lyman wrote:
>>>>>>>> I seem to have progressed past the 5054 error and now get the 5132
>>>>>>>> error, "an object of the same name already exists in the data
>>>>>>>> dictionary". I guess I don't understand how to activate the stored
>>>>>>>> procedure from within the application's code.
>>>>>>>> Gene Lyman
>>>>>>>>
>>>>>>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>>>>>>> news:4b155138@solutions.advantagedatabase.com...
>>>>>>>>> Hello,
>>>>>>>>>
>>>>>>>>> I have create a stored SQL procedure in my data dictionary. The
>>>>>>>>> syntax checks out fine and the SP name is saved in the dictionary.
>>>>>>>>> All users are granted permission to "Create" procedures and
>>>>>>>>> "Execute" procedures.
>>>>>>>>>
>>>>>>>>> The stored procedure is within in an AdsConnection tied to the
>>>>>>>>> data dictionary. When I attempt to open the procedure, I get a
>>>>>>>>> 5054 error, "Cannot create function object". I haven't figured
>>>>>>>>> out what the problem is and would appreciate any suggestions.
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> Gene Lyman
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>
>>


Edgar Sherman Posted on 2009-12-16 20:03:30.0Z
Date: Wed, 16 Dec 2009 13:03:30 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: Advantage.AEP
Subject: Re: SP Permissions-5054 error
References: <4b155138@solutions.advantagedatabase.com> <4b15be43@solutions.advantagedatabase.com> <4b16f149@solutions.advantagedatabase.com> <4b1ec315@solutions.advantagedatabase.com> <4b1ee7ae@solutions.advantagedatabase.com> <4b1fbedc@solutions.advantagedatabase.com> <4b1fdf86@solutions.advantagedatabase.com> <4b27abbe@solutions.advantagedatabase.com> <4b27bda0@solutions.advantagedatabase.com> <4b286fee@solutions.advantagedatabase.com>
In-Reply-To: <4b286fee@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.164
Message-ID: <4b293ca8@solutions.advantagedatabase.com>
X-Trace: 16 Dec 2009 13:01:44 -0700, 10.24.34.164
Lines: 159
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.164
Xref: solutions.advantagedatabase.com Advantage.AEP:772
Article PK: 1104048

"Still seems the best solution is to resolve the problem with the SQL
stored procedure. "

Exactly. The key word here is SQL. So why not use a TAdsQuery to
execute the SQL stored procedure.

Maybe this will give you a better example of what I mean:

First, using TAdsStoredProc:
AdsStoredProc1.DatabaseName := AdsConnection1.Name;
AdsStoredProc1.StoredProcName := 'sp_myprocedure';
AdsStoredProc1.LoadParamsFromDictionary;
AdsStoredProc1.ParamByName('myinput').Value := 'abc';
AdsStoredProc1.Open;
output1 := AdsStoredProc1.FieldByName('myoutput1').AsString;
output2 := AdsStoredProc1.FieldByName('myoutput2').AsString;
ShowMessage(output1 + ' : ' + output2);

Now, using TAdsQuery in which you set compatibility locking.
AdsQuery1.AdsConnection := AdsConnection1;
AdsQuery1.AdsTableOptions.AdsLockType := Compatible;
AdsQuery1.SQL.Text := 'execute procedure sp_myprocedure(:myinput)';
AdsQuery1.ParamByName('myinput').Value := 'abc';
AdsQuery1.Open;
output1 := AdsQuery1.FieldByName('myoutput1').AsString;
output2 := AdsQuery1.FieldByName('myoutput2').AsString;
ShowMessage(output1 + ' : ' + output2);

The two are equivalent except you can set compatible locking when you
use the Query Component.

Edgar

On 12/15/2009 10:30 PM, Gene Lyman wrote:
> How do you return the value from single field from the query? Or how can I
> do the following in one transaction:
> Update field to generate new value
> Insert new record using new value (from above) and several other normal
> input values
> Still seems the best solution is to resolve the problem with the SQL
> stored procedure.
> procedure.
> Gene Lyman
>
> "Edgar Sherman" <no@email.com> wrote in message
> news:4b27bda0@solutions.advantagedatabase.com...
>> I don't understand what extra functionality is missing from a
>> TAdsQuery. Running "EXECUTE PROCEDURE <procedurename>()" in a
>> TAdsQuery should return the same results as using a TAdsStoredProc.
>> That is how you execute it within ARC.
>>
>> Edgar
>>
>> Gene Lyman wrote:
>>> Hi Edgar,
>>>
>>> TAdsQuery doesn't offer the functionality of the stored procedure so
>>> it is not a viable solution. In my specific instance I can use a
>>> stored procedure because it was easy for me to convert one table to
>>> the ADT type. But this is only one table of many in my application,
>>> and I would like to be able to use the stored Sql procedure more
>>> widely. Will an update be made to the stored procedure component
>>> soon, or can you provide me information I can use to make a patch to
>>> the component source code?
>>>
>>> Gene Lyman
>>>
>>>
>>> "Edgar Sherman" <no@email.com> wrote in message
>>> news:4b1fdf86@solutions.advantagedatabase.com...
>>>> Hi Gene,
>>>>
>>>> It does not look like TAdsStoredProc has the option to change the
>>>> lock type. I suggest using a TAdsQuery, set
>>>> AdsTableOptions.AdsLockType = compatible, then set the TAdsQuery.SQL
>>>> = "execute procedure <procedurename> (param1, pram2....);" and
>>>> finally TAdsQuery.execsql to run the stored procedure.
>>>>
>>>> Edgar
>>>>
>>>> Gene Lyman wrote:
>>>>> Thanks for the suggestions, but I must be missing something here. I
>>>>> am attempting to access the SQL procedure through a TAdsStoredProc
>>>>> component. I have attempted using various handles available to the
>>>>> component to setup the AdsStmtSetTableLockType 1st parameter, hSQL.
>>>>> None have been successful, and I'm not sure I'm on the right track.
>>>>> Gene Lyman
>>>>>
>>>>> "Edgar Sherman" <no@email.com> wrote in message
>>>>> news:4b1ee7ae@solutions.advantagedatabase.com...
>>>>>> The answer depends on the language you are using to write your
>>>>>> application.
>>>>>>
>>>>>> For Delphi, on the TAdsQuery Component expand the property
>>>>>> AdsTableOptions and then change "AdsLockType" to Compatible.
>>>>>>
>>>>>> For .Net set LockMode=COMPATIBLE in the connection string.
>>>>>>
>>>>>> If you are using the ODBC driver, change "Advantage Locking" to
>>>>>> Compatible in the ODBC DSN.
>>>>>>
>>>>>> For JDBC set LockType=compatible in the connection string.
>>>>>>
>>>>>> For VO you will want to call AX_AXSLocking( .F. )
>>>>>>
>>>>>> For ACE you will want to call AdsStmtSetTableLockType and pass
>>>>>> ADS_COMPATIBLE_LOCKING for the 2nd parameter (usLockType)
>>>>>>
>>>>>> Edgar
>>>>>>
>>>>>> Gene Lyman wrote:
>>>>>>> I have successfully run the SQL procedure using ARC. The problem
>>>>>>> now is that when I attempt to run the procedure through the
>>>>>>> application, I get a 7028 error. I have previously reported this
>>>>>>> problem in the SQL newsgroup, and got the response that I have an
>>>>>>> incompatible LockingMode problem. All my tables use the
>>>>>>> compatible mode, the SQL Stored procedure defaults to the
>>>>>>> proprietary mode. I would seem there should be a place to change
>>>>>>> this sstting, but I have failed to find it. Any suggestions.
>>>>>>> Gene Lyman
>>>>>>>
>>>>>>>
>>>>>>> "Edgar Sherman" <no@email.com> wrote in message
>>>>>>> news:4b16f149@solutions.advantagedatabase.com...
>>>>>>>> You should be able to run the SQL "execute procedure
>>>>>>>> <procedurename> (param1, param2,...);"
>>>>>>>>
>>>>>>>> Edgar
>>>>>>>>
>>>>>>>> Gene Lyman wrote:
>>>>>>>>> I seem to have progressed past the 5054 error and now get the
>>>>>>>>> 5132 error, "an object of the same name already exists in the
>>>>>>>>> data dictionary". I guess I don't understand how to activate
>>>>>>>>> the stored procedure from within the application's code.
>>>>>>>>> Gene Lyman
>>>>>>>>>
>>>>>>>>> "Gene Lyman" <glyman@myactv.net> wrote in message
>>>>>>>>> news:4b155138@solutions.advantagedatabase.com...
>>>>>>>>>> Hello,
>>>>>>>>>>
>>>>>>>>>> I have create a stored SQL procedure in my data dictionary.
>>>>>>>>>> The syntax checks out fine and the SP name is saved in the
>>>>>>>>>> dictionary. All users are granted permission to "Create"
>>>>>>>>>> procedures and "Execute" procedures.
>>>>>>>>>>
>>>>>>>>>> The stored procedure is within in an AdsConnection tied to the
>>>>>>>>>> data dictionary. When I attempt to open the procedure, I get a
>>>>>>>>>> 5054 error, "Cannot create function object". I haven't figured
>>>>>>>>>> out what the problem is and would appreciate any suggestions.
>>>>>>>>>>
>>>>>>>>>> Thanks,
>>>>>>>>>> Gene Lyman
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>
>>>
>