SHARE
TWEET

Untitled

a guest Mar 20th, 2016 89 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function [Out, Settings] = MSSQLconn(dbname, userpassmethod, varargin)
  2.  
  3. % MSSQLCONN Establishes connection with MS SQL
  4. %
  5. %   MSSQLCONN(DBNAME) Every other input by default
  6. %       - dbname: database name to which you want to connect (char format)
  7. %      
  8. %   MSSQLCONN(DBNAME, USERPASSMETHOD) Supply username and password, else by default
  9. %       - userpassmethod --> {'username','password'}; 1st cell username,2nd cell password
  10. %       - userpassmethod --> '-win'; use windows authentication (only for R2008b)
  11. %       - userpassmethod --> '-manual'; supply credentials through inputdlg
  12. %
  13. %   MSSQLCONN(DBNAME,USERPASSMETHOD,OPTIONAL1...3) Supply variable number of optionals.
  14. %       - servername: string with the server where the database is hosted
  15. %       - portnumber: integer of the port
  16. %       - oldversion: if MS SQL is older than 2005 ed. supply '-old';
  17. %
  18. %   [OUT,SETTINGS] = ...
  19. %       - Class 'database': connection to the server.
  20. %       - Class 'structure' with the settings used to connect (except password).
  21. %
  22. %   DEFAULT SETTINGS:
  23. %       - user = '';
  24. %       - pass = '';
  25. %       - server = 'localhost';
  26. %       - port = 1433;
  27. %       - driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver' (MS SQL 2005 and above);
  28. %       - windows authentication: false;
  29. %
  30. % Examples:
  31. %   - MSSQLconn('MyDB')                      % use the whole set of default settings
  32. %   - MSSQLconn('MyDB',{'Oleg','****'})      % supply username and password
  33. %   - MSSQLconn('MyDB',{'Oleg',''})          % supply just the username, password by inputdlg
  34. %   - MSSQLconn('MyDB','-win')               % use windows authentication
  35. %   - MSSQLconn('MyDB','-manual')            % supply both username and password through inputdlg
  36. %   - MSSQLconn(...,'myserver')              % supply servername with any combination of dbname and userpassmethod
  37. %   - MSSQLconn(...,1433, 'myserver','-old') % full set of optionals
  38. %
  39. % Additional features:
  40. % - <a href="matlab: web('http://msdn.microsoft.com/en-us/data/aa937724.aspx','-browser')">MS SQL server JDBC Driver Webpage</a>
  41. % - <a href="matlab: web('http://www.mathworks.com/matlabcentral/fileexchange/25577-ms-sql-jdbc-connection','-browser')">FEX MSSQLconn page</a>
  42. % - <a href="matlab: web('http://www.mathworks.com/support/solutions/en/data/1-9SHNAT/','-browser')">TMW Support Win Authentication</a>
  43. %
  44. % See also DATABASE
  45.  
  46. % Author: Oleg Komarov (oleg.komarov@hotmail.it)
  47. % Date: 13 oct 2009 - created
  48. %       14 oct 2009 - added links to MS JDBC drivers and to FEX submission page
  49. %       28 oct 2009 - reorganized input checks and added single input syntax
  50. %       11 nov 2009 - changed input syntax; added win authentication, TMW support link and settings output
  51.  
  52.  
  53. %-------------------------------------------------------------------------------------
  54. % CHECK part
  55. %-------------------------------------------------------------------------------------
  56.  
  57. % 1. # of inputs
  58. error(nargchk(1,5,nargin))
  59.  
  60. % 2. dbname
  61. if ~ischar(dbname);  error('MSSQLconn:strFmt', 'dbname must be char'); end
  62.  
  63. % 3. userpassmethod
  64. if nargin == 1 || isempty(userpassmethod);
  65.     method = '-default';
  66. elseif iscell(userpassmethod)
  67.     method = '-cell';
  68. else method = userpassmethod;
  69. end
  70. winAuth = 'false'; user = ''; pass = '';                                      % Default values
  71. switch method
  72.     case '-default'                                                           % Use default values
  73.     case '-cell'
  74.         if numel(userpassmethod) == 2                                         % [1] IF 2 cells
  75.         user = userpassmethod{1}; pass = userpassmethod{2};
  76.             if ~isempty(user) && ischar(user) && isempty(pass)                % [2] IF pass empty
  77.             while isempty(pass)
  78.                 pass = inputdlg('Supply password: ', 'Empty not admitted',1);
  79.                 pass = pass{:};
  80.             end
  81.             elseif ~isempty(pass) && ischar(pass) && isempty(user)            % [2] IF user empty
  82.             while isempty(user)
  83.                 user = inputdlg('Supply username: ', 'Empty not admitted',1);
  84.                 user = user{:};
  85.             end
  86.             end
  87.         else error('MSSQLconn:upmFmt', 'userpassmethod wrong format');              
  88.         end
  89.     case '-win'
  90.         if any(str2double(struct2cell(ver('database'))) < 3.5) ;
  91.            error('MSSQLconn:wauMth', 'Feature unavailable for Database Toolbox release older than 3.5 (R2008b)')
  92.         else winAuth = 'true';
  93.         end
  94.     case '-manual'
  95.          while isempty(user) || isempty(pass)
  96.             userpass = inputdlg({'Supply username: '; 'Supply password: '}, 'ENTER BOTH',1,{'',''},'on');
  97.             user = userpass{1}; pass = userpass{2};
  98.         end
  99. end
  100.  
  101. % 4. Oldver
  102. IDXo = strcmp('-old',varargin);
  103. if any(IDXo)
  104.     drv = 'com.microsoft.jdbc.sqlserver.SQLServerDriver';
  105. else
  106.     drv = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
  107. end
  108.  
  109. % 5. Port
  110. IDXn = cellfun(@isnumeric,varargin);
  111. if nnz(IDXn) == 1 && mod(varargin{IDXn},1) == 0
  112.     port = num2str(varargin{IDXn});
  113. elseif nnz(IDXn) > 1
  114.     error('MSSQLconn:prtFmt', 'Only one numeric integer port is accepted')
  115. else port = '1433';
  116. end
  117.  
  118. % 6. Server
  119. IDXs = cellfun(@ischar, varargin) & ~IDXo;
  120. if any(IDXs); server = varargin{IDXs}; else server = 'localhost'; end
  121.  
  122. %-------------------------------------------------------------------------------------
  123. % ENGINE part
  124. %-------------------------------------------------------------------------------------
  125.  
  126. % Url concatenation
  127. URL = ['jdbc:sqlserver://' server ':' port ';database=' dbname ';integratedSecurity=' winAuth ';'];
  128.  
  129. % Set connection timeout (s)
  130. logintimeout(drv, 10);
  131.  
  132. % Connect
  133. Out = database('', user, pass, drv, URL);
  134.  
  135. % Settings
  136. if nargout == 2
  137.     Settings = cell2struct({dbname; user; drv; server; port; ~strcmp(winAuth,';');Out.Message},...
  138.     {'login'; 'thusha-PCthusha'; 'com.microsoft.sqlserver.jdbc.SQLServerDriver'; 'localhost'; '53811'; 'windowsAuthentication'; 'errorMsg'});
  139. end
  140.  
  141. % [1] IF connected
  142. if isconnection(Out)
  143.     % Initialize Status
  144.     Status = '.';
  145.     % [2] IF readonly
  146.     if isreadonly(Out); Status = ' in "READONLY" mode.'; end % [2]
  147.     % Display connection status
  148.     sprintf('Connected%s', Status)
  149. else % [1] IF not connected
  150.     % Display error
  151.     error('MSSQLconn:conInv',Out.Message)
  152. end % [1]
  153.  
  154. end
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top