Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Application Programming › C# MySQL connection for the complete idiot
New Posts  All Forums:Forum Nav:

C# MySQL connection for the complete idiot - Page 2

post #11 of 16
Quote:
Originally Posted by ronnin426850 View Post

I wasn't aware that the reader gets all rows, I though it gets them one by one when you call Read();

ExecuteReader runs the actual query which fetches the result set from the database. Subsequent Read calls from the returned DataReader are from a buffered memory stream - the ExecuteReader returns the entire result set in one go from the query. Calling Read doesn't go back to the database for the next row - the database returns the entire result set after executing the query. Imagine if you had 1000 rows and calling Read caused a round-trip back to the database potentially over a slow network!
Quote:
Originally Posted by ronnin426850 View Post

Anyway, here's the stack trace:
Warning: Spoiler! (Click to show)
A first chance exception of type 'System.TimeoutException' occurred in MySql.Data.dll
System.Transactions Critical: 0 : http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/UnhandledUnhandled exceptionBackbone.vshost.exeSystem.TimeoutException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089Timeout in IO operation at MySql.Data.MySqlClient.TimedStream.StopTimer()
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)
at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)
at MySql.Data.MySqlClient.Driver.SkipDataRow()
at MySql.Data.MySqlClient.ResultSet.Close()
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlDataReader.Close()
at MySql.Data.MySqlClient.MySqlConnection.Close()
at MySql.Data.MySqlClient.MySqlConnection.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at DatabaseLayer.MySqlDatabase.TestGetShortDescriptions() in c:\Users\User1\Documents\Visual Studio 2012\Projects\Backbone\DatabaseLayer\MySqlDatabase.cs:line 76
at Backbone.Form1.productDescriptionToolStripMenuItem_Click(Object sender, EventArgs e) in c:\Users\User1\Documents\Visual Studio 2012\Projects\Backbone\Backbone\Form1.cs:line 45
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
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.Application.Run(Form mainForm)
at Backbone.Program.Main() in c:\Users\User1\Documents\Visual Studio 2012\Projects\Backbone\Backbone\Program.cs:line 19
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()System.TimeoutException: Timeout in IO operation
at MySql.Data.MySqlClient.TimedStream.StopTimer()
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)
at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)
at MySql.Data.MySqlClient.Driver.SkipDataRow()
at MySql.Data.MySqlClient.ResultSet.Close()
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlDataReader.Close()
at MySql.Data.MySqlClient.MySqlConnection.Close()
at MySql.Data.MySqlClient.MySqlConnection.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at DatabaseLayer.MySqlDatabase.TestGetShortDescriptions() in c:\Users\User1\Documents\Visual Studio 2012\Projects\Backbone\DatabaseLayer\MySqlDatabase.cs:line 76
at Backbone.Form1.productDescriptionToolStripMenuItem_Click(Object sender, EventArgs e) in c:\Users\User1\Documents\Visual Studio 2012\Projects\Backbone\Backbone\Form1.cs:line 45
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
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.Application.Run(Form mainForm)
at Backbone.Program.Main() in c:\Users\User1\Documents\Visual Studio 2012\Projects\Backbone\Backbone\Program.cs:line 19
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

And there isn't any other code, really, just this line:
Code:
MySqlConnection conn = new MySqlConnection(connectionString);

There's got to be more code, I see variables like "reader" etc are not defined in method scope...

Looking at trace now.

--

Add that LIMIT 10 into the query, it should fix the issue - be sure to call reader.HasRows as well.

I think what is going on here is that you are prematurely ending the calls to DataReader.Read and the MySQL driver doesn't like this - it expects you to call DataReader.Read the amount of times that there are rows, and you should do so within a timely fashion. Because you stop, you don't ever call it again and the MySQL driver closes the connection on a timeout because it thinks the operation is taking too long to complete.

I don't know if this is the cause for sure, but its easy to test to see smile.gif
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
post #12 of 16
Thread Starter 
Oh, yes, sorry, there is this thing too:
Code:
MySqlDataReader reader = null;

Here's the entire class:
Code:
namespace DatabaseLayer
{
    public class MySqlDatabase : Database
    {
        public new String connectionString = "Server=cant_tell_you_that;Database=cant_tell_you_that;Uid=cant_tell_you_that;Pwd=cant_tell_you_that;";
        MySqlDataReader reader = null;
        
        private MySqlConnection GetConnection()
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            return conn;
        }

        public List<String> TestGetShortDescriptions()
        {
            List<String> result = new List<string>();
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand("SELECT Item.shortDescription FROM Item", conn);
                reader = command.ExecuteReader();
                while (result.Count < 10)
                {
                    if(reader.Read())
                        result.Add(reader.GetString(0));
                }
            }
            return result;
        }
    }
}

in Program:
Code:
static class Program
    {
        public static MySqlDatabase db = new MySqlDatabase();
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }

in Form1:
Code:
private void buttonDescr_Click(object sender, EventArgs e)
        {
            List<String> shorDescriptions = Program.db.TestGetShortDescriptions();
        }

There's nothing else, literally, just a form, a button, and the DB.
My PC
(14 items)
 
Wife's PC
(15 items)
 
 
CPUMotherboardGraphicsRAM
Core i5 4460 AsRock H81M-DG4 Sapphire Rx470 Platinum KVR 1600 16Gb 
Hard DriveHard DriveCoolingOS
2x Seagate 3Tb Samsung 850 EVO 120 Scythe Ninja 3 Rev.B Windows 10 Pro 
MonitorKeyboardPowerCase
Fujitsu Siemens A17-2A Logitech K280e SuperFlower SF-550K12XP Thermaltake Versa H25 
MouseAudio
Logitech G402 Sony MDR XD150 
CPUMotherboardGraphicsRAM
Athlon 750K 4.0Ghz AsRock FM2A75 Pro4+ Sapphire R9 270X Dual-X Kingston 2x4Gb 1600 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 120  Western Digital 320Gb LiteON DVD-RW CoolerMaster Hyper Z600 
OSMonitorKeyboardPower
Windows 7 Pro x64 Toshiba 32" FullHD TV Logitech FSP Hexa 550 
CaseMouse
DeLUX Logitech 
  hide details  
Reply
My PC
(14 items)
 
Wife's PC
(15 items)
 
 
CPUMotherboardGraphicsRAM
Core i5 4460 AsRock H81M-DG4 Sapphire Rx470 Platinum KVR 1600 16Gb 
Hard DriveHard DriveCoolingOS
2x Seagate 3Tb Samsung 850 EVO 120 Scythe Ninja 3 Rev.B Windows 10 Pro 
MonitorKeyboardPowerCase
Fujitsu Siemens A17-2A Logitech K280e SuperFlower SF-550K12XP Thermaltake Versa H25 
MouseAudio
Logitech G402 Sony MDR XD150 
CPUMotherboardGraphicsRAM
Athlon 750K 4.0Ghz AsRock FM2A75 Pro4+ Sapphire R9 270X Dual-X Kingston 2x4Gb 1600 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 120  Western Digital 320Gb LiteON DVD-RW CoolerMaster Hyper Z600 
OSMonitorKeyboardPower
Windows 7 Pro x64 Toshiba 32" FullHD TV Logitech FSP Hexa 550 
CaseMouse
DeLUX Logitech 
  hide details  
Reply
post #13 of 16
OK check my edits smile.gif

And give this a try:
Code:
namespace DatabaseLayer
{
    public class MySqlDatabase : Database
    {
        public new String connectionString = "Server=cant_tell_you_that;Database=cant_tell_you_that;Uid=cant_tell_you_that;Pwd=cant_tell_you_that;";
        
        private MySqlConnection GetConnection()
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            return conn;
        }

        public List<String> TestGetShortDescriptions()
        {
            List<String> result = new List<string>();
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand("SELECT Item.shortDescription FROM Item LIMIT 10", conn);

                using ( MySqlDataReader reader = command.ExecuteReader() )
                {
                        while(reader.Read())
                                result.Add(reader.GetString(0));
                }
            }

            return result;
        }
    }
}
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
post #14 of 16
Thread Starter 
That worked! Thanks a ton! Here's some well deserved rep+ thumb.gif
My PC
(14 items)
 
Wife's PC
(15 items)
 
 
CPUMotherboardGraphicsRAM
Core i5 4460 AsRock H81M-DG4 Sapphire Rx470 Platinum KVR 1600 16Gb 
Hard DriveHard DriveCoolingOS
2x Seagate 3Tb Samsung 850 EVO 120 Scythe Ninja 3 Rev.B Windows 10 Pro 
MonitorKeyboardPowerCase
Fujitsu Siemens A17-2A Logitech K280e SuperFlower SF-550K12XP Thermaltake Versa H25 
MouseAudio
Logitech G402 Sony MDR XD150 
CPUMotherboardGraphicsRAM
Athlon 750K 4.0Ghz AsRock FM2A75 Pro4+ Sapphire R9 270X Dual-X Kingston 2x4Gb 1600 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 120  Western Digital 320Gb LiteON DVD-RW CoolerMaster Hyper Z600 
OSMonitorKeyboardPower
Windows 7 Pro x64 Toshiba 32" FullHD TV Logitech FSP Hexa 550 
CaseMouse
DeLUX Logitech 
  hide details  
Reply
My PC
(14 items)
 
Wife's PC
(15 items)
 
 
CPUMotherboardGraphicsRAM
Core i5 4460 AsRock H81M-DG4 Sapphire Rx470 Platinum KVR 1600 16Gb 
Hard DriveHard DriveCoolingOS
2x Seagate 3Tb Samsung 850 EVO 120 Scythe Ninja 3 Rev.B Windows 10 Pro 
MonitorKeyboardPowerCase
Fujitsu Siemens A17-2A Logitech K280e SuperFlower SF-550K12XP Thermaltake Versa H25 
MouseAudio
Logitech G402 Sony MDR XD150 
CPUMotherboardGraphicsRAM
Athlon 750K 4.0Ghz AsRock FM2A75 Pro4+ Sapphire R9 270X Dual-X Kingston 2x4Gb 1600 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 120  Western Digital 320Gb LiteON DVD-RW CoolerMaster Hyper Z600 
OSMonitorKeyboardPower
Windows 7 Pro x64 Toshiba 32" FullHD TV Logitech FSP Hexa 550 
CaseMouse
DeLUX Logitech 
  hide details  
Reply
post #15 of 16
Quote:
Originally Posted by ronnin426850 View Post

That worked! Thanks a ton! Here's some well deserved rep+ thumb.gif

Psychic debugging at its finest smile.gif

Enjoy!
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
post #16 of 16
Thread Starter 
(Solved, see Edit) Warning: Spoiler! (Click to show)
Sorry to bump again, but I've been hitting my head against this the whole day.
DB Setup:

Table Products
col ID (key)
col Downloads
col Status

Table Versions
col ID (key)
col Name
col Status

Table ProductsVersions
col ProdID (key)
col VersionID
col VersionSequence (key)

So if Product 1 ("Player") has 4 versions, and product 2 ("Archiver") has 3 versions, the database would look like:

Products:
1 : 122
2 : 62

Versions:
1 : Player
2 : Player
3 : Player
4 : Player
5 : Archiver
6 : Archiver
7 : Archiver

ProductsVersions:
1 : 1 : 1
1 : 2 : 2
1 : 3 : 3
1 : 4 : 4
2 : 5 : 1
2 : 6 : 2
2 : 7 : 3

What I need to extract as a result is the product id, name, downloads, and version ID of latest version only, something like:

1 : Player : 122 : 4
2 : Archiver : 62 : 7

The query that I have so far is:
Code:
SELECT P.ID, V.Name, P.Downloads
FROM
(
(SELECT Products.ID, Products.Downloads FROM Products WHERE Products.Status = 'LIVE' ORDER BY Products.Downloads DESC LIMIT 100) AS P
JOIN ProductsVersions AS SDIV 
ON SDIV.ProdID = P.ID
JOIN Versions AS V 
ON SDIV.VersionID = V.ID
) 

But that gives me a new row for every value of VersionSequence, and the table gets very bloated with a lot of duplicate results.
How can I limit the Join with ProductsVersions to only the rows with highest value of VersionSequence per ProductID ?

EDIT: Sorry, solved it with this query:
Code:
SELECT P.ID, V.Name, P.Downloads
FROM
(
(SELECT Products.ID, Products.Downloads FROM Products WHERE Products.Status = 'LIVE' ORDER BY Products.Downloads DESC LIMIT 100) AS P
JOIN ProductsVersions AS SDIV 
ON SDIV.ProdID = P.ID
JOIN Versions AS V 
ON SDIV.VersionID = V.ID AND V.Status = 'LIVE'
) 

Edited by ronnin426850 - 10/21/13 at 7:36am
My PC
(14 items)
 
Wife's PC
(15 items)
 
 
CPUMotherboardGraphicsRAM
Core i5 4460 AsRock H81M-DG4 Sapphire Rx470 Platinum KVR 1600 16Gb 
Hard DriveHard DriveCoolingOS
2x Seagate 3Tb Samsung 850 EVO 120 Scythe Ninja 3 Rev.B Windows 10 Pro 
MonitorKeyboardPowerCase
Fujitsu Siemens A17-2A Logitech K280e SuperFlower SF-550K12XP Thermaltake Versa H25 
MouseAudio
Logitech G402 Sony MDR XD150 
CPUMotherboardGraphicsRAM
Athlon 750K 4.0Ghz AsRock FM2A75 Pro4+ Sapphire R9 270X Dual-X Kingston 2x4Gb 1600 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 120  Western Digital 320Gb LiteON DVD-RW CoolerMaster Hyper Z600 
OSMonitorKeyboardPower
Windows 7 Pro x64 Toshiba 32" FullHD TV Logitech FSP Hexa 550 
CaseMouse
DeLUX Logitech 
  hide details  
Reply
My PC
(14 items)
 
Wife's PC
(15 items)
 
 
CPUMotherboardGraphicsRAM
Core i5 4460 AsRock H81M-DG4 Sapphire Rx470 Platinum KVR 1600 16Gb 
Hard DriveHard DriveCoolingOS
2x Seagate 3Tb Samsung 850 EVO 120 Scythe Ninja 3 Rev.B Windows 10 Pro 
MonitorKeyboardPowerCase
Fujitsu Siemens A17-2A Logitech K280e SuperFlower SF-550K12XP Thermaltake Versa H25 
MouseAudio
Logitech G402 Sony MDR XD150 
CPUMotherboardGraphicsRAM
Athlon 750K 4.0Ghz AsRock FM2A75 Pro4+ Sapphire R9 270X Dual-X Kingston 2x4Gb 1600 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 120  Western Digital 320Gb LiteON DVD-RW CoolerMaster Hyper Z600 
OSMonitorKeyboardPower
Windows 7 Pro x64 Toshiba 32" FullHD TV Logitech FSP Hexa 550 
CaseMouse
DeLUX Logitech 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Application Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Application Programming › C# MySQL connection for the complete idiot