Map rules for MS SQL 2005 and up

Hi,

We started migrating the projects from Delphi XE and dbXpress to Delphi
XE7 and FireDAC. All software we have made and are using SQL server are
connecting to MS SQL server 2005 and later (most common 2008 and
2008R2, some also 2012).

I am browsing the internet and newsgroups on tips how to translate
fields and parameters for FireDAC. Our biggest application is somehow
working okay when reading from database but I am having some problems
writing to the database using the stored procedures. We have quite some
stored procedures on the database for reading the data and storing.

So I came across a strange problem. I have a stored procedure with
parameters that are declared as integer and output. When FireDac want's
to write data there, it declares them not as integer but as type
decimal(), even with different scale and precision. In MS SQL
declaration is as follows:

ALTER PROCEDURE [dbo].[spPacientShrani]
   @IDLokacije      int, 
   @IDPacienta      int OUTPUT,
   @IDDruzine       int,
   @IDSkrbnika      int,
   @StevilkaZZ      varchar(9),
   @Ime             varchar(72), 
   @Priimek         varchar(72), 
   @DatumRojstva    datetime,
   @Spol            smallint,
   @EMSO            varchar(13),
   @Umrl            datetime,
   @SifraListine    int,
   @DatumListine    datetime,
   @IdentNosilca    varchar(100),
   @IdentKartice    varchar(100),
   
   @UlicaStal       varchar(50), 
   @PostaStal       varchar(6), 
   @KrajStal        varchar(50), 
   @SifraObcineStal varchar(3), 
   @SifraDrzaveStal varchar(4), 
   @TujaPostaStal   varchar(50), 
   @UlicaZac        varchar(50), 
   @PostaZac        varchar(6), 
   @KrajZac         varchar(50),
   @SifraObcineZac  varchar(3), 
   @SifraDrzaveZac  varchar(4), 
   @TujaPostaZac    varchar(50), 
   
   @TelefonDoma     varchar(30),
   @TelefonSluzba   varchar(30),
   @GSM             varchar(30),
   @EmailOsebni     varchar(100),
   @EmailSluzba     varchar(100),
   @Email3          varchar(100),
   @DavcnaStevilka  varchar(40),
   @VIP             int,
   @CAVE            varchar(100),
   @Komentar        varchar(100),
   
   @Opombe          text,
   @Slika           image,
   @rows            int OUTPUT
AS
BEGIN 
....


And FireDac send this to MS SQL (catched with MS SQL Profiler):
declare @p2 decimal(1,0)
set @p2=-1
declare @p40 decimal(30,6)
set @p40=NULL
exec [MEDICINA3].[dbo].[spPacientShrani] 0.00,@p2
output,NULL,NULL,NULL,'A','A','2015-08-12
00:00:00',1,'1208015',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,'A','1000','LJUBLJANA','061','705',NULL,NULL,NULL,NULL,NULL,NULL,NU
LL,NULL,NULL,NULL,NULL,'',NULL,@p40 output
select @p2, @p40

Why @P2 (=@IDPacienta) and @p40 (=@rows) are declared as decimal not
int? Why the difference in precision and scale? If I look at the
TFDStoredProc in software both paramters are properly defined as
integer.

Here are my map rules:
    with FormatOptions do
    begin
      OwnMapRules := True;
      with MapRules.Add do
      begin
        //NameMask     := 'BCD->Int16';
        SourceDataType := dtBCD;
        PrecMax        := 4;
        PrecMin        := 0;
        ScaleMax       := 0;
        ScaleMin       := 0;
        TargetDataType := dtInt16; // SmallInt
      end;
      with MapRules.Add do
      begin
        //NameMask     := 'BCD->Int32';
        SourceDataType := dtBCD;
        PrecMax        := 9;
        PrecMin        := 5;
        ScaleMax       := 0;
        ScaleMin       := 0;
        TargetDataType := dtInt32; // Integer
      end;
      with MapRules.Add do
      begin
        //NameMask     := 'BCD->Double';
        SourceDataType := dtBCD;
        PrecMax        := 18;
        PrecMin        := 9;
        ScaleMax       := 18;
        ScaleMin       := 1;
        TargetDataType := dtDouble; // Float
      end;
      with MapRules.Add do
      begin
        //NameMask     := 'Time->DateTime';
        SourceDataType := dtTime;
        PrecMax        := -1;
        PrecMin        := -1;
        ScaleMax       := -1;
        ScaleMin       := -1;
        TargetDataType := dtDateTime;
      end;
      with MapRules.Add do
      begin
        //NameMask     := 'Date->DateTime';
        SourceDataType := dtDate;
        PrecMax        := -1;
        PrecMin        := -1;
        ScaleMax       := -1;
        ScaleMin       := -1;
        TargetDataType := dtDateTime;
      end;
      with MapRules.Add do
      begin
        //NameMask     := 'DateTime->DateTime';
        SourceDataType := dtDateTime;
        PrecMax        := -1;
        PrecMin        := -1;
        ScaleMax       := -1;
        ScaleMin       := -1;
        TargetDataType := dtDateTime;
      end;
    end;

And also environment info...
================================
Connection definition parameters
================================
Database=MediCina3
User_Name=sa
Password=*****
Server=AX-MEDINF\SQL2012
MetaDefSchema=dbo
DriverID=MSSQL
================================
FireDAC info
================================
Tool = RAD Studio XE7
FireDAC = 11.0.1 (Build 73709)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
  Loading odbc32.dll driver manager
  Creating ODBC environment handle
  Searching for ODBC driver ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 11.0] ...
      Found [SQL Server Native Client 11.0]
Driver Manager version = 03.81.9200.0000
================================
Session info
================================
Checking session ...
  Warning: SQL NC 2012 and MS ODBC 11 fail to work with TVP.
Current catalog = 
Current schema = dbo
Driver name = sqlncli11.dll
Driver version = 11.00.2100
Driver conformance = 3
DBMS name = Microsoft SQL Server
DBMS version = 11.00.2218


Please help!

Best regards,
Goran
AUDAX d.o.o.
0
Goran
8/12/2015 8:35:15 AM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

1 Replies
1023 Views

Similar Articles

[PageSpeed] 49

FireDAC data type mapping rules work in both ways:
* fetching data: convert from source to target data type;
* posting data: convert from target to source data type.
So, the next rule will convert Int32 to BCD (decimal):

>       with MapRules.Add do
>       begin
>         //NameMask     := 'BCD->Int32';
>         SourceDataType := dtBCD;
>         PrecMax        := 9;
>         PrecMin        := 5;
>         ScaleMax       := 0;
>         ScaleMin       := 0;
>         TargetDataType := dtInt32; // Integer
>       end;

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
8/13/2015 5:29:01 AM
Reply:

Similar Artilces:

How to Upgrade MS-SQL 2005 Express Edition To MS-SQL 2005 Developers Edition
Hey, i am using VS2008 for development. I have already installed MS-SQL2005 Express Edition on my machine. I would like to upgrade it to Developer Edition. I have a few databases in Express Edition which i want to migrate to developer edition. So i was thinking if anyone could help me with upgrading my express edition to developer edition. I would highly appreciate it. ThanksJeff The simplest way is to just back your database(s) up, then restore them onto the Developer Edition instance of SQL Server....

How to create SQL Mobile 2005 database from a MS SQL 2005 database?
Hi guys,I'm trying to find solutions to help me in creating a SQL Mobile 2005 database from a SQL 2005 database, with the same table structure and data.May I know how can I achieve this?Thanks.Regards,Jenson Hi Jenson, Visual Studio 2005 or SQL Server 2005 is able to open SQL Mobile 2005 database. However you need either SQL or .NET code to convert SQL Mobile 2005 database this way. Here is an example from MSDN forums which is to convert to xls instead: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=78640&SiteID=1Zhao Ji MaSincerely,Microsoft Online Community Suppo...

how i will convert MS SQL Express Edition to MS SQL 2005
hi friend now i am using VS 2005 and MS SQL express edition as Database. i want to host the application. at server it will not support sql express edition. how i will convert .mdf file to MS SQL server 2005 please help me friends.abdul manzoor AOA Manzoor, There is no special requirement to convert MS SQL Express Database to SQL Server 2005 Database.. You can attach the SQL Express 2005 Database File (.mdf ) with the same database attachment procedure that we follow on SQL Server 2005 Database attachment , All you need is 1. Open the SQL Server 2005 Management Studio, Right Cl...

Problem with stored proc, migrating from MS SQL 2000 to MS SQL 2005
I've been migrating some DB from MS SQL 2000 to MS SQL 2005 all works fine but I have one problem with stored procedures. Even if there is actually no errors I get the message "The transaction ended in the trigger. The batch has been aborted.". The record is succesfully deleted but still I get the message above. I know MS SQL 2005 added the try/catch block did they change something else that cause compatibility issues with 2000? Here's a sample : *** DECLARE @Error bit DECLARE @ErrorMsg varchar (100) DECLARE @ContactIDToDelete int SELECT @ContactIDToDelete ...

install MS VISUAL STUDIO 2005 & MS SQL SERVER 2005 on Vista
Hi All, I just bought a new laptop with Vista Home Premium & I want to install the following :1- MS VISUAL STUDIO 2005  2- MS SQL SERVER 2005.Could any one help me to install it step by step. I'm still ont-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-alt:"Calisto MT"; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-alt:"Century Gothic"; mso-font-c...

Delphi 2010 dbexpress for MS SQL Server Express 2005
Hello, Has someone sucessfully configured a TSQLConnection with MS SQL Server Express 2005 with Delphi 2010? I get the following message: "DBX Error: Driver could not be properly initialized. Client library may be missing, not installed properly, or of the wrong version". I saw in the readme that the native client is now required: it is installed with SQL Express, and I have updated it I also updated the TSQLConnection as below: LibraryName = dbxmss9.dll instead of the default dbxmss.dll VendorLib = sqlncli.dll instead of the default sqlncli10.dll Those dll are available o...

MS SQL Server 2005 to MS Access
Hello...I have one big problem with this kit: my hoster doesn't provide a MS SQL database support so i can't even try this example. It is possible to convert SQL Server database (mdf) to MS Access database (mdb)??  It's not easy to migrate this particular kit over to Access, as it relies on stored procedures, which Access doesn't support.  That means you would have to re-write the DAL at the very least.  You can replicate the basic stored procs as saved queries, but anything that returns a value, or contains any type of control of flow code (such as if... els...

SQL Native Client, MS SQL 2005, connect
Did anybody sucesfully connect on MS SQL 2005 using SQL Native client? And how? lp, Matjaz I readed that MS SQL Server 2005 don�t accept API connections. Only OLE DB, ODBC ou ADO .NET. "Matjaz Justin" <matjaz.justin@3tav.si> escreveu na mensagem news:435ccf38@forums-1-dub... > Did anybody sucesfully connect on MS SQL 2005 using SQL Native client? And > how? > > > lp, Matjaz > I don't understand. SQL Native Client is from Microsoft. Read this http://blogs.msdn.com/dataaccess/archive/2005/04/26/412161.aspx lp, M...

Switch to SQL Anywhere from MS SQL Mobile/2005
Hello.... We currently have a system deployed that is using MS SQL Server 2005 and 100+ mobile devices (rugged PDA's running PPC2003) with SQL Mobile. We have merge replication set up between the devices and the server. For the past few months, ever since we moved to SQL 2005, we have experienced lots of issues, errors and the lack of stability. At this point, I need to research and review other solutions....and I am wondering if anyone has any thoughts on the effort/cost to move to SQL Anywhere from MS SQL. Any articles or other links would be welcome too. Thanks -...

Once again: SQL Native Client, MS SQL 2005, connect
Did anybody sucesfully connect on MS SQL 2005 using SQL Native client? And how? lp, Matjaz Using ole_db and ado.net both work. Ado.net has an issue on the same machine as the database. "Matjaz Justin" <matjaz.justin@3tav.si> wrote in message news:43c63717@forums-2-dub... > Did anybody sucesfully connect on MS SQL 2005 using SQL Native client? And > how? > > > lp, Matjaz > I recently started using SQL server 2k5.... you might want to check the sql server configuration manager options... named pipes and tcp/ip are disabled b...

MS SQL 2005 Locking problem with direct SQL
HI all I am using IDM 3.5.1 connecting to a MS 2005 SQL Database with the JDBC 1.2 Third party drivers. On the command trnsform channel I have the following rules. My problem is this. I can do UPDATE and INSERT commands BEFORE the current operation, i.e. I can add a record using the /nds/input/ instead of AFTER the current operation using ../ as in the rules below. However when I use th ../ as below it seems as if the table is being held open after the update (or insert), as you can not access it unles you restart the SQL server service, where the update and insert have...

SQL 2005 and SQL Express 2005
Hi all, I am sure it has been asked before, but i can't find the answer anywhere in the forum... I developed an application with SQL 2005 and .Net 2005 and i wanted to know how (if it is possible) to use that database in the SQL Express 2005 (for small customers). I have the database (tables, views and stored procedures) as it is done on the SQL 2005. If i will just attach the database in SQL Express 2005 it will work or should i do something else too? Thanks a lot. (Is it possible to install both SQL 2005 and SQL Express 2005 on the same PC? I already have SQL 2005 installed). Ye...

VS 2005 SQL 2005 Express and SQL 2005 Developer Edtion
I would like to know if there are any issues having VS2005 with SQL 2005 express and SQL 2005 Developer on the same computer.  I seem to have problem performing a Remote Connection to SQL 2005 Developer.  I  have enable the protocols and enable remote connection but I still have issues conneting. Any tips or help Thanks JulioJulio D....

FYI - Interesting difference between Oracle SQL and MS SQL
While converting some code from T-SQL to PL/SQL, I ran across an interesting bit of MS SQL code.  The code looked odd to me so I did a quick experiment to prove what it did.  I was quite surprised by the MS SQL results! In ORACLE: create table testunion (aaa number); insert into testunionselect 2 from dualunion select aaa * aaa from testunion;  select * from testunion;  -- Returns one record, as it should, "2" delete testunion; insert into testunionselect 2 from dualunion allselect aaa * aaa from testunion; select * from testunion; -- Returns one record, ...

Web resources about - Map rules for MS SQL 2005 and up - embarcadero.delphi.firedac

File:Aussie rules ground positions.svg - Wikipedia, the free encyclopedia
This file contains additional information, probably added from the digital camera or scanner used to create or digitize it.If the file has been ...

People's Daily editor Li Baoshan: SOEs obey rules
People's Daily editor Li Baoshan: SOEs obey rules The Australian Li Baoshan, the editor-in-chief of People's Daily , the official newspaper ...

Minecraft-like The Blockheads now lets you play by your own rules
... update. And as avid players of the game are sure to notice, this is the first update to arrive for The Blockheads in over a year. Custom rules ...

AT&T says it shelved a “bunch” of ideas because of net neutrality rules
... ) AT&T Senior VP Bob Quinn says his company has avoided offering some new services because of worries about violating net neutrality rules. ...

He rules their world
He rules their world by digby Drudge, of course. That's what Villager prince Mark Halperin once said of the political media.

Kansas City’s Royal Effort to Solarize City Rooftops – Episode 25 of Local Energy Rules
... leaders, and a unique opportunity that [&hellip Kansas City’s Royal Effort to Solarize City Rooftops – Episode 25 of Local Energy Rules was ...

The No. 1 rule for craft brewing
Here's Carol Roth's advice for the CEO of Lionheart Cider, a hard-cider maker started by a group of business-school students in Minnesota.

Democrats reject GOP bill to limit Syrian refugees, block Obama EPA rules
CNN International Democrats reject GOP bill to limit Syrian refugees, block Obama EPA rules CNN International Washington (CNN) Another congressional ...

McCain Calls For New Rules Of Warfare Against ISIS
Air strikes against ISIS will not be enough, says Arizona Sen. John McCain, the U.S. needs brand new rules of warfare. McCain’s remarks come ...

Katniss, ‘Creed’ & ‘Good Dinosaur’ Rule Thanksgiving, But Will ‘Star Wars’ Steal Christmas? – Monday ...
With the Thanksgiving theatrical business expanding by 11% in this year’s frame compared to last with $181.8 million thanks to The Hunger Games: ...

Resources last updated: 12/3/2015 3:50:21 PM