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.