13-4 使用MATLAB對??庫???詢

Ynϥ MATLAB ӹƮwidߩέקA򥻬y{iHUCXӨBJG

  1. ]w DSNA MATLAB g DSN ODBC ӾޱƮwC]ШW@`^
  2. ϥ logintimeout Oӳ]w MATLAB biƮwsɡA̪ճsɶCҦpAڭ̥i logintimeout(5) ӳ]wɶ 5 CbճsƮwɡA@WLɶAMATLAB NͿ~TӤAisC
  3. ϥ database OӳsܸƮwA榡pUG
  4. conn = database(dsn, loginName, password)
  5. 䤤 dsn ObBJ 1 ҳ]w DSNAloginName M passwordhOsƮwbMKXCYƮwݱbMKXAhoӰѼƳiH]wŦrCҶǦ^ܼ conn hONƮwsC
  6. ϥ ping OˬdƮwsAAҦp ping(conn)CoBJºOFˬdƮwsAAb@{XAiHٲC
  7. ϥ exec OӰ SQL ROAöǦ^ cursor AH}lơC榡pUG
  8. cursor = exec(conn, sqlCommand)
  9. 䤤 conn ObBJ 3 ҳ]w ƮwsAsqlCommand hO檺 SQL ROAҶǦ^ cursor AN SQLRO޹Dηq覡Aڭ̥iHھڦ޹DӨoҦơC
  10. iHϥ setdbprefs Oӳ]w^Ǹƪ榡A@O}C]w]ȡ^άOc}CAԨ᭱dҡC
  11. ϥ fetch OӧơAҦp cursor = fetch(cursor, 10) |10ơAñNƦsb cursor 󤤪 Data C
  12. N cursor 󪺸ƶǦ MATLAB ܼơAҦpGsongData = cursor.DataC
  13. iHШϥ cursor ӨoUF SQL ROұo쪺ҦơC
  14. ̫iHϥ close O cursor θƮwsC

oXӨBJAݰ_Ӧ@cơAbھާ@ɡAëDpQCڭ̪ݤ@ӽdҡA|eiJpCbUoӽdҤAڭ̨ϥΫezBJAXsong ƪ] song01.mdb^ơCMAbզdҮɡAAϥΫe@`kӳ]w DSNAN dsnSong01 V song01.mdb mCdҦpUC

Example 1: 13-PƮwX/getData01.mdsn = 'dsnSong01'; % ]wƨӷW١] song01.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw sql = 'select * from song'; % ]w SQL RO cursor = exec(conn, sql); % SQL ROAöǦ^ cursor cursor = fetch(cursor, 8); % g cursor A 8 songData = cursor.data % NƶǦ MATLAB ܼ songData close(cursor); % cursor close(conn); % ƮwssongData = [ 1] 'Τߨ}W' 'it' [1993] [ 2] 'ť' 'iff' [1998] [ 4] 'o' 'iff' [2001] [ 6] '̼xͤH' 'Ȱa' [2000] [ 8] 'ʤH' 'S.H.E' [2002] [ 9] 'I.O.I.O.' 'S.H.E' [2002] [11] 'CQIۧAWr' 'è' [2002] [12] '̼xͤH' 'Ȱa' [2000]

bWzdҤAcursor 󪺧@ΡAŪɮ׮ɩҥΪɮ׫СAڭ̦bUF@SQLROAiHOƤŪXpCҦpAcursor = fetch(cursor, 8) @άOg cursor 8 ơApGA@UF cursor = fetch(cursor, 2)AN|UⵧơAðO cursor CpGƶqjAڭ̤]iHUF cursor = fetch(cursor) Aɷ|NҦƥ^Aìb cursor C

b cursor conn eAA]iHb MATLAB J conn άO cursorANiHݨoӪ쩳äFǤTC

t~ڭ̥iH`NAǦ^GOb@Ӳ}CAè̷ƫAPӦPƫAAҦpƭȩΦr굥COoǸƨä]tW١AYnW١AiHϥ columnnames OALO٦ rows]Ƶơ^Bcols]Ӽơ^Bwidth]Y쪺eס^Battr]Y쪺ҦT^AdҦpUC

Example 2: 13-PƮwX/getDataAttr01.mdsn = 'dsnSong01'; % ]wƨӷW١] song01.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw sql = 'select * from song'; % ]w SQL RO cursor = exec(conn, sql); % SQL ROAöǦ^ cursor cursor = fetch(cursor, 10); % g cursor A 10 fprintf('Ƶ = %d\n', rows(cursor)); % ܸƵ fprintf('Ӽ = %d\n', cols(cursor)); % Ӽ fprintf('W = %s\n', columnnames(cursor));% ܸƮwW fprintf('e = %d\n', width(cursor, 3)); % ܲĤT쪺e attributes = attr(cursor, 3) % ܲĤT쪺ҦT close(cursor); % cursor close(conn); % ƮwsƵ = 10 Ӽ = 4 W = 'Ǹ','qW','D۪','~' e = 255 attributes = fieldName: 'D۪' typeName: 'VARCHAR' typeValue: 12 columnWidth: 255 precision: [] scale: [] currency: 'false' readOnly: 'false' nullable: 'true' Message: []

bWzdҤAcursor ҶǦ^ƬO}CAڭ̥iHϥ setdbprefs OAӱNǦ^Ƨ令c}CAdҦpUC]b榹dҤeAAʳ]wDSNAYN dsnScore01 VdҩҥΪƮw score01.mdbC^

Example 3: 13-PƮwX/getData02.mdsn = 'dsnScore01'; % ]wƨӷW١] score01.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw sql = 'select * from score'; % ]w SQL RO cursor = exec(conn, sql); % SQL ROAöǦ^ cursor setdbprefs('DataReturnFormat', 'structure'); % ]w cursor Ǧ^Ʈ榡Oc}C cursor = fetch(cursor, 10); % g cursor A 10 score = cursor.Data % gƳ]w score ܼ close(cursor); % cursor close(conn); % Ʈws setdbprefs('DataReturnFormat', 'cellarray'); % ^w]Ʈ榡score = studentID: {10x1 cell} studentName: {10x1 cell} homework1: [10x1 double] homework2: [10x1 double] homework3: [10x1 double] midterm: [10x1 double] final: [10x1 double] overall: [10x1 double]

bWzdҤAǦ^ܼ score O@ӵcܼơA]t 8 AC@ȫhO@Ӳ}CA]tƪ song ȡC

Hint
O`NOGMATLABc}CW٥uϥέ^AӤΤA]bϥΦؤ覡ഫeAT{ƮwW٥O^A_hN|oͿ~C]ҦpApGϥ song01.mdb songƪӶiഫAN|oͿ~A]songƪWٳOC^

OWzwƤ覡AoOŦXڭ̻ݭnCڭ̬O_Ǧ^ഫ@ӵc}CACӤNO@ơAçƮwOCӤOH׬O֩wAڭ̩ҭnΨ쪺OO cell2structAӱN}Cഫc}CAdҦpUC

Example 4: 13-PƮwX/db2struct01.mDSN = 'dsnScore01'; % ]wƨӷW١] score01.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(DSN, '', ''); % sƮw sql = 'select * from score order by studentID'; % ]w SQL RO cursor = exec(conn, sql); % SQL ROAöǦ^ cursor cursor = fetch(cursor); % g cursor A score = cursor.data % NƶǦ MATLAB ܼ scoregData temp = columnnames(cursor); % ܸƮwW eval(['fieldNames = {', temp, '}'';']); % NW٫w fieldNames ܼ score2 = cell2struct(score, fieldNames, 2) % N}C score নc}C score2 close(cursor); % cursor close(conn); % Ʈwsscore = '8802142' '\F' [86] [96] [92] [90] [78] [0] '882545' '' [86] [96] [92] [99] [77] [0] '882548' 'Ĭs' [89] [94] [90] [99] [78] [0] '898316' 'T' [87] [95] [94] [99] [88] [0] '914307' '©p' [89] [93] [93] [90] [77] [0] '914340' 'ū䬰' [82] [93] [87] [77] [80] [0] '914358' '}Z' [87] [93] [87] [90] [80] [0] '914370' '' [88] [93] [93] [95] [79] [0] '916310' '' [83] [93] [93] [99] [80] [0] '916701' 'ù' [90] [94] [90] [90] [77] [0] '916716' '' [88] [94] [87] [99] [77] [0] '916717' 'LCz' [90] [97] [92] [99] [80] [0] score2 = 12x1 struct array with fields: studentID studentName homework1 homework2 homework3 midterm final overall

ѤWzdҥiHݥXAscoreO@Ӳ}CAOϥcell2structӶiഫAscore2wgܦO@ӵc}CA䤤C@ӤNO@ơAӥBC@ӤWٳMƮwW٤@PC

pGƶqܤjAYnb MATLAB ˵AäOܤKAi઺ѨMDءG

  1. ϥMATLABu}Cs边v]Array Editor^˵CҦpLWzdҫAiHbMATLABJ open score open score2ANiH˵}CeA]iHקC
  2. ]iHNƼgAHKWU[ݡC

UoӽdҡAڭ̨ϥΤF@ӵ̶}o struct2html.mAiHNc}Cee{ܺAHK[ݡAdҦpUG

Example 5: 13-PƮwX/db2struct02.mDSN = 'dsnScore01'; % ]wƨӷW١] score01.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(DSN, '', ''); % sƮw sql = 'select * from score order by studentID'; % ]w SQL RO cursor = exec(conn, sql); % SQL ROAöǦ^ cursor cursor = fetch(cursor); % g cursor A score = cursor.data; % NƶǦ MATLAB ܼ score temp = columnnames(cursor); % ܸƮwW eval(['fieldNames = {', temp, '}'';']); % NW٫w fieldNames ܼ score2 = cell2struct(score, fieldNames, 2); % N}C score নc}C score2 close(cursor); % cursor close(conn); % Ʈws struct2html(score2); % ܵc}C score2 s

ҲͪspUG

HWOwƮwdߡAU@`NpƮwiקC


MATLAB{]pGig