SQL Server - How to capture database errors

One very useful addition to a database I have found recently is to capture database errors in a try...catch block and storing the information in a table. Here is what I did:

Table to store the error information:

CREATE TABLE dbo.DBErrors (
  DBErrorID INT IDENTITY
 ,Raised DATETIME NULL CONSTRAINT DF_DBErrors_Raised DEFAULT (GETDATE())
 ,Number INT NULL
 ,Severity INT NULL
 ,StateNumber INT NULL
 ,StoredProcedure NVARCHAR(128) NULL
 ,Line INT NULL
 ,ErrorMessage NVARCHAR(4000) NULL
 ,CONSTRAINT PK_DBErrors PRIMARY KEY CLUSTERED (DBErrorID)
) ON [PRIMARY]

Then I wrote a stored procedure to write to the table:

CREATE PROCEDURE dbo.DBErrorInsert
AS
BEGIN
DECLARE @DBErrorsExist bit;
  SET NOCOUNT ON;
  
  SELECT TOP (1) @DBErrorsExist = TableExists FROM dbo.efn_TableExists('DBErrors');
IF (@DBErrorsExist = 1)
BEGIN
INSERT INTO DBErrors (Number, Severity, StateNumber, StoredProcedure, Line,                ErrorMessage) VALUES ( 
ERROR_NUMBER(),  
ERROR_SEVERITY(),  
ERROR_STATE(),  
ERROR_PROCEDURE(),  
ERROR_LINE(),  
ERROR_MESSAGE());
    RETURN -2;
END
  ELSE
  BEGIN
    RETURN -1;
  END;
END;

Then where I wanted to capture any errors i.e. in a stored procedure I did the following:

BEGIN TRY
  ... Stored proc code
END TRY
BEGIN CATCH
  EXEC dbo.DBErrorInsert;
END CATCH

When an error occurs in the try...catch block it writes the error information including the line the error was triggered to a record in the table.

SQL Server - List all fixed length fields in a database

I noticed that a field in a database had been set to a nchar(), this means it's a fixed length and in a result query had trailing spaces. The 2 options I had were to change the field type to nvarchar() or add a RTRIM() to the field and alias it. To identify any other fields that were like this I ran the following query:

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME, 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH AS Defined_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char', 'nchar')
ORDER BY TABLE_NAME;

Mouse Wheel and TScrollBox

We had an issue where there was a TScrollbox on a form and the mouse wheel would not do the vertical scroll. The simplest solution I found for this was to do the following in the forms 'OnMouseWheel' event.

if MyScrollBox.BoundsRect.Contains(MyScrollBox.Parent.ScreenToClient(MousePos)) then
begin
  MyScrollBox.VertScrollBar.Position := MyScrollBox.VertScrollBar.Position -                   WheelDelta;
  Handled := True;
end; 

SQL Server - Query to return list of tables and the space they use on disk

Below is a useful MS SQL Server query I use to get the list of database tables and information on how much disk space they use. The results are ordered by the size on disk descending.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8.0) / 1024), 2) AS DECIMAL(18,2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8.0) / 1024), 2) AS DECIMAL(18,2)) AS UsedSpaceMB,
    CAST(ROUND(((SUM(a.data_pages) * 8.0) / 1024), 2) AS DECIMAL(18,2)) AS DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC;

When to pass a dependency to a class?

I was speaking to another developer the other day and he insisted that if a class needs an instance of another class it should always be passed in an 'Init' method and not in the constructor. I disagreed with him on this and thought I should clarify when I think a dependency should be passed.

There are 2 options when passing a dependency to a class:

1. Passing it as a parameter in the Create constructor.
2. Passing it in a separate 'Init' method after creation. It does not have to be called 'Init'. 

When to use the 'Create' method and its advantages:

• When the class absolutely needs the other class to operate properly.
• Ensures the object is in a valid state immediately after creation.
• Prevents accidental use of an uninitialized object.
• Simplicity, it can lead to cleaner and more concise code.

When to use an 'Init' method and its advantages:

• When the dependency could be seen as optional. The class can operate or partly operate without the other class. This can be done with the Create method, but passing it as nil.
• Need delayed initialization.   

In most cases, the Create method (constructor) approach is preferred for better safety and reliability.

Should you write a website using Delphi?

Over the years I've written various websites some of which used Delphi to publish the pages. This method used HTML templates, JS and CSS files and the data from the database to stitched them together to produce the pages. The question is if I was to write a website from scratch would I still use this method or would I look at using a Javascript framework like ReactJS, Angular or Svelte? 

I'm sure the answer to this question would depend on the spec for the website, but here are some advantages and disadvantages of writing a website using Delphi.

Advantages:

• If you have existing applications written in Delphi you can use existing code like common functions, classes and units that relate to your business, so there is no need to rewrite code in another language.
• Time to develop the site could be shorter than using a framework like ReactJS, especially if you need to learn a new language.
• Possible to implement JS libraries like Bootstrap.
• Have front and backend code in the same project. Have a single service that is responsible for retrieving data from the database then using that data to produce the front end page.

Disadvantages:

• You still have to write code in HTML, JS and CSS.
• Compared to popular web development languages, Delphi lacks a vibrant ecosystem of web-specific tools, libraries, and frameworks.
• Testing changes while developing is slow compared to other languages like ReactJS, which automatically refreshes the page you are viewing when you save a change.
• Modern web development frameworks often come with built-in security measures (e.g., CSRF protection, SQL injection prevention). In Delphi, many of these must be implemented manually, increasing the risk of vulnerabilities.
• Delphi's future as a web development platform is uncertain compared to the robust growth of modern web development languages.
• Difficult to find Delphi developers, especially ones with web site development experience.
• A lack of beginner-friendly tutorials, guides, and resources for web development in Delphi can make onboarding new developers more difficult.

In Conclusion:

While Delphi has its strengths, its disadvantages for web development primarily revolve around the lack of modern web development focus, limited developer availability, higher costs, and the complexity of implementing web-specific features. For teams already invested in Delphi or needing tight integration with Delphi-based systems, it can be a viable option, but for new projects or those targeting modern web standards, other technologies may be more practical.

Below is a table from ChatGPT with some security vulnerabilities & considerations:

Should naming procedures Setup and Teardown be reserved for unit testing?

I recently came across some code a developer had done where they had named 2 public procedures 'Setup' and 'Teardown', when I fist saw these I instinctively thought of unit tests and thought that class might relate to unit testing. After looking into the code it was apparent that these procedures where meant to be called after an object of the class was created and before the object was freed, but had nothing to do with unit testing.

Even though there are no restrictions on using these words for procedures I do think it is best practice not to use them in production code and name them something different like 'InitializeResources' and 'CleanupResources'. I think keeping procedures 'Setup' and 'Teardown' specifically for testing maintains a clear distinction between testing and application logic, which can be beneficial for maintainability.