Connecting Perl on UNIX or Linux to Microsoft SQL Server Perl DBIDBDODBC Tutorial Part 3. This tutorial shows you how to access MS SQL Server databases from Perl. Directory protection using. htaccess and. htpasswd. This tutorial applies to Apache based web servers. It requires Editing the server configuration file httpd. conf. Biopython Tutorial and Cookbook Jeff Chang, Brad Chapman, Iddo Friedberg, Thomas Hamelryck, Michiel de Hoon, Peter Cock, Tiago Antao, Eric Talevich, Bartek Wilczy. Permission is granted to copy, distribute, andor modify this document under the terms of the Open Publication Licence, Version 1. 0, or any later version. Introduction to retrieving data from your database. Introduction to retrieving data from your database Perl DBIDBD ODBC Tutorial Part 2. Contents. Introduction This is part 2 of a series of Easysoft tutorials on using Perl DBI with DBD ODBC. Pre requisites. Before you start part 2 of this tutorial you need to ensure you have satisfy all the pre requisites Perl We used Perl 5. DBI and DBD ODBC modules which is currently 5. Use perl version to see what version of Perl you have installed. DBI module We used DBI 1. To see if you have a recent enough version of DBI installed run perl e use DBI 1. If you get an error like DBI version 1. DBI. If you get an error saying DBI cannot be found in INC youve probably not got DBI installed. Go to CPAN to get an up to date version of the DBI module. DBD ODBCWe used DBD ODBC 1. You can use similar methods as above to determine if DBD ODBC is installed and to see what version you have To check you have the DBD ODBC module installed perl e use DBD ODBC If you have not got DBD ODBC installed you should see Enabling ODBC support in Perl with Perl DBI and DBD ODBC for instructions. To show the DBD ODBC version perl MDBD ODBC e print DBD ODBC VERSION To show all drivers DBI knows about and their versions perl MDBI e DBI installedversions Go to CPAN to get an up to date version of the DBD ODBC module. ODBC driver and driver manager Unsurprisingly you will need an ODBC driver for most of this tutorial and we recommend you use an ODBC driver manager under DBD ODBC see Enabling ODBC support in Perl with Perl DBI and DBD ODBC. Easysoft can supply ODBC drivers for many databases and operating systems and all ODBC drivers come with the unix. ODBC driver manager. You probably have the unix. ODBC driver manager installed if you have the odbcinst command for ODBC drivers from Easysoft, the unix. ODBC driver manager is located in usrlocaleasysoftunix. ODBC and the odbcinst command in the bin sub directory of that path. We used unix. ODBC 2. You can find out the unix. ODBC version you are using with odbcinst version We also used the Easysoft ODBC ODBC Bridge as the ODBC driver to access a remote MS SQL Server database from UNIX. Assumptions. Previous tutorials in this series This tutorial assumes you have read or understand all the concepts in the previous tutorial DBD ODBC Tutorial Part 1 Drivers, Data Sources and Connection. Operating System This tutorial was designed on UNIX and we have assumed you are using UNIX too. However, all the Perl examples should work equally well on Windows so long as minor alterations for the command line are made. ODBC Driver Manager We have assumed you are using the unix. ODBC driver manager. All discussion in this document relating to the location and definition of ODBC data sources is for unix. ODBC. DBI and ODBC drivers. DBI, DBD ODBC Architecture. Perl script using DBI methods. DBD ODBC. ODBC Driver Manager e. ODBC. ODBC Driver Simple methods of retrieving data. Specifying what you want You specify the data you want to retrieve from the database using SQL select statements. This is not a SQL tutorial and we have attempted to keep the SQL simple. The basic form of a SQL select statement we use here is select lt columnlist from lt table where lt columnlist is a comma separated list of columns, or for all columns. In fact it can be many more things consult a SQL reference manual. You can qualify the rows you want back by adding a where clause like where lt column text Your SQL select statement is passed to the DBI prepare method which in ODBC terms ends up in a call to the SQLPrepare or SQLExec. Direct. ODBC APIs. Be careful when creating SQL from Perl variables as you can include characters which invalidate the SQL. Most databases would require the second to be doubled up. You can use the DBI quote method to do this for you with mynamedbh quotecant mysql qqselect from table where column name The other way recommended is to use bound parameters see Using parameters. Simple prepareexecute The most simple way of obtaining all the rows in a table and displaying them is like this mydbh DBI connect connectmysql qqselect from table the query to executemysthdbh preparesql prepare the querysth execute execute the querymy row whilerow sth fetchrowarray retrieve one row. Here we prepare the SQL, execute it, then use the fetchrowarray method to return an array of column values for each row. Repeatedly calling the fetchrowarray method until fetchrowarray returns undef, returns all the rows in the result set. NOTE that NULL fields are returned as undef in the array returned by fetchrowarray more about NULL values later. NOTE. Dont use fetchrowarray in a scalar context unless you really know what you are doing as it can return the first or last column dependent on the driver which could be a NULL value and hence undef. Also fetchrowarray can return undef if there are no more rows or if an error occurs. There are other methods or obtaining the result set see later. Obtaining metadata from the result set. ODBC can describe the result set for you listing the number of columns and the names of the columns. You can obtain this information with the statement attributes NUMOFFIELDS, NAME, NAMEuc, NAMElc, NAMEhash, NAMElchash and NAMEucHASH. Assuming you have the table fred with columns a integer, b char1. NUMOFFIELDS returns 3. NAME 0 returns a although it could returnAsth NAMEuc 0 returns A. NAMElc 0 returns a. NOTE the NAME attribute can return lowercase or uppercase column names depending on the database. Some database will always uppercase column names in create statements if they are unquoted and some databases retain the case of column names if they are enclosed in the identifier quote character. TIP For this reason it is best to use NAMEuc or NAMElc. NOTE. Some drivers may be unable to return a column name at all e. MS SQL Server returns an empty string for sth NAME 0. TIP You can get around this using column aliases as in select 1 as col. NAMEhash, NAMElchash and NAMEuchash are like NAME, NAMElc and NAMEuc except the result is a hash instead of an array with the keys being the column names and the values being the column index starting at 0. Similarly, the TYPE attribute returns an array reference of column types. For the fred table above print join, sth TYPE, n returns 4, 1, 6. The column types are defined by international standards see the DBI manual. Using parameters The main reasons for using parameters are You can prepare the SQL once then execute many times with different parameters thus saving the prepare parsing. With bound parameters you dont need to bother about quoting issues. Expanding the simple example in Simple prepareexecute to include a where statement we have mydbh DBI connect connectmyvardbh quotevalue to search for mysql qqselect from table where column var the query to executemysthdbh preparesql prepare the querysth execute execute the querymy row whilerow sth fetchrowarray retrieve one row. This is fine but what if we want to execute the query multiple times with different values for var. We can repeat the sql assignement with different var and re run dbh prepare but this is inefficient because it causes the ODBC driver and database to re parse the SQL each time and is unnecessary. A better solution is the following my row row datamydbh DBI connect connectmyvarvalue to search for mysql qqselect from table where column the query to execute with parametermysthdbh preparesql prepare the querysth executevar execute the query with parameterwhilerow sth fetchrowarray retrieve one row. Here the SQL contains a parameter marker the indicating to the driver that we will provide this later. The SQL is passed to the prepare method where the database will parse the SQL and note a parameter is required. When the execute method is called we pass the parameters required. You can use multiple parameter markers e. PERL Tutorial for Beginners Complete Guide. What is Perl Perl stands in for Practical Extraction and Reporting Language even though there is no authorized acronym for Perl. You may create your own acronym, and no one will mind. Perl was created by Larry Wall in 1. AWK a programming language he was using for the purpose was not helping him much. He is still the chief architect and developer of Perl. If we want to define Perl in one sentence Perl is a high level, interpreted, dynamic programming language. Did it all sound Greek to youUnless you actually know Greek. Perl is a programming language specially designed for text editing. It is now widely used for a variety of purposes including Linux system administration, network programming, web development, etc. Lets put it in a simple manner. While computers understand just 0s and 1s binary languagemachine language low level language, it is very difficult to program in a binary language for us human. Perl is a programming language which uses natural language elements, words that are used in common English language and is, therefore, easier to understand by humans high level language. Now theres a problem computers cannot understand high level languages, which we humans can easily understand. For that, we need something which can translate the high level language to low level language. Here interpreter comes to our help. The interpreter is a piece of software which converts the program written in the high level language to low level language for the computer to understand and execute the instructions written in the program. Hence, Perl is an interpreted programming language. In this tutorial, you will learn Where is Perl usedThe power of Perl can be implemented in many fields. The most popular use of Perl is in Web development., Perl is also used to automate many tasks in the Web servers, and other administration jobs, it can automatically generate emails and clean up systems. Perl is still used for its original purpose i. It can produce reports on resource use and check for security issues in a network. Due to this reason, Perl has become a popular language used in web development, networking and bioinformatics too. Apart from all this perl can also be used for CGI programming. Perl can also be utilized for image creation manipulation. Apart from that networking via telnet, FTP, etc., Graphical User Interface creation, VLSI electronics to create mail filters to reduce spamming practices are some use cases of Perl Perl is also known for implementation of OOPobject oriented programming practices and supports all forms of inheritance simple, multiple diamond, polymorphism and encapsulation. Perl is flexible enough to support Procedural as well as OOP practices simultaneously. Perl also has extra modules which permit you to write or usereuse code written in Python, PHP, PDL, TCL, Octave, Java, C, C, Basic, Ruby and Lua in your Perl script. This means that you can combine Perl with these extra programming languages rather rewriting existing code. Why use Perl It is true that there are other programming languages that can be used to do all the stuff that has been stated above, then why should you specifically use Perl Perl is very easy to learn, especially if you have a background in computer programming. Perl was designed to be easy for humans to write and understand rather than making it easy for processing by computers. It uses regular expressions. Its natural style of language is different from other programming languages that use specific grammar and syntaxes therefore, Perl is very flexible and doesnt impose on you any particular way of thinking out a solution or a problem. Perl is extremely portable. It can run on any operating system that has Perl interpreter installed, so it is platform independent. All Linux Operating Systems come installed with Perl, so you can start Perl coding in Linux out of the box. This is unlike Shell scripts, where the code changes with the flavor of Linux distribution being used, making it less and less portable Small specific tasks in Perl become very easy and quick. Throughout this tutorial, you will learn how you can code small, quick programs for specific tasks. Lets take a simple example of the classic Hello World program which is used to begin learning any programming language which has UNIX as its roots Example Perl hello worldusrbinperl. Hello, world Output Hello, world The above two lines of code will print Hello, worldNow wasnt it too simple and quick Students with knowledge of C, C will know that it requires many more lines of code to obtain the same output in those languages. You might be wondering why Perl is so famous on the Web. It is simple as most of the things that happen on the web are of TEXT and Perl is very good at text processing. If we compare Perl with any of the languages, then Perl will be the best language which is good in File handling, text processing, and output reporting One of the best advantages of Perl is that it is free to use. The Perl community strongly believes that software should be freely available, freely modifiable and freely distributable. Several volunteers from Perl community strive to make the programming language as good as possible. Pros Cons Compared to other Programming languages Perl is most powerful for text handling and Parsing. This is an interpreted language with fast execution time as there is no need to compile a Perl script. Simple and easy to program and understand. It is object oriented. CPAN library eases Perl development we will learn more about this later in the subject. Used in Web development for mostly Payment Gateways. Used in Automation and to test most of the Network and Storage related stuff. There is minimal GUI support as compared to other Programming languages. You need to refer to complex library modules which are not so easy to understand Dont be scared once you know how to do the things everything will be easy. Understanding complex Patterns requires experience. Lets get started. With enough knowledge about the history of Perl and basic concepts of computer programming required for coding in Perl, it is time we take the dive and get started with Perl. The next chapter will teach you how you can setup Perl on your system and get ready for the coding journey in Perl. The tutorial will take Linux as the OS which students will be using for coding in Perl. Download Install Perl Windows, Mac Linux. How to get Perl Good news you probably have itBut if you cant find it already on your system, you can still get it for free. To find out if you already have Perl installed, go into the command line and type perl v The command will display version of Perl if its installed. In this case, the version is v. But if not dont panic Updating Perl on Linux If you need to update the Perl version then just enter one single line of command sudo apt get install perl and relax. The rest will be taken care of. Just make sure you have an active internet connection. Install perl for Windows First, download the Active Perl from this link. Follow these steps to install Active. Perl on Windows system. See the below screenshots for the same. Step 1 Once you download the installer and start the installation you will see the below window, click on next to proceed. Step 2 Accept Licensing agreement to proceed the installation. Step 3 Below are different packages that will be installed. By default, all will be selected. The only thing different is PPM Perl Package Manager. This is the utility provided by Active Perl to install external Perl modules or libraries in your system. Click on Next to proceed. Step 4 These are different types of Perl extensions that can be used for Perl. Mostly we will be using. Pl,. Plx and. Pm for Perl. Perl modules basically use. Pm as their file extension to refer to a library file. Select all the options and click on the Next button. Step 5 Click on Install button to proceed with the installation. Step 6 Once installed, execute the command Perl v to check whether Perl is successfully installed in your system. There are lots of things which needs to be discussed for setting Perl environment in both Linux and Windows, as there wont be many library files included in this installation. You need to manually install those. You can install those manually using CPANComprehensive Perl Archive Network or either PPM which works only for perl windows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |