SQL – How To Return Ref of Inserted Row

It is extremely useful to return the URN, or any other field, of a newly inserted row for a given table in a single query. The OUTPUT clause makes this possible.

insert into MY_TABLE (Field_1, Field_2, Field_3)
OUTPUT Inerted.REF
values (‘Foo’,’Bar’,’Example’)

The above example returns the REF field, which contains auto-incremented urn in MY_TABLE.

Advertisements

SQL – How to Update and Select in the same query

The OUTPUT clause Returns information on each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

These results can be returned to the processing application for further use.

The following example uses the OUTPUT clause to return row values in conjunction with an UPDATE query which effectively enables a SELECT and UPDATE command in the same query.
 
— CREATE TEMP TABLE
CREATE TABLE #TempTable ( URN INT, F1 VARCHAR( 50), F2 VARCHAR(50 ), USED VARCHAR (1))
— INSERT DATA
insert into #TempTable values ( 1,’F1_Val_1′ ,’F2_val_1′, ‘N’)
insert into #TempTable values ( 2,’F1_Val_2′ ,’F2_val_2′, ‘N’)
insert into #TempTable values ( 3,’F1_Val_3′ ,’F2_val_3′, ‘N’)

— UPDATE SINGLE ROW IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROW
UPDATE #TempTable
SET F1 = ‘F1_New_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN = 1

— UPDATE MULTIPLE ROWS IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROWS
UPDATE #TempTable
SET F1 = ‘F1_And_F3_new_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN in (1 ,3)

— UPDATE ALL ROWS IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF ALL ROWS IN TABLE
UPDATE #TempTable
SET F1 = ‘New_Global_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2

— UPDATE TOP 1 ROW IN TABLE WITH NEW VALUE
— MARK USED = ‘Y’
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROW
UPDATE #TempTable
SET F1   = ‘F1_New_Val’
,USED = ‘Y’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN in (select max( URN) from #TempTable WHERE used = ‘N’)

— DROP TEMP TABLE
DROP TABLE #TempTable

 

JAVA – JFreeChart – How To Save a JFreeChart to JPEG File

 

A quick how to use JFreeChart quide can be found at- https://robbamforth.wordpress.com/2008/10/30/java-jfreechart-graphs-and-charts-in-java/

I wanted to be able to output the the charts to a picture file (JPEG) programatically. After a bit of research I got the following methods to paint the chart and output the image to a file.

Add these 2 methods to the class that creates the charts:

Method 1 saveToFile():

public static void saveToFile(JFreeChart chart,

    String aFileName,

    int width,

    int height,

    double quality)

    throws FileNotFoundException, IOException

    {

        BufferedImage img = draw( chart, width, height );

 

        FileOutputStream fos = new FileOutputStream(aFileName);

        JPEGImageEncoder encoder2 =

        JPEGCodec.createJPEGEncoder(fos);

        JPEGEncodeParam param2 =

        encoder2.getDefaultJPEGEncodeParam(img);

        param2.setQuality((float) quality, true);

        encoder2.encode(img,param2);

        fos.close();

    }

 

Method 2 draw():

    protected static BufferedImage draw(JFreeChart chart, int width, int height)

    {

        BufferedImage img =

        new BufferedImage(width , height,

        BufferedImage.TYPE_INT_RGB);

        Graphics2D g2 = img.createGraphics();

                       

        chart.draw(g2, new Rectangle2D.Double(0, 0, width, height));

 

        g2.dispose();

        return img;

    }

 

Call the saveToFile method to save the chart to a JPEG:

try{

    saveToFile(pieChart,”c:/test.jpg”,500,300,100);
}catch(Exception e){

    e.printStackTrace();

}

This tells the application to save the current pieChart to a file called c:/test.jpg, with a width of 500 a height of 300 and a quality of 100.

The createPiePanel() method will now look like:

public static JPanel createPiePanel(int ring, int call, int off, String title) {      

        JFreeChart pieChart = createPieChart(createPieDataset(ring, call, off), title); 

        try{

            saveToFile(pieChart,”c:/test.jpg”,500,300,100);

        }catch(Exception e){

            e.printStackTrace();

        }

        return new ChartPanel(pieChart);

    }

SQL – Output Data from SQL Server To File – BCP

The following BCP command selects data from a table called CUSTOMERS in the DATA database and exports it to a file called FOO_REPORT.CSV:


declare
@bcpcommand           varchar(1000)

 

SET @bcpcommand = ‘bcp “SELECT id, title, forename, surname, product FROM DATA.dbo.customers where product = ”FOO”” queryout C:\reports\FOO_REPORT.CSV -c -T”‘

EXEC master..xp_cmdshell @bcpcommand

 You can also output to .XLS and .TXT.