Dailycode.info

Short solution for short problems

Oracle: Concatinate columns in a group by function

I wanted to concatinate information of a column into a ; seperated column in a group by.

eg

Projectmanagerdepartmentcustomer
1MarkABDO
2PietBBDO
3StevenAMicrosoft
I want to count the number of project for each customer and see which managers and departments there are.

With a LISTAGG function this works

SELECT

count(Project) as nr, LISTAGG(manager, ' ') WITHIN GROUP (order by manager) as managers 

FROM table 

GROUP BY customer

ORDER BY customer


result will be

2            Mark;Piet            BDO

1            Steven                Microsoft


Drop tables procedure for .Net and MS SQL

I wrote a simple procedure that will drop tables in a database that have a certain prefix. 

First I initiate the process layer:

MESCockpitProcessLayer.DirectDataAccess pl = new MESCockpitProcessLayer.DirectDataAccess();

Then I will fill a dataset with the following statement:

//This script will get all tables with the selected prefix and generates a drop statement. I load it in a dataset, each drop statement

//is a row

string DropTableString = "SELECT 'DROP TABLE ' + TABLE_NAME + ' ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'GP_MES%'";

DataSet ds = pl.GetDataSet(new StringBuilder(DropTableString), serverName, DBName, DBUserName, DBUserPWD);

Next I simply go over each row in the dataset table and execute the statement:

if (ds != null)

{

    foreach (DataRow table in ds.Tables[0].Rows)

    {

        //Drop the table using the generated script

        pl.ExecuteQueryDirect(new StringBuilder(table[0].ToString()), serverName, DBName, DBUserName, DBUserPWD);

    }

}


The generated statement look like this:

DROP TABLE EventLog 

The data class that gets the data and executes the SQL statements is just the very simplest using the Data.SqlClient:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace MESCockpitDataLayer.SqlClient

{

    publicclassMESDataAccess

    {

        publicstaticint ExecuteNonQuery(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            //SqlTransaction MyTrans = null;

            try

            {

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                return executeCommand.ExecuteNonQuery();

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

 

        publicstaticobject ExecuteScalar(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            //SqlTransaction MyTrans = null;

            try

            {

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                return executeCommand.ExecuteScalar();

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

 

        publicstaticDataSet GetDataSet(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            SqlDataAdapter MyDataAdapter = null;

            DataSet returnDS = newDataSet();

            try

            {

                MyDataAdapter = newSqlDataAdapter();

               

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                MyDataAdapter.SelectCommand = executeCommand;

               

                MyDataAdapter.Fill(returnDS);

                return returnDS;

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

    }

}

 


How to get a dataset with variable SQL from the entity framework

When you are working with the EF, you will finally get to the point that you need some joined data from the database that could be based on variable SQL queries. In our case we can define SQL queries in the database that will decide what to show in grids. Since this data is combining several tables from a non relational DB, its not returning entity data. So I found a good way to get a dataset and still use the entity framework connection. So no new connection logic to be instantiated:

 

public DataSet ExecuteStoreQuery(string commandText)

{

    DataSet retVal = new DataSet();

    EntityConnection entityConn = (EntityConnection)MyContext.Connection;

    SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;

    SqlCommand cmdReport = new SqlCommand(commandText, sqlConn);

    SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);

    using (cmdReport)

    {

        cmdReport.CommandType = CommandType.Text;

        daReport.Fill(retVal);

    }                  



    return retVal;

}

 

You should only use this for view purposes only!!!


Entity Framework and There is already an open DataReader associated with this Command which must be closed first.

There is already an open DataReader associated with this Command which must be closed first.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Source Error: 

When using the entity Framework I got that error. Got it when I was looping within a loop. Since there was no association between the objects, I had to do a loop en inner loop, it looks like this:

foreach (GP_PROJ_ProjectSteps step in Ents.GP_PROJ_ProjectSteps.Where(s=>s.ProjectID == project.ProjectID))

{

    foreach (GP_PROJ_ProjectTasks task in Ents.GP_PROJ_ProjectTasks.Where(d => d.StepID == step.StepID))

    {

        Ents.GP_PROJ_ProjectTasks.DeleteObject(task);

    }

    Ents.GP_PROJ_ProjectSteps.DeleteObject(step);

}

Ents.GP_PROJ_Projects.DeleteObject(project);

Ents.SaveChanges();

 

The solution to this problem was to allow or enable Multiple Active Result Sets (MARS). This is done in your connection string:

Data Source=DBServer;Initial Catalog=MyDB;Persist Security Info=True;User ID=DBUSER; Password=PWD;MultipleActiveResultSets=true;


Fill Entity with custom query code. (Inner join etc)

 

Just a quick example of an ExecuteStoreQuery. This is a really nice gift of the entity framework. If you need fill an entity with some dynamic sql, you can use the ExecuteStoreQuery. Really easy to use!

return ents.ExecuteStoreQuery<GP_PROJ_Projects>("Select P.* From GP_PROJ_Projects P left join GP_PROJ_Codes C on C.CodeValue = P.ProjectStatus and C.CodeType = 'Project_Status' where C.CodeValue1 < {0} ", 1000).ToList();

Same result but can help with passing dates and other values:

return ents.ExecuteStoreQuery<GP_PROJ_Projects>("Select P.* From GP_PROJ_Projects P left join GP_PROJ_Codes C on C.CodeValue = P.ProjectStatus and C.CodeType = 'Project_Status' where C.CodeValue1< @p0",new SqlParameter { ParameterName = "p0", Value = 1000 }).ToList();

 

Source: http://msdn.microsoft.com/en-us/library/ee358769.aspx


Entity framework and MS SQL database (identity insert)

Using the entity framework in combination with an existing MS SQL database, comes with some restrictions and sometimes you need to change the DB structure.

For example, when you start from an existing database and you use identities as primary keys, the entity framework won't like it. There are several things you can do, you can let the entity framework create the database, or you can switch for example to guids and insrt the guid yourself. Because MSSQL doesn't allow changing the primary keys, I just generated a table creation script, changed the primary keys to guids, dropped all tables and ran the scripts.

To erase tables quickly in MS SQL, use:

(Warning, the following script will delete all tables!!!!)

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

This will delete all tables, then you can recreate the tables from a script you earlier created and alter the script as needed.


Outer join in Oracle SQL

I'm by origin a MS SQL  writer. This is the SQL I grew up with, although at school I learned Oracle, at my first job it was MS SQL and since then 90 %  of the SQL I use was MQ SQL. But at my current employer, it the other way around. It's 90% Oracle. Working more then 1 year with Oracle, I'm becoming more and more aware of the differences between MS SQL en Oracle SQL. I'm will discuss one of these differences.

When you need information from 2 tables, in MS SQL we use the Join clause, inner or outer. When it's possible that the child table has no related data for the record of the master table in the query, and still you want to see the record from the master table, you will need to use outer join. Now I tried this in Oracle SQL, there was no syntax error, but also no data. I could figure out what I was doing wrong.

This syntax returned nothing:

SELECT a.GUID, a.ROLE_NAME, b.APP_NAME, b.guid as APPGUID 
FROM CIG_MGMT.ROLE a
LEFT OUTER JOIN CIG_MGMT.ROLE_APPS b ON b.ROLE_GUID = a.GUID 
WHERE a.IS_VALID = 1 AND b.IS_VALID = 1 
ORDER BY a.ROLE_NAME,b.APP_NAME

So I started looking for an answer. I came up with this site: http://www.adp-gmbh.ch/ora/sql/outer_join.html

I changed the code into this and it worked!

SELECT a.GUID, a.ROLE_NAME, b.APP_NAME, b.guid as APPGUID 
FROM CIG_MGMT.ROLE a, CIG_MGMT.ROLE_APPS b 
WHERE b.ROLE_GUID(+) = a.GUID and 
a.IS_VALID = 1 AND b.IS_VALID(+) = 1 
ORDER BY a.ROLE_NAME,b.APP_NAME
 

Just use the (+) syntax for every field of the (child) table in the where clause. 

A solution was found, but not a reason why the left outer join wouldn't work and the (+) worked fine. The first reason could be that I was using Oracle 8i wich only supports the (+). But this is not the case. We are using 9i and the compiler doesn't complain to the syntax.

So I'm still looking for an explanation, because I have very little time, I cannot investigate this and I will just implement the (+) solution. I hope somebody who knows the answer takes the time to post it here... Or maybe in the future when I will have some more time, I will remember this and look for an answer. But this first 2 years will be hectic ;-)


SQL CE, minimize the database size!

Before you start working on a SQL CE application, you should realize that there is more and thorough analysing needed. Because size is almost always a problem, we have to understand the importance of correctly settings up the database.What you should keep in mind is the following:

* bigint Integer (whole number) data from -2^63 (- 9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The storage size is 8 bytes.

* integer Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). The storage size is 4 bytes. 

* smallint Integer data from –32,768 to 32,767. The storage size is 2 bytes.

* tinyint Integer data from 0 to 255. The storage size is 1 byte.

* bit Integer data with either a 1 or 0 value.

* numeric (p, s) Fixed-precision and scale-numeric data from -10^38 +1 through 10^38 ?1. The p specifies precision and can vary between 1 and 38. The s specifies scale and can vary between 0 and p. Numeric always uses 19 bytes, regardless of its precision and scale. 

* money Monetary data values from -2^63 (- 922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with an accuracy to a ten-thousandth of a monetary unit. The storage size is 8 bytes.

* float Floating point number data from -1.79E + 308 through 1.79E + 308 The storage size is 8 bytes.

* real Floating precision number data from -3.40E + 38 through 3.40E + 38.

* datetime Date and time data from January 1, 1753 to December 31, 9999 with an accuracy of one three-hundredth of one second or 3.33 milliseconds. Values are rounded to increments of .000, .003 or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date (January 1, 1900). The base date is the system's reference date. Values for datetime that are earlier than January 1, 1753 are not permitted. The other 4 bytes store the time of day as the number of milliseconds after midnight. Seconds have a valid range from 0 through 59.  

* national character(n) Synonym: nchar(n) Fixed-length Unicode data with a maximum length of 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered.

* national character varying(n) Synonym: nvarchar(n) Variable-length Unicode data with a length of 1 to 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered.

* ntext Variable-length Unicode data with a maximum length of (2^30 - 2) / 2 (536,870,911) characters. The storage size, in bytes, is two times the number of characters entered.

* binary(n) Fixed-length binary data with a maximum length of 510 bytes. The default length is 1.

* varbinary(n) Variable-length binary data with a maximum length of 510 bytes. The default length is 1.

* image Variable-length binary data with a maximum length of 2^30 – 1 (1,073,741,823) bytes.

* uniqueidentifier A GUID. Storage size is 16 bytes.

* IDENTITY [(s, i)] This is a property of a data column, not a distinct data type. Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified, and the column cannot be updated. s (seed) = starting value i (increment) = increment value. 

* ROWGUIDCOL This is a property of a data column, not a distinct data type. This is a column in a table that is defined by using the uniqueidentifier data type. A table can only have one ROWGUIDCOL column.  

So if you add a column creationdate, but it is not needed by the system nor for reporting. Then you should consider deleting it. Keep in mind that for one record it is 4 bytes, but for 1000000 records it needs 4000000bytes or 3.8 mb.

In an application we used Netpad from psion teklogix with a fixed memory of 40 mb. Only 5 Mb is calculated for the DB. We don’t store million of records, but we do store ten thousands of records and a leaving out a datetime in a table could spare us 0.38 mb.

In case of synchronizing with an other DB, be sure to write all data to the mother DB and do not keep unnecessary data on the netpad.

When you estimate the size of a table or of a database, consider the following information:

* 

Each row in the table has an overhead of 6 bytes.

* 

Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.

*

Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.

*

Zero-length string columns occupy 1 byte in the row.

I understand that with the uprizing flash memory, capacities will grow larger, but so will systems become more complex and in need of more storage, therefore it always will be a good practise to try to minimize the size of the databases.

I’ll write more on this subject in a coming post. I’m currently optimizing a SQL Ce database.

source: http://support.microsoft.com/kb/827968