I was looking at MySQL.com and found a new GUI, which came as replacement of old MYSQL administrative GUI. My impression was not good to learn that they have something new, as I thought it might be just that they get their old interface opensourced or something. But when I install this application, hmm .. it looks really good, a decently good managed user interface. Meet all modern application GUI stuff, still keep it professional. Though I download a Beta version but so far so good, it does all basic work I need to perform in manner I am habitual. Looks like I can relay on this interface in future.
In recent past, we got few problem when we need to do Pagination with SQL Server. I specially got frustrated when I saw that MySQL server has “limit” word that does pagination work in MySQL in best known manner for me, BUT microsoft didn’t consider that option yet. But today I found a method for SQL server that does the pagination in similar way as MySQL limit works. I am not very sure if it runs on SQL Server 2000 or earlier but it runs on SQL Server 2005. The magic word here is “ROW_NUMBER()” function that provides a Serial No. kind of Numbering to each record. So to do paging all you need to do is, make Query calling ROW_NUMBER() and you are done.
Here is sample of such query
SELECT * FROM
OVER (ORDER BY EmployeeName) AS Row,
EmployeeId, EmployeeName, Salary
FROM Employees) AS EMP
WHERE Row BETWEEN 2 AND 4
I took this sample and knowledge from a great resources for .NET developer. Website URL is http://www.openwinforms.com/row_number_to_sql_select.html
Hope you like the solution. Any alternative is welcome.
A useful like to know the reason of “Server has gone away” error of MySQL.
It has very good information on why it happens, so you can check and correct the error.
As title says, I was looking to compare User give date range with date range saved in mysql Database. I search internet and found a great small function that solve this problem for me, though they create a function for some programming language but I use same function in mysql (as per mysql syntax) and its all done
select * from TableName where
(‘2008-04-16’ = start_date) or
if((‘2008-04-16’ > start_date),(‘2008-04-16′<=end_date),(start_date<=’2008-08-20’))
here 2008-04-16 is user enter date, and 2008-08-20 is user end end date for date range I collect from web form, and start_date and end_date is field in my table that define date range.
you can read about function here http://ryanfarley.com/blog/archive/2004/08/19/966.aspx
Often we need to take quick backup of database with data before we can make changes in table. for this I create the image of table within same database, as it is the fastest method. It is one of the reason why one need to create image a table.. or copy one table from another.
It can be achieved in all RDBMS
Here is SQL for MS SQL
select * into newTable from OldTable
However, their is another command (it works for me on mysql)
create table newtable as (select * from oldtable)
Best of luck with backup and server transfers.
Once you create a table and what to reset autoincrement value of a table to your desire number, you need to set some system table values, which are not available for direct editing. To perform this you need to do following:
SELECT OBJECT_ID,OBJECT_NAME(OBJECT_ID) AS TABLENAME, NAME AS COLUMNNAME, SEED_VALUE, INCREMENT_VALUE, LAST_VALUE, IS_NOT_FOR_REPLICATION FROM SYS.IDENTITY_COLUMNS ORDER BY 2
This statement simply show all table in your select table with auto increment value. Once this gives you good information, you can run following command to change value
DBCC CHECKIDENT(‘mytable’,RESEED,[new numeric value])
If this runs perfect give something like this:
Checking identity information: current identity value ’14’, current column value ’30’.DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Otherwise it give an error. (In above message 14 and 30 will be replaced with your values.
MSDN Link for more details : http://msdn2.microsoft.com/en-us/library/ms176057.aspx