Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I was trying to query a complicated sql query with joins, groups, etc and after upgrading mySQL the group by stopped working.

In order to fix issue I had to open mySQL and on the database I had to run the following query:

SET GLOBAL sql_mode = '';

Advertisements

Escaping single quote in SQL

Very simple, in order to escape a single quote in SQL, just add another single quote before your actual single quote as the following example:

Error:  UPDATE SampleTable SET (Title = ‘Welcome to Bob’s Burgers’)

Solution:  UPDATE SampleTable SET (Title = ‘Welcome to Bob”s Burgers’)

ERROR: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

This week I was working on a migration script where I needed to run multiple sql queries. Since I was going to use this only once, I decided to use SqlHelper classes to connect and run sql to MsSql2012. Part of this included inserting and updating a date into the database. On my local computer this was working fine however, when placed on the server, I kept on getting the below error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

A sample of the Sql I was trying to use was:

UPDATE Table SET StartDate = '26-Mar-15 2:53:42 PM' WHERE ID = 15

I googled this error and amongst the top suggestions/solutions I found:

  • sql query is passing the wrong date format so it goes out of range
  • you should not pass DateTime values as sting in your SQL statement. Instead use parameters such as (    cmd.Parameters.AddWithValue("@mvendid", mVendid);  )

None of the 2 above worked. I tried all sorts of formats (“dd/mm/yyyy hh:mm:ss”, “mm/dd/yyyy hh:mm:ss”, “yyyy/mm/dd hh:mm:ss”, etc etc). I tried also declaring a new date in the sql and saving it, which still gave same error. I was about to give up when I though what about the computer’s date?

Solution:

We checked my computer’s date format from “Control Panel -> Region and Language -> Formats” and noticed that the short date was completely different. Since I was desperate, we tried changing the server’s local date to match my date. Just like a miracle it worked fine.

Region

 

 

Hope this helped … as I was going mental for a while there!!!

SQL Computed Values

Lets say I would like to query a table but I would like to compute a column according to another value.

Example:

Original Table Columns:

  • Id (int)
  • FirstName (varchar)
  • LastName (varchar)
  • Married (bit)
  • FamilyName (varchar)

I would like to get from table “Females” the following data, where Last Name should have the original nee surname if married:

  • Id
  • FirstName
  • LastName

Sql Statement: 

SELECT Id, First Name, (CASE WHEN Married = ‘1’ THEN LastName + ‘ Nee ‘ + FamilyName ELSE LastName END) As LastName FROM Females

 

Result would look like:

Id First Name Last Name
1  Jane  Smith
2  Sue  Duffy Nee Perry
3  Kelly  Johnson Nee Smith
4  Marie  Dickson

Where 1 and 4 have Married as False, while 2 and 3 have Married as True

 

 

 

 

Nhibernate simple Select, Insert, Update and Delete

nhibernate

Nhibernate is one of the many Object-Relational Mapping (ORM) tools for the .NET platform.

A very quick description of ORM:

ORM is the process of mapping objects to database relations without the requirement of writing SQL statements (such as SELECT, INSERT, UPDATE, DELETE, JOIN, etc etc) generally through the use of XML files.

Nhibernate eliminates the requirement of using ADO.NET, Linq, etc and thus is the back bone of the Data Access Layer (in nThier Architectures).

Requirements for this Tutorial:

This tutorial will be split in 12 steps:

Step 1:- Create a 2-Thier Project

Step 2:- Install Nhibernate

Step 3:- Define Business Objects (Domain Folder)

Step 4:- Define the Mapping (Mappings Folder)

Step 5:- Setting XML Schema

Step 6:- Configure Nhibernate

Step 7:- Create ISessionFactory

Step 8:- Create Table from Schema and Test Connection

Step 9:- Create Repositories

Step 10:- Define Method from Repositories

Step 11:- Testing Add, Update, Delete Method

Before we continue, I want to add that “I know this is a very long and mostly text based tutorial” but I have searched the internet sooo much for a decent working tutorial without errors and complications that this post here should cover it ALL … I also did it again while writing it to make sure no errors are found. So, hold on and read through… EVERYTHING should be there 🙂

So, lets begin …

Continue reading

Storing Greek | Polish | Cyrillic (Russian) Alphabet in Database

When saving cryillic alphabet or other languages which do not use the english alphabet, the datatypes text and varchar do not support the alphabet symbols.

It usually turns all text into ??????

For instance I had tested saving “Интернет-программирование ” into a column with datatype text and it saved all as ” ???????- ? ???? ???”.

In order for this not to happen we simply save the column with the datatype nvarchar(MAX) since nvarchar supports a vast range of symbols which other datatypes do not. The only disadvantage of such datatype will be that it takes some more space than other datatypes.

PS. To send Such alphabets in an email it is required to first add the following:

MailMessage mm = new MailMessage(“EmailAddress”, “EmailAddess”);

mm.IsBodyHtml = true;
mm.SubjectEncoding = System.Text.Encoding.UTF8;
mm.BodyEncoding = System.Text.Encoding.UTF8;