Hello,

My project supports both Oracle and SQL Server.

The database does not use stored procedures and does not use foreign keys. In fact the database just does data storage.

I started my LLBLGen Pro 2.6 Project by generating code for the SQL Server schema. I added the foreign keys for LLBLGen Pro. I'm sure I will have to tweak later, but do far so good.

So here is my question: Now I want to add support for Oracle. I've looked at many postings regarding this topic. It seems that there are multiple options.

1. The first solution mentions using type converters.

Using type converters you can create a single entity set and target both databases without losing details for the specific databases (e.g. timezone specific timestamps on oracle), and you decide how you convert the values back/forth.

1. The second solution has to do with using Project Converter.

You will need to generate one generic project and then on SQL and one Oracle DBSpecific project. If you are using 2.0 you can use the Project Convert to to take your current Oracle or MSSQL project and convert it to the other type. It can be downloaded under extras in the customer section.

1. The other recommendation has to do with creating an SQL Server project, and and Oracle Project then merging the two.

You should use Adapter TemplateSet. This are the steps:

1. Create a separate llblgen projects for each database type that you want to interface to.
2. Change the ConnectionStringKeyName "Project property" for projects. For example "SQL.ConnectionString" and "Oracle.ConnectionString". This way DBSpecific projects could distinguish the connection string.
3. Choose one of these llblgen projects as my "master" project.
4. Generate the code out of both projects.
5. Take both the DB Generic and DB Specific VS projects out of the llblgen "master" project. Take only the DB Specific VS project out of the other llblgen project.
6. Now, put all together in a VS solution. You now have a solution with two DBScpecific projects and one DBGeneric one.

Persistence information is determined based on EntityName and EntityFieldName. You have to be sure the entity names and entity field names are the same in both projects.

I suspect that there are only 2 options (2 & 3) and that 1 is something you apply to both 2 & 3. Is this correct?

What are the pros and cons of using 2 vs. 3?

I created the foreign keys on the SQL Server database for LLBL Gen. Now I want to generate the Oracle code. What do I do next?

Thanks

Barry

BB wrote:

I suspect that there are only 2 options (2 & 3) and that 1 is something you apply to both 2 & 3. Is this correct?

2 and 3 make sense to me... please post the link where you read 1.

BB wrote:

What are the pros and cons of using 2 vs. 3?

2 and 3 are the same, or at least complementary. 3 is the general way to proceed and 2 is a shortcut to generate one llblgen project based on another. For example, if you have a SQLServer LGP and you want the same thing (without loose custom relations, entity modifications, etc) for another DB (say Oracle), then your point 2 could help you here.

BB wrote:

I created the foreign keys on the SQL Server database for LLBL Gen. Now I want to generate the Oracle code. What do I do next?

See point 2

Point 1 is required if you for example want to use bit fields in sqlserver, which become bool fields in an entity, but Oracle doesn't have a bool/bit type. You then can create a NUMBER(1,0) field in oracle instead and use a typeconverter to be able to keep the entities the same in both projects as you can change the int field in the oracle project to bool using the type converter.

So, in general, you'd do: - create sqlserver llblgen pro project - use project converter to convert sqlserver llblgen pro project to an oracle llblgen pro project (original is kept, so you create a copy which is converted) - load the oracle project and check whether there are type converters needed because some types in sqlserver aren't available in oracle. - use the DDL SQL templates (see customer area -> tasks and templates section) to generate from the Oracle project a create script so you can create the oracle schema. - generate from both projects code for adapter - keep 1 generated DBGeneric vs.net project (as you've kept them the same it's not important which one) and both DBSpecific projects.

Guys, Thanks for the suggestions. I had created the Oracle Project from the database. Sounds like you are recommending to use the Project Converter, so I will do it that way too.

Regarding use of the Project Converter.

I found 2 read me type documents for the Project Converter in the project directories. If there is additional documentation I didn't find it.

It took me a while, to long actually, to get it compiled and to do a conversion. I would address these issues in the read me files.

1. Suggest an installation location or specify that it doesn't matter.
2. User must correct References to applictionCore and DBDriverCore before compiling.- (you already touched on this in the readme)
3. Setup the correct paths in the appSettings setting of app.Config.

Barry

OH BOY, I am so confused.

I did not know what the DDL Template did until I ran it. It created a script from the SQLServer database to create an Oracle Database. (Cool!)

This doesn't help me. I guess we had a misunderstanding along the way. My databases, both Oracle and SQLServer have been in use now for 10+ years.

When the original developers created the database they did not use foreign keys. I added foreign keys to the SQLServer (development) database so that it would play well with LLBLGen Pro. We don't intend to add the FKs to the production databases.

So what Is unclear is if I need to add the foreign keys into the Oracle Project. From what I can see the relations is part of generic. If this the case? Is there any reason for me to add the FKs to the Oracle project? (If so then the DDL created by the templates would be useful.)

As your schemas are in production for a long time, is it true that the schema on sqlserver is equal to the schema in oracle? (e.g. same tables, fields etc. ) ? Or are they different (e.g. oracle has different normalization, more/less tables, more/less fields in a table etc.)

The FK's are used for relation creation. You don't need the production database to create a project however. You can use any schema on a developer box (and which is the same as the production schema but WITH FK's) and create the project from that schema and at runtime, simply use schema overwriting which works the same as catalog overwriting.

So I think the first thing to determine is if the schemas are equal. If not, it will be problematic to use a single set of entities for BOTH databases. Though it might also be that we misunderstood you in what you are planning to do with sqlserver and oracle . Most people who need more than 1 database type use that to have 1 code base and be able to support the code base on multiple databases. But I get the feeling you simply want to work with multiple databases in one project: e.g. The CRM data from an Oracle box and the HRM data from a sqlserver box. Am I correct?

We currently use ODBC to connect to the databases. In the past the developers avoided datatypes that were not ODBC compatible. With the conversion to dot.net that changes for a few fields.

To put this in perspective, EACH system consist of App Server, DB Server, and Web Server. My company's db standard is SQLServer in the states and Oracle in Europe and in Canada. Therefore the software must run on both. For the past 10 years not a problem with ODBC and C++.

For this release we will be using date fields in the databases instead of integer. DateTime in SQLServer and Timestame(3) in Oracle. We will also be using 1 CLOB field in Oracle which will have to be treated differently from SqlServer since the data size might exceed the 4K limit of Oracle.

I don't know about the CLOB yet, but the timestamp(3) to dot.net datetime is probably no problem with your data type converters.

So in answer to your question...

The Objective is : ONE code base that works with SQLServer and Oracle.

I believe the Schemas are identical and there is no reason for them to be different.

Our databases are pretty simple, like 25 tables and 100FKs. We have refineries and chemical plants all over the world. We also have external customers who have the same. Each SITE has its own database. So I have a plenty of SITES and plenty of databases.

I hope it helps to have a picture.

So, you think I'm ok with the FKs only on the SQLServer then I use Project Converter to create an Oracle Project.

So now I'm going to try to add the type converter for the datetime fields on the Oracle project.

This will be really cool if it works when I get finished.

Barry

I am so close!

In review: 1. Created SQLServer master Project 2. Created Oracle Project using Project Converter 3. Generated code for both. 4. Many iterations to get all the names correct ... 5. Created a console application that gets data from 3 databases, 2 SQLServer and 1 Oracle.

I am dynamically setting the connection parameters. The SQLServer operations work! The Oracle operations are not working.

I probably don't have something setup correctly, but I can't see the query in debug mode. The SQLServer queries are printing, but Oracle query is not. Here is the error message:

Additional information: An exception was caught during the execution of a retrieval query: ORA-00942: table or view does not exist. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Here is my Oracle Code:

//Now Lets Try Oracle

      RelationPredicateBucket filter3 = new RelationPredicateBucket(LoopsFields.Unitid == 22);
      EntityCollection<LoopsEntity> loops3 = new EntityCollection<LoopsEntity>();
      string CS3 = "data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = HOUIC-S-7039.americas.shell.com)(Port = 1521)))(CONNECT_DATA = (SID = WTC1)));user id=XXXXXXXX;password=XXXXXXX;persist security info=false;";
    
      using (MDProOnline.Oracle.DataAccessAdapter adapter3 = new MDProOnline.Oracle.DataAccessAdapter(CS3, false, SchemaNameUsage.ForceName, "cpauser_mtz"))
      {
        adapter3.FetchEntityCollection(loops3, filter3);
      }
      Console.WriteLine("Number of Entities fetched: {0}", loops3.Count);
      foreach (LoopsEntity l3 in loops3)
      {
        Console.WriteLine("{0} {1}", l3.Loopid, l3.LoopName);
      }
      s = Console.ReadLine();

1. Did I setup the call correctly around SchemaNameUsage?
2. It would really help to see the query.

Barry

To trace the generated sql queries you should have the following in your application config file.

<system.diagnostics>
    <switches>
        <add name="SqlServerDQE" value="4" />
        <add name="OracleDQE" value="4" />
    </switches>
</system.diagnostics>

Then inspect the V.S. output window at debug mode.

Very good! I had those lines in the app config except SQLServerDQE was set to 0.

OK, getting the query did help.

I ran the query as is and it did not work.

SELECT 
"cpauser_mtz"."Loops_tb"."Loop_id" AS "Loopid", "cpauser_mtz"."Loops_tb"."Unit_id" AS "Unitid", "cpauser_mtz"."Loops_tb"."LoopName_txt" AS "LoopName", "cpauser_mtz"."Loops_tb"."LoopType_id" AS "LoopTypeid", "cpauser_mtz"."Loops_tb"."LoopDescription_txt" AS "LoopDescription", "cpauser_mtz"."Loops_tb"."LoopCreationTime_dte" AS "LoopCreationTime", "cpauser_mtz"."Loops_tb"."LoopValidation_flg" AS "LoopValidation", "cpauser_mtz"."Loops_tb"."LoopStatusTag_id" AS "LoopStatusTagid", "cpauser_mtz"."Loops_tb"."LoopLPObjectiveFuncTag_id" AS "LoopLPObjectiveFuncTagid", "cpauser_mtz"."Loops_tb"."LoopManualInService_flg" AS "LoopManualInService", "cpauser_mtz"."Loops_tb"."CalcWindowNumPts_nbr" AS "CalcWindowNumPts", "cpauser_mtz"."Loops_tb"."CalcAnalysisFreq_nbr" AS "CalcAnalysisFreq", "cpauser_mtz"."Loops_tb"."ControllerExecutionRate_nbr" AS "ControllerExecutionRate", "cpauser_mtz"."Loops_tb"."LoopIncentiveFactor_nbr" AS "LoopIncentiveFactor", "cpauser_mtz"."Loops_tb"."LoopCPACalculations_flg" AS "LoopCPACalculations", "cpauser_mtz"."Loops_tb"."LoopCPMCalculations_flg" AS "LoopCPMCalculations", "cpauser_mtz"."Loops_tb"."LoopCPMReporting_flg" AS "LoopCPMReporting", "cpauser_mtz"."Loops_tb"."LastTimeModified_dte" AS "LastTimeModified", "cpauser_mtz"."Loops_tb"."LastUserToModify_txt" AS "LastUserToModify", "cpauser_mtz"."Loops_tb"."LoopStatusTagMap_nbr" AS "LoopStatusTagMap", "cpauser_mtz"."Loops_tb"."ComplexLoop_flg" AS "ComplexLoop" FROM "cpauser_mtz"."Loops_tb" WHERE ( ( "cpauser_mtz"."Loops_tb"."Unit_id" = :Unitid1))

ORA-00942: table or view does not exist. The error marker

I then removed all the " characters and the query worked.

SELECT 
cpauser_mtz.Loops_tb.Loop_id AS Loopid, 
cpauser_mtz.Loops_tb.Unit_id AS Unitid, 
cpauser_mtz.Loops_tb.LoopName_txt AS LoopName, 
cpauser_mtz.Loops_tb.LoopType_id AS LoopTypeid, 
cpauser_mtz.Loops_tb.LoopDescription_txt AS LoopDescription, 
cpauser_mtz.Loops_tb.LoopCreationTime_dte AS LoopCreationTime, 
cpauser_mtz.Loops_tb.LoopValidation_flg AS LoopValidation, 
cpauser_mtz.Loops_tb.LoopStatusTag_id AS LoopStatusTagid, 
cpauser_mtz.Loops_tb.LoopLPObjectiveFuncTag_id AS LoopLPObjectiveFuncTagid, 
cpauser_mtz.Loops_tb.LoopManualInService_flg AS LoopManualInService, 
cpauser_mtz.Loops_tb.CalcWindowNumPts_nbr AS CalcWindowNumPts, 
cpauser_mtz.Loops_tb.CalcAnalysisFreq_nbr AS CalcAnalysisFreq, 
cpauser_mtz.Loops_tb.ControllerExecutionRate_nbr AS ControllerExecutionRate, 
cpauser_mtz.Loops_tb.LoopIncentiveFactor_nbr AS LoopIncentiveFactor, 
cpauser_mtz.Loops_tb.LoopCPACalculations_flg AS LoopCPACalculations, 
cpauser_mtz.Loops_tb.LoopCPMCalculations_flg AS LoopCPMCalculations, 
cpauser_mtz.Loops_tb.LoopCPMReporting_flg AS LoopCPMReporting, 
cpauser_mtz.Loops_tb.LastTimeModified_dte AS LastTimeModified, 
cpauser_mtz.Loops_tb.LastUserToModify_txt AS LastUserToModify, 
cpauser_mtz.Loops_tb.LoopStatusTagMap_nbr AS LoopStatusTagMap, 
cpauser_mtz.Loops_tb.ComplexLoop_flg AS ComplexLoop 
FROM cpauser_mtz.Loops_tb
WHERE ( ( cpauser_mtz.Loops_tb.Unit_id = :Unitid1))

This one returned the results.

I found that I had to remove the quotes around all the schema, table, and fields for the query to work.

How do I fix this?

Barry

All names are upper case in oracle I pressume? (and your oracle schema is set to case sensitive (default) ? )

Yes,

I don't know about the second question. When I look at the schemas, table, and name in the database they have all been converted to capital letters. The queries we normally use are not case sensitive.

"Select * from loops_tb" works just fine.

It seems like your oracle database is case sensitive. The sqlserver names have different casing apparently and can't be used directly, as they'll cause case mismatches.

This can be fixed by a change in the ProjectConverter:

ProjectConverter.Core/ElementHandlers/TableFieldsHandler.cs, routine: ConvertField. There, you should convert the name to uppercase.

Additionally, after line 66 in the same code file, upper case the table name. Additionally, after line 63 in the same code file, upper case the schema name.

BB wrote:

Yes,

I don't know about the second question. When I look at the schemas, table, and name in the database they have all been converted to capital letters. The queries we normally use are not case sensitive.

"Select * from loops_tb" works just fine.

Additionally to what Walaa said: oracle automatically converts names without "" to uppercase in a query, so Select * from loops_tb is equal to SELECT * FROM LOOPS_TB

When a name is surrounded by "", the name is taken as-is, to allow case sensitive names.

Yes I tested the query with all caps and it worked.

Would it make more sense to set the database to not be case sensitive?

Is there an option to turn off the quotes in the query generator?

BB wrote:

Yes I tested the query with all caps and it worked.

Would it make more sense to set the database to not be case sensitive?

I don't think that's an option for your DBA, as case sensitivity affects also string compares inside the database. I don't think your DBA will change that.

Is there an option to turn off the quotes in the query generator?

No, the quotes are always emitted. If you can't make the project converter change, we'll add the feature for you. This does require that you to reconvert the sqlserver project to oracle. I'm not sure if that's an option for you, considering you mentioned 'many iterations to get the names correct', which suggests re-converting the project will require those iterations all over again, correct?

The many iterations had to do with project settings and making the SQLServer and Oracle play well together inside the generated code. For example "database specific" default value in the project options needed to be changed appropriately. That would be obvious to you, but as a first time user I discovered the issues at compile time. After looking at the generated code the necessary modifications became obvious. At first the project options were a bit overwhelming.

I'm still trying to think through this. I don't know the situations with the various Oracle installations. The uppercase everything solution would only work if all Oracle installations were set to automatically uppercase objects. If Oracle had an option for that then all bets are off.

Barry

BB wrote:

The many iterations had to do with project settings and making the SQLServer and Oracle play well together inside the generated code. For example "database specific" default value in the project options needed to be changed appropriately. That would be obvious to you, but as a first time user I discovered the issues at compile time. After looking at the generated code the necessary modifications became obvious. At first the project options were a bit overwhelming.

Ok, so a change to the project converter should fix it.

I'm still trying to think through this. I don't know the situations with the various Oracle installations. The uppercase everything solution would only work if all Oracle installations were set to automatically uppercase objects. If Oracle had an option for that then all bets are off.

As oracle is case sensitive by default and you have current software in place which is ran on those boxes, I'd be surprised if one installation is case sensitive and the other one isn't: to utilize case sensitivity one has to emit the "" around the names, so if the current code is generic for both oracle boxes, the changes are they don't use the "" around the names, or the names are the same on both boxes .

The "" characters are emitted in the DQE which is also available to you in sourcecode form. Though I think a change to the project converter is a better choice (and also a reason why we ship it in sourcecode as often tiny changes are required to make things truly fit the project)

Frans,

As oracle is case sensitive by default and you have current software in place which is ran on those boxes, I'd be surprised if one installation is case sensitive and the other one isn't: to utilize case sensitivity one has to emit the "" around the names, so if the current code is generic for both oracle boxes, the changes are they don't use the "" around the names, or the names are the same on both boxes.

I agree with your analysis. The production servers have to be converting the names to uppercase.

I will modify the ProjectConverter and give that a try.

Thanks!

Barry

I made the changes that Walaa recommended. Thanks for that... The query now works. The row count is correct.

Disappointed to be on to the next problem.

I'm getting a "Specified cast is not valid Exception at this line:

get { return (System.Int32)GetValue((int)LoopsFieldIndex.Loopid, true); }

This field is a "Number" in Oracle and specified as "System.Int32" in the LLBLGen Pro project.

I'm stuck again!

Barry

A first chance exception of type 'System.InvalidCastException' occurred in MDProOnline.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>MDProTest3Sites.vshost.exe</AppDomain><Exception><ExceptionType>System.InvalidCastException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Specified cast is not valid.</Message><StackTrace>   at MDProOnline.EntityClasses.LoopsEntity.get_Loopid() in C:\LLBLGen Pro Projects\Generated Code\MDP_SQLServer_001\DatabaseGeneric\EntityClasses\LoopsEntity.cs:line 1828
   at MDProTest3Sites.Program.Main(String[] args) in C:\LLBLGen Pro Projects\MDProTest3Sites\Program.cs:line 64
   at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.InvalidCastException: Specified cast is not valid.
   at MDProOnline.EntityClasses.LoopsEntity.get_Loopid() in C:\LLBLGen Pro Projects\Generated Code\MDP_SQLServer_001\DatabaseGeneric\EntityClasses\LoopsEntity.cs:line 1828
   at MDProTest3Sites.Program.Main(String[] args) in C:\LLBLGen Pro Projects\MDProTest3Sites\Program.cs:line 64
   at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>
An unhandled exception of type 'System.InvalidCastException' occurred in MDProOnline.dll

Additional information: Specified cast is not valid.

Which oracle provider do you use, MS Oracle or ODP.NET? If you use MSOracle, all NUMBER(x, y) types are converted to Decimals. If ODP.NET, please consider the following table (ODP.NET type to .NET conversion, also not in their docs)

Number(x, y) -> if y == 0: x < 5 -> Int16 x < 10 -> Int32 x < 19 -> Int64 else -> Decimal else x < 8 -> Single x < 16 -> Double else -> Decimal

Float(x) if x < 50 -> Double else -> Decimal

If this mismatches with what you see with sqlserver, please use a type converter. These are easy to create, see the SDK sourcecode which contains the shipped type converter for numeric <-> bool in sourcecode.

It could be a conversion with the project converter went wrong. Though to check that I need from you the sqlserver type and which db type you chose when converting to oracle (e.g .msoracle or odp.net 9/10)

I'm using ODP.Net.

I don't understand and time is running out. I have 4 hours to turn this project over to the developers before I leave for vacation until Jan. 2. I would prefer to leave victorious.

The Oracle database field is "Number" and precision is not specified.

  CREATE TABLE "CPAUSER_MTZ"."LOOPS_TB" 
   (    "LOOP_ID" NUMBER NOT NULL ENABLE, 
    "UNIT_ID" NUMBER NOT NULL ENABLE, 
    "LOOPNAME_TXT" VARCHAR2(32 BYTE) DEFAULT ' ' NOT NULL ENABLE, 
    "LOOPTYPE_ID" NUMBER NOT NULL ENABLE, 
    "LOOPDESCRIPTION_TXT" VARCHAR2(32 BYTE) DEFAULT ' ' NOT NULL ENABLE, 

Tell me that you are not suggesting that I create a type conversion for every field?

Barry

BB wrote:

I'm using ODP.Net.

I don't understand and time is running out. I have 4 hours to turn this project over to the developers before I leave for vacation until Jan. 2. I would prefer to leave victorious.

The Oracle database field is "Number" and precision is not specified.

  CREATE TABLE "CPAUSER_MTZ"."LOOPS_TB" 
   (    "LOOP_ID" NUMBER NOT NULL ENABLE, 
    "UNIT_ID" NUMBER NOT NULL ENABLE, 
    "LOOPNAME_TXT" VARCHAR2(32 BYTE) DEFAULT ' ' NOT NULL ENABLE, 
    "LOOPTYPE_ID" NUMBER NOT NULL ENABLE, 
    "LOOPDESCRIPTION_TXT" VARCHAR2(32 BYTE) DEFAULT ' ' NOT NULL ENABLE, 

Tell me that you are not suggesting that I create a type conversion for every field?

Barry

NUMBER without precision/scale is equal to NUMBER(38, 0) so will become decimal (older oracle schemas often don't have a precision defined for NUMBER). That's something you can't change, ODP.NET will return the value as a System.Decimal.

Type converters are between types, so you create one type converter from Decimal to Int32 and back. This is 1 class. I've created one for you, see below:

[Description("Converter with as core type Int32, for mapping a field with a .NET type Int32 onto a Number(39, 0) database field")]
public class Number38ToInt32Converter : TypeConverter
{
    public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType)
    {
        return (sourceType == typeof(decimal));
    }

    public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType)
    {
        return (destinationType == typeof(decimal));
    }

    public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value)
    {
        return Convert.ToInt32(value);
    }

    public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
    {
        return Convert.ToDecimal(value);
    }

    public override object CreateInstance(ITypeDescriptorContext context, System.Collections.IDictionary propertyValues)
    {
        return 0; // return an int
    }
}

Compile this in a separate .NET 2.0 assembly (no fancy references needed).

Then you place that assembly into the TypeConverters folder in the llblgen pro installation folder (and restart the llblgen pro designer) Then you load the Oracle project into llblgen pro and right click the project node in project explorer and select from the context menu 'Edit Type conversion definitions'. This opens the type conversion definition dialog. You add a new type conversion definition for System.Decimal. You can specify additional filters. You should check the datatype checkbox and select NUMBER. You should also check the precision checkbox and specify 10 or 38, depending on the precision value CURRENTLY set for one of the fields you have problems with. I think it's 10, as it's converted from sqlserver.

Then click OK.

Then right-click 'Entities' and select 'Run plugin -> Assign type converter plugin'. At the first tab, check the top checkbox so all entities are checked. On tab two, you should see your type conversion definition you just created. Check its checkbox and hit OK. All fields matching the sourcetype will now have the type converter you defined.

After you've regenerated the code (the type converter is used in the DBSpecific vs.net project), you have to reference the type converter dll in the DBSpecific project of Oracle.

As you use a project conversion cycle, this could be automated perhaps with a couple of lines of code added to the routine where you also changed the casing. But for now this should be enough to get the types corrected.

I'm back. Vacation was great! Its recommended! I should do that more often.

So I spent most of the day yesterday trying to get the type converter to work. I understand what we are trying to do, but I can't get it to work.

1. After I compiled the Type Converter I put it in the Type Converter Directory
2. In the LLBLGen Designer Type Conversion Editor, I Add new..., then I select my type converter, Set Database Type to "NUMBER" Length to 22, and Precision to 9. This is the correct type info for the fields I want to convert.
3. Run the Assign Type converters Plugin... I select all of my Entity fields, and on the second tab I check off my converter type (System.decimal).

It is here I run into my first question: I had assigned the Database Type - "NUMBER", but in this dialog the DbType is set to "22". IS THIS CORRECT?

1. Click on the Run button: Progress bar, really quick, possibly 1 second.

2. OK, so now I open one of my entities. I expected that all of the entries matching "NUMBER", "22", and "9" to be set to the system.decimal type converter, but it is not.

So it looks like it didn't work and suspect its because "22" doesn't match "NUMBER".

So then I tried to manually set one of the fields that match "NUMBER", "22", and "9" to the System.Decimal, but It doesn't show up on the list. Again, I suspect its the filter.

Where should I go from here?

Thanks,

Barry

Would you please attach the lgp project and the type conversion class you have used.

Maybe you'll need to create a HelpDesk (private) thread, so nobody else sees your project. Or maybe you want to attach a repro project, not the real one.