Expand your skills from the perspective of an RPG developer: Keep your IBM database close; keep your Microsoft database closer with Microsoft SQL Server.
As a writer for MC Press, I understand how the sentiments on Microsoft can be, and I just happen to be watching The Godfather while putting this article together, so I thought that keeping your friends close and your enemies closer would be a good opener for my new series on Microsoft products.
I've been out of the IBM arena for a few months, and I frequently have people reach out to me asking what's a good way to get started expanding their skill set to become more marketable. I've gone through a variety of changes in my professional career—from IBM i to Linux, Macintosh, and Microsoft. Currently, my focus is on Microsoft, and I'd like to share what I believe to be a good starting point using Microsoft tools, which would be Microsoft SQL Server.
I started out in the open-source genre, primarily because it was free, but also for the quality, innovation, and philosophy. Microsoft has been seeing the light somewhat in the area of open source (although not completely), and in that respect it is now very easy to download the Express versions of the development tools and have almost all of the capabilities of the full-blown products. And if you register your express version, you're not restricted to a 90-day trial period. You can develop your skills and create full projects while learning and then purchase the full product when you're ready to deploy it to production.
In this TechTip, I'll go through the installation process of the latest and greatest Microsoft SQL Server 2012 Express Edition. This is intended to be the first step in a series of additional articles that I would like to present as an introduction to Microsoft and other tools that could help to expand the skill set of IBM developers.
Download Microsoft SQL Server 2012 Express
You can download Microsoft SQL Server 2012 Express directly from Microsoft. If you have a 64-bit system, you download the 64-bit version. I downloaded the "Express with Advanced Services," which contains pretty much everything and is the one I'll be illustrating for this article.
Figure 1: This is the Microsoft SQL Server Express download Web page.
There are several versions and deployment methods available, which are described below the download list.
After downloading the desired version, you will run the executable. I'll walk you through some basic settings to get your local Microsoft SQL database operational.
Instance Configuration
The first configuration you'll need to make is the instance configuration. You could use the default installation, and that would work just fine, but when you start having multiple installations it's better to have your instances named to avoid any confusion. Just click on the Named Instance radio button and enter the name you would like.
Figure 2: For instance configuration, enter the name you want to use.
Server Configuration
For the server configuration, just take the defaults for the version you've downloaded.
Figure 3: Follow the defaults for server configuration.
Database Engine Configuration
The default for Authentication Mode is Windows authentication mode, and this is usually the safest way to go. But for development purposes, I recommend using Mixed Mode. That way, you have more options when experimenting with your database development.
Figure 4: For database engine configuration, choose Mixed Mode.
Reporting Services Configuration
Because I've downloaded the "Express with Advanced Services" of Microsoft SQL Server, the Reporting Services are included with the installation. This step is optional, but why not get it ready now so you can follow along in a future article on reporting services?
Figure 5: Install and configure Reporting Services.
Setup Complete
Once you've followed the bouncing ball to the end with the next buttons and completed all your configurations, you should get a completing screen indicating your successful installation.
Figure 6: The install is complete!
Sign On
Upon completion of the installation, you will have SQL Server Management Studio available in you programs list. Click on that to sign on to your new database.
Figure 7: Sign on!
Squeaky Clean New Server
So, the installation is complete, and now you have a squeaky clean database server with no tables or data. It's ready to play with.
Figure 8: The Object Explorer shows what you have in the database.
Creating a Database
The first thing you'll do is create a database on your server that you can start putting tables into. To create a new database, right-click on Databases and select New Database. You'll then be provided with a window to specify the name of the database to create. Simply enter a name and click Enter.
Figure 9: Create a new database.
Creating a Table
With the database created, you can now create a table within the database. You could write some DDL to create your table, but let's continue through the GUI screens by opening the navigation tree for your new database. Right-click on Tables and select New Table.
For this example, you'll create a simple user table that will contain a unique key, first name, last name, and middle initial as follows:
Figure 10: Create a new table.
You can specify the table name in the Properties window on the right. Once you have specified all of the fields that you'd like to create, save your table with CTRL+S. Then, refresh the tables list to see your new table.
Query Windows
You finally have a table! Now you can run queries on the table to insert some data and do a Select statement on it. To open a window, use CTRL+N or click on the New Query button.
Figure 11: Run a query.
Execute the following statements to insert a record into your newly created jr_user table. Then run a Select statement to see your new data with its unique key automatically generated:
insert into dbo.jr_user(jr_user_fname, jr_user_lname, jr_user_mi)
values('Don', 'Corleone', 'V')
select * from dbo.jr_user
Creating DDL from Your Table
Of course, when you're done creating your table, you'll want to have the DDL from your table so that you can use it for deployment into other environments (or simply to have a backup if anything should ever happen to your server). Getting the DDL is very easy. Simply right-click on your table and then select Script Table as -> CREATE To -> New Query Editor Window.
Figure 12: Create DDL from an existing table.
Upon doing this, you will now see your DDL in a new query window.
Figure 13: Your DDL is created in the query window.
Now you can take your DDL code and check it into your source code repository to be saved, stored, audited, and shared with other developers. I wrote an article about git that can get you started in this direction if you're interested: "Use Git to Document and Manage Any Source Code with Version Control."
"Leave the Gun, Take the Cannoli"
I've delivered my first TechTip in the Microsoft series, and I hope that you found something of interest to take away from it. In future TechTips, I intend to cross over into areas of open-source that may be of interest to you.
For those of you who are curious to check out the open-source database route with MySQL, Jan Jorgensen wrote an informative article called "TechTip: MySQL and PHP Are a Perfect Match, Part I" a few years back that does just that.
Also in the open-source arena, I wrote "Library Lists, Schemas, and Other Properties in SQuirreL SQL Client," which contains a few articles using SQuirreL, the open-source SQL client with DB2, that may be of interest.
SQL is SQL, so it makes no big difference what database you use. Of course, there are nuances between the different databases, but it won't take you long to pick them up. Exposure to the different databases will make it easier to learn more about a variety of languages and platforms, so why not get the basics out of the way to make your exploration into other technologies more enjoyable? As you'll find when you get into the wide varieties of technologies, it's a common goal to be database-agnostic anyway, but it doesn't hurt to know the mechanics and get familiar with them.
Download the Code
You can download the code for this article here.
Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.
Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.
Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.
Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.
MC Press books written by Thomas Snyder available now on the MC Press Bookstore.
 |
||
 |
|
Advanced, Integrated RPG See how to take advantage of the latest technologies from within existing RPG applications. List Price $79.95 Now On Sale
|
|
||
 |
|
Extract, Transform, and Load with SQL Server Integration Services Learn how to implement Microsoft’s SQL Server Integration Services for business applications. List Price $79.95
Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online