Oracle Schema Provider using System.Data.OracleClient

Previous | Next

Latest post 09-05-2007 9:48 PM by vbanda1178. 34 replies.

01-16-2004 4:24 PM

VikingCoder
Joined on 11-30-2003
Westchester, NY
Posts 11
Points 720

Oracle Schema Provider using System.Data.OracleClient

OracleNativeSchemaProvider.zip

Contact

I've taken the liberty of enhancing the prior art...

This one Uses System.Data.OracleClient exclusivly. Also I rethought the Metadata Queries from scratch.

IDbSchemaProvider methods still to IMPLEMENT...

GetDatabaseExtendedProperties
GetCommandText

USE Connect string:

Data Source=SERVER; User ID=joe; Password=secret;

Comments?

LATEST VERSION 1/23/2004:

Post Edited (VikingCoder) : 1/23/2004 3:56:44 PM GMT

Post Points: 640

01-16-2004 6:27 PM In reply to

gmcelhanon
Joined on 05-19-2003
Austin, TX
Posts 62
Points 137,250

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

I don't know how to ask this without making you think that my toes have been stepped on, but trust me - they haven't. ( nono

)

Why did you feel the need to write a new schema provider? What does this one offer that the other one didn't?

Also, just today I was considering implementing extended properties. It'd be great if we could possibly split up the work (unless you wanted to do it all yourself!) smilewinkgrin

So far I had just put together the SQL for building the extended properties table in the connected user's schema...

CREATE TABLE CODESMITH_EXTENDED_PROPERTIES
(
   OBJECT_NAME                VARCHAR2(61) NOT NULL,
   CODESMITH_SCHEMA_TYPE      VARCHAR2(200) NOT NULL,
   PROPERTY_NAME              VARCHAR2(75) NOT NULL,
   PROPERTY_VALUE             VARCHAR2(4000),
   CLR_TYPE                   VARCHAR2(50) NOT NULL)
/

ALTER TABLE ELVIS.CODESMITH_EXTENDED_PROPERTIES
ADD CONSTRAINT PKCODESMITHEXTENDEDPROPERTIES
PRIMARY KEY
( OBJECT_NAME, CODESMITH_SCHEMA_TYPE, PROPERTY_NAME )
/

Thoughts and explanations?

Post Edited (gmcelhanon) : 1/18/2004 2:03:48 AM GMT

Post Points: 5

01-16-2004 7:39 PM In reply to

ejsmith
Joined on 12-28-2002
Dallas, TX USA
Posts 2,196
Points 922,330

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

VikingCoder,

Very nice! You guys are going to kill me, but I made one more small change to the IDbSchemaProvider interface. I changed the name of GetDatabaseExtendedProperties to GetExtendedProperties. Here is the new signature:

ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject);

I changed this to allow the extended properties to be lazy loaded on all objects. This made a huge perf difference in the SqlSchemaProvider.

Sorry,
Eric J. Smith

Eric J. Smith
CodeSmith Tools, LLC
Chief Software Architect

Post Points: 5

01-16-2004 7:40 PM In reply to

ejsmith
Joined on 12-28-2002
Dallas, TX USA
Posts 2,196
Points 922,330

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

gmcelhanon,

It would be awesome if you guys could work together and maintain a really solid Oracle schema provider. This would be extremely valuable to the CodeSmith community.

Thanks,
Eric J. Smith

Eric J. Smith
CodeSmith Tools, LLC
Chief Software Architect

Post Points: 5

01-16-2004 8:48 PM In reply to

VikingCoder
Joined on 11-30-2003
Westchester, NY
Posts 11
Points 720

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

gmcelhanon,

I built the new schema provider initially as an exercise to see if I could get rid of that sloth System.Data.OleDb.

Also, as my initial post states, I wanted to take a fresh stab at the metadata queries... Please take no offence.

I'm intrigued by your extended properties suggestion. How would you use that?

Also, I'm happy to work together on a solid Oracle Schema provider. That is if your toes have healed smilewinkgrin

Post Points: 5

01-16-2004 9:35 PM In reply to

gmcelhanon
Joined on 05-19-2003
Austin, TX
Posts 62
Points 137,250

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

I believe my original implementation used just the System.Data.Oracle provider, but I was using Regular expressions to parse out the information from the packages (because I falsely believed that that information would not be available from the provider based on my experiences working with ADOX and OLEDB providers from several vendors).

Roelof Blom (sp?) stripped out my regular expressions (ultimately a good thing), and added the System.Data.OleDb namespace into the mix because (as I understand it) a overflow exception problem when dealing with the FLOAT data type. I honestly have not inspected the changes he made too closely, but I do know that maybe a fresh start would not be a bad idea. However, I would encourage you to create a table that uses a FLOAT type and report back what you experience... and maybe look through the posts of the original thread to make sure I'm remember right why he added the OleDB to the mix.

I actually implemented the extended properties today based on the table layout listed in my earlier post. I'm supporting 4 basic data types initially... System.Int32, System.Double, System.DateTime, and System.String. It's a bit hacked up, but I wanted to get things rolling with it and get on with my real work.

Post Edited (gmcelhanon) : 1/16/2004 9:35:34 PM GMT

Post Points: 5

01-16-2004 10:07 PM In reply to

VikingCoder
Joined on 11-30-2003
Westchester, NY
Posts 11
Points 720

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

Well I did more than that...

I looked at the code and I found that the FLOAT problem had to do with Commands (not tables) and also the method that Roelof was using to reverse engineer the param list for a Command. IMHO going to the SYSTEM.ALL_ARGUMENTS table as I've done is the way to go. Please understand that my goal here is to resolve these issues and unify the codebase.

I'm still not entirely sure what the CODESMITH_EXTENDED_PROPERTIES table you suggest is all about. Can you explain?

BTW Eric,

I have a small problem with the CommandSchemaPicker

Oracle allows Sprocs to be overloaded like this…

Procedure foo(); // foo with no params

Procedure foo( x in number); // foo that takes a number

As you would expect the CommandSchemaPicker presents both in its listbox but no mater which ListBoxItem you select

foo() // with no params is returned.

It would be nice if the CommandSchemaPicker didn’t assume name uniqueness. In addition it would be nice if the CommandSchemaPicker displayed the selected item’s parameter list in a r/o textbox.

Can you help? Please don't delay ver 2.5 for this...

Post Points: 5

01-17-2004 5:17 AM In reply to

gmcelhanon
Joined on 05-19-2003
Austin, TX
Posts 62
Points 137,250

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

See... now I'm just pissed. ALL_ARGUMENTS... that was the View I was looking for 8 months ago! Grrrr....

Extended properties are a SQL server feature that allows you to attach any name/value pair to just about any object in the database. I have added support for reading extended properties to my version of the provider. What this all means for CodeSmith users is that this allows you to strategically attach your own metadata to accomplish cool and wonderful things in your templates.

For example, today I added the capability of my Oracle package templates to join in designated "lookup" tables and certain columns in those tables to the procedures. I added values to the extended properties table something like as follows (different table names, but you get the idea):

OBJECT_NAME = 'ADDRESSTYPE',
CODESMITH_SCHEMA_TYPE = 'TableSchema',
PROPERTY_NAME = 'IsLookupTable',
PROPERTY_VALUE = 'true',
CLR_TYPE = 'System.Boolean'

...and...

OBJECT_NAME = 'ADDRESSTYPE.DESCRIPTION',
CODESMITH_SCHEMA_TYPE = 'ColumnSchema',
PROPERTY_NAME = 'IsLookupColumn',
PROPERTY_VALUE = 'true',
CLR_TYPE = 'System.Boolean'

...and finally...

OBJECT_NAME = 'ADDRESSTYPE.DESCRIPTION',
CODESMITH_SCHEMA_TYPE = 'ColumnSchema',
PROPERTY_NAME = 'LookupAlias',
PROPERTY_VALUE = 'ADDRESSTYPEDESCRIPTION',
CLR_TYPE = 'System.String'

Now my procedure ADDRESS.LOAD_BY_ID will be generated to join to ADDRESSTYPE and include the DESCRIPTION column in the resultset aliased as 'ADDRESSTYPEDESCRIPTION' (so as not to confuse it with the DESCRIPTION column from the PHONENUMBERTYPE table, or whatever).

Right now I'm using MS Access to manage the properties, but Eric is planning an editor to be included in a future release of CodeSmith Studio.

Post Points: 5

01-18-2004 7:42 PM In reply to

VikingCoder
Joined on 11-30-2003
Westchester, NY
Posts 11
Points 720

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

Don't be pissed... I only found it by accedent!

I get the Extended propertied thing now... Thanks for the example. I see how valuable this will be once Eric provides an editor.

Eric... any thoughts on the problem I posted above?

-Mike

Post Points: 5

01-19-2004 1:22 AM In reply to

ejsmith
Joined on 12-28-2002
Dallas, TX USA
Posts 2,196
Points 922,330

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

Mike,

I thought I posted a response, but I guess I didn't. Sorry.

I'm not sure how to handle this. I had no idea that Oracle had the ability to overload procs. That's a very cool feature that I wish SQL Server had (I wonder if we will have this once the CLR is integrated into SQL with Yukon?). Anyway, as SQL is a procedural language, I really don't think I could list the signature of the proc since I've seen procs with 100's of parameters before. This is a tough issue to solve. I will have to think about how we are going to go about solving it. It will definately have to wait until after the 2.5 release though. Any ideas on what we might be able to do?

Thanks,
Eric J. Smith

Eric J. Smith
CodeSmith Tools, LLC
Chief Software Architect

Post Points: 5

01-20-2004 4:46 AM In reply to

gmcelhanon
Joined on 05-19-2003
Austin, TX
Posts 62
Points 137,250

RE: Oracle Schema Provider using System.Data.OracleClient

Overload UI.jpg

Contact

I've attached an image that I threw together as a possible UI mechanism for handling Oracle overloads using a treeview.

Additionally, you could provide more details about a particular version of the procedure via a "Properties" dialog, or a label at the bottom of the UI presenting the list. To prevent the list from growing unreasonably wide, you could show just the first 50 characters of the signature before truncating it with ellipses (...).

Rather than force the user to select their procedures from every provider using a treeview (which is probably going to fill and interact more slowly than a simple listbox), you could determine which UI element to use based on the presence (or lack of) overloads in the command list.

Just my 2c worth...

Post Edited (gmcelhanon) : 1/20/2004 4:12:19 PM GMT

Post Points: 5

01-22-2004 4:24 PM In reply to

gmcelhanon
Joined on 05-19-2003
Austin, TX
Posts 62
Points 137,250

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

Mike,

Would you post your latest version of the provider again. I'd like to see what you've got.

How confident are you that what you have is stable? How complete is it?

Geoff

Post Points: 5

01-22-2004 6:04 PM In reply to

VikingCoder
Joined on 11-30-2003
Westchester, NY
Posts 11
Points 720

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

I'm away from my machine today, I'll get to this tomorrow...

Sorry.

Mike

Post Points: 5

01-22-2004 9:54 PM In reply to

gmcelhanon
Joined on 05-19-2003
Austin, TX
Posts 62
Points 137,250

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

That's a pretty neat trick... posting to the forum without a machine.

Post Points: 5

01-23-2004 4:53 PM In reply to

VikingCoder
Joined on 11-30-2003
Westchester, NY
Posts 11
Points 720

RE: Oracle Schema Provider using System.Data.OracleClient

Contact

What? You don't have the neural network implant yet?

I've updated the source so that in now compiles against 2.5 RC3 (See top post)

Last minute interface changes? Tsk, tsk Eric ;)

Now to the questions...

Geoff said...
How confident are you that what you have is stable?

I have a high degree of confidence that it wont crash! As far as pending changes goes, well there are a few things yet to implement...

Geoff said...
How complete is it?

TODO:

GetTableColumns - Add the comments.

GetCommandText - Not Done... I want to return ONLY the text associated with a given command (Sproc or Package func). I haven't yet figured out how to do this in Oracle. I could return ALL the text in a package but that's not exactly it... I could use RegEx to extract just the one command but there I'm again flummoxed by function overloading. What to do, What to do?

GetExtendedProperties- Do something usefull!

Mike

Post Edited (VikingCoder) : 1/23/2004 4:55:58 PM GMT

Post Points: 5

Page 1 of 3 (35 items) 1 2 3 Next > | RSS