CLR TYPES AND SQL TYPES

Question

CLR TYPES AND SQL TYPES

MarlonRibunal

Ten Centuries

Points: 1332
More actions
October 18, 2008 at 3:57 am

#214144

Here's my class {ref:msdn}:
public static long DirSize(DirectoryInfo d)
{
long Size = 0;
FileInfo[] fis = d.GetFiles();
foreach (FileInfo fi in fis)
{
Size += fi.Length;
}
DirectoryInfo[] dis = d.GetDirectories();
foreach (DirectoryInfo di in dis)
{
Size += DirSize(di);
}
return (Size);
}
So I created the assembly (successful). But when I'm trying to create a function from the assembly...
CREATE FUNCTION FileSize (@dir VARCHAR(max))
RETURNS BIGINT
EXTERNAL NAME MyAssembly.MyClass.DirSize
GO
...I am getting this error:
Msg 6552, Level 16, State 3, Procedure FileSize, Line 1
CREATE FUNCTION for "FileSize" failed because T-SQL and CLR types for parameter "@dir" do not match.
SQL Server Database Administrator

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The error message is fairly clear. The data types don't match. A VARCHAR(max) is not the same as a DirectoryInfo. What are you expecting to be passing to this function?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 2

GilaMonster (10/18/2008)
The error message is fairly clear. The data types don't match. A VARCHAR(max) is not the same as a DirectoryInfo. What are you expecting to be passing to this function?

Well, I did not ask the *obvious* question. What is the compatible SQL Server Data Type for C# Type DirectoryInfo? I want to pass well, again it's quite *obvious*, a directory path. Any other suggestion rather than repeating my own question?

SQL Server Database Administrator

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

MarlonRibunal (10/18/2008)
GilaMonster (10/18/2008)
The error message is fairly clear. The data types don't match. A VARCHAR(max) is not the same as a DirectoryInfo. What are you expecting to be passing to this function?
Well, I did not ask the *obvious* question. What is the compatible SQL Server Data Type for C# Type DirectoryInfo? I want to pass well, again it's quite *obvious*, a directory path. Any other suggestion rather than repeating my own question?

Hey... smart guy... settle down. You want help, be nice. 😉 If it's so bloody obvious, look it up in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 4

What I was trying to say is..please do not reply to the post if you do not have the answer. Thanks.

SQL Server Database Administrator

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

MarlonRibunal (10/18/2008)
What I was trying to say is..please do not reply to the post if you do not have the answer. Thanks.

I can only imagine that you are trying to control us with "rules" like this because it makes you feel better about having to ask for help, however, you should realize that trying to control how we help you is much more likely to result in your not finding the solution that you need.

For instance, in this case, it is not possible for us to both help you with your technical problem and at the same time comply with the rules that you are trying to impose (that is, "do not reply to the post if you do not have the answer" to your question). This is because you have asked the wrong question and it has no answer.

And this puts me in a bit of a quandary. Normally, I would just ignore the fact that you are asking the wrong question and just tell you the solution to your problem anyway. However, I am a great believer in mutual respect and even though you seem to be treating us (and in particular, the people trying to help you) with disrespect, I would still not want to disrespect you in turn by ignoring your request.

So instead, I will leave it up to you. I believe that I can assist you with your technical problem. Please let me know if you would like me to provide that assistance.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

MarlonRibunal (10/18/2008)
Well, I did not ask the *obvious* question. What is the compatible SQL Server Data Type for C# Type DirectoryInfo? I want to pass well, again it's quite *obvious*, a directory path. Any other suggestion rather than repeating my own question?

Really? I would never have guessed! </sarcasm> Perhaps I should have been a little more clear. What's an example of value that you want to pass to the function from SQL? Where does that value come from?

SQL doesn't have anything compatible with DirectoryInfo. SQL does not use the .Net data types. What you are probably going to have to do (and I say probably, because I don't know what exactly you're passing to the function) is to have the function accept a SQLString and then convert that into a directoryInfo before operating on it.

Just to add to what the others have said, please remember that we're posting and answering questions in our free time. None of us get paid to do this. We like helping people but in general, but if you post with attitude, people may just ignore your posts as others are easier to answer.

Oh, and as for repeating your own question. In the initial post, you didn't actually ask a question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 7

Mea Culpa....

My Bad, did not mean to be sarcastic.

Let's go back to business guys...let's forgive and forget....

I am trying to do something like the extended procedure xp_dirscan. Is there an equivalent procedure in SQL Server 2005?

That is what I am trying to do on my first ever CLR build (xp_dirscan-like function). But CLR brought me bad luck. Let's be friends...

I apologize.

SQL Server Database Administrator

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

No problem. 🙂

Ok, so what exactly will you be passing to the function? Something like 'C:\Somedir\SomeOtherDir\'? What do you want returning from the function?

Also, what do you need it for?

What might work (and I say might, because I'm not a C# expert) is to change the function you gave to something like this.

public static long DirSize(SQLString dir)

{

DirectoryInfo d = new DirectoryInfo((String) dir);

long Size = 0;

Now you can create that function in SQL with a nvarchar(4000) parameter (I don't think it'll take varchar(MAX), though if you have directory trees more than 4000 characters wide, I'd worry for other reasons).

The cast to String may or maynot be necessary. Try both ways, see what works.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

GilaMonster (10/19/2008)
Now you can create that function in SQL with a nvarchar(4000) parameter (I don't think it'll take varchar(MAX), though if you have directory trees more than 4000 characters wide, I'd worry for other reasons).
The cast to String may or maynot be necessary. Try both ways, see what works.

An nvarchar(max) could be used, but the recommended equivalent type is SqlChars, which can stream data to the function. Both have a .Value method which returns a String datatype that can be used as:

DirectoryInfo d = new DirectoryInfo(dir.Value);

You can also declare the function method as:

public static long DirSize(string dir)

{

and the conversion occurs inline. I am not a big fan of allowing the conversion to be implicitly made, but it does work.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Thanks Jonathan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

MarlonRibunal (10/19/2008)
Let's be friends...

Absolutely! 🙂

Anyway, I was going to say what Gail said (thanks, Gail 🙂 ). And, Jonathan, as always, is the man on these types of questions.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 12

#1 Lesson Learned from this:

When it's boiling inside your head because you cannot solve a problem, stay away from the keyboard and from the people around you...

thanks for the help guys and for understanding my tantrums!

SQL Server Database Administrator

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Glad it worked out.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

chandan.gupta SSC Rookie Points: 44 More actions · Answer 14

We can send Data Types thoses are compitable with sql server types, so sending the Directory path (as It is a string, Compitable with Sql Server nVarhcar(4000)) is a very good Idea and then process it in your functions to get files details.