1 / 56

Using SQL in Higher Level Languages for Data Manipulation and Querying

Learn why SQL is important in higher level languages and how to read data from files, manipulate data, insert values into relations, and compute results based on SQL queries. Discover how to create a user interface for SQL queries and connect to databases using PHP and MySQL.

sarahmartin
Download Presentation

Using SQL in Higher Level Languages for Data Manipulation and Querying

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL in higher level languages

  2.  SQL in higher level languages Why do we want to use SQL in a higher level language? • Read in data from file, manipulate data before insert into relation Loop until the EOF read values from file – higher level code manipulate values with higher level code insert into relation values (SQL) End loop • must read in values into C/C++ variables then use those values to insert using SQL • still need SQL statement to insert, select tuples

  3. Why cont’d • Compute results based on result from query e.g. generate a report Query database (SQL) Compute results from query Print results • must be able to manipulate results from SQL query, but mismatch between higher level language and SQL • sets versus one record at a time

  4. Provide a user interface (Web) for SQL if the current one is lacking Prompt user for query Send query to DBMS (SQL) Receive results Display results to user need to accept queries from user - create SQL queries

  5. To do this? Given the query: Select * From department, employee Where dno=dnumber and lname=?? What is needed? • Variables in which to i) specify a select condition or table to use ii) variable to place result (:Host variables) • Processing of result table (cursors) • Data structure for communicating with DBS in case of errors (SQLCA) • What if we want to process any query typed in? (Dynamic SQL) • Also must connect to the DB, authorized by DBMS • Must specify where to find the DB, the user login and the user PW

  6. To do this • Embedded SQL Precede each statement with EXEC SQL 2. Programming language that controls a software application PHP, Go, etc. 3. Platform specific classes, interfaces Oracle’s OLE 4. Platform independent classes, interfaces JDBC

  7. 2. PHP (and MYSQL)

  8. PHP • Personal Home Page tools publicly released 1995 by R. Lerdorf (Danish-Canadian) • In 1998 became PHP: Hypertext Preprocessor • Creates DYNAMIC web pages • HTML traditionally static • Contents regenerated every time visit or reload site • (e.g. can include current time)

  9. PHP • PHP is a scripting language • Script like a dialogue for play interpreted by actors • Because it is interpreted? • Strong at communicating with program components written in other languages • E.g. can embed PHP statements within HTML

  10. PHP • Takes input from a file or stream containing text and PHP instructions • Outputs stream of data for display • MySQL, support for SQLite, performance enhancements • SQLite – ACID compliant embedded relational DB contained in small C programming library. Source code in public domain. Entire DB stored as a single file on a host machine. • PHP 7 – Now available

  11. PHP • Resembles C in structure • Requires semicolons after each statement ; • Types are (booleans, integers, strings, etc.) • Syntax (For/While/If) • Assignment is right to left ($num = 56;) • Object-Oriented (Class support, inheritance, virtuals, polymorphism) • Functions!

  12. PHP • Variables begin with $ sign ($name = "John Doe";) • No explicit declaration of variable types • Introduction of “lazy” functions (foreach, explode, mail) • No Function Overloading • “Hidden” functions-within-a-function • Compiled/interpreted during every page load • Documented! • Echo for output • Concatenate is dot .

  13. PHP - specifics • Delimiters: <?php ?> or just <? ?> • PHP parses code within delimiters • Code outside delimiter sent to output, not parsed • Block comments /* */ • Inline comments // #

  14. Sample code <?php // do not put a space between ? and php Echo "Hello CS457"; // can use either " or ' ?> To run this, only need to specify a link to this program - http://cs457.ua.edu/~login/hello.php Example

  15. PHP • Web Specific: • Dynamic HTML based on user-defined logic • Interact and process a form’s action • Process URL Parameters • Easy Database Integration • For example – MySQL!!

  16. MySQL • MySQL is a relational DBMS • Has many of the same capabilities as traditional DBMSs (newest releases) • MySQL queries mostly the same as SQL in Oracle (subsidiary of Sun) • Popular for web databases • It’s freeware! • “Monty” Widenius founder of MySQL –sold it to Sun (bought by Oracle) for $1B

  17. PHP – procedural versus OO • My examples are procedural

  18. Some php mysql functions • Connecting to MySQL through PHP • mysqli_connect ("IP_address", "login", "pw") //returns $link_id • mysqil_select_db ($link_id, "name_db") //assume same as login • mysqli_query ($link_id, $query) • Executes a query, place result in variable, like a cursor • Resource specifies a connection, otherwise last connection opened used • Returns a special variable holding a reference to external resource

  19. Some php mysql functions • mysqli_fetch_array ($result, how) • Traverses through cursor of query result • Returns array corresponding to fetched row, moves pointer to next row • “how” is either mysqli_assoc (use col. names) or mysqli_num (use index number) or mysqli_both • mysqli_error ($link_id) $link not needed for connect • Returns error message from previous sql operation • mysqli_num_fields ($link, $result) • Returns number of columns in table (fields in recordset) http://www.php.net/manual/en/

  20. <?php error_reporting(E_ALL); ini_set("display_errors", "1"); $link=mysqli_connect("cs-sql2014.ua-net.ua.edu", "mybamaID", "CWID"); if (!$link) {die("Not connected: ". mysqli_error()); } // see if connected mysqli_select_db($link, 'login') or die('Cannot select database'); $query = 'CREATE TABLE testit( '. 'id INT NOT NULL, '. 'age int)'; $result = mysqli_query($link, $query); if(!$result) {die( 'Error in SQL: ' . mysqli_error($link));} echo "table created"; mysqli_close($link); ?>

  21. http://cs457.ua.edu/~svrbsky/ex1.php

  22. <?php error_reporting(E_ALL); ini_set("display_errors", "1"); echo "Welcome to Vrbsky's DB"; // Connect to MySQL $link = mysqli_connect("cs-sql2014.ua-net.ua.edu", "mybamaID", "CWID"); if (!$link) {die('Not connected: '. mysqli_error()); } // see if connected // Select DB will use mysqli_select_db($link, 'login') or die ('Could not select database'); // see if worked // Now the query $query = "Select * from testit"; // testit has 2 columns, id and age $result = mysqli_query($link, $query); if (!$result) {die( 'Error in SQL: ' . mysqli_error($link));} // process results using cursor while ($row = mysqli_fetch_array($result)) { echo "<hr>"; //horizontal line echo "id: ". $row["id"] . "<br />"; echo "age: " . $row["age"] . "<br />"; } mysqli_free_result ($result); mysqli_close($link); // disconnecting from MySQL ?>

  23. http://cs457.ua.edu/~svrbsky/ex2.php

  24. Accessing result rows // Using an index while($row = mysqli_fetch_array($result, MYSQLI_NUM)) { echo “ID:{$row[0]} <br>" . “Age: {$row[1]} <br><br>"; }

  25. http://cs457.ua.edu/~svrbsky/ex2i.php

  26. Updated final project • HW#8 posted • Friday – come to class extra credit, bring a friend and get additional extra credit

  27. Forms and input • Can use HTML to create forms • Interaction with form results in php code executing (e.g. send query to DB)

  28. HTML code • In our ex3.html, code uses HTML form to ask for input values to a table • When user inputs values, event (action) occurs and php code is executed • To use those values in php file, must use $_POST[‘var_name’] • Like a host variable

  29. $_POST function <form method="post" action="example.php"> • variables from a form will be placed into $_POST • $_ POST is an associative array (key, value) • Index into array is form data name • Info sent from form • POST is a superglobal variable, available in all scopes • With POST no limits on the amount of info to send • Different from $_GET function where • Info sent is displayed in browser’s address bar • Max 100 characters

  30. HTML and PHP and MYSQLex3.html <html> </head> <center> <!- The following line results in php code executed after input values in form -> <form method="post" action="ex3.php"> <table> <tr><td align="left">ID</td> <td><input type="text" name="id"></td> </tr> <tr><td align="left">Age</td> <td><input type="text" name="age" size="15"></td> </tr> <tr><colspan="2"> <p align="center"> <input type="submit" value="Enter record"> </td> </tr> </table> </form> </center> </html>

  31. PHP code • PHP code places values input from form into its own local variables • Connects to database • Inserts values into tables • Prints out values

  32. ex3.php <?php error_reporting(E_ALL); ini_set("display_errors", "1"); // This is example3.php used in previous .htm code $link = mysqli_connect("cs-sql2014.ua-net.ua.edu", "mybamaID", "CWID"); if (!$link) {die('Not connected: '. mysqli_error()); } mysqli_select_db($link, 'login') or die ('Could not select database'); //have 2 host variables $id= $_POST['id']; $age = $_POST['age']; //the query $query = "insert into testit values ('$id', '$age')"; $result = mysqli_query($link, $query); if (!$result) {die('SQL error: ' . mysqli_error($link));} mysqli_close($link); print "<html><body><center>"; print "<p>You have just entered this record<p>"; print "ID: $id<br>"; print "Age: $age"; print "</body></html>"; ?>

  33. http://cs457.ua.edu/~svrbsky/ex3.html • http://cs457.ua.edu/~svrbsky/ex2.php

  34. Our set up • Two machines • cs457.ua.edu - php/html login: your_bama_idPassword: your_bama_pw • cs-sql2014.ua-net.ua.edu - MySQL server machine login: your_bama_id Password: CWID

  35. html php 1 cs457.ua.edu – ssh use mybama id/pw 4 2 3 Your DB using MySQL cs-sql2014.ua-net.ua.edu – use mybama id/CWID

  36. Connecting to MySQL • You can connect to MySQL through a higher level language: .php, go, ruby on rails, etc. • write PHP code to: insert tuples into your tables/views Or • You can connect to MySQL directly using a client tool

  37. MySQL Client Tools • You can connect to MySQL directly using a client tool • Host is: cs-sql2014.ua-net.ua.edu   • User:  your_bama_id • Password: your CWID • Database: you may have to create a database, use same name as user login

  38. MySQL Client Tools • You can use a toolset to create tables and insert tuples into your tables on cs-sql2014.ua-net.ua.edu • Possible toolsets: • HeidiSQL • Sequel Pro (Mac) • PhpMyAdmin • Toad: http://www.quest.com/toad-for-mysql/ • But only good for 30 days

  39. Test PHP on cs457.ua.edu • Create your php and html files on cs457.ua.edu using an editor, e.g. vi • Make sure the file is on the cs457.ua.edu machine in the public_html directory – chmod 755 so can execute • Sample program: <?php Echo “Hello World”; ?> • To run it, from a web browser, type in: http://cs457.ua.edu/~yourbamaid/filename For example, I use: http://cs457.ua.edu/~svrbsky/hello.php

  40. Running Examples • Next copy some of the programs from examples in these slides using php and mySQL and run them. • Make sure you change the login, password and database to yours • Make sure you create any needed tables for the examples.

  41. HW#8 • Need 2 files: • 1 for html 1 for php • You can test your SQL statements first using MySQL client (or whatever) • To grant permission to others use their bama_id, e.g., for me it is svrbsky

  42. Lots of great links on the web to get into • Disadvantage: How to determine what is error?

  43. Stopped here

  44. Dynamic query

  45. Example HTML and PHPex4.html <html> <body> <center> <form method="post" action="ex4.php"> <!-- places values in associative array called $_POST --> <font size="18">Complete the Select Statement</font><br> Select <input type="text" name="select" size="60" value=" " ><br> <input type="submit" value="Get Query"></form> <table border="1"> </table></center></body></html>

  46. <?php //This is example4.php referenced in previous .html code error_reporting(E_ALL); ini_set("display_errors", "1"); $link = mysqli_connect("cs-sql2014.ua-net.ua.edu", "mybamaID", "CWID"); if(!$link) { die('Not connected: '.mysqli_error());} mysqli_select_db($link, “login"); // isset tests if the value of the variable is set if(isset($_POST['select'])) { //$select = 'select '.$_POST['select']; //echo $select; //can have problems with col=.string., so must get rid of \.s inserted by php for mysql| $select = stripslashes('select '.$_POST['select']); echo $select; $result = mysqli_query($link, $select); if(!$result) { echo mysqli_error($link); } else { while($row = mysqli_fetch_array($result, MYSQLI_NUM)) { echo "<hr>"; // horizontal line echo "<tr>"; for($count = 0; $count < 10; $count++) { if(isset($row[$count])) echo " <td>{$row[$count]}</td>"; if(!isset($row[$count]) && isset($row[++$count])) { echo "<td></td>"; $count--; } } echo "</tr>"; } } } ?>

  47. http://cs457.ua.edu/~svrbsky/ex4.html

  48. 1. Embedded SQL

  49. Embedded SQL Embedded SQL – not used much these days – basis for all others Precede each statement with EXEC SQL Embedded Select statement EXEC SQL Select [distinct] expr {, expr} into host_var {, host_var} From table_name [alias] {, table_name [alias]} [Where search_cond] [Group by col {, col}] [Having search_cond]

  50. Embedded SQL Only returns 1 value - EXEC SQL select lname, salary into :lname, :sal From employee Where ssn=123456789; If Returns multiple values - char emp_name[50][20]; int emp_number[50]; float salary[50]; EXEC SQL SELECT ENAME, EMPNO, SAL INTO :emp_name, :emp_number, :salary FROM EMP WHERE SAL > 1000;

More Related