Archive

Posts Tagged ‘Scripts’

Uploading Images in SQL

June 17th, 2009 Russell No comments

To upload a file to field with a type of varbinary(max), use the following:

To update a record

update <TABLE>
set <FIELD> = (Select * FROM OPENROWSET(BULK '<PATH>', SINGLE_BLOB) <FIELD>)
where <FINDRECORD>

To insert a record

insert into <TABLE>
Select
<FIELDS>,
<FIELDNAME>.*
FROM OPENROWSET
(BULK '<PATH>', SINGLE_BLOB) <FIELDNAME></code>

The path has to be accessible by the SERVER to update. Make sure the varbinary is set to varbinary(max).

Opening New Browser Windows Part 3

June 11th, 2009 Russell No comments

Now we have the design from part 2, we need to do a proof of concept to check browser compatibility, and check that our design meets the requirements outlined in part 1.

We need to create two javascript files:

  • ParentWindow.js
  • ChildWindow.js

We will begin writing the ParentWindow.js.

Firstly, we need to create a parameter object. Javascript objects can behave as “associative arrays”, therefore creating a new Object instance is all we need to do to have a collection of parameters:

var parameters = new Object();

We also want to attach this object to our window object. Then it can be accessed by child windows when required:

window["Parameters"] = parameters;

So far so good. We also need to have a reference to the child window that is currently open:

var childWindow = null;

We want to provide a function that can be used to retrieve parameters as well. This means that the child window can either use a property or call a function:

window.GetParameters = function ()
{
return parameters;
}

We need a function which handles the event when the child window has been closed. It will reset the paramter values, clear the child window reference and call a custom event handler if specified. We want it to only ever get called once. Once a child window has been unloaded, it is no longer in a state to return more data.

window.HandleChildUnloadEvent_Internal = function(args)
{
// Reset parameters.
parameters = new Object();
window["Parameters"] = parameters;

// Remove child window reference.
childWindow = null;

// If the page defines an event handler, we will notify it.
// Will only call this method once.
if (HandleChildUnloadEvent && !ChildUnloadEventHandled)
{
HandleChildUnloadEvent(args);
}

ChildUnloadEventHandled = true;
}

The main function, which opens the child window, checks if a window is already opened and then create a new one. If a window is already open, we will set focus to it.

function OpenWindow(url, width, height)
{
if (childWindow == null)
{
// Reset variable.
ChildUnloadEventHandled = false;

// Set childWindow reference to an empty object.
// We want the childWindow to NOT be null while the window is loading.
// This prevents multiple windows opening.
childWindow = new Object();

childWindow = window.open (url, ”, config=’height=’ + height + ‘, \
width=’ + width + ‘, toolbar=no, menubar=no, scrollbars=yes, resizable=yes, \
location=no, directories=no, status=no’);
}
else
{
childWindow.focus();
}
}

That is all we need for the parent javascript file. In the HTML page that is the parent page, we need to call the correct function to open the window, and updtae the page when the child window has returned:


function OpenSuburbWindow() {
// Set parameter values.
parameters["SuburbText"] = document.getElementById(”txtSuburb”).value;

// Open the window.
OpenWindow(’../SuburbLookup.htm’, ‘520′, ‘600′);
}

function HandleChildUnloadEvent(args) {
if (args != null) {
document.getElementById(”txtSuburb”).value = args["SuburbText"];
}
else {
document.getElementById(”txtSuburb”).value = “”;
}
}

The above code will get the existing value from a text input box and add it as a parameter. It will then open the window. Once the window closes, we handle the child unload event by updated the text input box with the selected parameter. If the parameter is null, (unexpectedly closed), we clear the text input box.

The child window javascript file is even simpler.
We want a parameter variable that will be populated by the parent window:

var parameters = window.opener.GetParameters();

We also want a function that will be called when the window is unloaded. It will notify the parent window of the event and pass back updated parameters:


function NotifyParentWindow(args)
{
window.opener.HandleChildUnloadEvent_Internal(args);
}

We also want a function that will pass back our parameter object, and close the window. This function will be used by the child window HTML page:


function CloseWindow()
{
NotifyParentWindow(parameters);
window.close();
}

Now all we need to do is populate the child window when the page has loaded and update the parameters and close the window. To populate the child window we create a function that is called from the body onload event:


function DoInitialSearch() {
var suburbValue = parameters["SuburbText"];
if (suburbValue) {
document.getElementById(”txtSuburb”).value = suburbValue;
}
}

We also need to create a function that will be called when the window wants to close and pass back values. In this case, when a suburb is selected we will return and close the window:


function SelectSuburb(suburb) {
parameters["SuburbText"] = suburb;
CloseWindow();
}

The proof of concept seems to be working. I encountered the following issues that need resolutions:

  • Opera does not fire the window.unload event.
  • When the site is run as static HTML pages, firefox does not allow the child window access to its parent.
  • Opera has issues with setting focus to a window.

It seems to be a widely known problem that Opera does not fire the unload event consistently with other browsers.  Setting focus to a window is not an issue that will stop functionality. It simply increases usability.

At this stage only the prototype is running as a static HTML page, so this will not be a problem when running under IIS (under the same host).

So proof of concept succeeded, now on to implementation.

SQL Server Create Login Script

June 10th, 2009 Russell No comments

The following code creates a login for a user and assigns it datareader and datawriter access to the database under which this script is run.

Note: This does NOT create permissions to execute stored procedures. To do this, you need to either give the user ‘dbo’ role, or use GRANT EXECUTE statements for each stored procedure.

--Add New User Permissions for X.
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'XAppUser')
BEGIN
EXEC master.dbo.sp_addlogin @loginame = N'XAppUser', @passwd = N'pwd#', @deflanguage = N'us_english'
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'XAppUser')
EXEC dbo.sp_grantdbaccess @loginame = N'XAppUser', @name_in_db = N'XAppUser'
GO
EXEC sp_addrolemember N'db_datareader', N'XAppUser'
GO
EXEC sp_addrolemember N'db_datawriter', N'XAppUser'
GO

SQL Server Stored Procedure Grants

June 10th, 2009 Russell No comments

We are at the final stages of development and preparing the database scripts to be built. We test on a staging server and realise the user we created doesn’t have permissions to use the stored procedures we created! Ahh!

This means we forgot to grant our stored procedures execute permissions for our user. Normally we put this at the bottom of our stored procedure script:

GO
GRANT EXECUTE ON [dbo].[spAddressInsert] TO [OurUser]
GO

We are at the end of development and it will take way too long to add this to each script. What to do!

We can query the database to find all stored procedures in the database, and create a list of grant commands. Note they must be separated with GO’s. Here is some sample code to do this:

SELECT 'GRANT EXECUTE ON [dbo].['+[name]+’] TO [OurUser]
GO’

FROM sys.objects WHERE TYPE=’P’

Run this in a SQL management program (SQL management studio or Toad) and output the result to text. You will have a list of grant commands for all of your stored procedures!