Archive for the ‘SQL’ Category

SQL Developer’s “SQL History”

Monday, October 3rd, 2011

I don’t know about you, but I now love the SQL History button (F8) in SQL Developer. So many times I have found that I have lost unsaved work, or overwritten existing. I have always wished that there was a way to recover past queries.

Well, as it turns out, SQL Developer has a SQL History button. Pressing the “F8” key, or from the “View” menu, select the menu option:

This will bring up a new tab, which shows all the queries that have been run in the last while:

Unfortunatly, the problem is that you can look, but you can’t really touch. There is no way to copy the entries to the clipboard or back into a worksheet. Luckily there is a solution/workaround. There are a number of files that are kept locally, which is a running history of everything you have done.

UPDATE: It appears that if you double click on the History item, it will be copied onto the currently active SQL Worksheet. This saves having to search through the history files.

These files can be found at C:\Documents and Settings\.sqldeveloper, or in my case, C:\Documents and Settings\<login profile name>\Application Data\SQL Developer\system1.5.1.54.40\.history\.

In my case, the folder named “0” had a number of files for the last month, each of which contained revisions of previous days work. Sort by Date Modified and find what you need from there:

 

Using a date time field in SQL Server as a field on a View Form in ARServer 5.x

Wednesday, September 14th, 2011

If you have a table in a SQL Server database that you would like to access via a View Form in ARSystem, you will find that date/time fields will not show up as an available field when you create the View.

So, how do you access the date/time fields with your View Form?

You can create a view for the table in which the View stores the date time info as an integer in Epoch Time (number of seconds since 1/1/1970).

As an example, the Northwind database has an Orders table with 3 date time fields.

Here is the SQL to create a view of this table.

  • CREATE VIEW dbo.OrderView
    AS
    SELECT OrderID, CustomerID, EmployeeID, DATEDIFF(second, ‘Jan 1, 1970’,
    OrderDate) AS Order_Date, DATEDIFF(second, ‘Jan 1, 1970’, RequiredDate) AS Required_Date, DATEDIFF(second, ‘Jan 1, 1970’, ShippedDate) AS Shipped_Date, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
    FROM dbo.Orders

 Make sure ARAdmin or Public has permissions to this view.

From the AR System Admin Tool, create a view form against the Northwind.dbo.order view.  In this example, I use OrderID as the ‘Key’ field.

Use the Wizard to add all fields to the form EXCEPT FOR THE DATE TIME FIELDS.

Now manually add a Date/Time field onto the form. Provide the Label and Database name as a descriptive name such as Order Date.

Next, change to the Database Tab, and in the Name field under View Information, type in the name for the column in the Database View, such as Order_Date in this example.

Do the same for all Date/Time fields you want on your form and save the form.

Note: I tried this with an ARSystem 7.6.04 system running on top of Oracle, however, I get the following error when attempting to specify any of the Date/Time fields and saving. It appears that the newer implementations of the ARSystem keeps track of the valid fields, or that this is a limitation created by using an Oracle database.

Now open the form in the User Tool and verify that you see the data correctly.

Time Zone Note: You will most likely need to account for TimeZone in your CREATE VIEW statement. The Remedy Client automatically assumes that the integer value if already offset to accommodate the TimeZone when the data was stored.  So the Remedy Client offsets it when it displays the value in a Date/Time field.   We need the database view to appear as though the integer value needs to be offset.  You can do this by adding the number of hours you are offset from GMT times the number of seconds in an hours to the DATEDIFF function. 

(You can also check out another article on how to convert ARSystem Epoch time to Oracle TimeStamp, which also shows how to account for time zones.)

 For example, if you are in Pacific Time Zone, GMT-8,  you would add 8 hours to the DATEDIFF function.

 Here is the corresponding CREATE VIEW statement:

  •  CREATE VIEW dbo.OrderView
    AS
    SELECT OrderID, CustomerID, EmployeeID, (DATEDIFF(second, ‘Jan 1, 1970’,
    OrderDate)+(60*60*8)) AS Order_Date, (DATEDIFF(second, ‘Jan 1, 1970’,
    RequiredDate)+(60*60*8)) AS Required_Date, (DATEDIFF(second, ‘Jan 1, 1970’, ShippedDate)+(60*60*8)) AS Shipped_Date, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM dbo.Orders

 

 

Converting ARSystem Epoch time to Oracle TimeStamp

Friday, June 3rd, 2011

In ARSystem, all timestamp fields (CreateDate, LastModifiedDate, etc) are stored in Epoch format. If you don’t know, UNIX epoch is the elapsed # of seconds since January 1, 1970 12:00:00 AM GMT.

When viewing the data from the ARSystem, remedy automatically does the conversion process so that you always see the correct Date/Time.

TimeStamp from Epoch

 However, when doing a SQL query, all timestamp fields will return back a number in the format of “1307064623”. This makes it rather difficult when you wish to view the data in an application like SQL Developer, and have the data make sence.

The following SQL code will return back the Epoch values, but in a Date/Time format, as the example image to the right shows:

SELECT
  to_char(cast(to_date(‘19700101′,’YYYYMMDD’)+createdate/86400 as timestamp with local time zone),’YYYY-MM-DD-YYYY HH:MI:SS AM’) as Epoch_TimeStamp
FROM
  ARADMIN.bmc_core_bmc_computersystem
WHERE
  datasetid = ‘BMC.ASSET’
;

 The date/time format can be changed depending on your needs. The example above uses ‘YYYY-MM-DD-YYYY HH:MI:SS AM’. By reformatting the string, your timestamp format will adjust accordingly.