Creating extended stored procedures on Delphi.

Well, originally we were going to implement a sexy Delphi.Net CLR Based Stored procedure now that we are using MSSQL 2005 and hearing all the good things about this highly publicized feature. Sadly, we hit the wall after initial testings showed us the big amount of limitations on the assemblies that we could use and all the exceptions we must made to make it work on MSSQL 2005. For example:

* First, you need to enable CLR on the database. (Thats ok.)
* Classes must be public and static. (Makes it harder, but we can manage it)
* Avoid namespaces with anything that is not the list of "safe" name spaces approved by Microsoft. (mmm)
* "Safe" assembly is simply a way of saying "non-microsoft built" assembly. You have a couple more "modes" on how to install your assembly on MSSQL, but as you can read below (MSSQL Online help), it gets touchy, and probably complicated. (yeah, I tried it, got ugly)

“To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. “

* MSSQL will complaing about the CodeGear assemblies being unsafe, some tweaking is necessary.

* Third party assemblies are not welcome on MSSQL. i.e. RemObjects.

Well, in summary, after playing with it, adding our assemblies required a lot of code trimming and removing any namespace that has any reference to anything visual (makes sense) even if it is not being used at all. Finally, the detail that just finish helping us to dropped the idea was that we don't have the source code for those "extra" assemblies (RemObjects) that we required and the amount of trimming just got out of control.

With one option out we went back to the drawing board with the plan to extend and update an old implementation of "Old School" extended stored procedures, as soon as I open that old project I realized why I never liked working with it, parsing the parameters, checking their types, defining data columns to return a proper record set was simply a big painful and slow process.

Thankfully, I was able to find in the vault of memories a great presentation by Berend de Boer made in Inprise DevCon '99 where he explains the general steps to implement them and even included a great piece of source code of the mighty "TSQLXProc" object that simply takes over the painful implementation and takes it to the Delphi way. After that finding Berend's website was easy.

For anyone needing this, check this link.

My Kudos to Berend,

EVERY DAY TIPS: Manually deleting a windows service

There I was playing with setting up subversion server running as a Windows Service until I made a tragic mistake and I get to the point of needing to remove the service that I just recently installed.

Ok, fine, as a good Delphi guy, I went looking for that sexy graphic console (Service Manager) that will give me the all mighty power to delete the service, but, I hit a wall after realizing that such an option was disabled for me.

So, after googling a bit I found two nice options.

1. If you remember the exact name of the service (not the description), you can use the sc command:
C:\>sc delete "service name"

If you don't know it, then you can go straight to the registry, find your guy and delete it.

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

You indeed will need to restart the computer to see these changes fully reflected.

Delphi for PHP 2 en Español

Well, great news for my mother language! We have now Delphi for PHP in Spanish!.

Yup, just after the official release we are now getting the news of a translation on the Cervantes language.

Truncating your transaction log.

First, HAPPY NEW YEAR GUYS!!

Now, some actual blogging...

Here I was, trying to restore our metadata only database into a production server, getting constantly an error messaging saying that it was waaay to big for the available space. Well, my concern was that the entire back up of the database was only 6MB, and the database itself only contains tables with very basic data.

What is worst, the space available on that production drive was 14GB, so gosh, it simply didn't make any sense. Well, after reviewing the original metadata-only database I realized that our constant adding, updating and deleting of structures made our transaction log simply huge. (40gb)

So, after some reading I found this nice recipe to cut down the fat of your log database:

First, backup your existing log file by running

BACKUP LOG  TO DISK = ''

Now, shrink the transaction log, executing this

DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

File name is the transaction log filename and target size you wanted to be, don't be too demanding on the shrinking, but as a hint, I ended up making mine only 1MB cause we don't need a log on our metadata db. :)


Update:
My apologies, the database in question is MSSQL 2005. Thanks for the feedback.

Help Update 1 for Delphi 2007 available.

Another great sign of constant improvement of my favorite IDE.

If you have not get an information dialog from your installation just go to Programs\Code Gear RAD Studio\Check for updates.

You can go here to read the install and Release notes, straight from CodeGear.

Migrating from ADO to dbExpress...

I finally started.

After holding the migration for a long time I am doing it, the reason? well, CodeGear has a renew effort on working on dbExpress, and its latest version dbExpress 4 is a great example of it, with good performance, great connectivity with WIN32 and .Net, support for BlackFish SQL, code included and completely rewritten in Delphi, and lots of improvements on D2007 to work with it like a new SQL Query Editor.

My initial testings always showed a better performance with dbExpress than ADO, now add the fact that ADO is being discontinued by MS in benefit of ADO.Net, the choice was a lot easier.

But, what I did to migrate thousands and thousands of components? Well it wasn't easy, there was no way I could do it in the form designer, it will simply take years.

So just go, open your Data Module, switch to code view, and Find/Replace the following:

1. Replace Connection with SQLConnection.
2. Replace TADOQuery for TSQLQuery.
3. Replace TADOStoredProc for TSQLStoredProc.
4. Replace TADODataSet for TSQLDataSet.
5. Parameters for Params.
6. CmdStoredProc for ctStoredProc.
7. Direction for ParamType.
8. pdOutput for ptOutput.
9. pdInput for ptInput.
10. Add under every dataset that uses SQLConnection, Schema = "dbo" or whatever your schema is.
11. Remove every parameter item called "@RETURN_VALUE".
12. Replace TDateTimeField for TSQLTimeStampField.


Geesh, thats what I remember, I will add more to the list if I hit more rocks on the road.

Consolas fonts.

Well, I was reading the CodeGear's non-technical newsgroups, and found an interesting conversation regarding what kind of font you use on your development IDE, among the recommendations there was the new Consolas Font from Microsoft. Well, I surrender to temptation, downloaded the font, set it up on my RAD Studio 2007 and expected to see something cool in front of me, but.... nothing happened, tried several times, and nothing happened, the IDE didn't recognize the font, nothing changed.

Luckily I did my googling and found out this nice tool, that avoided me the trouble to attempt to configure the font from the command prompt .

Well, I did it, restarted my sexy IDE and yahoooo!! I have a nice sexy font. I must admit I like it, it will take a while to feel fully comfortable with it, but i think is here to stay.

Try it! you may like it too.

Restarting your application.

We are creating a project to automatically update our different modules. That usually implies an application restart, lots of ideas went around, a service that does that, a batch file, or another program working as a proxy of the original one, but, hey, I found this G R E A T article from Zarko Gajic who is always giving amazing tips with Delphi on how to do it in a VERY easy way.


Enjoy!

The overload directive.

Well, it is fairly common to use the Delphi's overload directive to create a method with different sets of parameters in an object.

What I wasn't aware is that it actually works on functions or procedures that are not part of an object. That is cool.

RemObjects and DataSnap.

I'm using a mix of Delphi's RemObjects SDK and DataSnap on our middletier, and we have implemented session management on all the services and in all the DataSnap remote modules.

Thanks to RO, that can be easily done on the DataSnap modules by turning on the RequiresSession property of the RODataSnapModule. Now the only problem with this is during design time, once it is on you will not be allowed to connect to your DataSnap datasetproviders without first logging in. So, as you can read that is a problem.

The solution? We added a command line parameter to our application server that simply controls if we want to require a session or not in our services or datamodules. Once that flag is set, we just use it as an indication while we are creating the service/datamodule.

Soon, I will post the results of a LETHAL combination, KbmMW, RO and DataSnap in a single SUPER POWERFUL MIDDLETIER MILKSHAKE.

Developer Day en Español!!

Hi guys,

CodeGear is hosting "Developer Days en Español" a nice gathering of great developers with the spanish speaking Delphi community.

There is a wide variety of sessions including Delphi, Interbase, PHP and Java.

It is only for 2 days, today was the first session and it was great. An average of 300+ guys were listening online to the different sessions, in my opinion a great success.

So if you want to check it out, this is the website.

Enjoy!