SYBASE - stored procedure name

Posts   
1  /  2
 
    
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 11:09:36   

Hi,

I get an exception on sybase 15 when I select a stored procedure that has an underscore( _) in its name: ex (my_proc01) Here after the exception.

Fatal Exception caught in SybaseAseSchemaRetriever::PopulateAllStoredProcedureParameters: Input string was not in a correct format.
Input string was not in a correct format.


-----[Core exception]--------------------
   at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.PopulateAllStoredProcedureParameters(AseConnection openSybaseAseConnection)
   at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags)
   at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags)
   at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties)
   at ai.a(Boolean A_0, Hashtable A_1)
   at ai.a(Object A_0, a5 A_1)
   at ab.a4(Object A_0, EventArgs A_1)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
-----[InnerException]--------------------
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ToInt32(Object value)
   at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.PopulateAllStoredProcedureParameters(AseConnection openSybaseAseConnection)


SD.LLBLGen.Pro.DBDrivers.SybaseAseDBDriver
mscorlib
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 11:27:56   

Hmm. It might be the query which retrieves parameter direction data, however it contains a case statement which should always result in an int. Is the issue true for all procs with an underscore or just for a few? THis because the error occurs in an int conversion and IMHO it has little to do with the '' being in the proc name, as our testdb has '' in the proc names. e.g. pr_GetAllCustomers.

So if you could give me more specifics about the proc which fails it would be great.

Queries executed: SELECT DISTINCT sc.name, CASE sc.status2 WHEN 2 THEN 1 ELSE 0 END AS direction FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id WHERE so.name = @procedure_name ORDER BY sc.colid

and

we execute sp_sproc_columns on the proc

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 11:37:49   

On sybase 12 i got this one:

Method not found: 'Void Sybase.Data.AseClient.AseParameter..ctor(System.String, Sybase.Data.AseClient.AseDbType, Int32, System.Data.ParameterDirection, Boolean, Int32, Int32, System.String, System.Data.DataRowVersion, System.Object)'.

Yes it happens on all procedures (in the project I only have procedure with (_) in their name) .

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 11:52:25   

Result of : SELECT DISTINCT sc.name, CASE sc.status2 WHEN 2 THEN 1 ELSE 0 END AS direction FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id WHERE so.name = 'ps_aatest' ORDER BY sc.colid

name direction


0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 16/04/08 11:43:42 CEST ] [Execution: 0/ms]

here the result of :sp_sproc_columns ps_aatest

Warnings: ---> W (1): 010P4: Un paramètre de sortie reçu a été ignoré. <---

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

procedure_qualifier procedure_owner procedure_name column_name column_type data_type type_name precision length scale radix nullable remarks ss_data_type colid column_def sql_data_type sql_datetime_sub char_octet_length ordinal_position is_nullable mode


0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 16/04/08 11:42:09 CEST ] [Execution: 32/ms]

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 11:56:06   

erl wrote:

On sybase 12 i got this one:

Method not found: 'Void Sybase.Data.AseClient.AseParameter..ctor(System.String, Sybase.Data.AseClient.AseDbType, Int32, System.Data.ParameterDirection, Boolean, Int32, Int32, System.String, System.Data.DataRowVersion, System.Object)'.

Yes it happens on all procedures (in the project I only have procedure with (_) in their name) .

Could you post the proc here (if possible) ?

About the parameter issue... hmm. That's ADO.NET, so it should use the same provider dll... I don't see how methodnotfound exceptions can pop up there...

0 rows returned means all loops are skipped... So I fail to see where the issue occurs.. I've attached a debug build of the driver so we can better determine the line number. Could you please place this driver in the Drivers\SybaseASE folder?

Sorry for this inconvenience.

Attachments
Filename File size Added on Approval
DebugBuildASE.zip 33,244 16-Apr-2008 11:57.00 Approved
Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 11:58:23   

Note: if I rename the proc. ps_aatest ==> psaatest there is no problem

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 12:10:49   

erl wrote:

Note: if I rename the proc. ps_aatest ==> psaatest there is no problem

confused I'm stunned... also because our test procs work as expected... (with or without parameters).

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 12:35:14   

Here the procedure:

CREATE PROCEDURE dbo.ps_aatest ( @login varchar(255) , @error_code int OUT , @error_lib int OUT ) as begin

select @error_code = 10
select @error_lib = 12

select ta_users.usr_id, ta_users.usr_first_name, ta_users.usr_last_name, ta_users.usr_is_status, ta_users.usr_creation_date, ta_users.usr_last_update, ta_users.usr_login, ta_users.prof_id, ta_users.of_id from ta_users

return

end GO sp_procxmode 'dbo.ps_aatest', 'Unchained' GO

trying to debug the exception occurs HTH

00000362 C7 45 E4 FC 00 00 00 mov dword ptr [ebp-1Ch],0FCh 00000369 68 73 D5 66 0B push 0B66D573h 0000036e EB 00 jmp 00000370 00000370 90 nop **00000371 8B 4D BC mov ecx,dword ptr [ebp-44h] ** 00000374 33 D2 xor edx,edx 00000376 FF 15 6C 09 A2 00 call dword ptr ds:[00A2096Ch] 0000037c 90 nop 0000037d 8B 4D BC mov ecx,dword ptr [ebp-44h] 00000380 33 D2 xor edx,edx

This should be better:

Fatal Exception caught in SybaseAseSchemaRetriever::P opulateAllStoredProcedureParameters: Input string was not in a correct format. Input string was not in a correct format.

-----[Core exception]-------------------- at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.PopulateAllStoredProcedureParameters(AseConnection openSybaseAseConnection) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseSchemaRetriever.cs:line 843 at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseSchemaRetriever.cs:line 121 at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseCatalogRetriever.cs:line 150 at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseDBDriver.cs:line 424 at ai.a(Boolean A_0, Hashtable A_1) at ai.a(Object A_0, a5 A_1) at ab.a4(Object A_0, EventArgs A_1) at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) -----[InnerException]-------------------- at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

SD.LLBLGen.Pro.DBDrivers.SybaseAseDBDriver mscorlib

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 12:55:35   

I am very confused I' ve re-do the test You ask before and the (good ones) resullts are:

SELECT DISTINCT sc.name, CASE sc.status2 WHEN 2 THEN 1 ELSE 0 END AS direction FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id WHERE so.name = 'ps_aatest' ORDER BY sc.colid

name direction


@login 0 @error_code 1 @error_lib 1

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 16/04/08 12:45:38 CEST ] [Execution: 16/ms]

sp_sproc_columns ps_aatest

procedure_qualifier procedure_owner procedure_name column_name column_type data_type type_name precision length scale radix nullable remarks ss_data_type colid column_def sql_data_type sql_datetime_sub char_octet_length ordinal_position is_nullable mode


TRADEVISION dbo ps_aatest;1 RETURN_VALUE 5 4 int 10 4 0 10 0 NULL 56 0 NULL 4 NULL NULL 0 NO Type renvoyé TRADEVISION dbo ps_aatest;1 @login 0 12 varchar 255 255 NULL NULL 0 NULL 39 1 NULL 12 NULL 255 1 NO in TRADEVISION dbo ps_aatest;1 @error_code 0 4 int 10 4 0 10 0 NULL 56 2 NULL 4 NULL 0 2 NO out TRADEVISION dbo ps_aatest;1 @error_lib 0 4 int 10 4 0 10 0 NULL 56 3 NULL 4 NULL 0 3 NO out

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 16/ms]

1 record(s) affected

1 record(s) affected

[Executed: 16/04/08 12:46:55 CEST ] [Execution: 156/ms]

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 14:57:04   

You cut off the inner exception in that exception stack, and that one is the important one simple_smile

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 15:21:01   

Otis wrote:

You cut off the inner exception in that exception stack, and that one is the important one simple_smile

here it is

-----[Core exception]-------------------- at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.PopulateAllStoredProcedureParameters(AseConnection openSybaseAseConnection) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseSchemaRetriever.cs:line 843 at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseSchemaRetriever.cs:line 121 at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseCatalogRetriever.cs:line 150 at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseDBDriver.cs:line 424 at ai.a(Boolean A_0, Hashtable A_1) at ai.a(Object A_0, a5 A_1) at ab.a4(Object A_0, EventArgs A_1) at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) -----[InnerException]-------------------- at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.String.System.IConvertible.ToInt32(IFormatProvider provider) at System.Convert.ToInt32(Object value) at SD.LLBLGen.Pro.DBDrivers.SybaseAse.SybaseAseSchemaRetriever.PopulateAllStoredProcedureParameters(AseConnection openSybaseAseConnection) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\Drivers 2.5\SybaseASE\SybaseAseSchemaRetriever.cs:line 786

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 15:42:14   

crash happens here:


if(parameters.Rows[j]["scale"].ToString().Length > 0)
{
    newParameter.TypeDefinition.Scale = Convert.ToInt32(parameters.Rows[j]["scale"]); <<<<
}

Which is only possible if 'scale' isn't an int value and ALSO not null, as NULL values have no length when tostring is called on them in a datatable.

Very odd. I'll see if I create the proc you used if that works here.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 16:42:53   

I can't reproduce it.

I used your proc with a different select statement:


CREATE PROCEDURE dbo.ps_aatest
(
    @login          varchar(255)         ,
    @error_code     int  OUT    ,
    @error_lib       int     OUT
)
as
begin


    select @error_code = 10
    select @error_lib = 12
    

    select  EmployeeID, Name, StartDate, WorksForDepartmentID from Employee
    return
end
GO
sp_procxmode 'dbo.ps_aatest', 'Unchained'
GO

(table code:


use TestDB
go 

setuser 'dbo'
go 

create table Employee (
    EmployeeID                    int                             identity  ,
    Name                            varchar(50)                   not null  ,
    StartDate                      datetime                      not null  ,
    WorksForDepartmentID            int                               null   
)
lock allpages
 on 'default'
go 


setuser
go 

And I can create/refresh catalog a project normally without error. The proc's meta data is retrieved, the parameters are retrieved normally.

ALso when I manually select the proc, during refreshing the catalog it works properly . Creating a new project... same.

I'm confused what is causing this...

(edit) I also see now that sp_sproc_columns DOES reveal the parameter direction! How could I have missed that flushed ... (last column, mode). (in v15+)

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 18:34:37   

I think I found something strange :

I created 2 stored procedures : ps_aatest & _psUaatest _ when I call

a) sp_sproc_columns psUaatest (result is OK)

b) sp_sproc_columns ps_aatest (result is VERY STRANGE)

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 16-Apr-2008 18:38:38   

Otis wrote:

I can't reproduce it.

I used your proc with a different select statement:


CREATE PROCEDURE dbo.ps_aatest
(
    @login          varchar(255)         ,
    @error_code     int  OUT    ,
    @error_lib       int     OUT
)
as
begin


    select @error_code = 10
    select @error_lib = 12
    

    select  EmployeeID, Name, StartDate, WorksForDepartmentID from Employee
    return
end
GO
sp_procxmode 'dbo.ps_aatest', 'Unchained'
GO

(table code:


use TestDB
go 

setuser 'dbo'
go 

create table Employee (
    EmployeeID                    int                             identity  ,
    Name                            varchar(50)                   not null  ,
    StartDate                      datetime                      not null  ,
    WorksForDepartmentID            int                               null   
)
lock allpages
 on 'default'
go 


setuser
go 

And I can create/refresh catalog a project normally without error. The proc's meta data is retrieved, the parameters are retrieved normally.

ALso when I manually select the proc, during refreshing the catalog it works properly . Creating a new project... same.

I'm confused what is causing this...

(edit) I also see now that sp_sproc_columns DOES reveal the parameter direction! How could I have missed that flushed ... (last column, mode). (in v15+)

Same in Sybase v12

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Apr-2008 20:41:08   

erl wrote:

I think I found something strange :

I created 2 stored procedures : ps_aatest & _psUaatest _ when I call

a) sp_sproc_columns psUaatest (result is OK)

b) sp_sproc_columns ps_aatest (result is VERY STRANGE)

Hmm... could it be something with the global database parameters or catalog settings? We use sybase ASE with default settings (we don't use it for anything other than code testing ,so we didn't setup anything special, just the defaults).

Btw, what exactly do you mean with 'Same in sybase12' ? The docs at sybase.com say that v12 didn't have the 'mode' column ?

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 17-Apr-2008 11:09:18   

On our Sybase 12.5.03 the column "mode" is retrieved

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 17-Apr-2008 12:06:53   

Could you please send me a DLL that does:

sp_sproc_columns @procedureName2

where

procedureName2 = procedureName.Replace("", "[]");

Note: sp_sproc_columns Sybase stored procedure interpretes "_" as a wildcard the "%" also as well

question :

Is there a way to that kind of think with the SDK you provide ? Is this SDK available somewhere ?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Apr-2008 12:58:47   

erl wrote:

Could you please send me a DLL that does:

sp_sproc_columns @procedureName2

where

procedureName2 = procedureName.Replace("", "[]");

that would make pr_test become pr[]test why? I mean: when you called sp_sproc_columns with the pr... name, it worked (you posted that above)

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 17-Apr-2008 13:00:01   

Note: sp_sproc_columns Sybase stored procedure interpretes "_" as a wildcard the "%" also as well

question :

Is there a way to that kind of think with the SDK you provide ? Is this SDK available somewhere ?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Apr-2008 15:00:18   

erl wrote:

Note: sp_sproc_columns Sybase stored procedure interpretes "_" as a wildcard the "%" also as well

You got to be kidding... frowning . And [_] solves it? But what I don't understand is how can it work here? if it is assumed to be a wildcard?

When I check the documentation on sp_sprocs_columns:

procedure_name

is the name of the stored procedure. The use of wildcard characters in pattern matching is not supported.

i.o.w.: wildcards aren't supported. confused

What I don't understand is how your interact SQL test above worked OK... Is in your database the '_' defined as a valid wildcard too ?

question : Is there a way to that kind of think with the SDK you provide ? Is this SDK available somewhere ?

The SDK is available to customers in the customer area, as it contains all the sourcecode for the codegenerators and drivers.

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 17-Apr-2008 15:17:14   

Please don't post long snippets of sourcecode owned by others. Thanks -- Otis

Yes, the doc says no wildcard but this is an error here the original stored procedure code: See the bolded part.

[] is the escape sequence for Sybase this way the '' isn't interpreted as wildcard.

the test is : create 2 procedures ps_aaaabbb and paUaaabbb when you call

sp_sproc_columns paUaaabbb it is OK

sp_sproc_columns pa_aaabbb it returns info for both procedures since it consider the '_' as wildcard

/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */

/* ** Messages for "sp_sproc_columns" ** ** 18039, "Table qualifier must be name of current database" */

CREATE PROCEDURE dbo.sp_sproc_columns @procedure_name varchar(261) = '%', /* name of stored procedure / @procedure_owner varchar(257) = null, / owner of stored procedure / @procedure_qualifier varchar(257) = null, / name of current database / @column_name varchar(257) = null / col name or param name */ as -- SNIP -- Otis

/* Get Object ID */ select @procedure_id = object_id(@full_procedure_name)

if ((charindex('%',@full_procedure_name) = 0) and (charindex('_',@full_procedure_name) = 0) and @procedure_id != 0)begin /* ** this block is for the case where there is no pattern ** matching required for the table name / select / INTn, FLOATn, DATETIMEn and MONEYn types */ procedure_qualifier = db_name(), procedure_owner = user_name(o.uid), procedure_name = o.name +';'+ ltrim(str(c.number,5)), column_name = case when c.name = 'Return Type' then 'RETURN_VALUE' else c.name end, column_type = case when c.name = 'Return Type' then convert(smallint, 5) else convert(smallint, 0) end, -- SNIP -- Otis

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Apr-2008 15:23:51   

Hmm... indeed.

Ok, we're in a complicated situation here: 1) you show that the code we have could bug on some databases 2) we can't reproduce it 3) you suggest a fix where '_' are escaped with '[]' chars.

the problem is: if we escape the chars, won't that cause other problems on systems which work properly now? I mean: sure we can test it here and if it still works, it doesn't mean anything apparently as on your side, you run into errors we can't reproduce.

If '_' is seen as a wildcard, it still doesn't make it return 'scale' as non-integer... (which caused the exception in the first place)

I'll create a proc with almost the same name as you suggested but without a '_' and see what happens.

Frans Bouma | Lead developer LLBLGen Pro
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 17-Apr-2008 16:12:55   

Good question ! I can't answer I have no way to debug and see what happens.

Could be my demo version build release Could be our Sybase version the v12.5 and the v 15.0.2 Could be the Sybase ASE driver: Sybase.Data.AseClient.dll 1.15.152 ... Not easy

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Apr-2008 16:59:27   

I've created a new proc, similar to an existing one but I replaced teh '_' with 'T', so instead of pr_GetEmployeeName, I have prTGetEmployeeName.

I get no errors whatsoever. Everything works.

Though you are right that '_' is used as a wildcard. sp_sproc_columns gives the parameters of all the matching procs.

However as sybase supports stored procedure overloading (with the ;_n_ suffixes) the parameter read routine checks the proc name and if it doesn't match to the current proc, it skips the parameter.

This thus gives no errors and the right parameters retrieved for the right procs.

So for v15, I don't see how this could go wrong (as I can't make it go wrong.. the sp_sproc_columns gives the proper results)

The difference might be in the fact that you're using v12 ? I thought you're using v15 as well. Anyway, the error you ran into in your startpost is thrown when 'scale' is read from the returned list of parameters. That value is returned by the sp_sproc_columns procedure, and as it's a smallint, it can either be a number (so convert will go ok) or NULL (which will make the routine skip, as it does on varchar parameters for example).

I've tested the driver with [] and it works the same, so I've attached a new build of the driver with [] instead of _ so you can test it out on v12 to see if it fixes things for you.

(the demo you have is the same code as we use, except the designer has some demo related protection code. The rest is the same, e.g. the drivers are the same so that's not it)

Frans Bouma | Lead developer LLBLGen Pro
1  /  2