مشکل با CLR Table-Valued Function

[hdv212]

سلام به همه ی برو بچز خوشتیپ بخش sql server به خصوص شما آقای ثباتی عزیز.
طبق سوالات و در خواستهای قبلی من مبنی بر جستجوی متن بی اعراب در متن با اعراب عربی در دیتابیس، مجبور شدم روی Regular Expression کار کنم و الحمدلله تونستم کلاسی بنویسم که مشکلم رو حل کنه، حالا یه مشکل بزرگتر، فرض کنید اطلاعات یعنی همون متن با اعراب عربی در دیتابیس ذخیره شده و کاربر میخواد مثلا یه جستجو در دیتابیس در مورد کلمه ی 'صراط' بکنه، حالا من میخوام خود sql (و نه برنامه ی خود من) بیاد توی دیتابیس بگرده و رکورد هایی رو که این کلمه در فیلد مربوطه وجود داره رو برگردونه (با توجه به اینکه کلمه ی مورد جستجو بی اعرابه و متن ذخیره شده در دیتابیس با اعراب)، لذا باید از Regular Expression در خود دیتابیس sql استفاده بشه تا کارایی برنامه اقزایش پیدا کنه، رو همین اساس رفتم دنبال clr table-valued function ودیدم که یکی از امکاناتش همینه یعنی میشه توش مثلا از regular expression هم استفاده کرد، حالا من یه function توی vs2005 ساختم که میخوام دو تا ورودی داشته باشه و یه جدول برگردونه حالا ورودیهاش یکی کلمه ی مورد جستجوست و دیگری فیلدی که باید جستجو توی اون انجام بشه، جدولی هم که برمیگردونه، جدول حاوی رکوردهایی است که اون کلمه توی فیلد مورد نظرش وجود دارن، حالا من نمیدونم فیلدها رو کجا بهش بدم و توی کدوم تابع پردازش روش انجام بدم(چون clr table-valued function ها دو تا تابع نیاز دارن)، اینم کد تابع هام توی vs2005 به سی شارپ :


[Microsoft.SqlServer.Server.SqlFunction(DataAccess= DataAccessKind.Read,FillRowMethodName="ContainTerm",TableDefinition="titleName nvarchar(100),text nvarchar(max)")]
public static IEnumerable SearchWithDiactritics()
{
using(SqlConnection con = new SqlConnection("Context connection=true;"))
{
string strSql = "select titleName,text from t_titles";
con.Open();
SqlCommand command = new SqlCommand(strSql,con);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds.Tables[0].Rows;
}
}

public static void ContainTerm(object record, out string titleName, out string text)
{
DataRow row = (DataRow)record;
//کلاس من برای جستجوی متن بی اعراب در متن با اعراب
CRegex cr = new CRegex();

titleName = string.Empty;
text = string.Empty;
if (!(row["text"] is System.DBNull))
{
if (cr.ContainSearchTerm("صراط", (string)row["text"]))
{
titleName = (string)row["titleName"];
text = (string)row["text"];
}
}

الان من اینطوری توی sql این تابع رو فراخوانی میکنم :

select * from dbo.SearchWithDiactritics()

ولی میخوام اینطوری باشه :

select * from dbo.SearchWithDiactritics(N'صراط',textField)

در حال حاضر هم که از function استفاده میکنم یه جدول با تعدادی رکورد خالی بر میگردونه ولی نتیجه من هم توش هست که درسته(یعنی کلمه ی 'صراط' توش هست)،ولی رکورد های خالی رو چکارش کنم، دیگه اینکه تکلیف پارامترهای تابع چی میشه ؟

لطفا کمک کنید، مرسی

[hdv212]

یه نفر کمک کنه

[AminSobati]

اگر ممکنه از تگ Code استفاده کنین تا دقیق ببینیم داخل این تابع چی میگذره!

[hdv212]

با عرض معذرت از شما، کد رو درست کردم، راستی یه مشکل دیگه، من فیلد مورد نظرم که میخوام توش جستجو انجام بشه از نوع ntext بود، ولی توی Deploy کردن در vs2005 این خطا رو میگیره :
Cannot use 'ntext' column in the result table of a streaming user-defined function.
و من هم نوع فیلدمو در جدول به nvarchar(max) تغییر دادم، در صورتی که توی یکی از مقالات در سایت codeproject خونده بودم که با ntext مشکلی نداره، با غیر یونیکدها مشکل داره. با توجه به ذخیره ی حجم عظیم متون در این فیلد، بعدا به مشکل بر نمیخورم ؟

[AminSobati]

nvarchar max و ntext دقیقا یک ظرفیت دارند.
حالا مشکل کجاست؟ اگر دو پارامتر ورودی اضافه کنین به تابع چه مشکلی پیش میاد؟

[hdv212]

حالا مشکل کجاست؟ اگر دو پارامتر ورودی اضافه کنین به تابع چه مشکلی پیش میاد؟

خب موضوع اینه که من دوتا پارامتر رو تو کدوم تابع بذارم و چطوری بهش بگم که فقط اون رکوردهایی رو برگردونه که مثلا نتیجه ی چک کردنش درسته، منظورم این قسمته :


if (cr.ContainSearchTerm("صراط", (string)row["text"]))

[AminSobati]

در قسمت:
public static IEnumerable SearchWithDiactritics
دو پارامتر ورودی رو تعریف کنین. اما مسئله اینجاست که شما کل جدول رو Load میکنین و بعد جستجو میکنین. آیا مطمئن هستین که با توجه به تعداد رکوردها این روش مناسبه؟

[hdv212]

دو پارامتر ورودی رو تعریف کنین. اما مسئله اینجاست که شما کل جدول رو Load میکنین و بعد جستجو میکنین. آیا مطمئن هستین که با توجه به تعداد رکوردها این روش مناسبه؟

خب راه دیگه ای به نظرم نمیرسه، در ضمن من اون کدهامو اصلاح کردم، فکر کنم راهشو پیدا کردم ، اینم کد برنامم :


[Microsoft.SqlServer.Server.SqlFunction(DataAccess= DataAccessKind.Read,FillRowMethodName="ContainTerm",TableDefinition="titleName nvarchar(100),text nvarchar(max)")]
public static IEnumerable SearchWithDiactritics(SqlString searchTerm)
{
using(SqlConnection con = new SqlConnection("Context connection=true;"))
{
string strSql = "select titleName,text from t_titles";
con.Open();
SqlCommand command = new SqlCommand(strSql,con);
CRegex cr = new CRegex();
DataTable dt = new DataTable();
SqlDataReader reader = command.ExecuteReader();
object[] obj;
while (reader.Read())
{
if (cr.ContainSearchTerm((string)searchTerm, reader.GetString(1)))
{
obj = new object[2];
reader.GetValues(obj);
dt.Rows.Add(obj);
}
}
return dt.Rows;
}
}

public static void ContainTerm(object record, out string titleName, out string text)
{
DataRow row = (DataRow)record;
titleName = row["titleName"].ToString();
text = row["text"].ToString();
}

ولی موقع اجرا در SqlServer به این صورت :


select * from dbo.SearchWithDiactritics(N'صراط')

این پیغام رو میده که احتمالا استثنایی برای رکوردهای null پیش میاد، نمیدونم چطوری مشکلو حل کنم، این پیغام خطاست :

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'SearchWithDiactritics':
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at Microsoft.SqlServer.Server.ValueUtilsSmi.GetString (SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)
at System.Data.SqlClient.SqlDataReaderSmi.GetString(I nt32 ordinal)
at UserDefinedFunctions.SearchWithDiactritics(SqlStri ng searchTerm)

در ضمن من توی فیلد text که عملیات مقایسه توش انجام میشه، مقدار null دارم، میشه کمکم کنید، مرسی

[AminSobati]

حامد جان کد رو در VS باید Trace کنی ببینی دقیقا کدوم عمل با NULL مشکل داره

[hdv212]

آقای ثباتی مشکل همون مقدار null در فیلد text بود، همه رو مقدار دادم دیگه اون پیغام رو نمیده ولی یه پیغام دیگه میده که اینه :

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'SearchWithDiactritics':
System.ArgumentException: Input array is longer than the number of columns in this table.
System.ArgumentException:
at System.Data.DataTable.NewRecordFromArray(Object[] value)
at System.Data.DataRowCollection.Add(Object[] values)
at UserDefinedFunctions.SearchWithDiactritics(SqlStri ng searchTerm)

میگه آرایه ی ورودی بزرگتر از تعداد فیلدهاست، هر کاری میکنم این یکی دیگه عقلم به جایی قد نمیده، طولش هم درسته، نمیدونم اگه میشه یه تستی خودتون بکنید ببینید واسه شما هم همینطوریه، شاید راه بهتری به غیر از آرایه که من تو کدم استفاده کردم به نظرتون بیاد، مرسی

[AminSobati]

والا به نظر من شما رفته رفته دارین اصلا صورت مسئله رو پاک میکنین! صرف نظر از سرعت کند این روش برای جستجو، چنین تابعی نباید برای Null آسیب پذیر باشه. من جای شما باشم Trace میکنم تا دقیقا ببینم کدوم سطر مولد خطا هستش و چه مقادیری داره به تابع وارد و از اون خارج میشه تا به این خطا پی ببریم. Nullها رو به جدول برگردونین تا از همه لحاظ آزمایش واقعی باشه.
در ضمن، اگر حذف اعراب به هنگام جستجو مطمئن هستین که تنها روشه، چرا از TVF استفاده میکنین؟ مثلا چرا یک تابع Scalar نمینویسین و حذف اعراب رو در اونجا انجام نمیدین؟ منظور بنده این هست:


select * from mytable
where dbo.myfn(TextCol)=N'صراط'

[hdv212]

اینم نظر بدی نیست، روش کار میکنم ببینم چی میشه، در ضمن من که اعراب رو حذف نمیکنم (اگه اینطور بود که خیلی خوب بود)، من از طریق کلاسی که ساختم توی فیلد text که متن با اعراب ذخیره شده، دنبال کلمه ی مورد جستجو (که در اینجا صراط است) میگردم، به هیچ عنوان اعرابی حذف نمیشه، این کلاس منه که توی متن با اعراب میگرده و میگه که این کلمه توی این فیلد هست یا نه، حالا من هم فکر کنم یه جورایی نظر شما رو فهمیدم، ولی فکر کنم باید تابعی scalar بنویسم که مقدار true یا false برگردونه، اونوقت فکر کنم درست بشه، یعنی یه همچین چیزی بشه :


select * from t_titles where dbo.SearchWithDiactritics(N'صراط') = true

منتها نمیدونم چطوری توی sql، درست یا نادرست بودن مقدار برگشتی تابعمو بفهمم، اگه میشه کمکم کنید، مرسی

[AminSobati]

select * from mytable
where dbo.myfn(TextCol,N'صراط')=

بعد از تساوی مقدار دلخواه خودتون رو بگذارین. مثلا قرارداد میکنین اگر این لغت در فیلد Match شد، 1 یا 0 یا هر چیزه دیگه ای رو برمیگردونین

[hdv212]

آقای ثباتی عزیز مژده بده، مشکلم حل شد، از طریق scalar function و کد زیر تونستم مشکلم رو حل کنم و جواب بگیرم، تابعم اینطوری شد :

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fn_CompareDiacriticsText(SqlString searchTerm,SqlString text)
{
CRegex cr = new CRegex();
if (cr.ContainSearchTerm((string)searchTerm, (string)text))
return SqlBoolean.True;
else
return SqlBoolean.False;
}

و توی sql هم ازش اینطوری استفاده کردم :

select * from t_titles
where dbo.fn_CompareDiacriticsText(N'رحمن',text)=1

به هر حال از لطف و راهنماییتون ممنون که هیچ وقت از من دریغ نکردید، فعلا که مشکلم حل شد ول یقین دارم که بازم مزاحم شما و سایر دوستان خواهم شد.

[AminSobati]

خدا رو شکر که خانواده ای از نگرانی رهایی پیدا کردند ;)