Reverse-engineer Resultsets to TypedView Definitions... greyed out

Posts   
 
    
jrg
User
Posts: 10
Joined: 12-Oct-2018
# Posted on: 02-Jan-2019 09:30:07   

I would upload a screenshot of this, but I can't.

Anyway,

In my catalog, I have a list of stored procedures. I want to Reverse engineer a TypedView definition, but the option is greyed out.

I am using LLBLGen Pro v5.2 RTM (5.2.1). Also, my database is Sybase, which probably explains why it doesn't work, as Sybase doesn't seem to work at all.

Anyway, can you please hep me.

Thanks

Attachments
Filename File size Added on Approval
2-01-2019 7-25-15 PM.jpg 38,738 02-Jan-2019 09:30.27 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Jan-2019 09:48:33   

Sybase isn't a supported database. We have open sourced the driver/dqe/templates for the sybase databases on github so users who do want to use these databases can do so, I assume you're using the latest code from that repository. https://github.com/SolutionsDesign/LLBLGenProSybaseASA and: https://github.com/SolutionsDesign/LLBLGenProSybaseASE

It's greyed out if there's no resultset shape known. Your screenshot doesn't show that, so I can't see if that's the case for you (expand the node, has it a resultset with fields?). You have to check the checkbox in front of the stored procedure at the last step of the sync wizard when pulling meta-data from the database.

Frans Bouma | Lead developer LLBLGen Pro
jrg
User
Posts: 10
Joined: 12-Oct-2018
# Posted on: 02-Jan-2019 10:18:23   

Otis wrote:

Sybase isn't a supported database. We have open sourced the driver/dqe/templates for the sybase databases on github so users who do want to use these databases can do so, I assume you're using the latest code from that repository. https://github.com/SolutionsDesign/LLBLGenProSybaseASA and: https://github.com/SolutionsDesign/LLBLGenProSybaseASE

It's greyed out if there's no resultset shape known. Your screenshot doesn't show that, so I can't see if that's the case for you (expand the node, has it a resultset with fields?). You have to check the checkbox in front of the stored procedure at the last step of the sync wizard when pulling meta-data from the database.

Hi,

Thanks for responding so quickly.

I checked the box, and I can see that that fixed it in some cases but not others. So it's not a feature that I will be able to make use of unfortunately.

I am not the boss. The company I work for uses Sybase + LLBLgen. So what you are saying is that it's a miracle our code still works , give that you don't support the database we use? OK. I guess I will have to tell my boss.

Thanks

jrg
User
Posts: 10
Joined: 12-Oct-2018
# Posted on: 02-Jan-2019 10:23:45   

So I have one more question:

is it at all possible for me to manually create a typed view for a stored procedure? I would rather define the data returned by the stored procs in LLBLGEN than in the code.

Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Jan-2019 15:19:55   

jrg wrote:

Otis wrote:

Sybase isn't a supported database. We have open sourced the driver/dqe/templates for the sybase databases on github so users who do want to use these databases can do so, I assume you're using the latest code from that repository. https://github.com/SolutionsDesign/LLBLGenProSybaseASA and: https://github.com/SolutionsDesign/LLBLGenProSybaseASE

It's greyed out if there's no resultset shape known. Your screenshot doesn't show that, so I can't see if that's the case for you (expand the node, has it a resultset with fields?). You have to check the checkbox in front of the stored procedure at the last step of the sync wizard when pulling meta-data from the database.

I checked the box, and I can see that that fixed it in some cases but not others. So it's not a feature that I will be able to make use of unfortunately.

What it does is that it orders the driver to execute the stored procedure with default parameter values, then rolls back the transaction (so no data is left behind if the proc does anything with that). It can be the stored procedure crashes on the input given, doesn't result in a resultset with the input given etc. You know the proc, I don't so I can't say if it should return a resultset regardless. Please check the applicationoutput pane in the designer, it should enlist some error if the proc gave an error during resultset retrieval.

You can change the default value sent to the proc for a type by clicking the button 'Define default values for parameter types' on the stored procedure selection tab in the wizard.

I am not the boss. The company I work for uses Sybase + LLBLgen. So what you are saying is that it's a miracle our code still works , give that you don't support the database we use? OK. I guess I will have to tell my boss.

Since v5.0 we don't support Sybase databases anymore (https://www.llblgen.com/Documentation/5.5/Designer/BreakingChanges.htm#designer-3). We did our best to make this as painless as possible by providing the source code for the driver, the DQE and the templates on GitHub so users could continue with their projects into v5.x. We updated these repositories from time to time, we had a community contribution who fixed a couple of things etc. The fact you get normal support here also shows we don't let you stand in the cold because you use Sybase. It's just that we won't test things on sybase anymore (simply because we don't have access to sybase databases) and therefore if you have an issue we can't fix it hence we dropped support. We can however try to point you in the right direction so you can get it fixed yourself.

'miracle' is a bit dramatic, you obviously used the driver / dqe from the github repo as v5.x doesn't ship with support for Sybase at all. The code isn't that much and straight forward. With an update of the designer it might be some things break and you have to do some refactoring. But as the code is available to you, it won't be much more work than say a couple of hours. And if you get stuck, just let us know.

jrg wrote:

So I have one more question: is it at all possible for me to manually create a typed view for a stored procedure? I would rather define the data returned by the stored procs in LLBLGEN than in the code. Thanks

For a typed view to be defined we need the resultset shape, and if the proc doesn't return that because it e.g. crashes on the input given in the driver, then the designer doesn't know the resultset and it can't create a typedview definition for the proc.

So the first thing to check is why the stored procedure doesn't return a resultset when you run sync in the designer: does it give an exception? We pass default values to the proc in the parameters, so e.g. a varchar parameter receives "", an int parameter receives 0 etc. If the proc e.g. has an if statement and based on the input returns different resultsets, it won't work, as the designer can't know what the resultset looks like. It might be your stored procedure looks like that, but as you haven't given any more info, we can't be sure.

Also, you haven't specified if you use ASE or ASA, there are 2 databases from Sybase we supported (both aren't supported anymore), from your post history I recon it's ASE.

What surprises me a bit is that 'sybase isn't supported anymore' is new to you, considering I told you that before, in October: https://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=142366&ThreadID=24936

Frans Bouma | Lead developer LLBLGen Pro
jrg
User
Posts: 10
Joined: 12-Oct-2018
# Posted on: 21-Jan-2019 03:21:30   

Otis wrote:

jrg wrote:

Otis wrote:

Sybase isn't a supported database. We have open sourced the driver/dqe/templates for the sybase databases on github so users who do want to use these databases can do so, I assume you're using the latest code from that repository. https://github.com/SolutionsDesign/LLBLGenProSybaseASA and: https://github.com/SolutionsDesign/LLBLGenProSybaseASE

It's greyed out if there's no resultset shape known. Your screenshot doesn't show that, so I can't see if that's the case for you (expand the node, has it a resultset with fields?). You have to check the checkbox in front of the stored procedure at the last step of the sync wizard when pulling meta-data from the database.

I checked the box, and I can see that that fixed it in some cases but not others. So it's not a feature that I will be able to make use of unfortunately.

What it does is that it orders the driver to execute the stored procedure with default parameter values, then rolls back the transaction (so no data is left behind if the proc does anything with that). It can be the stored procedure crashes on the input given, doesn't result in a resultset with the input given etc. You know the proc, I don't so I can't say if it should return a resultset regardless. Please check the applicationoutput pane in the designer, it should enlist some error if the proc gave an error during resultset retrieval.

You can change the default value sent to the proc for a type by clicking the button 'Define default values for parameter types' on the stored procedure selection tab in the wizard.

I am not the boss. The company I work for uses Sybase + LLBLgen. So what you are saying is that it's a miracle our code still works , give that you don't support the database we use? OK. I guess I will have to tell my boss.

Since v5.0 we don't support Sybase databases anymore (https://www.llblgen.com/Documentation/5.5/Designer/BreakingChanges.htm#designer-3). We did our best to make this as painless as possible by providing the source code for the driver, the DQE and the templates on GitHub so users could continue with their projects into v5.x. We updated these repositories from time to time, we had a community contribution who fixed a couple of things etc. The fact you get normal support here also shows we don't let you stand in the cold because you use Sybase. It's just that we won't test things on sybase anymore (simply because we don't have access to sybase databases) and therefore if you have an issue we can't fix it hence we dropped support. We can however try to point you in the right direction so you can get it fixed yourself.

'miracle' is a bit dramatic, you obviously used the driver / dqe from the github repo as v5.x doesn't ship with support for Sybase at all. The code isn't that much and straight forward. With an update of the designer it might be some things break and you have to do some refactoring. But as the code is available to you, it won't be much more work than say a couple of hours. And if you get stuck, just let us know.

jrg wrote:

So I have one more question: is it at all possible for me to manually create a typed view for a stored procedure? I would rather define the data returned by the stored procs in LLBLGEN than in the code. Thanks

For a typed view to be defined we need the resultset shape, and if the proc doesn't return that because it e.g. crashes on the input given in the driver, then the designer doesn't know the resultset and it can't create a typedview definition for the proc.

So the first thing to check is why the stored procedure doesn't return a resultset when you run sync in the designer: does it give an exception? We pass default values to the proc in the parameters, so e.g. a varchar parameter receives "", an int parameter receives 0 etc. If the proc e.g. has an if statement and based on the input returns different resultsets, it won't work, as the designer can't know what the resultset looks like. It might be your stored procedure looks like that, but as you haven't given any more info, we can't be sure.

Also, you haven't specified if you use ASE or ASA, there are 2 databases from Sybase we supported (both aren't supported anymore), from your post history I recon it's ASE.

What surprises me a bit is that 'sybase isn't supported anymore' is new to you, considering I told you that before, in October: https://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=142366&ThreadID=24936

Hi,

I use ASE. Also, I am not surprised by the fact that you don't support Sybase.

The stored procs have to be called in a particular order, otherwise they will crash.

So I will just manually create classes that each of which represents the output of a stored proc. I was thinking that your gui tool had a feature that could let me create these classes, but I don't think so.

Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Jan-2019 09:20:49   

Hmm, could you elaborate abit why they have to be in a given order? ASE's ADO.NET provider was problematic to work with, IIRC, it was very buggy. No idea if SAP has updated it, but in the years we did support ASE they didn't release many bugfixes (if at all)

If the resultset is obtained, the designer will generate classes for them, but obviously can't if the resultset isn't known.

Frans Bouma | Lead developer LLBLGen Pro
jrg
User
Posts: 10
Joined: 12-Oct-2018
# Posted on: 05-Feb-2019 00:39:12   

Otis wrote:

Hmm, could you elaborate abit why they have to be in a given order?

Each stored proc executes a step in a business process (order creation).

Stored proc b uses data created by stored proc a, hence stored proc a has to be called before b.

I just want to make sure that (a) each stored proc returns data and (b) that the type of each column returned is correct. I think it would be a good feature if you could in the gui tool auto generate a class that represents the output of a stored proc.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 05-Feb-2019 06:46:53   

jrg wrote:

Otis wrote:

Hmm, could you elaborate abit why they have to be in a given order?

Each stored proc executes a step in a business process (order creation).

Stored proc b uses data created by stored proc a, hence stored proc a has to be called before b.

Understood, this is specific of your application, not in general in Sybase Databases.

I just want to make sure that (a) each stored proc returns data and (b) that the type of each column returned is correct. I think it would be a good feature if you could in the gui tool auto generate a class that represents the output of a stored proc.

You didn't answer above, about whether or not you get an error in application output:

Otis wrote:

What it does is that it orders the driver to execute the stored procedure with default parameter values, then rolls back the transaction (so no data is left behind if the proc does anything with that). It can be the stored procedure crashes on the input given, doesn't result in a resultset with the input given etc. You know the proc, I don't so I can't say if it should return a resultset regardless. Please check the applicationoutput pane in the designer, it should enlist some error if the proc gave an error during resultset retrieval. ... So the first thing to check is why the stored procedure doesn't return a resultset when you run sync in the designer: does it give an exception? We pass default values to the proc in the parameters, so e.g. a varchar parameter receives "", an int parameter receives 0 etc. If the proc e.g. has an if statement and based on the input returns different resultsets, it won't work, as the designer can't know what the resultset looks like. It might be your stored procedure looks like that, but as you haven't given any more info, we can't be sure.

David Elizondo | LLBLGen Support Team