Dynamic SQL: Use of Dynamic SQL Statements

Dynamic SQL, unlike embedded SQL statements, are built at the run time and placed in a string in a host variable. The created SQL statements are then sent to the DBMS for processing. Dynamic SQL is generally slower than statically embedded SQL as they require complete processing including access plan generation during the run time.

However, they are more powerful than embedded SQL as they allow run time application logic. The basic advantage of using dynamic embedded SQL is that we need not compile and test a new program for a new query.

Let us explain the use of dynamic SQL with the help of an example:

Example: Write a dynamic SQL interface that allows a student to get and modify permissible details about him/her. The student may ask for subset of information also. Assume that the student database has the following relations.

STUDENT (enrolno, name , dob)
RESULT (enrolno, coursecode, marks)

In the table above, a student has access rights for accessing information on his/her enrolment number, but s/he can’t update the data. Assume that user names are enrolment number.

/* declarations in SQL */

EXEC SQL BEGIN DECLARAE SECTION;
Char inputfields (50);
Char tablename (10);
Char sqlquery ystring (20);

EXEC SQL END DECLARE SECTION;
Printf(“Enter the fields you want to see \n”);
Scanf(“SELECT %s”, inputfields);
Printf(“Enter the name of table STUDENT or RESULT”);
Scanf(“FROM %s”, tablename);
Sqlqueryystring= “SELECT” +inputfields+””+”FROM” +tablename+ “WHERE enrolno + “USER”

/*Plus is used as a symbol for concatenation operator; in some DBMS it may be ||*/
/* Assumption: the user name is available in the host language variable USER */

EXEC SQL PREPARE sqlcommand FROM: sqlqueryystring;
EXEC SQL EXECUTE sqlcommand;

Please note the following points in the example above.

• The query can be entered completely as a string by the user or s/he can be suitably prompted.
• The query can be fabricated using a concatenation of strings. This is language dependent in the example and is not a portable feature in the present query.
• The query modification of the query is being done keeping security in mind.
• The query is prepared and executed using a suitable SQL EXEC commands.

What is an SEO friendly Website?

The term SEO friendly relates to search engine friendly. A website is called SEO/search engine friendly, if it is easily crawled and ranked by search engines. Search Engines give special preference to SEO friendly websites and these websites get good Search Engine Ranking Placements(SERP) on most competitive keywords with less efforts. A website becomes SEO friendly if it is properly optimized with SEO standards.

The most important SEO standards for making a website search engine friendly is to set a very informative Title of page based on the most targeted keyword(s) of the page. Title is the very basic but most important point for the on page optimization. One H1 tag will be used on one page and this h1 tag will be based on the most targeted/important keyword of that page.

Sitemap is very important for a website. There are basically two types of sitemaps that are required. One is HTML sitemap and other is XML sitemap. XML sitemap is very important and every website must have an XML sitemap. XML sitemap is one, from where search engines can easily crawl all the pages of a website.

Every image must have an alt tag associated with it and for every link, a specific title for the link should be given. Some search engines also check meta descriptions and keywords, so give appropriate meta description and keywords for every page.

More important, never use any hidden text or don’t try any black hat SEO techniques. All these will have some bad effects on website and Google never give preference to such sites. So, always apply some standard SEO techniques to make your website SEO friendly. If your website is search engine friendly then search engines will automatically give preference to your website.

Embedded SQL: Use of Embedded SQL Statements

The embedded SQL statements can be put in the application program written in C, Java or any other host language. These statements may sometimes be called static. The term ‘static’ is used to indicate that the embedded SQL commands, which are written in the host program, do not change automatically during the lifetime of the program. Thus, such queries are determined at the time of database application design. For example, a query statement embedded in C to determine the status of train booking for a train will not change. However, this query may be executed for many different trains. Please note that it will only change the input parameter to the query that is train-number, date of boarding, etc., and not the query itself.

Below is the example of embedded SQL where to write a C program segment that prints the details of a student whose enrolment number is input.

Student(enrolno:char(9), name:char(25), phone:integer(12), prg-code:char(3))
/* add proper include statements */
/* declaration in C program */
Exec SQL BEGIN DECLARE SECTION;
Char enrolno[10], name[26], p-code[4];
int phone;
int SQLCODE;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

/* The connection needs to be established with SQL */
/* Program segment for the required function */
printf(“enter the enrolment number of the student”);
scanf(“%s”, &enrolno);
EXEC SQL
SELECT name, phone, prog-code INTO
:name, :phone:, :p-code
FROM STUDENT
WHERE enrolno=:enrolno;
If(SQLCODE==0)
Printf(“%d, %s, %s”, enrolno, name, phone, p-code)
Else
Printf(“Wrong Enrolment Number”);

Please note the following points in the program above:

• The program is written in the host language ‘C’ and contains embedded SQL statements.
• Although in the program an SQL query (select) has been added. You can embed any DML, DDL, or views statements.
• The distinction between and SQL statement and host language statement is made by using the keyword EXEC SQL; thus this keyword helps in identifying the Embedded SQL statements by the pre-compiler.
• Please note that the statements including(EXEC SQL) are terminated by a semi-colon(;).
• As the data is to be exchanged between a host language and a database, there is a need of shared variables that are shared between the environments. Please note that enroll[10], name[20], p-code[4]; etc. are shared variables, colon(:) declared in ‘C’.
• The Type mapping between ‘C’ and SQL types is defined in the following table:
  

‘C’ Type SQL Type
Long INTEGER
Short SMALLINT
Float REAL
Double DOUBLE
Char[I+1] CHAR(i)

• Please also note that these shared variables are used in SQL statements of the program. They are prefixed with the colon(:) to distinguish them from database attribute and relation names. However, they are used without this prefix in any C language statement.
• Please note that these shared variables have almost the same name(except p-code) as that of the attribute name of the database. The prefix colon(:) this distinguishes whether we are referring to the shared host variable or an SQL attribute. Such similar names is a good programming convention as it helps in identifying the related attribute.
• Please note that the shared variables are declared between BEGIN DECLARE SECTION and END DECLARE SECTION and there typed is defined in ‘C’ language.

How to create an XML Sitemap

To create an XML sitemap is very important for a website. If you have a website with thousands of pages, then it becomes more important to create XML sitemap for the website. XML sitemap is one which helps in easy crawling of all the web pages of a site. From here Google crawler can easily read your content and cache the content easily. There are many other advantages of XML sitemap, like if you have broken links in website which crawlers can’t check easily, it is XML sitemap from where crawlers can read broken links as well. XML sitemaps also play important role in dynamic websites. Dynamic website’s pages can’t be properly crawled by crawlers. In that case If you have XML sitemap in website then they will be easily crawled.

An XML sitemap contains all the pages of a website. Pages in XML sitemap is set with priorities. The most important pages of website like homepage and some other important pages are given high priority in XML sitemap. It is very easy to create an XML sitemap for a website. If you have website with less than 500 pages then you can use the well known online tool for creating sitemap which is http://www.xml-sitemaps.com/.

And if your website is containing more than 500 pages then you need to create the XML sitemap manually. To create a manual XML sitemap check the code below:
<'url'>
<'loc'>http://www.domain.com/<'/loc'>
<'priority'>1.0<'/priority'>
<'changefreq'>weekly<'/changefreq'>
<'/url'>
<'url'>
<'loc'>http://www.domain.com/name.html<'/loc'>
<'priority'>0.5<'/priority'>
<'changefreq'>weekly<'/changefreq'>
<'/url'>

Note: Remove quotes from code
For homepage priority is set as 1.0 and for sub pages it can be 0.5. changefreq as weekly means crawlers will come to your all the pages every week to check the updates. Save this file in .xml format. After creating XML sitemap, upload this on your website server. XML sitemap is very important for a website to be crawled regularly. So, always create XML sitemap of your website.

Java Programming Language

Java is called a pure object oriented programming language. It was developed by Sun Microsystems in 1991. Java is partially based on C and C++ with more advanced features of object oriented model. Java programs are compiled and interpreted using JVM called Java Virtual Machine. One of the main features of Java is that, it is portable. Java is known as a very simple, portable, reliable and powerful programming language.

Java Features
The main purpose of development of Java Programming language was to develop a language that could provide a solution to the problems of modern programming. The purpose was to create a compact, simple and interactive language. Some of the main features of Java Programming Language are:
· Platform-Independent and Portable
· Robust and Secure
· Compiled and Interpreted
· Distributed
· Familiar, Simple and Small
· Multithreaded and Interactive
· High Performance
· Dynamic and Extensible

Java Development Kit
Java Development Kit contains a number of tools for the development and running of java programs. Tools in Java Development Kit includes:
· appletviewer: appletviewer is used to view the Java applets.
· javac: javac is used to compile a java program.
· java: java is used to interpret a java program.
· javap: javap is Java dissembler
· javah: javah is for creating HTML documents
· jdb: jdb is Java debugger

Difference between HTML and XML

HTML
HTML stands for Hypertext Markup Language. HTML standardizes the description of document layout and display, and is an integral part of every website today. In other words, HTML is a language for describing how pages of text, graphics and other information are organized, formatted and linked together.

Today, HTML pages are the standard interface to the internet. They can include animated graphics, sound and video, complete interactive programs, and good old-fashioned text. Millions of web pages are retrieved each day from thousands of web server computers around the world.


XML
XML stands for Extensible Markup Language. It is used to describe documents and data in a standardized, text-based format, easily transportable via standard internet protocols. XML, is based on the mother of all markup languages-Standard Generalized Markup Language(SGML). XML can be used to provide more details on the structure and meaning of the data pages rather than just specifying the format of the web pages. The formatting aspects can be specified separately, by using a formatting language such XSL(extended style sheet language). XML can describe data as records of data store or as a single document.

HTML vs. XML
As a language, XML defines both syntax and grammar rules. The rules are called Document Type Definition(DTD), and are one of the major differences between HTML and XML. XML uses metadata for describing data. The metadata of XML is not complex and adds to the readability of the document. XML, like HTML, also uses tags to describe data however, tags, unlike HTML, describes data and not how to present it. To display XML data,. You often transform it using XSLT into an HTML page.

HTML is comprised of a defined set of tags, XML on the other hand has very few defined gas. However, it does not mean that XML is powerless, the greatest power of XML is that it is extensible. You can create your own tags with your own semantic meaning. For example, you can create to use for your customer information data such as: Customer_First_Name like Manoj

This tag has meaning for you and, thus, to your application. This tag has been created by you to designate customer’s first name but its tells nothing about its presentation. But how is this gag useful to us? Consider now that data stream contains multiple customers information. If you want to find all customers with first name “Manoj” you can easily search for the "customer_first_name" tags. You can not perform such type of operation in HTML with the same ease and consistency, as HTML was bit designed for such purposes. XML is case sensitive while HTML is not.

Object Oriented Database

An object oriented database is used for complex databases. Such database applications require complex interrelationships among object hierarchies to be represented in database systems. These interrelationships are difficult to be implement in relational systems.

Object oriented models represent complex systems very naturally through an inheritance hierarchy. Thus it is a very natural choice for such complex objects.

Consider a situation where you want to design a class, (let us say a Date class), the advantage of object oriented database management for such situations would be that they allow representation of not only the structure but also the operation on newer user defined database type such as finding the difference of two dates. Thus, object oriented database technologies are ideal for implementing such systems that support complex inherited objects, user defined data types (that require operations in addition to standard operation including the operations that support polymorphism).

Another major reason for the need of object oriented database system would be the seamless integration of this database technology with object-oriented applications. Software design is now, mostly based on object oriented technologies. Thus, object oriented database may provide a seamless interface for combining the two the technologies.

The object oriented databases are also required to manage complex, highly interrelated information. They provide solution in the most natural and easy way that is closer to our understanding of the system. Michael Brodie related the object oriented system to human conceptualization of a problem domain which enhances communication among the system designers, domain experts and the system end users.

The he concept of object oriented database was introduced in the late 1970s, however, it became significant only in the early 1980s. The initial commercial product offerings appeared in the late 1980s. Today, many object oriented databases products are available like Objectivity/DB (developed by Objectivity, Inc.), ONTOS DB (developed by ONTOS, Inc.), VERSANT (developed by Versant Object Technology Corp.) and ObjectStore (developed by Object Design, Inc.), GemStone (developed by Servio Corp.) and ObjectStore PSE Pro (developed by Object Design, Inc.) An object oriented database is presently being used for various applications in areas such as e-commerce, engineering product data management; and special purpose database in areas such as, securities and medicine.