Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL in a Server Environment 2

Lipyeow
September 25, 2015

SQL in a Server Environment 2

JDBC, Stored Procs, UDFs.

Lipyeow

September 25, 2015
Tweet

More Decks by Lipyeow

Other Decks in Education

Transcript

  1. ICS  321  Data  Storage  &  Retrieval   SQL  in  a

     Server  Environment  (ii)   Informa<on  &  Computer  Science  Department   University  of  Hawaii  at  Manoa   1   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
  2. Alterna<ve  to  Embedded  SQL   •  What  if  we  want

     to  compile  an   applica<on  without  the  need  for  a   DBMS-­‐specific  pre-­‐compiler  ?   •  Use  a  library  of  database  calls   –  Standardized  (non-­‐DBMS-­‐specific)  API   –  Pass  SQL-­‐strings  from  host  language   and  presents  result  sets  in  a  language   friendly  way   –  Eg.  ODBC  for  C/C++  and  JDBC  for  Java   –  DBMS-­‐neutral   •  A  driver  traps  the  calls  and  translates   them  into  DBMS-­‐specific  code   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   2   Applica<on   DBMS-­‐specific  Driver   ODBC/JDBC  API   DBMS   network  
  3. ODBC/JDBC  Architecture   •  Applica<on   –  Ini<ates  connec<ons  

    –  Submits  SQL  statements   –  Terminates  connec<ons     •  Driver  Manager   –  Loads  the  right  JDBC  driver   •  Driver   –  Connects  to  the  data  source,     –  Transmit  requests,     –  Returns  results  and  error  codes   •  Data  Source   –  DBMS   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   3   Applica<on   Driver  Manager   Driver   Data  Source  
  4. 4  Types  of  Drivers   •  Type  I:  Bridge  

    –  Translate  SQL  commands  to  non-­‐na<ve  API   –  eg.  JDBC-­‐ODBC  bridge.  JDBC  is  translated  to  ODBC  to  access  an   ODBC  compliant  data  source.   •  Type  II:  Direct  Transla<on  to  na<ve  API  via  non-­‐Java  driver   –  Translates  SQL  to  na<ve  API  of  data  source.     –  Needs  DBMS-­‐specific  library  on  each  client.   •  Type  III:  Network  bridge   –  SQL  stmts  sent  to  a  middleware  server  that  talks  to  the  data   source.  Hence  small  JDBC  driver  at  each  client   •  Type  IV:  Direct  Transla<on  to  na<ve  API  via  Java  driver   –  Converts  JDBC  calls  to  network  protocol  used  by  DBMS.     –  Needs  DBMS-­‐specific  Java  driver  at  each  client.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   4  
  5. High  Level  Steps   1.  Load  the  ODBC/JDBC  driver  

    2.  Connect  to  the  data  source   3.  [op<onal]  Prepare  the  SQL   statements   4.  Execute  the  SQL  statements   5.  Iterate  over  the  resultset   6.  Close  the  connec<on   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   5  
  6. Gedng  Data  to/fro  Host  Language   •  No  declara<on  of

     shared  variables   •  Variables  in  host  language  is  bound  to  columns  of   a  SQL  cursor   •  ODBC   –  SQLBindCol  –  gets  data  from  SQL  environment  to  host   variables.   –  SQLBindParameter  –  gets  data  from  host  variables  to   SQL  environment   •  JDBC   –  ResultSet  class   –  PreparedStatement  class   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   6  
  7. Prepare  Statement  or  Not  ?   •  Execu<ng  without  preparing

     statement   –  Afer  DBMS  receives  SQL  statement,     •  The  SQL  is  compiled,       •  An  execu<on  plan  is  chosen  by  the  op<mizer,   •  The  execu<on  plan  is  evaluated  by  the  DBMS  engine   •  The  results  are  returned   •  conn.prepareStatement   –  Compiles  and  picks  an  execu<on  plan   •  pstmt.executeUpdate   –  Evaluates  the  execu<on  plan  with  the  parameters  and  gets  the   results   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   7   String  sql=“SELECT  *  FROM  books  WHERE  price  <  ?”;   PreparedStatement  pstmt  =  conn.prepareStatement(sql);   Pstmt.setFloat(1,  usermaxprice);   Pstmt.executeUpdate();     cf.  Sta<c  vs   Dynamic   SQL  
  8. ResultSet   •  Iterate  over  the   results  of  a

     SQL   statement  -­‐-­‐  cf.   cursor   •  Note  that  types  of   column  values  do   not  need  to  be   known  at  compile   <me   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   8   ResultSet  rs  =  stmt.executeQuery(sqlstr);     while(  rs.next()  ){    col1val  =  rs.getString(1);  …   }   SQL  Type   Java  Class   accessor   BIT   Boolean   getBoolean   CHAR,   VARCHAR   String   getString   DOUBLE,   FLOAT   Double   getDouble   INTEGER   Integer   getInt   REAL   Double   getFloat   DATE   Java.sql.Date   getDate   TIME   Java.sql.Time   getTime   TIMESTAMP   Java.sql.TimeStamp   getTimestamp  
  9. RowSet   •  When  inser<ng  lots  of  data,  calling  an

     execute   statement  for  each  row  can  be  inefficient   –  A  message  is  sent  for  each  execute   •  Many  APIs  provide  a  rowset  implementa<on   –  A  set  of  rows  is  maintained  in-­‐memory  on  the  client   –  A  single  execute  will  then  insert  the  set  of  rows  in  a   single  message   •  Pros:  high  performance   •  Cons:  data  can  be  lost  if  client  crashes.   •  Analogous  rowset  for  reads  (ie.  ResultSet)  also   available   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   9  
  10. Stored  Procedures   •  What  ?   –  A  procedure

     that  is  called  and  executed  via  a  single  SQL   statement   –  Executed  in  the  same  process  space  of  the  DBMS  server   –  Can  be  programmed  in  SQL,  C,  java  etc   –  The  procedure  is  stored  within  the  DBMS   •  Advantages:   –  Encapsulate  applica<on  logic  while  staying  close  to  the   data   –  Re-­‐use  of  applica<on  logic  by  different  users   –  Avoid  tuple-­‐at-­‐a-­‐<me  return  of  records  through  cursors   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   10  
  11. SQL  Stored  Procedures   CREATE  PROCEDURE  ShowNumReserva<ons    SELECT  S.sid,

     S.sname,  COUNT(*)    FROM  Sailors  S,  Reserves  R    WHERE  S.sid  =  R.sid    GROUP  BY  S.sid,  S.sname   •  Parameters  modes:  IN,  OUT,  INOUT   CREATE  PROCEDURE  IncreaseRa<ng  (  IN  sailor_sid   INTEGER,  IN  increase  INTEGER  )    UPDATE  Sailors      SET  ra<ng  =  ra<ng  +  increase      WHERE  sid  =  sailor_sid   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   11  
  12. Java  Stored  Procedures   CREATE  PROCEDURE  TopSailors  (    

       IN  num  INTEGER)    LANGUAGE  JAVA    EXTERNAL  NAME    “file:///c:/storedProcs/ rank.jar”   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   12  
  13. Calling  Stored  Procedures   •  SQL:    CALL  IncreaseRa<ng(101,  2);

      •  Embedded  SQL  in  C:    EXEC  SQL  BEGIN  DECLARE  SECTION    int  sid;  int  ra<ng;    EXEC  SQL  END  DECLARE  SECTION    EXEC  SQL  CALL  IncreaseRa<ng(:sid,  :ra<ng);   •  JDBC    CallableStatement  cstmt  =    conn.prepareCall(“{call  Show  Sailors});    ResultSet  rs=cstmt.executeQuery();   •  ODBC    SQLCHAR  *stmt  =  (SQLCHAR  *)"CALL  ShowSailors";      cliRC  =  SQLPrepare(hstmt,  stmt,  SQL_NTS);      cliRC  =  SQLExecute(hstmt);     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   13  
  14. User  Defined  Func<ons  (UDFs)   •  Extend  and  add  to

     the  support  provided  by   SQL  built-­‐in  func<ons   •  Three  types  of  UDFs   – Scalar:  returns  a  single-­‐valued  answer.  Eg.  Built-­‐ing   SUBSTR()   – Column:  returns  a  single-­‐valued  answer  from  a   column  of  values.  Eg.  AVG()   – Table:  returns  a  table.    Invoked  in  the  FROM   clause.   •  Programable  in  SQL,  C,  JAVA.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   14  
  15. Scalar  UDFs   •  Returns  the  tangent  of  a  value

      CREATE  FUNCTION  TAN  (X  DOUBLE)      RETURNS  DOUBLE      LANGUAGE  SQL      CONTAINS  SQL      RETURN  SIN(X)/COS(X)     •  Reverses  a  string   CREATE  FUNCTION  REVERSE(INSTR   VARCHAR(4000))      RETURNS  VARCHAR(4000)    CONTAINS  SQL     BEGIN  ATOMIC      DECLARE  REVSTR,  RESTSTR    VARCHAR(4000)  DEFAULT  '';      DECLARE  LEN  INT;      IF  INSTR  IS  NULL  THEN        RETURN  NULL;      END  IF;      SET  (RESTSTR,  LEN)  =  (INSTR,    LENGTH(INSTR));      WHILE  LEN  >  0  DO        SET  (REVSTR,  RESTSTR,  LEN)   =  (SUBSTR(RESTSTR,  1,  1)  CONCAT   REVSTR,  SUBSTR(RESTSTR,  2,  LEN   -­‐  1),  LEN  -­‐  1);      END  WHILE;      RETURN  REVSTR;     END     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   15  
  16. Table  UDFs   •  returns  the  employees  in  a  specified

     department  number.     CREATE  FUNCTION  DEPTEMPLOYEES  (DEPTNO  CHAR(3))      RETURNS  TABLE  (        EMPNO  CHAR(6),          LASTNAME  VARCHAR(15),          FIRSTNAME  VARCHAR(12))      LANGUAGE  SQL      READS  SQL  DATA      RETURN        SELECT  EMPNO,  LASTNAME,  FIRSTNME        FROM  EMPLOYEE        WHERE  EMPLOYEE.WORKDEPT            =  DEPTEMPLOYEES.DEPTNO     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   16  
  17. Java  UDFs   CREATE  FUNCTION  tableUDF  (  DOUBLE  )  

      RETURNS  TABLE  (      name  VARCHAR(20),      job  VARCHAR(20),      salary  DOUBLE  )     EXTERNAL  NAME  'MYJAR1:UDFsrv! tableUDF‘   LANGUAGE  JAVA     PARAMETER  STYLE  DB2GENERAL   NOT  DETERMINISTIC     FENCED     NO  SQL     NO  EXTERNAL  ACTION     SCRATCHPAD  10     FINAL  CALL     DISALLOW  PARALLEL     NO  DBINFO@     import  COM.ibm.db2.app.UDF;     public  void  tableUDF(    double  inSalaryFactor,      String  outName,      String  outJob,      double  outNewSalary)      throws  Excep<on     {      int  intRow  =  0;                  …    }  //  tableUDF  }  //  UDFsrv  class     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   17