|
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
|
|
|
|
|
Thanks
Thanks
Technology News @ www.JassimRahma.com
|
|
|
|
|
We have a Windows 2008 R2 server running SQL 2012 Express RTM (11.0.2100.60).
All three protocols (shared memory, named pipes, TCP/IP) are enabled for the server and both 32-bit and 64-bit clients. No certificate is configured, and encryption is not required. SQL is configured for mixed-mode authentication.
A few weeks back, we added the registry key to disable SSLv3 for server software[^], but didn't restart the server. (All three TLS protocols - 1.0, 1.1 and 1.2 - are enabled.)
This morning, after installing the critical MS14-066 patch[^] and restarting, SQL would not accept any connections. Using shared memory or named pipes returned the dreaded "no process is on the other end of the pipe" error. Using TCP/IP returned a "connection forcibly closed" error.
Uninstalling the patch made no difference - we were still unable to connect to SQL. Only after re-enabling SSLv3 and restarting the server were we able to reconnect. We have since reinstalled the patch, and the problem has not returned.
Therefore, I can only conclude that SQL requires SSLv3 to be enabled, even if the connections are not encrypted. However, I can't find this documented anywhere.
Can anyone else confirm this? Is it a known issue?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
modified 12-Nov-14 16:09pm.
|
|
|
|
|
..it might not be SQL Server, but one of the dependencies. Does the AD-server require a secure connection to authenticate users?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
It's a good idea, but we couldn't connect using SQL authentication either, which shouldn't involve AD at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Some Users are receiving Emails and some not, below is the Error message from event log.
"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-11-11T06:09:35).
Exception Message: Cannot send mails to mail server. (The operation has timed out.)."
What could be the problem? How to sort this?
|
|
|
|
|
Your SQL box timed out sending the mail to your mail server. You need to increase the timeout.
Depending on which version of SQL you have, you might need to install a Cumulative Update in order to change the timeout:
https://support.microsoft.com/kb/968834[^]
You should then be able to use the sysmail_update_account_sp stored procedure[^] to increase the timeout.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I understand that this is what I asked for, but I'm trying to figure out how to just get 1 record of each item, instead of the 2 when the Join is valid.
DECLARE @CategoryID INT;
SET @CategoryID = 23;
SELECT *
FROM ProductInfo p
LEFT JOIN ProductInfo_Inventory pi ON p.ProductID = pi.ProductID
WHERE p.Category = @CategoryID
|
|
|
|
|
I think I get it now.
If I were to request a single record, then the join would be fine.
But since I requesting all records that match the category ID, you get what you ask for.
So I decided to just make a separate call back to the database for the inventory data as I loop through the presentation aspect of the program.
|
|
|
|
|
Don't select *, just select the columns you need, and you can probably use DISTINCT to filter the list down to a unique set.
|
|
|
|
|
I ended up wrapping the select in a select statement, and adding the union to get the history and the current records. I think I went through like 5 or 6 versions of this before I got it to work. The numbers they produce are correct, so perhaps I got it right, and it's fast.
"SELECT " & _
" SUM(FSHIPQTY * FCOST) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", SUM(FAMOUNT) " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST) " & _
" FROM " & _
"( " & _
" SELECT " & _
" h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" , h.FAMOUNT " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" AND " & _
" h.FITEMNO = @FITEMNO " & _
" UNION ALL " & _
" SELECT " & _
" v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" , v.FAMOUNT " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" v.FITEMNO = @FITEMNO " & _
") "
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
