Frans, I just upgraded everything to run under LLBLGEN 2.0. It looks great and I am looking forward to using some of the new features.

I am looking at the new database function support and would like to ask/suggest a few things.  I apologize for the length of this message and if necessary I will break it into multiple threads.  I know you have been missing hearing from me.....8) 

1) I wrote a MSSQL specific factory class called SystemFunctionFactory. This factory has some of the more useful system functions in it, so the function name and code does not need to be spread around everywhere. Here is a snippet of the class....

public class SystemFunctionFactory
    {

        private SystemFunctionFactory()
        {
        }

        public static IExpression GetDate()
        {
            return new DbFunctionCall("GETDATE", null);
        }

        public static IExpression Year(object date)
        {
            return new DbFunctionCall("YEAR", new object[] { date });
        }
    }

From code you just use SystemFunctionFactory.Year(SomeEntity.SomeField) or whatever, so that makes the code much easier to read. What I would like to do is include this file somewhere in the generated code. Where would you suggest I put this? Really belongs in its own class file off the FactoryClasses folder. This is just static code, not templated in anyway and is Microsoft SQL specific. I would imagine many users are going to need (or would like) this sort of thing - and I am happy to share my class which currently has all the Date and String functions in it...

2) When writing all the above functions, I realized it would be easy to code generate factories for all user defined functions also! You would create a public method with the name of the UDF. That method would simply take arguments named after the parameters to the function and be a straight substitution, just like above. That would at least get the function name from being placed in the code as in your examples in the docs. I would write such a template, but I do not believe functions and their parameters are available to me in the templates. I was hoping you could add functions, parameters and results to the metadata available to templates.

A snippet of your code from the documentation:

 // C#
ResultsetFields fields = new ResultsetFields( 8 );
fields.DefineField( OrderFields.OrderId, 0, "OrderID" );
fields.DefineField( OrderFields.OrderDate, 1, "Month" );
fields.DefineField( OrderFields.OrderDate, 2, "Year" );
fields.DefineField( OrderFields.OrderDate, 3, "YearPlus4" );
fields.DefineField( OrderFields.OrderDate, 4, "OrderTotalWithDiscounts" );
fields.DefineField( OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts" );
fields.DefineField( OrderFields.OrderDate, 6, "YearOfGetDate" );
fields.DefineField( OrderFields.OrderDate, 7, "RealOrderDate" );

fields[1].ExpressionToApply = new DbFunctionCall( "MONTH", new object[] { OrderFields.OrderDate } );
fields[2].ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { OrderFields.OrderDate } );
fields[3].ExpressionToApply = new Expression( new DbFunctionCall( "YEAR", new object[] { OrderFields.OrderDate } ), ExOp.Add, 4 );
fields[4].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 1 } );
fields[5].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 0 } );
fields[6].ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { new DbFunctionCall( "GETDATE", null ) } );

If using my factory class from #1 above and you generated a factory class for all the functions it would look like this:

 // C#
ResultsetFields fields = new ResultsetFields( 8 );
fields.DefineField( OrderFields.OrderId, 0, "OrderID" );
fields.DefineField( OrderFields.OrderDate, 1, "Month" );
fields.DefineField( OrderFields.OrderDate, 2, "Year" );
fields.DefineField( OrderFields.OrderDate, 3, "YearPlus4" );
fields.DefineField( OrderFields.OrderDate, 4, "OrderTotalWithDiscounts" );
fields.DefineField( OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts" );
fields.DefineField( OrderFields.OrderDate, 6, "YearOfGetDate" );
fields.DefineField( OrderFields.OrderDate, 7, "RealOrderDate" );

fields[1].ExpressionToApply = SystemFunctionFactory.Month(OrderFields.OrderDate);
fields[2].ExpressionToApply = SystemFunctionFactory.Year(OrderFields.OrderDate);
fields[3].ExpressionToApply = new Expression( SystemFunctionFactory.Year(OrderFields.OrderDate)), ExOp.Add, 4 );
fields[4].ExpressionToApply = UserFunctionFactory.fn_CalculateOrderTotal(OrderFields.OrderId, 1);
fields[5].ExpressionToApply = UserFunctionFactory.fn_CalculateOrderTotal(OrderFields.OrderId, 0);
fields[6].ExpressionToApply = SystemFunctionFactory.Year(SystemFunctionFactory.GetDate());

I think you would agree the second code looks much better.

What are the chances of getting something like this??

3) Can you cover how to call the CAST() function in MSSQL? The syntax for CAST is 'CAST( somevalue AS sometype)'. So, it would be like CAST(123 as varchar(4)). How do you represent the 'varchar(4)' and the 'AS' keyword?

4) In SQL Server, function calls are broken up into two groups - one returns Scalar values and the other returns a table/resultset. Given ones that return scalar values, is there a way to get the values from those functions without putting the result in a DataTable? For example, lets say I want this query: SELECT GETDATE()

There is no table involved in this and it returns a one row, one column result, perfect for a ExecuteScalar call. Current code to do such a query would be:

    private DateTime GetSQLServerDate()
    {
        ResultsetFields fields = new ResultsetFields(1);
        fields.DefineField(WhatTableFieldIndexHere.FieldName, 0, "TheDate");  //?????
        fields[0].ExpressionToApply = new DbFunctionCall("GETDATE", null);
        TypedListDAO dao = new TypedListDAO();
        //dao.ExecuteScalarQuery()
        DataTable results = new DataTable();
        dao.GetMultiAsDataTable(fields, results, 1, null, null, null, true, null, null, 0, 0);
        if (results.Rows.Count > 0)
            return (DateTime)results.Rows[0][0];
        return null;
    }

So, two questions - what goes in the second line for the field we are defining and can we somehow do an ExecuteScalarQuery on this??

EDIT Found the answer to the Scalar Part of this last question - posted below in case it helps others - however still wonder what goes in for WhatTableFieldIndexHere.FieldName though....(I just randomly picked a field from a table...)

private DateTime GetSQLServerDate()
    {
        ResultsetFields fields = new ResultsetFields(1);
        fields.DefineField(WhatTableFieldIndexHere.FieldName, 0, "TheDate");  //?????
        fields[0].ExpressionToApply = new DbFunctionCall("GETDATE", null);
        TypedListDAO dao = new TypedListDAO();
        return (DateTime) dao.GetScalar(fields, null, null, null, null);
    }

WayneBrantley wrote:

Frans, I just upgraded everything to run under LLBLGEN 2.0. It looks great and I am looking forward to using some of the new features.

I am looking at the new database function support and would like to ask/suggest a few things.  I apologize for the length of this message and if necessary I will break it into multiple threads.  I know you have been missing hearing from me.....8) 

Yeah, I already wondered where you've been

1) I wrote a MSSQL specific factory class called SystemFunctionFactory. This factory has some of the more useful system functions in it, so the function name and code does not need to be spread around everywhere. Here is a snippet of the class....

public class SystemFunctionFactory
    {

        private SystemFunctionFactory()
        {
        }

        public static IExpression GetDate()
        {
            return new DbFunctionCall("GETDATE", null);
        }

        public static IExpression Year(object date)
        {
            return new DbFunctionCall("YEAR", new object[] { date });
        }
    }

From code you just use SystemFunctionFactory.Year(SomeEntity.SomeField) or whatever, so that makes the code much easier to read. What I would like to do is include this file somewhere in the generated code. Where would you suggest I put this? Really belongs in its own class file off the FactoryClasses folder. This is just static code, not templated in anyway and is Microsoft SQL specific. I would imagine many users are going to need (or would like) this sort of thing - and I am happy to share my class which currently has all the Date and String functions in it...

Good suggestion. I've been puzzling with providing such a list of functions myself, however the problem became very big when I realized some databases have literaly 1000's of functions so that wouldn't be feasable.

2) When writing all the above functions, I realized it would be easy to code generate factories for all user defined functions also! You would create a public method with the name of the UDF. That method would simply take arguments named after the parameters to the function and be a straight substitution, just like above. That would at least get the function name from being placed in the code as in your examples in the docs. I would write such a template, but I do not believe functions and their parameters are available to me in the templates. I was hoping you could add functions, parameters and results to the metadata available to templates.

One reason it's not there is that creating a project can already be time consuming and in most cases the UDFs in a project aren't used in client code. Furthermore, some databases don't make a distinction between a UDF and a system function, e.g.: you can't filter out which function is written by the user and which one is mapped into the schema by the RDBMS. This is unfortunate as it would retrieve 1000's of functions into the project for no particular reason.

So not really feasable.

[...] What are the chances of getting something like this??

Unfortunately, very slim.

3) Can you cover how to call the CAST() function in MSSQL? The syntax for CAST is 'CAST( somevalue AS sometype)'. So, it would be like CAST(123 as varchar(4)). How do you represent the 'varchar(4)' and the 'AS' keyword?

Hmm, good remark. that's indeed a problem with the stock DbFunctionCall class as that one assumes a function with parameters, not special syntax. The way around this is to implement your own IExpression-using DbFunctionCall class, by using the DbFunctionCall code and modify it a little. The ToQueryText method isn't virtual as inheriters would have to rewrite the main part of the class anyway.

4) In SQL Server, function calls are broken up into two groups - one returns Scalar values and the other returns a table/resultset. Given ones that return scalar values, is there a way to get the values from those functions without putting the result in a DataTable? For example, lets say I want this query: SELECT GETDATE()

There is no table involved in this and it returns a one row, one column result, perfect for a ExecuteScalar call. Current code to do such a query would be:

    private DateTime GetSQLServerDate()
    {
        ResultsetFields fields = new ResultsetFields(1);
        fields.DefineField(WhatTableFieldIndexHere.FieldName, 0, "TheDate");  //?????
        fields[0].ExpressionToApply = new DbFunctionCall("GETDATE", null);
        TypedListDAO dao = new TypedListDAO();
        //dao.ExecuteScalarQuery()
        DataTable results = new DataTable();
        dao.GetMultiAsDataTable(fields, results, 1, null, null, null, true, null, null, 0, 0);
        if (results.Rows.Count > 0)
            return (DateTime)results.Rows[0][0];
        return null;
    }

So, two questions - what goes in the second line for the field we are defining and can we somehow do an ExecuteScalarQuery on this??

Use: fields.DefineField(new EntityField("TheDate", new DbFunctionCall(...)));

I am happy to share my class

This would be nice to have either in svn or the 3rd party section.

This is unfortunate as it would retrieve 1000's of functions into the project for no particular reason.

So not really feasable.

As my gradpa use to say "unused computer time is wasted computer time"

Better have the ui spend it's time building the project than the programmer spend time coding?

Hmm, good remark. that's indeed a problem with the stock DbFunctionCall class as that one assumes a function with parameters, not special syntax. The way around this is to implement your own IExpression-using DbFunctionCall class, by using the DbFunctionCall code and modify it a little. The ToQueryText method isn't virtual as inheriters would have to rewrite the main part of the class anyway.

Another approach might be to use convert instead of cast.

From code you just use SystemFunctionFactory.Year(SomeEntity.SomeField) or whatever, so that makes the code much easier to read. What I would like to do is include this file somewhere in the generated code. Where would you suggest I put this? Really belongs in its own class file off the FactoryClasses folder. This is just static code, not templated in anyway and is Microsoft SQL specific. I would imagine many users are going to need (or would like) this sort of thing - and I am happy to share my class which currently has all the Date and String functions in it...

The question is - how do I get this static class included somewhere in the generated code? As I said, really needs to be its own file in the FactoryClasses folder. Can you advise on how I can easily include it somewhere?

One reason it's not there is that creating a project can already be time consuming and in most cases the UDFs in a project aren't used in client code. Furthermore, some databases don't make a distinction between a UDF and a system function, e.g.: you can't filter out which function is written by the user and which one is mapped into the schema by the RDBMS. This is unfortunate as it would retrieve 1000's of functions into the project for no particular reason.

So not really feasable.

Well, it would be a list of functions and I would choose exactly what goes in the project, just as I do now! There are 100's of stored procs, but I just pick the ones I want. Also, just like you have an option to NOT bring in stored proc definitions, you could have a checkbox to NOT get the function definitions. Those of us that have a database that differentiates between system and UDF and wanted to call from client code could turn that on!

Unfortunately, very slim.

I was afraid you would say that - and since no code for the GUI, that leaves me doing a Codesmith template to generate this file. That gets sort of messy - but I guess I could do a task runner that executes the comand line from codesmith. Please - dont make me do this, at least add the option to load in the metadata - even if you dont provide any templates or anything - give us - the user - the ability to do what we need with functions (while leaving the ability to NOT read the metadata in for those that dont need/use it).

Another approach might be to use convert instead of cast.

Yes, I could use Convert - but it has the same problem. Convert syntax is: 'Convert( datatype, expression [, style])'. So, an example call is Convert(varchar(32), 123). How do you get the varchar(32) into the call? It is a 'string', but it needs to go out of the DQE without quotes...

Better have the ui spend it's time building the project than the programmer spend time coding?

I could not agree more...

Please consider my plea.

This would be nice to have either in svn or the 3rd party section.

Exactly what I was thinking, unsure how to get stuff in the 3rd party section.

WayneBrantley wrote:

From code you just use SystemFunctionFactory.Year(SomeEntity.SomeField) or whatever, so that makes the code much easier to read. What I would like to do is include this file somewhere in the generated code. Where would you suggest I put this? Really belongs in its own class file off the FactoryClasses folder. This is just static code, not templated in anyway and is Microsoft SQL specific. I would imagine many users are going to need (or would like) this sort of thing - and I am happy to share my class which currently has all the Date and String functions in it...

The question is - how do I get this static class included somewhere in the generated code? As I said, really needs to be its own file in the FactoryClasses folder. Can you advise on how I can easily include it somewhere?

• make it a template, so convert it to a .template file
• create a new .templatebindings file, please see the SDK docs uploaded yesterday for details about this format. It's tictactoe but to get started you might want to familiar yourself with the XML format
• place the template in the folder you've specified in the project properties' AdditionalTemplates folder

In the generator configuration dialog, select your targetplatform/target language and target templategroup of choice. Go to tab 2, you should see your templatebindings file. Go to tab 3. Select the first task in the runqueue BELOW the directory creation tasks which are at the top, so when you add a new task it's added there. Add to the runqueue the task: SD.Tasks.Base.ConsumeTDLTemplate

It gets added to the runqueue. Select it. You'll see its parameters in the bottom grid.

Select in the bottom grid the value 'FolderName' and type HelperClasses for fileformat, specify the fileformat you want the template to have For the templateID, specify the templateID you've bound the template to in your templatebindings file. To check which ID, go to tab2 and select your templatebindings file.

Now, save your preset under a new name.

Generate code. It should now create the file in the HelperClasses folder

One reason it's not there is that creating a project can already be time consuming and in most cases the UDFs in a project aren't used in client code. Furthermore, some databases don't make a distinction between a UDF and a system function, e.g.: you can't filter out which function is written by the user and which one is mapped into the schema by the RDBMS. This is unfortunate as it would retrieve 1000's of functions into the project for no particular reason.

So not really feasable.

Well, it would be a list of functions and I would choose exactly what goes in the project, just as I do now! There are 100's of stored procs, but I just pick the ones I want. Also, just like you have an option to NOT bring in stored proc definitions, you could have a checkbox to NOT get the function definitions. Those of us that have a database that differentiates between system and UDF and wanted to call from client code could turn that on!

I understand what you want, but trust me, you don't want to wait 10 minutes till the project is created on oracle ALWAYS, unless you specify some properties in the preferences, use a dialog to select the functions etc. You might not use Oracle, but some do, and we also have to take into account that people not always read manuals and set preferences before they do things, unfortunately.

There's also no room for UDFs in the meta-data at the moment: the designer knows 4 elements: entity, typedlist, typedview and sproc call. The catalogs know catalog, schema, table view and proc.

There's a way, but its not usefull for all, as it requires you to do a little hacking with the driver code, see below.

Unfortunately, very slim.

I was afraid you would say that - and since no code for the GUI, that leaves me doing a Codesmith template to generate this file. That gets sort of messy - but I guess I could do a task runner that executes the comand line from codesmith. Please - dont make me do this, at least add the option to load in the metadata - even if you dont provide any templates or anything - give us - the user - the ability to do what we need with functions (while leaving the ability to NOT read the metadata in for those that dont need/use it).

What you can do is download the SDK, open the SQLServer Driver sourcecode and go to the SqlserverSchemaRetriever.cs file. Then browse to the method: private void RetrieveAllStoredProcedures(SqlConnection openSqlServerConnection)

That's the routine which retrieves procs. What should be done is read the UDFs also as procs but set the property IsExternal to true.

On SqlServer 2000, the query to retrieve the UDFs is: SELECT user_name(sysobjects.uid) AS ROUTINE_SCHEMA, sysobjects.name AS ROUTINE_NAME FROM sysobjects WHERE sysobjects.xtype='FN' AND user_name(sysobjects.uid)='dbo' ORDER BY ROUTINE_NAME ASC

You retrieve the procs as the current routine already does, but you should set the DBStoredProcedure property: IsExternal to true, to mark it an UDF.

To get the parameters in these procs, you don't have to do anything, these will be retrieved in the routine private void PopulateAllStoredProcedureParameters(SqlConnection openSqlServerConnection). It might be you will run into a slight problem with the return values of a scalar UDF, as these don't have a name.

Then, create a .lpt template, and traverse the schema objects in the catalogs of the project. Of these schemas, traverse all DBStoredProcedure objects in the StoredProcedure property's sortedlist. If the IsExternal flag is set to true, you found an UDF and emit whatever you need to emit into the output.

[quote]

Another approach might be to use convert instead of cast.

Yes, I could use Convert - but it has the same problem. Convert syntax is: 'Convert( datatype, expression [, style])'. So, an example call is Convert(varchar(32), 123). How do you get the varchar(32) into the call? It is a 'string', but it needs to go out of the DQE without quotes...

This would be nice to have either in svn or the 3rd party section.

Exactly what I was thinking, unsure how to get stuff in the 3rd party section.

Mail it to support AT llblgen.com and we'll upload it to the site

Frans,

Thanks for the help, I will work on what you suggested.

Yes, I could use Convert - but it has the same problem. Convert syntax is: 'Convert( datatype, expression [, style])'. So, an example call is Convert(varchar(32), 123). How do you get the varchar(32) into the call? It is a 'string', but it needs to go out of the DQE without quotes...

Do you have a solution for how to do the above with Convert? Is there a way to get a literal put into the output sql (it will be a string that is passed in, but it cannot be quoted when it is output).

Mail it to support AT llblgen.com and we'll upload it to the site

I will do that once I get it included in the generated code and can share that with it.

Thanks again.

WayneBrantley wrote:

Frans,

Thanks for the help, I will work on what you suggested.

Yes, I could use Convert - but it has the same problem. Convert syntax is: 'Convert( datatype, expression [, style])'. So, an example call is Convert(varchar(32), 123). How do you get the varchar(32) into the call? It is a 'string', but it needs to go out of the DQE without quotes...

Do you have a solution for how to do the above with Convert? Is there a way to get a literal put into the output sql (it will be a string that is passed in, but it cannot be quoted when it is output).

Hmm... That's indeed tricky. I first thought to pass another DbFunctionCall with the function 'varchar' and as parameter '32', but that wouldn't work of course as you would get '32' as a parameter.

It's a little unfortunate I didn't foresee this. As these functions aren't 'standard' but still sometimes needed in these scenario's, DbFunctionCall won't give you what you want.

I've to think this through. I see this as a bug in DbFunctionCall, so a likely fix is a different constructor, but I'm not sure if this will work out so I need some time to test things.

Otis wrote:

It's a little unfortunate I didn't foresee this. As these functions aren't 'standard' but still sometimes needed in these scenario's, DbFunctionCall won't give you what you want.

Wouldn't a type converter solve the problem of needing to Cast / Convert in the SQL?

BOb

Wouldn't a type converter solve the problem of needing to Cast / Convert in the SQL?

No, not necessarily. Remember the result of a Convert might be an intermediate result that you then do something else with, like:

'Year:'+Convert(varchar(4), Year(GetDate())

WayneBrantley wrote:

Wouldn't a type converter solve the problem of needing to Cast / Convert in the SQL?

No, not necessarily. Remember the result of a Convert might be an intermediate result that you then do something else with, like:

'Year:'+Convert(varchar(4), Year(GetDate())

Why would you be a calculation in the SQL anyway? Isn't this better done in the business layer?

BOb

Why would you be a calculation in the SQL anyway? Isn't this better done in the business layer?

I gave a very simplistic example. There are plenty of times this sort of thing comes up and you need the Convert to be at the SQL level.

• make it a template, so convert it to a .template file
• create a new .templatebindings file, please see the SDK docs uploaded yesterday for details about this format. It's tictactoe but to get started you might want to familiar yourself with the XML format
• place the template in the folder you've specified in the project properties' AdditionalTemplates folder

In the generator configuration dialog, select your targetplatform/target language and target templategroup of choice. Go to tab 2, you should see your templatebindings file. Go to tab 3. Select the first task in the runqueue BELOW the directory creation tasks which are at the top, so when you add a new task it's added there. Add to the runqueue the task: SD.Tasks.Base.ConsumeTDLTemplate

It gets added to the runqueue. Select it. You'll see its parameters in the bottom grid.

Select in the bottom grid the value 'FolderName' and type HelperClasses for fileformat, specify the fileformat you want the template to have For the templateID, specify the templateID you've bound the template to in your templatebindings file. To check which ID, go to tab2 and select your templatebindings file.

Now, save your preset under a new name.

Generate code. It should now create the file in the HelperClasses folder

Wow. I did it AND it worked. This is a much better system than you had before, enabling me to easy modify and distribute my changes without having to edit the original files that shipped. Before you wrote this to me, I had modified the original files that shipped for some custom stored proc code I am generating (to be shared with the group later) - moved that to the template bindings file - works great!

What you can do is download the SDK, open the SQLServer Driver sourcecode and go to the SqlserverSchemaRetriever.cs file. Then browse to the method: private void RetrieveAllStoredProcedures(SqlConnection openSqlServerConnection)

That's the routine which retrieves procs. What should be done is read the UDFs also as procs but set the property IsExternal to true.

On SqlServer 2000, the query to retrieve the UDFs is: SELECT user_name(sysobjects.uid) AS ROUTINE_SCHEMA, sysobjects.name AS ROUTINE_NAME FROM sysobjects WHERE sysobjects.xtype='FN' AND user_name(sysobjects.uid)='dbo' ORDER BY ROUTINE_NAME ASC

You retrieve the procs as the current routine already does, but you should set the DBStoredProcedure property: IsExternal to true, to mark it an UDF.

To get the parameters in these procs, you don't have to do anything, these will be retrieved in the routine private void PopulateAllStoredProcedureParameters(SqlConnection openSqlServerConnection). It might be you will run into a slight problem with the return values of a scalar UDF, as these don't have a name.

Then, create a .lpt template, and traverse the schema objects in the catalogs of the project. Of these schemas, traverse all DBStoredProcedure objects in the StoredProcedure property's sortedlist. If the IsExternal flag is set to true, you found an UDF and emit whatever you need to emit into the output.

I have looked at this code, does not look too bad, I may attempt it. Question is, if I attempt it and get it working - would you consider it for inclusion in the shipping product so I do not have to continue to maintain it and merge into your codebase?

Also - one other question - how can I look at project properties - in this case a 'custom' property to turn this function feature on/off, which is key...

On a related note, I need to modify the current code that is generated for stored procs. What I need in the current parser is something to the equivalent of 'does custom property X exist'. I see this is not available, which means unless you added it (I saw others ask for it), I would have to convert this to the LPT style?

Wayne

WayneBrantley wrote:

• make it a template, so convert it to a .template file
• create a new .templatebindings file, please see the SDK docs uploaded yesterday for details about this format. It's tictactoe but to get started you might want to familiar yourself with the XML format
• place the template in the folder you've specified in the project properties' AdditionalTemplates folder

In the generator configuration dialog, select your targetplatform/target language and target templategroup of choice. Go to tab 2, you should see your templatebindings file. Go to tab 3. Select the first task in the runqueue BELOW the directory creation tasks which are at the top, so when you add a new task it's added there. Add to the runqueue the task: SD.Tasks.Base.ConsumeTDLTemplate

It gets added to the runqueue. Select it. You'll see its parameters in the bottom grid.

Select in the bottom grid the value 'FolderName' and type HelperClasses for fileformat, specify the fileformat you want the template to have For the templateID, specify the templateID you've bound the template to in your templatebindings file. To check which ID, go to tab2 and select your templatebindings file.

Now, save your preset under a new name.

Generate code. It should now create the file in the HelperClasses folder

Wow. I did it AND it worked. This is a much better system than you had before, enabling me to easy modify and distribute my changes without having to edit the original files that shipped. Before you wrote this to me, I had modified the original files that shipped for some custom stored proc code I am generating (to be shared with the group later) - moved that to the template bindings file - works great!

Yes this was exactly the idea behind the re-design: changes made by customers should be easily applyable, so if you wanted to add 2 sets of changes, it should be no problem.

What you can do is download the SDK, open the SQLServer Driver sourcecode and go to the SqlserverSchemaRetriever.cs file. Then browse to the method: private void RetrieveAllStoredProcedures(SqlConnection openSqlServerConnection)

That's the routine which retrieves procs. What should be done is read the UDFs also as procs but set the property IsExternal to true.

On SqlServer 2000, the query to retrieve the UDFs is: SELECT user_name(sysobjects.uid) AS ROUTINE_SCHEMA, sysobjects.name AS ROUTINE_NAME FROM sysobjects WHERE sysobjects.xtype='FN' AND user_name(sysobjects.uid)='dbo' ORDER BY ROUTINE_NAME ASC

You retrieve the procs as the current routine already does, but you should set the DBStoredProcedure property: IsExternal to true, to mark it an UDF.

To get the parameters in these procs, you don't have to do anything, these will be retrieved in the routine private void PopulateAllStoredProcedureParameters(SqlConnection openSqlServerConnection). It might be you will run into a slight problem with the return values of a scalar UDF, as these don't have a name.

Then, create a .lpt template, and traverse the schema objects in the catalogs of the project. Of these schemas, traverse all DBStoredProcedure objects in the StoredProcedure property's sortedlist. If the IsExternal flag is set to true, you found an UDF and emit whatever you need to emit into the output.

I have looked at this code, does not look too bad, I may attempt it. Question is, if I attempt it and get it working - would you consider it for inclusion in the shipping product so I do not have to continue to maintain it and merge into your codebase?

I don't think I can do that, as that would mean everyone will pull the functions into the project as stored procedures which might not be what people want so I have to alter the gui, add code to that etc. and I'm not going to do that now. The driver is pretty stable though, so I don't think there will be a lot of changes in the sqlserver driver from now on during v2.0's lifespan.

Also - one other question - how can I look at project properties - in this case a 'custom' property to turn this function feature on/off, which is key...

You can't as the properties are fixed, but you could add a value to the llblgenpro.exe.config file and read that in the driver, as the contents should be available to you.

On a related note, I need to modify the current code that is generated for stored procs. What I need in the current parser is something to the equivalent of 'does custom property X exist'. I see this is not available, which means unless you added it (I saw others ask for it), I would have to convert this to the LPT style? Wayne

You mean, you need to generate different code for proc calls if you have UDFs read from the db?

I don't think I can do that, as that would mean everyone will pull the functions into the project as stored procedures which might not be what people want so I have to alter the gui, add code to that etc. and I'm not going to do that now. The driver is pretty stable though, so I don't think there will be a lot of changes in the sqlserver driver from now on during v2.0's lifespan.

As long as I know you will add this in at some point in the future - next release or whatever that will be good. My plan will be to have my mod completely be disabled from a config file so people can try it, turn it off in config file and still get the same 'shipping' code you are using now. If I do it and get it working and very clean, I will send it to you and we can discuss..

Quote:

On a related note, I need to modify the current code that is generated for stored procs. What I need in the current parser is something to the equivalent of 'does custom property X exist'. I see this is not available, which means unless you added it (I saw others ask for it), I would have to convert this to the LPT style? Wayne

You mean, you need to generate different code for proc calls if you have UDFs read from the db?

Actually, this is for a different mod. I have added code to dbutils.cs so that it can do an ExecuteScalar call on StoredProcs (CallScalarRetrievalStoredProcedure). This saves the work of loading it into a dataset, extracting first row, first column, etc.

I then add a custom property to each stored procedure called 'Scalar' with a value of the type of return. So, if I have a stored proc that returns a scalar 'bit' value, I add a custom property of Scalar with a value of bool.

I then generate code in RetrievalProcedures to call the proc with 'CallScalarRetrievalStoredProcedure' and return the correct value/type.

Currently I am naming the procedure with '<ProcedureName>'Scalar. From code I then can do;

bool result=SomeStoredProcScalar(12);

Ideally, I would NOT call it <ProcedureName>Scalar, but just not generate ANY code from your template if the custom property of 'Scalar' is defined and just generate my call as <ProcedureName>.

This mod might be good for the 3rd party/add-ons section too....

WayneBrantley wrote:

I don't think I can do that, as that would mean everyone will pull the functions into the project as stored procedures which might not be what people want so I have to alter the gui, add code to that etc. and I'm not going to do that now. The driver is pretty stable though, so I don't think there will be a lot of changes in the sqlserver driver from now on during v2.0's lifespan.

As long as I know you will add this in at some point in the future - next release or whatever that will be good. My plan will be to have my mod completely be disabled from a config file so people can try it, turn it off in config file and still get the same 'shipping' code you are using now. If I do it and get it working and very clean, I will send it to you and we can discuss..

Ok.

Quote:

On a related note, I need to modify the current code that is generated for stored procs. What I need in the current parser is something to the equivalent of 'does custom property X exist'. I see this is not available, which means unless you added it (I saw others ask for it), I would have to convert this to the LPT style? Wayne

You mean, you need to generate different code for proc calls if you have UDFs read from the db?

Actually, this is for a different mod. I have added code to dbutils.cs so that it can do an ExecuteScalar call on StoredProcs (CallScalarRetrievalStoredProcedure). This saves the work of loading it into a dataset, extracting first row, first column, etc.

Isn't a scalar proc just a proc with 1 output parameter (action proc)? And if it's a resultset proc, you can use the new datareader feature to fetch the proc as a datareader. At the moment already there's a routine generated for each retrieval proc to wrap it into an IRetrievalQuery object.

Isn't a scalar proc just a proc with 1 output parameter (action proc)? And if it's a resultset proc, you can use the new datareader feature to fetch the proc as a datareader. At the moment already there's a routine generated for each retrieval proc to wrap it into an IRetrievalQuery object.

Scalar proc - as in something you execute with ExecuteScalar - is a proc that returns a result set - but that result set has exactly one row and one column. The executescalar method just retrieves that value (using a datareader of course) without mapping it onto a dataset.

So, using your new IRetrievalQuery, I could do it without the current dbutils code I generated, but would need additional code everywhere I called this instead of just calling some method. So, generically, I would now need this in dbutils:

object CallProcAsScalar(IRetrievalQuery theQuery){
    object result=null
    TypedListDAO dao = new TypedListDAO();
    IDataReader reader = dao.GetAsDataReader(null, theQuery, CommandBehavior.CloseConnection );
    if( reader.Read() )
    {
        return reader.GetValue(0);
    }
    reader.Close(); 
}

I would then generate this code for each stored proc with my custom attribute:

decimal CustomerNumberOrdersScalar(object customerNum){
    return (decimal)CallProcAsScalar(GetCustomerNumberOrdersCallAsQuery(customerNum));
}

Again, this is basically what I am already doing, I am just not using your new IRetrievalQuery stuff. The first question/problem still remains - when the 'Scalar' attribute is defined, I want to skip the normal output of the proc and just do one that returns the Scalar value. This would prevent me from having Scalar appended to the method. The first method is unnecessary, because if it is a Scalar proc you would not ever call it any other way.

Scalar procs could be redefined in the database as a proc with an output parameter. I however find it to be cleaner to not have output parameters when possible. Sort of along same lines as C# - which one of these would you choose?

void GetSomeResult(out int Someval){ blah} or int GetSomeResult() {blah}

If it was a .net method, the latter, however with procs, an output parameter is the norm.

I'm wondering why you wouldn't use the stuff that's already in place like the datareader code? you then need a small method in dbutils and that's about it. An ExecuteScalar also does somewhat what you're doing in dbutils: execute the reader, and retrieve the first value.

I'm wondering why you wouldn't use the stuff that's already in place like the datareader code? you then need a small method in dbutils and that's about it. An ExecuteScalar also does somewhat what you're doing in dbutils: execute the reader, and retrieve the first value.

Well, what I had done was before LLBLGen 2.0. Now, knowing what is in LLBLGen pro, I would do/want the following:

1) Add my CallProcAsScalar that I posted above in dbutils (as you said - need this small method) - no problem.

2) I would then like to have this in my code:

bool result=SomeStoredProcedureIHave(12,154,"Test");

However, if I did not modify or enhance any other templates, I would have:

bool result=(bool)CallProcAsScalar(GetSomeStoredProcedureIHaveAsQuery(12,154,"Test"));

Since I am generating code and since it should be easy I thought, for each scalar returning stored procedure:

1) Add a custom property called Scalar and set its value to the type of value it returns. 2) Do not output the current 'StoredProcName' method - instead output my own 'StoredProcName' method that returns the correct type.

This would make it extremely simple to call said procedure - since I defined that I want a scalar result in the GUI designer - would make sure people did not call it 'normally' and then get a dataset back, look in the table, etc....

Since 2.0, what you have makes this much easier to support without doing what I had to do in 1.0. I am just trying to do an add-on to LLBLGen to make it directly support Scalar returning procs very nicely. In order to do that in regular templates, I need 'DoesPropertyExist' - However - I am guessing that you do not have such a method as 'does property exist' for your templates?

Does custom property exist? No, unfortunately that's not there. You can loop over the custom properties in a foreach loop and then test if the current custom property name is equal to some string, using a TDL if statement. Not the brightest way to do it but it could work. A .lpt template is easier I think.

You can loop over the custom properties in a foreach loop and then test if the current custom property name is equal to some string, using a TDL if statement

Yeah, but you cannot tell if one is NOT there!!

I guess to do this, I would have to convert your stored proc stuff to lpt templates - either that or live with the word 'Scalar' on the end of the proc name, some never called generated code, and other users of the LLBLGen generated code doing extra work potentially to call such a proc...

Will have to sleep on it...

Frans,

Thanks for the help, I will work on what you suggested.

Yes, I could use Convert - but it has the same problem. Convert syntax is: 'Convert( datatype, expression [, style])'. So, an example call is Convert(varchar(32), 123). How do you get the varchar(32) into the call? It is a 'string', but it needs to go out of the DQE without quotes...

Do you have a solution for how to do the above with Convert? Is there a way to get a literal put into the output sql (it will be a string that is passed in, but it cannot be quoted when it is output).

Hmm... That's indeed tricky. I first thought to pass another DbFunctionCall with the function 'varchar' and as parameter '32', but that wouldn't work of course as you would get '32' as a parameter.

It's a little unfortunate I didn't foresee this. As these functions aren't 'standard' but still sometimes needed in these scenario's, DbFunctionCall won't give you what you want.

I've to think this through. I see this as a bug in DbFunctionCall, so a likely fix is a different constructor, but I'm not sure if this will work out so I need some time to test things.

Any progress on the above? I need to do something like this. If not, I can use a view. Here is an example of what might be used: convert(char(10),dbo.DateToFirstDayOfMonth(datebilled),111) as BillPeriod and datename(m, datebilled)+' '+cast(datepart(yyyy, datebilled) as varchar(4)) as PeriodName

No progress since then, as it's a low priority issue.

Well, I guess I can use views until you enhance this part - as long as it is 'on your list', I am good!