Archive

SQL

3 Articles
28th May 2019
DevelopmentLaravelLaravelmySQLPHPPHPProgrammingSQLSQLWeb Development

How to Execute a Stored Procedure in Laravel 5.6

I’ve been stumped on this and tried everything I can think of but I can’t get Laravel to execute a working mysql query to reorder an ordering column when deleting a row.

Here’s a sample simplified query, which works directly in mysql:

SET @number = 3; UPDATE images SET order = (@number := @number + 1) WHERE id >2 AND project_id = 10

Laravel code that doesn’t update the database but throws no errors:

$sql = 'SET @number = 3; UPDATE images SET order = (@number := @number + 1) WHERE id >2 AND project_id = 10';
DB::raw($sql);

Other things I’ve tried which throw errors including saying there’s an error in the query:

DB::unprepared($sql);
DB::statement(DB::raw($sql);
DB::statement($sql);
DB::update($sql);
DB::select($sql);

I’ve also tried splitting it the statements with no luck:

DB::statement("SET @number = 3;");
$sql = 'UPDATE images SET order = (@number := @number + 1) WHERE id >2016 AND project_id = 10';
$update = DB::update($sql);

I tried a few other things which I didn’t log in Git but I had no luck getting any of it to work. I asked for help on the forums and people told me to use Laravel’s Eloquent model to update rows one by one which is pretty inefficient. You get extra trips over the network (especially important when the code is not on the same server as the database server since you can get dropped calls), extra connections to the server, the extra overhead of processing the query on the server and in the database, etc instead of just having mysql make the update.

The recommended solution may not seem like a big deal but I’ve run into issues where client connections have been dropped mid-update and left a table partially renumbered.

As a last resort and workaround, I opted to use a stored procedure to accomplish my goal. That presented its own can of worms as searching for examples on how to execute stored procedures in Laravel 5.x was also not easy. There’s nothing in the documentation and all the examples I found didn’t work.

Here’s what worked for me with Laravel 5.6, with mySQL 5.6.38:

DB::statement('call spRenumberComments(?, ?)',  [$id, $projectid);

16th February 2015
ASP.NETDevelopmentProgrammingSitecoreSitecoreSQL

How to Reset Sitecore 7.1 & Sitecore 7.5 Forgotten/Lost Admin Password

In working on implementing a Sitecore site into an existing code base inherited from another vendor, I discovered that the admin password had been modified and the vendor would not share it. Not being able to login to the admin section of Sitecore was not ideal to say the least. After scouring the web, most articles contained instructions on how to reset the password, but almost all of them applied to Sitecore 6 and below. For Sitecore 7 and above, most articles were not applicable as they introduced the PasswordSalt field into the database which Sitecore uses to hash the password.

If you’ve run into a similar situation, or you’ve forgotten or lost your admin account password, getting access back to everything is pretty simple. Load SQL Management (or your favorite SQL editor) and execute this query against your Core database:

UPDATE dbo.aspnet_Membership
SET [Password]=’qOvF8m8F2IcWMvfOBjJYHmfLABc=’, [PasswordSalt]=’OM5gu45RQuJ76itRvkSPFw==’, [IsApproved] = ‘1’, [IsLockedOut] = ‘0’
WHERE UserId IN (SELECT UserId FROM dbo.aspnet_Users WHERE UserName = ‘sitecore\Admin’)

This will now reset the default admin password to ‘b’ so that you may login to the Sitecore desktop. Happy editing!

20th September 2011
2008AdministrationDTSXNTFSProgrammingSolutionsSQLTips and TricksTroubleshootingWindowsWindows Server

SQL 2008 DTSX

The Problem

Earlier today, I was working on setting up DTSX so some end users could run some packages. After loading and testing the packages successfully, the users tried running the package and encountered an interesting error:

SSIS Execution Properties
Failed to open package file “C:\Program Files\Microsoft SQL Server\100\DTS\Packages\dts_filename.dtsx” due to error 0x80070005 “Access is denied.”. This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. ({FFEE8F2F-A0A6-40BE-8CDA-86BEC124F874})

The packages were provided by another vendor so I wasn’t keen on trying to modify things within the packages themselves. I was able to run the packages under my admin account but the end users kept running into the error which lead me to believe that the user needed some special permissions. The users were connecting to this virtual server via remote desktop. While it was a dedicated virtual machine specifically for this project, I really didn’t want to give users admin rights because…well I don’t think that needs to be explained so I hunted around and of course there are no settings for controlling access via permissions in management studio. It was time to take to the interwebs and use my Google-Fu and see what others have found on this error. I found others who had similar errors but none had the exact issue. Some similar errors:

The Solution

I remembered that SQL Management Studio had issues with accessing files in different locations (i.e. My Documents). With the new security settings in Windows, you may have noticed you need admin rights to add, run, or or modify folders/files in locations like c:\Program Files in Windows 7/2008. I wondered if DTSX used a special permission that allowed it to access files and checked the groups under the Server Manager. I found a group called SQLServerDTSUser$[MachineName]. I added the users who were executing the packages to this group and then checked the permissions on the folder C:\Program Files\Microsoft SQL Server\100\DTS which didn’t have the group listed. I added the group to the folder permissions, tested the package and voila – it worked.

Continue Reading