{"id":308,"date":"2010-05-25T12:27:24","date_gmt":"2010-05-25T20:27:24","guid":{"rendered":"http:\/\/www.andreanolanusse.com\/en\/?p=308"},"modified":"2011-02-02T03:10:14","modified_gmt":"2011-02-02T11:10:14","slug":"parameterized-queries-with-dbexpress-dbx-framework","status":"publish","type":"post","link":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/","title":{"rendered":"Parameterized queries with dbExpress (DBX) Framework"},"content":{"rendered":"

In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.<\/p>\n

Let start understanding the small difference between parameterized queries using DBX Framework and VCL. While VCL uses \u201c:PARAMETER\u201d to define query parameters, DBX Framework represent parameter as \u201c?\u201d, and it doesn\u2019t include name.<\/p>\n

We have been using VCL for a long time and are accustomed to read and write our code based on DataSet parameters. When you start using the DBX Framework you expect it to work in a very similar way, but the framework has some differences. The Delphi language allow us to extend the framework and it would became more VCL friendly.<\/p>\n

In case you use only what you have in the DBX Framework, your code will look like this:<\/p>\n

program DBX4Example;\r\n\r\n{$APPTYPE CONSOLE}\r\n\r\nuses\r\n  SysUtils,\r\n  DBXDynalink,\r\n  DBXCommon,\r\n  DBXInterbase;\r\n\r\nvar\r\n\r\n  aDBXConn: TDBXConnection;\r\n  aDBXTrans : TDBXTransaction;\r\n  aCmnd: TDBXCommand;\r\n  aReader: TDBXReader;\r\n\r\nbegin\r\n\r\n    aDBXConn := TDBXConnectionFactory.GetConnectionFactory.GetConnection('employee ib','sysdba','masterkey');\r\n\r\n    if aDBXConn <> nil then\r\n    begin\r\n\r\n      aCmnd := aDBXConn.CreateCommand;\r\n\r\n      \/\/ Start transaction\r\n      aDBXTrans:= aDBXConn.BeginTransaction(TDBXIsolations.ReadCommitted);\r\n\r\n      \/\/ Prepare and execute the SQL Statement\r\n      aCmnd.Text := 'SELECT * FROM Country  Where Country = ?';\r\n      aCmnd.Prepare;\r\n      aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');\r\n      aReader := aCmnd.ExecuteQuery;\r\n\r\n      while aReader.Next do\r\n      begin\r\n        Writeln( aReader.Value['Country'].GetAnsiString );\r\n      end;\r\n\r\n      \/\/ Commit transaction\r\n      aDBXConn.CommitFreeAndNil(aDBXTrans);\r\n\r\n      Readln;\r\n      aReader.Free;\r\n      aCmnd.Free;\r\n      aDbxConn.Free;\r\n    end;\r\nend.<\/pre>\n
Note that in the SQL we use question mark to define the parameter, prepare the query and follow the long line to define the parameter value at position zero.<\/p>\n
We can make this code more VCL friendly using Class Helpers, one of the new features implemented in Delphi 2006. It will allow us to extend the class without inheriting from it.<\/p>\n
So we\u2019ll extend the class TDBXParameterList, which will allow us to name the parameters and set their values by name.<\/p>\n
The follow code extend the class through class helpers<\/strong>, look this is not inherited. This extension define two new methods:
\nSetParametersName: this method will receive a list of names, which will be used to name the parameters, you must pass a number of names equal to the number of parameters;
\nByName: method that allows us to access parameters by name, the DBX Framework has the method GetOrdinal, it allow us to access parameters by name, but byName will be more VCL friendly;<\/p>\n
unit DBXParameterListHelper;\r\n\r\ninterface\r\n\r\nuses\r\n  DBXCommon, SysUtils;\r\n\r\ntype\r\n  TDBXParameterListHelper = class helper for TDBXParameterList\r\n\r\n  public\r\n    procedure SetParamatersName(aNames: Array of String);\r\n    function ByName( Name : String ) : TDBXWritableValue;\r\n  end;\r\n\r\nimplementation\r\n\r\n{ TDBXParameterListHelper }\r\n\r\nfunction TDBXParameterListHelper.ByName(Name: String): TDBXWritableValue;\r\nbegin\r\n  Result := Self.Parameter[Self.GetOrdinal(Name)].Value;\r\nend;\r\n\r\nprocedure TDBXParameterListHelper.SetParamatersName(aNames: array of String);\r\nvar\r\n  i: Integer;\r\nbegin\r\n\r\n  if Length(aNames) <> Self.Count then\r\n     raise Exception.Create('aNames should have the same number of parameters');\r\n\r\n  for i := 0 to Self.Count - 1 do\r\n  begin\r\n    Self[i].Name := aNames[i];\r\n  end;\r\nend;\r\n\r\nend.<\/pre>\n
Using the extension this part of the code:<\/p>\n
      \/\/ Prepare and execute the SQL Statement\r\n      aCmnd.Text := 'SELECT * FROM Country  Where Country = ?';\r\n      aCmnd.Prepare;\r\n      aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');<\/pre>\n
Will be replaced by this one.<\/p>\n
      \/\/ Prepare and execute the SQL Statement\r\n      aCmnd.Text := 'SELECT * FROM Country  Where Country = ?';\r\n      aCmnd.Prepare;\r\n      aCmnd.Parameters.SetParamatersName(['COUNTRYNAME']);\r\n      aCmnd.Parameters.ByName('COUNTRYNAME').SetAnsiString('USA');<\/pre>\n
In case your query has multiple parameteres, like this:<\/p>\n
      \/\/ Prepare and execute the SQL Statement\r\n      aCmnd.Text := 'SELECT * FROM Country  Where Country = ? and Currency = ?';\r\n      aCmnd.Prepare;\r\n      aCmnd.Parameters.SetParamatersName(['COUNTRYNAME', 'CURRENCY']);\r\n      aCmnd.Parameters.ByName('COUNTRYNAME').SetAnsiString('USA');\r\n      aCmnd.Parameters.ByName('CURRENCY').SetAnsiString('Dolar');<\/pre>\n
In closing, we’ve learned how to work with query parameters in DBX Framework and how to use class helper to extend our classes.<\/p>\n
Source code download, available here<\/a><\/strong>.<\/p>\n
\n
COUNTRYNAME<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post. Let start understanding the small difference between parameterized queries using DBX Framework and VCL. While VCL uses \u201c:PARAMETER\u201d to define query parameters, DBX Framework represent parameter as \u201c?\u201d, and it […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_s2mail":"","footnotes":""},"categories":[10],"tags":[34,90],"class_list":["post-308","post","type-post","status-publish","format-standard","hentry","category-delphi","tag-dbexpress","tag-delphi"],"yoast_head":"\nParameterized queries with dbExpress (DBX) Framework | Andreano Lanusse | Technology and Software Development<\/title>\n<meta name=\"description\" content=\"In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Parameterized queries with dbExpress (DBX) Framework | Andreano Lanusse | Technology and Software Development\" \/>\n<meta property=\"og:description\" content=\"In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\" \/>\n<meta property=\"og:site_name\" content=\"Andreano Lanusse | Technology and Software Development\" \/>\n<meta property=\"article:published_time\" content=\"2010-05-25T20:27:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-02-02T11:10:14+00:00\" \/>\n<meta name=\"author\" content=\"Andreano Lanusse\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Andreano Lanusse\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\"},\"author\":{\"name\":\"Andreano Lanusse\",\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\"},\"headline\":\"Parameterized queries with dbExpress (DBX) Framework\",\"datePublished\":\"2010-05-25T20:27:24+00:00\",\"dateModified\":\"2011-02-02T11:10:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\"},\"wordCount\":358,\"commentCount\":7,\"publisher\":{\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\"},\"keywords\":[\"dbExpress\",\"Delphi\"],\"articleSection\":[\"Delphi\"],\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\",\"url\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\",\"name\":\"Parameterized queries with dbExpress (DBX) Framework | Andreano Lanusse | Technology and Software Development\",\"isPartOf\":{\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#website\"},\"datePublished\":\"2010-05-25T20:27:24+00:00\",\"dateModified\":\"2011-02-02T11:10:14+00:00\",\"description\":\"In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.\",\"breadcrumb\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.andreanolanusse.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Parameterized queries with dbExpress (DBX) Framework\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#website\",\"url\":\"https:\/\/www.andreanolanusse.com\/en\/\",\"name\":\"Andreano Lanusse | Technology and Software Development\",\"description\":\"Where Andreano Lanusse talk about technology, software development, programming techniques, databases, games and more through articles, tutorials and videos\",\"publisher\":{\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.andreanolanusse.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\",\"name\":\"Andreano Lanusse\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g\",\"caption\":\"Andreano Lanusse\"},\"logo\":{\"@id\":\"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/\"},\"description\":\"Andreano Lanusse is an expert and enthusiastic on software development industry, at Embarcadero he is focused on helping to make sure the products being developed meet the expectations of Embarcadero's customers, as well as defining market strategies for Latin America. Today as Latin Lead Evangelist he spends great deal of time in developer conferences, tradeshows, user group, and visiting customers throughout Latin America. Before Embarcadero, he worked 13 years for Borland, Andreano has worked as Support Coordinator, Engineer, Product Manager, including Product Line Sales Manager, where was responsible to manage the relationship with Brazil developer community, also has worked as Principal Consultant for Borland Consulting Services on the development and management of critical applications. He previously served as Chief Architect for USS Solu\u00e7\u00f5es Gerenciadas (now USS Tempo). Andreano holds a bachelor's degree in Business Administration Marketing Emphasis from Sumare Institute, MBA in Project Management from FGV, certification in Microsoft products, all Borland ALM products, and all CodeGear product line.\",\"sameAs\":[\"http:\/\/www.andreanolanusse.com\",\"https:\/\/x.com\/andreanolanusse\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Parameterized queries with dbExpress (DBX) Framework | Andreano Lanusse | Technology and Software Development","description":"In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/","og_locale":"en_US","og_type":"article","og_title":"Parameterized queries with dbExpress (DBX) Framework | Andreano Lanusse | Technology and Software Development","og_description":"In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.","og_url":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/","og_site_name":"Andreano Lanusse | Technology and Software Development","article_published_time":"2010-05-25T20:27:24+00:00","article_modified_time":"2011-02-02T11:10:14+00:00","author":"Andreano Lanusse","twitter_misc":{"Written by":"Andreano Lanusse","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#article","isPartOf":{"@id":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/"},"author":{"name":"Andreano Lanusse","@id":"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b"},"headline":"Parameterized queries with dbExpress (DBX) Framework","datePublished":"2010-05-25T20:27:24+00:00","dateModified":"2011-02-02T11:10:14+00:00","mainEntityOfPage":{"@id":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/"},"wordCount":358,"commentCount":7,"publisher":{"@id":"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b"},"keywords":["dbExpress","Delphi"],"articleSection":["Delphi"],"inLanguage":"en","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/","url":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/","name":"Parameterized queries with dbExpress (DBX) Framework | Andreano Lanusse | Technology and Software Development","isPartOf":{"@id":"https:\/\/www.andreanolanusse.com\/en\/#website"},"datePublished":"2010-05-25T20:27:24+00:00","dateModified":"2011-02-02T11:10:14+00:00","description":"In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.","breadcrumb":{"@id":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.andreanolanusse.com\/en\/parameterized-queries-with-dbexpress-dbx-framework\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.andreanolanusse.com\/en\/"},{"@type":"ListItem","position":2,"name":"Parameterized queries with dbExpress (DBX) Framework"}]},{"@type":"WebSite","@id":"https:\/\/www.andreanolanusse.com\/en\/#website","url":"https:\/\/www.andreanolanusse.com\/en\/","name":"Andreano Lanusse | Technology and Software Development","description":"Where Andreano Lanusse talk about technology, software development, programming techniques, databases, games and more through articles, tutorials and videos","publisher":{"@id":"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.andreanolanusse.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en"},{"@type":["Person","Organization"],"@id":"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b","name":"Andreano Lanusse","image":{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g","caption":"Andreano Lanusse"},"logo":{"@id":"https:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/"},"description":"Andreano Lanusse is an expert and enthusiastic on software development industry, at Embarcadero he is focused on helping to make sure the products being developed meet the expectations of Embarcadero's customers, as well as defining market strategies for Latin America. Today as Latin Lead Evangelist he spends great deal of time in developer conferences, tradeshows, user group, and visiting customers throughout Latin America. Before Embarcadero, he worked 13 years for Borland, Andreano has worked as Support Coordinator, Engineer, Product Manager, including Product Line Sales Manager, where was responsible to manage the relationship with Brazil developer community, also has worked as Principal Consultant for Borland Consulting Services on the development and management of critical applications. He previously served as Chief Architect for USS Solu\u00e7\u00f5es Gerenciadas (now USS Tempo). Andreano holds a bachelor's degree in Business Administration Marketing Emphasis from Sumare Institute, MBA in Project Management from FGV, certification in Microsoft products, all Borland ALM products, and all CodeGear product line.","sameAs":["http:\/\/www.andreanolanusse.com","https:\/\/x.com\/andreanolanusse"]}]}},"_links":{"self":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/posts\/308","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/comments?post=308"}],"version-history":[{"count":0,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/posts\/308\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/media?parent=308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/categories?post=308"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/tags?post=308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}