|
I am trying to replace names found in 'xml' fieldname as hyperlinks, by matching with the names in database_tags.
I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @N VARCHAR(MAX)
SELECT @N = [Name] FROM [dbo].[database_tags]
WHERE @XML LIKE '%'+[Name]+'%'
AND UploadDate >= '2014-09-01'
IF @N IS NOT NULL
BEGIN
SELECT @XML = REPLACE(@XML,
@N,
'<a href="<a href="pagename.aspx?tag='+@N+'">'+@N+'</a>')
END
RETURN @XML
END
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.
But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.
Is there a way to get the function to recognize more than one names as hyperlinks.
Thank you very much for your time and help.
|
|
|
|
|
Try this:
ALTER FUNCTION [dbo].[ReplaceTags]( @XML VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN
SELECT
@XML = Replace(@XML, [Name], '<a href="pagename.aspx?tag=' + [Name] + '>' + [Name] + '</a>')
FROM
[dbo].[database_tags]
WHERE
@XML LIKE '%' + [Name] + '%'
And
UploadDate >= '2014-09-01'
;
RETURN @XML;
END
GO
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much for your reply and help.
I have already tried the above approach and unfortunately, it gives me incorrect output.
I pass the following XML Input to the UDF:
<Body><p align="justify">One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.<p></Body>
the function above, outputs the following (which is incorrect).
One is a £1m block of <a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a></a>"><a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>
The desired output should be :
<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>, which has never appeared on SMO.<p></Body>
I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are.
http://sqlfiddle.com/#!6/96cac8/2
I looked into a cursor approach for this replace function and have come up with the following below. However, I am still experiencing the same output error, as explained above. the function loops through continuously and creates duplicate names of hyperlinks, within the XML data.
ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT name
FROM [dbo].[database_tags]
Where UploadDate >= '2014-09-01'
and @XML LIKE '%' + Name + '%'
OPEN CUR
WHILE 1 = 1
BEGIN
FETCH cur INTO @name
IF @@fetch_status <> 0
BREAK
BEGIN
SELECT @XML = REPLACE(@XML,
@Name,
'<a href="<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>')
END
END
CLOSE CUR;
DEALLOCATE CUR;
RETURN @XML
END
Please advice further, if possible.
Thank you for your help and time.
|
|
|
|
|
I can't reproduce the problem using your SQL Fiddle data and the code I posted.
<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3>AIREM 2006-1X 2A3</a>, which has never appeared on SMO.<p></Body>
<body>It consists of: <a href="pagename.aspx?tag=BANKP>BANKP</a>, <a href="pagename.aspx?tag=BCJA>BCJA</a>, <a href="pagename.aspx?tag=BCJA>BCJA</a>M, <a href="pagename.aspx?tag=<a href="pagename.aspx?tag=BFTH>BFTH</a>><a href="pagename.aspx?tag=BFTH>BFTH</a></a>, <a href="pagename.aspx?tag=<a href="pagename.aspx?tag=BFTH>BFTH</a>><a href="pagename.aspx?tag=BFTH>BFTH</a></a>, and <a href="pagename.aspx?tag=EMPOP>EMPOP</a>.</body>
DECLARE @XML nvarchar(max);
SELECT
@XML = XML
FROM
article
WHERE
id = '1'
;
SELECT
@XML = Replace(@XML, [Name], N'<a href="pagename.aspx?tag=' + [Name] + N'>' + [Name] + N'</a>')
FROM
[dbo].[database_tags]
WHERE
@XML LIKE N'%' + [Name] + N'%'
;
SELECT @XML As Result;
NB: You should avoid using text, ntext or image types in SQL. They are deprecated and will be removed in a future version[^]. Use varchar(max), nvarchar(max) and varbinary(max) instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Please note that I've repeated Richard's solution and don't get any problem either. I know that it's frustrating!
Can we see the code you are using to call the UDF?
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
|
|
|
|
|
Thank you for your suggestion and help.
I am manage to get the cursor function work by adding a distinct clause in the tags select query of function:
DECLARE @fullame VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT distinct name
FROM database_tags
Where UploadDate >= '2014-09-01'
Thank you very much Richard for your solution, I am sorry to inform, the above solution was little slow loading the articles with hyperlinks name, hence I choose the cursor function approach.
Thank you all, for your time and suggestion for this post. I appreciate all your help.
|
|
|
|
|
You are welcome! Richard's solution should have worked - that is the one I also tested and is exactly the approach I would have used. I only mentioned the cursor to help you understand the problem, but if it works ...!
If Richard's query is performing badly, it could probably be improved with a well-chosen additional index. When you have some time, I'd suggest running it with the SQL query analyzer.
Good luck!
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
|
|
|
|
|
Your problem is caused by the fact that only one name match will work. Although your first select statement will potentially return multiple rows @N will only ever have one value. Using Richard's method you are wrapping everything in together. Your approach would have required being wrapped in a cursor to enumerate your first select.
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
modified 18-Nov-14 16:23pm.
|
|
|
|
|
suggest various tables for shopping site
|
|
|
|
|
How about these[^]? I particularily like the round one in mahogany.
Joke aside, your question is to vague to be answered. Explain in more detail what you want your site to be able to do.
Meanwhile have a look for ideas here[^]. <-- that's the serious answer
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
You might want to have a look through this site data models/[^]
Caveat- I have never used them but they seem to be reasonably normalised.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You want indexes with that, or are you going to eat it here?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
You're starting at the wrong end. Start by defining the features you want and work down to the tables you'll need.
|
|
|
|
|
Hi,
I am trying to save an html text which is less than 1MB using LONGTEXT datatype but getting:
Data too long for column
what could be the reason?!
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
sorry, forgot to mention it..
It's MySQL
Technology News @ www.JassimRahma.com
|
|
|
|
|
Are you sure the error relates to your LONGTEXT column? Assuming this is MySQL, the column should be able to store up to 4,294,967,295 characters.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I tried saving a very small image and it was find but when I save larger image in the html (less than 1 mb image) I'll get that error.
this is the complete error:
MySql.Data.MySqlClient.MySqlException was unhandled
HResult=-2147467259
Message=Data too long for column 'param_message' at row 2
Source=MySql.Data
ErrorCode=-2147467259
Number=1406
StackTrace:
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at BizCards.frmMessage.btnSave_ItemClick(Object sender, ItemClickEventArgs e) in c:\Users\CakeBoutique\Documents\Visual Studio 2012\Projects\BizCards\BizCards\message.cs:line 100
at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarBaseButtonItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarButtonItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarItemLink.OnLinkClick()
at DevExpress.XtraBars.BarButtonItemLink.OnLinkClick()
at DevExpress.XtraBars.BarItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
at DevExpress.XtraBars.BarItemLink.OnLinkActionCore(BarLinkAction action, Object actionArgs)
at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.ClickLink(BarItemLink link)
at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)
at DevExpress.XtraBars.Controls.CustomLinksControl.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraBars.Controls.CustomControl.WndProc(Message& msg)
at DevExpress.XtraBars.Controls.DockedBarControl.WndProc(Message& msg)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at BizCards.frmMain.btnMessages_Click(Object sender, EventArgs e) in c:\Users\CakeBoutique\Documents\Visual Studio 2012\Projects\BizCards\BizCards\main_form.cs:line 455
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m)
at DevExpress.XtraEditors.BaseControl.WndProc(Message& msg)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at BizCards.Program.Main() in c:\Users\CakeBoutique\Documents\Visual Studio 2012\Projects\BizCards\BizCards\Program.cs:line 20
InnerException:
Technology News @ www.JassimRahma.com
|
|
|
|
|
How are you saving an image in an HTML string - embedded as a data URI, or something else?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
base64 string like this:
<img src="data:image/png;base64,.........
Technology News @ www.JassimRahma.com
|
|
|
|
|
OK, that shouldn't significantly increase the size. The Base64 encoded string shouldn't be more than 1/3 larger than the raw bytes.
So param_message is definitely the parameter you're using to pass the HTML?
Can you post the code you're using to create the command object and its parameters?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
this is the code:
this.Cursor = Cursors.WaitCursor;
sql_connection = new MySqlConnection("my_connection");
sql_connection.Open();
sql_command = new MySqlCommand("sp_save_message", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.CommandTimeout = Convert.ToInt32(sql_command_timeout);
sql_command.Parameters.AddWithValue("param_message_id", message_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_message", txtMessage.HtmlText).MySqlDbType = MySqlDbType.LongText;
int result_rows = sql_command.ExecuteNonQuery();
this.DialogResult = System.Windows.Forms.DialogResult.OK;
Technology News @ www.JassimRahma.com
|
|
|
|
|
OK, nothing obviously wrong there. How about the stored procedure?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
you are the man 
I missed the longtext in my sp. It was text only.
Now I have one more and last problem.
How can I solve:
Quote: Packets larger than max_allowed_packet are not allowed.
for using large text?
Technology News @ www.JassimRahma.com
|
|
|
|
|
It looks like you need to change the configuration of MySQL:
http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html[^]
[mysqld]
max_allowed_packet=1024M
There doesn't seem to be any other way to fix that error.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
