{"id":14,"date":"2008-01-15T11:40:29","date_gmt":"2008-01-15T19:40:29","guid":{"rendered":"http:\/\/www.installaware.com\/blog\/?p=14"},"modified":"2008-01-15T12:12:33","modified_gmt":"2008-01-15T20:12:33","slug":"online-serial-key-validation-with-installaware-the-database-part-final","status":"publish","type":"post","link":"https:\/\/www.installaware.com\/blog\/?p=14","title":{"rendered":"Online Serial Key Validation with InstallAware, the Database Part (final)"},"content":{"rendered":"

In this last installment of the serial validation series, we’re taking a look at the database structures used by the web side scripts. You’ll need to know some SQL-92 or T-SQL basics, if you don’t already please visit http:\/\/www.mysql.com\/<\/a> and www.microsoft.com\/sql\/<\/a> to get acquainted.<\/p>\n

First, some database design. We’ll keep serial data on one table and user data on another table.<\/p>\n

The serials table<\/strong> columns:
\nid (bigint) , productname (nvarchar), serial (nvarchar), activated (tinyint)<\/strong><\/p>\n

The users table <\/strong>columns:
\nid (bigint), firstname (nvarchar), lastname (nvarchar), username (nvarchar), password (nvarchar)<\/strong><\/p>\n

During validation we can use the SQL SELECT statement <\/span>to make sure the serial number has not been used before and has not been activated yet:
\nSELECT id FROM serials WHERE <\/span>serial=@serial<\/a> AND activated != 1<\/span><\/p>\n

This is a T-SQL compliant query, where @serial <\/span>is the parameter containing the serial number being queried. If this query doesn’t return a value, that’ll mean that the serial number specified is either invalid or has already been activated.<\/p>\n

Otherwise, we can go ahead and activate this serial number. The T-SQL statement for this activation is:
\nUPDATE serials SET activated=1 WHERE <\/span>serial=@serial<\/a><\/p>\n

Now, all that remains to do is to add an entry in the users table:
\nINSERT INTO users(firstname,lastname,username,password) VALUES(@firstname,@lastname,@username,@password);<\/strong><\/p>\n

My suggestion for you is to implement all of the above statements as separate stored procedures <\/strong>and not stand-alone “injected” queries lying around inside multiple web script files. It’s better that way since code maintenance overhead is reduced – just like replacing all separate Run Program <\/strong>statements with a common Label <\/strong>statement like we did earlier for the MSIcode update script.<\/p>\n

So as you can see, in a few simple lines, we’ve implemented the business logic\/workflow of our application, in an integrated fashion with InstallAware<\/strong>. You’ve probably noticed that there isn’t any directly InstallAware<\/strong> related content in this post, but we’ve had a lot of requests for samples of web server back-end behavior for things like serial activation, so we hope this helps!<\/p>\n

Thank you and we’ll be back soon,<\/p>\n

Panagiotis Kefalidis
\nSoftware Design Team Lead
\nInstallAware<\/span> Software Corporation<\/p>\n","protected":false},"excerpt":{"rendered":"

In this last installment of the serial validation series, we’re taking a look at the database structures used by the web side scripts. You’ll need to know some SQL-92 or T-SQL basics, if you don’t already please visit http:\/\/www.mysql.com\/ and www.microsoft.com\/sql\/ to get acquainted. First, some database design. We’ll keep serial data on one table … <\/p>\n

Continue reading “Online Serial Key Validation with InstallAware, the Database Part (final)”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-14","post","type-post","status-publish","format-standard","hentry","category-news"],"_links":{"self":[{"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/14","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14"}],"version-history":[{"count":0,"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/14\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.installaware.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}