Advanced-01:Querying SQL Databases from XQuery (SQLModule)

This howto explains how you may query or update SQL databases from XQuery returning the results as XML nodesets. This tutorial makes use of the sql extension module for eXist; this is available in eXist from 2006-09-25.

1.1 eXist Configuration

Firstly you need to configure eXist to load the additional module, you will need to add the following to the xquery/builtin-modules node of conf.xml (which can be found in EXIST_HOME -
view sourceprint?
1.

NB - eXist will need to be restarted for this change to take effect.

1.2 JDBC Drivers

The SQL Module uses JDBC for its database connectivity and as such for each database type that you wish to connect to a JDBC Driver is required. JDBC Drivers should be placed in EXIST_HOME/lib/user.

1.3 The SQL module

The SQL module provides two main functions - get-connection() and execute().
1.3.1 get-connection()

Used for opening a connection to the database. The connection persists for the lifetime of the executing query. There are two implementations -

view sourceprint?

1.get-connection($jdbcClass, $jdbcConnection)

2.get-connection($jdbcClass, $jdbcConnection, $dbUser, $dbPassword)

jdbcClass is the JDBC Driver Class, e.g. for MySQL this would be "com.mysql.jdbc.Driver"

jdbcConnection is the JDBC Connection String. e.g. for a MySQL server running on the local machine with a database called "pies" this would be - "jdbc:mysql://localhost/pies"

dbUser is the database/schema user's username

dbPassword is the password for the database/schema user

The get-connection() function returns an xs:long which is the id of the open database connection.

1.3.2 execute()

Executes either a query or update against the database. The implementation is -
view sourceprint?
1.execute($connection, $sql, $useColumnNames)

connection is the connection id obtained from get-connection()

sql is the SQL statement to execute

useColumnNames is an xs:boolean value indicating whether the resultant XML should use the Column Names as the node names.

The execute() function returns a Node representing the SQL results. If the SQL query was an update then an update count is returned.

1.4 Example: Querying a SQL Database

xquery version "1.0";
declare namespace sql="http://exist-db.org/xquery/sql";
let $connection := sql:get-connection("com.mysql.jdbc.Driver", "jdbc:mysql://localhost /pies", "root", "")
return
sql:execute($connection, "select * from pieFillings;", fn:true())

1.5 Example: Updating a SQL Database

view sourceprint?
1.xquery version "1.0";

2.declare namespace sql="http://exist-db.org/xquery/sql";

3.let $connection := sql:get-connection("com.mysql.jdbc.Driver", "jdbc:mysql://localhost /pies", "root", "") return

4.sql:execute($connection, "insert into pieFillings (filling, cost) values ('apple', 1.0);", fn:false())

Notes(I collect it from comments)

-> This points used to run simple sql query in eXist db..

Copy the jdbc driver e.g mysql-connector-java-3.1.14-bin.jar into _HOME/lib/user and restart the server.

Copy and paste the 1.4 example code into a text file (e.g. sqltest.xql) and save it in a directory beneath exist. I use _HOME/webapp/xquery/tests

Change the database connection settings and SQL query as necessary

Point your browser to (in my case) http://localhost:8080/exist/xquery/tests/sqltest.xql

And you should get the data back. View source and you will the XML tags.

General awareness-1 : Read Barcode From your Mobile

Today all products come with Barcode for Authentication.....Some Mobile phone Production companies provides Application for read a 1D, 2D barcodes...

How My mobile phone Read Barcode?

Now a days maximum of mobile come with 2mp camera..from that camera you can read barcode
you do only one thing that is download application from your mobile phone official website for applications and put it into your mobile phone...

for Nokia you can search barcode applications in http://www.ovistore.com/

But this barcode reader option for highended mobile phones only......

What is omit-xml-declaration in serialization options?

What is omit-xml-declaration in serialization options?

XML Output Method:

The omit-xml-declaration and standalone Parameters
The XML output method MUST output an XML declaration if the omit-xml-declaration parameter has the value no.

The XML declaration MUST include both version information and an encoding declaration. If the standalone parameter has the value yes or the value no, the XML declaration MUST include a standalone document declaration with the same value as the value of the standalone parameter. If the standalone parameter has the value omit, the XML declaration MUST NOT include a standalone document declaration; this ensures that it is both an XML declaration (allowed at the beginning of a document entity) and a text declaration (allowed at the beginning of an external general parsed entity).

A serialization error [err:SEPM0009] results if the omit-xml-declaration parameter has the value yes, and the standalone parameter has a value other than omit; or
the version parameter has a value other than 1.0 and the doctype-system parameter is specified.
The serializer MUST signal the error.

Otherwise, if the omit-xml-declaration parameter has the value yes, the XML output method MUST NOT output an XML declaration.

PDF Generation in XQuery

The steps required to generate a PDF document are:

* retrieve or compute the base XML document
This Means we can refer any xml document which serves data to our pdf report.Compute means some time we need to format the output as a users need(ex:I need to generate letter with the data from a db).

* transform to XSL-FO, perhaps using XSL
For the case am using eXistdb i must enable the XSL-FO module for this transformation.
First of all we write XSL file for our output.This XSL is responsible for output of the pdf document.

Here we compute the XML for pdf report

Here we get the XSL document

$bid := 'myfilename' ---> Usually it comes from db
let $filename := concat('letter-',$bid) ---->Creating Letter name using variable data
let $xslfilepath := concat('/vserve/xsl/','inst_req','.xsl') ----> get the xsl document
let $xslfile := concat('http://localhost:8080/exist/rest/db/test/',$xslfilepath)

This params parameter is optional one

let $params :=



Here we declare the output method

let $serialization-options := 'method=xml media-type=text/xml omit-xml-declaration=yes indent=no'


* transform the XSL-FO to PDF using Apache FOP

This transform function used for transform XSL file + XML into XSL-FO file

let $fo := transform:transform($form, $xslfile, $params, $serialization-options)

Here we using render() - built-in function to convert XSL-FO into PDF

let $pdf := xslfo:render($fo, 'application/pdf', ())

This file can be saved directly to the XML file system. It will be stored as a non-searchable binary.
You can then view this directly by providing a link to the file or you can send it directly to the browser by using the response:stream-binary() function as follows:
let $pdf1 := response:stream-binary($pdf, 'application/pdf', concat($filename,'.pdf'))

How will change the Session period in eXistdb ?

The Default Session period is 30 Minutes for eXistdb.. If you need to change this session period use the following steps

1. Open the eXist home folder(where it is installed)

2. In that eXist folder open the Tools folder

3. In that tools folder open the Jetty folder

4. In that jetty folder open the etc folder

5. In that etc folder open the webdefault.xml file in a Text Editor

6. Her you can see a following tags change the value of session-timeout node

HTML URL Encoding

URL encoding converts characters into a format that can be transmitted over the Internet.
URL - Uniform Resource Locator

Web browsers request pages from web servers by using a URL.

The URL is the address of a web page, like: http://www.w3schools.com.
URL Encoding

URLs can only be sent over the Internet using the ASCII character-set.

Since URLs often contain characters outside the ASCII set, the URL has to be converted into a valid ASCII format.

URL encoding replaces non ASCII characters with a "%" followed by two hexadecimal digits.

URLs cannot contain spaces. URL encoding normally replaces a space with a + sign.

CTS Technical Interview

Good Noon Friends,

Today i faced Technical interview in CTS

Venue : Mepz(Near Sanitorium Railway station) - CTS campus

You must have : Updated Resume, Photos, Hall Ticket

You must Know : Your Strength and Weakness, What skills required for sucessfull software professional, why you are for CTS, What is ur expectation from CTS

My Interviewer : Mr Gupta(May be Project Manager)

Question asked : Diff between Structure and union
Diff between Method and function
what is class
tell about urself
tell about your project...

Copy Element in XForms

Structurally, this element is similar to the value Element. It differs in that it can only be used within itemset, and that it works with subtrees of instance data rather than simple values.

If the single node binding attributes indicate a node in a model other than the bound node of the containing selection control, then an xforms-binding-exception must occur.. When a copy item is selected, the following rules apply:

* The target node, selected by the binding attributes on the list form control, must be an element node, otherwise an exception results (4.5.1 The xforms-binding-exception Event).
* The element node associated with the item, selected by the binding attributes on copy, is deep copied as a child of the target node by using an insert action (10.3 The insert Element).
* A full computational dependency rebuild is done, followed by recalculate, revalidate, and refresh.

When a copy item is deselected, the following rules apply:

* The target node, selected by the binding attributes on the list form control, must be an element node, otherwise an exception results (4.5.1 The xforms-binding-exception Event).
* The child element node associated with the item, selected by the binding attributes on copy, is deleted by using a delete action (10.4 The delete Element).
* A full computational dependency rebuild, followed by recalculate, revalidate, and refresh.

Note:

If the target node of the select or select1 is readonly, then the insertion or deletion associated with the copy operation is not performed.

If you want more about Select control + copy element visit Xforms 1.1

I am Trying this concept and it working properly Try following code....

selectstore.xhtml - Source code for Copy element in xforms

Try this....

Readonly option Reduce validation in XForms

There are also possibilities in adding useful extensions. You can
already try this experimental extension: add

xf:instance/@readonly="true" to each instance with a static content
and it will not be validated at loading time. For your test case, the
loading time is divided by 2!

WOW! This test really rocks now in FF and shows the client-side XForms
engine advantage in full glory.

Tips for Better Life

1. Take a 10-30 minutes walk every day. And while you walk, smile.
2. Sit in silence for at least 10 minutes each day.
3. Sleep for 7 hours
4. Live with the 3 E's -- Energy, Enthusiasm, and Empathy.
5. Play more games.
6. Read more books than you did in 2010
7. Make time to practice meditation, yoga, and prayer. They provide us with daily fuel for our busy lives.
8. Spend time with people over the age of 70 & under the age of 6.
9.. Dream more while you are awake.
10. Eat more foods that grow on trees and plants and eat less food that is manufactured in plants.
11. Drink plenty of water.
12. Try to make at least three people smile each day.
13. Don't waste your precious energy on gossip.
14. Forget issues of the past. Don't remind your partner with his/her mistakes of the past. That will ruin your present happiness.
15. Don't have negative thoughts or things you cannot control. Instead invest your energy in the positive present moment.
16. Realize that life is a school and you are here to learn. Problems are simply part of the curriculum that appear and fade
away like algebra class but the lessons you learn will last a lifetime.
17. Eat breakfast like a king, lunch like a prince and dinner like a beggar.
18. Smile and laugh more.
19. Life is too short to waste time hating anyone. Don't hate others.
20. Don't take yourself so seriously. No one else does.
21. You don't have to win every argument. Agree to disagree.
22. Make peace with your past so it won't spoil the present.
23. Don't compare your life to others'. You have no idea what their journey is all about. Don't compare your partner with others.
24. No one is in charge of your happiness except you.
25. Forgive everyone for everything.
26. What other people think of you is none of your business.
27. However good or bad a situation is, it will change.
28. Your job won't take care of you when you are sick. Your friends will. Stay in touch.
29. Get rid of anything that isn't useful, beautiful or joyful.
30. Envy is a waste of time. You already have all you need.
31. The best is yet to come.
32. No matter how you feel, get up, dress up and show up.
33. Do the right thing!
34. Call your family often.
35. Your inner most is always happy. So be happy.
36. Each day give something good to others.
37. Don't over do. Keep your limits.

Capabilities of XQuery

Capabilities of Xquery

Selecting information based on specific criteria
Filtering out unwanted information
Searching for information within or set of documents
Joining data from Multiple documents or collection of documents
sorting, grouping and agreegating data
Transforming and restructuring XML data into another XML vocabulary or structure
Performing arithmetic calculations on numbers and dates
Manipulating strings to reformat text

Note: Update Option not available in Xquery 1.0. It will be included in future versions of XQuery

Xquery

XQuery Tutorial

What ?

XQuery is to XML what SQL is to database tables.

XQuery is designed to query XML data - not just XML files, but anything that can appear as XML, including databases.

Want to know more about Xquery visit

About Me

 

         Myself Srinivasan Krishnamoorthy. My native place is Dindigul, Tamilnadu.Currently am working as a Software Engineer in Innodata Private Limited, Noida. My education qualification is Master of Computer Applications. I am Volunteering for Bhumi NGO. My Interests are Painting, Photography and Traveling.