Category: sql

MS SQL Server and other SQL related posts

Setting up a MS SQL Server FireDAC connection in code

Setting up a MS SQL Server FireDAC connection in code

In case you haven’t set up a connection in code yet: 

    FireDriverLink := TFDPhysMSSQLDriverLink.Create(nil);

    

    FConnection := TFDConnection.Create(nil);

    FConnection.DriverName := FireDriverLink.BaseDriverId;

    // http://docwiki.embarcadero.com/RADStudio/XE7/en/Connect_to_Microsoft_SQL_Server_(FireDAC)

    FConnection.Params.Values[‘Server’] := Trim(HostName);

    FConnection.Params.Values[‘Database’] := Trim(DatabaseName);

    FConnection.Params.Values[‘OSAuthent’] := OSAuthent;  // ‘Yes’ for Windows user, ‘No’ for SQL user name 

    FConnection.Params.Values[‘User_Name’] := Trim(UserName);

    FConnection.Params.Values[‘Password’] := Trim(Password);

    FConnection.Params.Values[‘ApplicationName’] := ‘YourApplicationInfoHere’;

    FConnection.LoginPrompt := False;  // Suppresses dialogs

    FConnection.Connected := True;

   From this point, you create the kind of db object (Query, StoredProc, etc.) you need.

In addition, you need to put this (or a subset) in the uses section.

Data.DB,  FireDAC.Stan.Intf, FireDAC.Stan.Consts, FireDAC.Phys.ODBCBase, FireDAC.Phys.MSSQL,

{$ifdef Console}

  FireDAC.ConsoleUI.Wait,

{$else}

  FireDAC.UI.Intf, FireDAC.VCLUI.Wait, FireDAC.Comp.UI,

{$endif}

  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.Phys.Intf, FireDAC.Stan.Def,

  FireDAC.Stan.Pool,  FireDAC.Stan.Async, FireDAC.Phys,

  FireDAC.Stan.Param, FireDAC.DApt, FireDAC.comp.DataSet, FireDAC.comp.Client;

FYI, the SQL Server Native Client install kits are available from MS and will be a lot faster than the default drivers.  

You’ll want the newest version available (such as 2012), even if the SQL Server version you want to connect to, is older.

You can download the 2012 version from here: http://www.microsoft.com/en-us/download/details.aspx?id=29065

See under: Install Instructions, 

MICROSOFT SQL SERVER CONNECTIVITY FEATURE PACK COMPONENTS

Microsoft® SQL Server® 2012 Native Client

Microsoft SQL Server Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2012 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server code name ‘Denali’ features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

X86 Package (sqlncli.msi)

X64 Package (sqlncli.msi)

You don’t need the SDK bit.

To do silent installs, have a look at

http://msdn.microsoft.com/en-us/library/ms131321.aspx

 

http://msdn.microsoft.com/en-us/library/ms131321.aspx

Migrating from ADO to FireDAC – MS SQL Server

Migrating from ADO to FireDAC – MS SQL Server

Yet another tidbit.  Default stored proc parameter sizing.

The old parameter passing to the stored procedures is done with array of const and variants.  No issues with that, until my xml logging feature had the mother of all stack traces, and exceeded 9k bytes in total xml size, upon which the assignment of TFDParam.Value complained that TFDParam.Size was 8002 bytes, and the size of the string was bigger than that.

vtUnicodeString:

begin

   p.DataType := ftString;

   p.Value    := String(ConstParams[ix].VUnicodeString); //Size barf

end;

Quick and dirty fix… For the future, I intend to lose the array of const and move to more explicit parameter encoding.

vtUnicodeString:

begin

   p.DataType := ftString;

   s := String(ConstParams[ix].VUnicodeString);

   len := Length(s) * SizeOf(Char);

   if Len > p.Size  // Autosize for XML

   then p.Size := Len + 2;

   p.Value    := s;

end;

 

FireDAC – MSSQL – Stored Proc context details?

FireDAC – MSSQL – Stored Proc context details?

Without adding parameters to the stored procedure, is it possible to pass / push more process context info than what MSSQL already has in sp_who? http://msdn.microsoft.com/en-us/library/ms174313.aspx

In particular: client app processid and thread id

Any optional connection parameter or similar?

   

http://msdn.microsoft.com/en-us/library/ms174313.aspx

FireDAC and bi-directional stored procedure parameters in SQL Server.

FireDAC and bi-directional stored procedure parameters in SQL Server.  I am diving into FireDAC, from a ADO starting point, and there are differences.

The SQL Server stored procedure below has two bidirectional parameters Error and Id. Description is input only, and the the procedure returns an int (0 = success, non-0 = check Error)

Error may be set, and Id is either a value > 0 which will be used as key, or if it is 0 or NULL, an auto-incremented value is returned through Id.

My ADO code which so far has translated easily for queries, etc – is barfing on invalid parameters for the stored proc.

How do you properly set up the bidirectional parameters?

How do you retrieve the output parameter values?

How do you retrieve the result value?

A simple code example would be appreciated, as the docs for TFDParams and Stored Procedures are Carefully Refined Awesome Prose.

{code}

CREATE PROCEDURE [dbo].[p_upsert_Description]

   @ErrorMsg varchar(1000) output,

   @Id int output,

   @Description varchar(100)

AS

BEGIN

    DECLARE @aId int;

    SET @ErrorMsg= ”;

    SET @aId= 0;

    BEGIN TRY

        SELECT @aId= Id FROM t_descriptions WHERE Id=@Id;

        IF @aId> 0

        BEGIN

            UPDATE t_descriptions

            SET Description=@Description

            WHERE Id =@aId;

            SELECT @Id=@aId;

        END

        ELSE

        BEGIN

            DECLARE @TmpIdTable table(ID int)

            INSERT INTO t_descriptions (

                CreatedTime,

                Description

            )  OUTPUT inserted.ID into @TmpIdTable

            VALUES (

                SYSDATETIME(),

               @Description

            );

            SELECT TOP 1 @Id= ID  FROM @TmpIdTable

        END

    END TRY

    BEGIN CATCH

        SELECT @ErrorMsg= ERROR_MESSAGE();

        RETURN SELECT ERROR_NUMBER();

    END CATCH

    RETURN 0;

END

{code}

Not strictly Delphi, but useful for those that have to do diagnostics on SQL tables.

Not strictly Delphi, but useful for those that have to do diagnostics on SQL tables.

Originally shared by Lars Fosdal

Finding fields with a specific value in any table

(SQL Server)

Trying to find some mixed up data references, I wrote this little snippet to check for any field named “something” in any table, where the value =”value”.

Example 1

Find rows with fields ending in ‘id’ and value equals 6653

exec [p_UTIL_FindFields]@Name= N’%id’,@Value= N’6653′ 

gives an output like

[dbo].[p_UTIL_FindFields]@Name= N’%id’,@Value= N’6653′

Finding Fields with names like %id where value = 6653

select @countOut= count() from t_purchase_order_line_mvx_data where Id =  6653 returns 1

select @countOut= count() from t_purchase_order_tpack_reportings where Id =  6653 returns 1

select @countOut= count() from t_purchase_order_tpack_reportings where PurchaseOrderTPackLnkId =  6653 returns 2

select @countOut= count() from t_tpack_logs where Id =  6653 returns 1

select @countOut= count() from t_tpack_logs where TPackId =  6653 returns 4

select @countOut= count() from t_tpack_logs where DeliveryId =  6653 returns 14

select @countOut= count() from t_customer_order_delivery_shipping_packages where Id =  6653 returns 1

select @countOut= count() from t_customer_order_delivery_shipping_packages where CustomerOrderDeliveryId =  6653 returns 12

select @countOut= count() from t_tpack_move_orders where ToStoragePositionId =  6653 returns 1

select @countOut= count() from t_tpack_move_orders where OriginalToStoragePositionId =  6653 returns 1

select @countOut= count() from t_lot_tpack_logs where Id =  6653 returns 1

select @countOut= count() from t_lot_tpack_logs where LotTPackId =  6653 returns 1

select @countOut= count() from t_lot_tpack_logs where LotId =  6653 returns 8

select @countOut= count() from t_lot_tpack_logs where TPackId =  6653 returns 2

select @countOut= count() from t_lot_logs where LotId =  6653 returns 7

select @countOut= count() from t_tpack_purchaseorder_imports where Id =  6653 returns 1

select @countOut= count() from t_tpacks_to_production where Id =  6653 returns 1

Checked 435 table and column combos with 7 errors

Example 2

Find rows with fields containing ‘storage’ in their name, and value equals 6553

exec [p_UTIL_FindFields]@Name= N’%storage%’,@Value= N’6653′

gives an output like

[p_UTIL_FindFields]@Name= N’%storage%’,@Value= N’6653′

Finding Fields with names like %storage% where value = 6653

select @countOut= count() from t_tpack_move_orders where ToStoragePositionId =  6653 returns 1

select @countOut= count(*) from t_tpack_move_orders where OriginalToStoragePositionId =  6653 returns 1

Checked 64 table and column combos with 3 errors

http://pastebin.com/VujT0iGt