DBTool User Manual
Contents
1. gt gt close rs To verify our modification is correct try the following code gt gt rs rset db select from arrays gt gt xc fieldc rs gt gt xc 0 abc 1x512 double gt gt plot xc 3 gt gt close rs gt gt close db See figure 4 2 for the plot of xc 3 Figure Ho 1 jolx File Edit View Insert Tools Window Help JOSS KAA PER 300 250 200 150 100 50 0 0 100 200 300 400 500 600 Figure 4 2 Data extracted from the BLOB field 23 Chapter 5 References 5 1 dbase The class object dbase is a database object It can be opened by assigning a ODBC data source and then use several methods operate on the tables in the database 5 1 1 Properties The properties of class object dbase can t be accessed directly The only way to read it is by using the method display 5 1 1 1 handle Use the handle property to distinguish between different dbase objects 5 1 1 2 dsn The dsn property is the data source name in a string 5 1 1 3 uid The uid property is the user login name of the database In many cases uid is not needed and an empty string is used 5 1 1 4 pwd The pwd property is the user login password of the database In many cases pwd is not needed and an empty string is used 5 1 2 Methods 5 1 2 1 dbase Creat an ODBC database object and open it Calling convention db dbase dsn uid pwd options Inputs 24 DBTool U2. Windows DSN name ESE MySQL host name or IP lezez NN MySQL database name ET User PT Password O Port ff not 3306 PN se Options that affects the behaviour of MyODBC IX Dont optimize column width 7 Pad CHAR to full length JE Return matching rows JE Return table names in SOLDescribeCol Trace MyODBC IV Use compressed protocol FF Allow BIG results JE Ignore space after function names JE Don t prompt on connect JE Force use of named pipes FF Simulate ODBC 1 0 T Change BIGINT columns to INT FF Ignore H in table JE No catalog exp IS Use manager cursors exp J Read options from C my cnt I Don t use setlocale JE Safety Check this if you have problems FF Disable transactions Figure 2 14 MySQL ODBC data source configuration window 10 DBTool User Manual After that click OK to return to the ODBC Data Source Administrator window as shown in figure 2 15 We can see that two data sources have been added testaccess and testmysql ODBC Data Source Administrator ax User DSN System DSN File DSN Drivers Tracing Connection Pooling About User Data Sources Dive Remove Configure El An ODBC User data source stores information about how to connect to the indicated data provider A User data source is only visible to you and can only be used on the current machine Figure 2 15 ODBC Setup with 2 new data sources 2 3 4 Setup ODBC data source for MA
3. gt gt rs rset db select Name Sex Age City Date Photo from mytable gt gt xc fieldc rs gt gt xc i Bill gt gt xc 3 22 gt gt insert rs xc gt gt close rs gt gt rs rset db select from mytable where Name Bill gt gt xc fieldc rs Columns 1 through 6 49 B111 male 22 New York 7 3123e 005 1x5099 double gt gt close rs gt gt close db gt gt In this example the ID of the new inserted row is 49 but not 7 this is because ID is the key and index field and its value is maintained by the ODBC driver automatically Multi rows inserting is not supported 4 3 Deleting row Use delete method to delete the current row from the recordset It s used in the following format delete rs For example we delete the row inserted in the previous section gt gt db dbase testaccess gt gt rs rset db select from mytable where Name Bill gt gt delete rs gt gt xc fieldc rs 17 DBTool User Manual XC gt gt gt gt gt gt U A 3 U sd U S close rs close db 4 4 Exececute SQL directly When a databse is opened you can use execsgl method to exececute SQL command or stored procedure directly The calling convention is execsgl db sgl command For example the following command create a new table named pet in the database and then drop it gt gt gt
4. BRANCH NO 8 YA BAO LU BEIJING CHINA SWIFT CODE BKCHCNBJ110 Name Qiang He Account 4080603 0188 017731 7 And send me an email with e Your name or names each name for a copy e Your orgnization e Your email e License type and number of copies Note If you want to buy several copies of DBTool different registration information should be supplied for each copy Wire transfer is prefered If wire transfer is not convenient for you a check via ordinary mail is also acceptable in this case please contact me to ask for my post address I ll email the license file to you immediately The author s email is obase 163 net Chapter 2 Install DB Tool 2 1 Demo version installation 2 1 1 Unpack dbtool zip Unpack all the files to a folder using WinZIP such as c matlab toolbox dbtool See figure 2 CA MATLAB toolbox dbtool Figure 2 1 Install demo version of DBTool 2 1 2 Update MATLAB path Now we add the DBTool path to the MATLAB search path First click menu File gt Set Path the Set Path dialog opens as seen in figure 2 2 DBTool User Manual Set Path O x All changes take effect immediately MATLAB search path Add Folder e an CAMATLABitoolboxwoicebox Add with Subfolders Move to Top Move Up Remove Move Down Move to Bottom PRPRPRPRPRRRRR ER Save Close Revert Default Help Figure 2 2 The Set Path dialog Click Add Folder button an
5. MATLAB path Add the DBTool path to the MATLAB search path just as the same as installing the demo version 2 3 Setup ODBC data source 2 3 1 Prepare to setup ODBC Click the icon of My Computer and open it find the Control Panel icon and open it If you are running Windows 9x you can find the ODBC icon there If you are running Windows 2000 you should first click the icon Administrative Tools in it as seen in figure 2 6 EJ Control Panel File Edt view Favorites Tools Help le gt aa og X aE Configures administrative settings for your computer YA Figure 2 6 The Control Panel window DBTool User Manual Open the Administrative Tools window and find the Data Sources ODBC icon as seen in figure 2 7 FJ Administrative Tools Fie Edit View Favorites Tools Help jas OS B G x B ladds removes and configures Ope 1 43 KB e My Computer yA Figure 2 7 The Administrative Tools window Click the ODBC icon and begin to setup the data sources the ODBC Data Source Administrator window shows up as seen in figure 2 8 ODBC Data Source Administrator 21 x User DSN System DSN File DSN Drivers Tracing Connection Pooling About EA Data Sources Configure CA An ODBC User data source stores information about how to connect to the indicated data provider A User data source is OLS err visible to you and can only be used on the current machine Figure 2 8 The ODBC Data
6. map gt gt The GIF file extracted from the BLOB field is shown in figure 4 1 20 DBTool User Manual ox File Edit View Insert Tools Window Help jo eas Figure 4 1 Show GIF extracted from BLOB 4 6 3 Storing double array in Access In the Access test file test mdb another table named arrays is provided It has the following fields e name The name of the matrix to be saved text e rows Number of rows of the matrix numeric e cols Number of columns of the matrix numeric e data Stores binary data of the matrix set to OLE Object at design and changes to long binary automatically after data is written in In the following example we first generate a random matrix x convert it into byte series using the mex program num2byte and construct a cell array xc contains the name and dimension information and the data in byte series Then insert a new row into the table arrays Finally read it back into a structure xs and restore the data of the matrix from xs data using mex program byte2num and reshape it back into the original dimension gt gt db dbase testaccess gt gt gt rs rset db select from arrays gt gt xc fieldc rs xc dd U U U gt gt x rand 3 4 0 4447 0 9218 0 4057 0 4103 0 6154 0 7382 0 9355 0 8936 0 7919 0 1763 0 9169 0 0579 gt gt Kedai 21 DBTool User Manual gt gt xc 2 3 gt gt xc 3 4 gt gt xc 4
7. DBTool User Manual 2 0 Qiang He Ph D obase 163 net http energy 51 net matlab dbtool htm Contents 1 Introduction 1 Ll What s DBTo0l cias rial a a a RN a 1 1 2 DBTool features ee RRR 1 13 Order DB Taal 80s a6 5 6 as e AA A Se ee 1 2 Install DBTool 3 2 1 Demo version installation 2 e 2 406468 yale ee aaa EE wees 3 211 Unpack dbtoolzip 66 koa ma dauns ee da ee da 3 2 1 2 Update MATLAB path e 3 2 2 Standard professional and redistributable version installation 5 Bol Uppaek dbtool ap cs cc eas dba oe fone ee eo RA a ed 5 222 Copy the liene De oe Ti e le ei ee a BO E Ea 5 22 Update MATLAB pabi ics poio ea eek ee ee ee ee eO and 6 23 Setup ODBC data BOO lt a A BE A eS 6 2 3 1 Prepare tosetip ODBC sace aos e oae nb a ea 6 2 3 2 Setup an Access ODBC data source a 7 2 3 3 Setup an MySQL ODBC data source 000000 9 2 3 4 Setup ODBC data source for MATLAB Web Server 11 3 Getting Started With DBTool 12 ool Test th installation io ww be BN ee ea ee eee we Un 12 ee pena recordset oh ea aes ee A EA ewe ae Ae A a de A 13 3 3 Navigating in the recordset casa csa caasa sati siap ia a ee 13 34 Reading data from recordset 254 ae 2 eo ee a a e a a 14 3 5 Close database and recordset gt oo csa coooa R RR RRR a aaa a a 15 4 Using DBTool 16 AG EOI TOR wk AB ee oe ee a SE es e ee ee 16 AD Inserting row ss na Ba Re eH Re a a Re Se oe 17 do Deleting ro
8. Source Administrator window 2 3 2 Setup an Access ODBC data source You can setup any kind of data sources In this example we demonstrate how to setup an Access data source First click the ADD button in the Create New Data Source window select Driver do Microsoft Access mdb from the ODBC driver list and click Finish as seen DBTool User Manual in figure 2 9 Create New Data Source Xx Select a driver for which you want to set up a data source Name ZO Driver do Microsoft Access mdb lt Back Cancel Figure 2 9 The Create New Data Source window In the ODBC Microsoft Access Setup dialog type in the Data Source Name in this exam ple we use testaccess as seen in figure 2 10 XI Comcel Help ODBC Microsoft Access Setup Data Source Name Desciption nn Hol Advanced System Database Figure 2 10 The ODBC Microsoft Access Setup dialog Then click the Select button to select an access file mdb In this example we select the file c matlab toolbox dbtool test mdb as seen in figure 2 11 DBTool User Manual Select Database x Database Name Directories c matlab toolbox dbtool test mdb List Files of Type Figure 2 11 Select an Access file After selected an Access file the ODBC Microsoft Access Setup dialog looks like the figure 2 LA ODBC Microsoft Access Setup xl Data Source Name Jtestaeeess WN ox Description D
9. TLAB Web Server If you are running MATLAB Web Server to provide HTTP service using MATLAB and DBTool don t create your ODBC Data Source as User DSN use System DSN instead which is the second tab in figure 2 8 11 Chapter 3 Getting Started With DBTool 3 1 Test the installation We ll test the installation by opening a database Start MATLAB and type the command db dbase testaccess Where testaccess is the DSN name of Access ODBC data source we just setup the following two empty strings are the user name and password separately In this case we don t need login authentication so they can be empty If the installation is OK we ll get the followning display in the MATLAB command window gt gt db dbase testaccess Database object members Data source name testaccess User name Password handle 1 gt gt Notice the last line handle 1 means the database is opened and is assinged a handle 1 to the dbase object db Of course if the statement is followed by a there will be on output If the installation is not correct or the user has assigned an error DSN user name or password or any other errors happens there should be some error message printed and the handle should be 0 For example we open a DSN named foo which is not exist gt gt db dbase 067 Warning Data source name not found and no default driver specified Failed to open data
10. a 0 this means the last or the first row has already been reached navigating should then stop 3 4 Reading data from recordset Once a rset object is successfully opened we can fetch data from it There are two methods to read data from the rset object e fields Read and arrange the data into a structure e fieldc Read and arrange the data into a cell The fields method returns a structure its field names are the field names of the recordset and its values are the values of the recordset For example gt gt xs fields rs xs ID 4 Name Mike Sex male Age 25 City New York Date 7 3123e 005 14 DBTool User Manual Photo 1x5099 double gt gt Now you can use xs Name xs Age etc directly The fieldc method returns a cell no field name information is included only the field values are saved in sequence For example gt gt xc fielde rs xe KR Mike male 25 New York 7 3123e 005 1x5099 double gt Now you can use xc 1 for field ID xc 3 for field Age and so on Adding a parameter 0 to read all rows into an array of structure or cell array xc fieldc rs 0 xs fields rs 0 Or specify the number of rows expected to read xc fieldc rs 5 xs fields rs 5 3 5 Close database and recordset To close dbase and rset object just use the close method For example gt gt close rs gt gt close db Be sure not to close dbase objects w
11. apshot uses SQLExtendedFetch static cursor default 2 forwardOnly uses SQLFetch 3 dynamic uses SQLExtendedFetch dynamic cursor Open Options 0x0000 none 0x0004 readOnly 0x0008 appendOnly 0x0010 skipDeletedRecords default Turn on skipping of deleted records Will slow Move n 0x0020 noDirtyFieldCheck Disable automatic dirty field checking 0x0100 useBookmarks Turn on bookmark support 0x0800 useExtendedFetch Use SQLExtendedFetch with forwardOnly type recordsets 0x2000 executeDirect Directly execute SQL rather than prepared execute Choose all options needed and add them up convert it to decimal For example the default skipDeletedRecords is 0x10 and in decimal is 16 For detailed information see the description of CRecordset Open in Visual C Documentation of MSDN 5 2 2 2 close Close the recordset Calling convention ret close rs Input rs rset object Return 1 Success 0 Fail 5 2 2 3 fields Return field data in a structure array Calling convention data fields rs rows Input rs rset object rows optional max rows to read default to 1 use O to read all following rows 28 DBTool User Manual Return data rset object fields in structure 5 2 2 4 fieldc Return field data in a cell array Calling convention data fieldc rs rows Input rs rset object rows optional max rows to read default to 1 use O to read all following rows Return d
12. ata rset object fields in cell 5 2 2 5 movefirst Move to the first row Calling convention ret movefirst rs Input rs rset object Return 1 Success O Fail 5 2 2 6 movelast Move to the last row Calling convention ret movelast rs Input rs rset object Return 1 Success 0 Fail 29 DBTool User Manual 5 2 2 7 movenext Move to the next row Calling convention ret movenext rs Input rs rset object Return 1 Success O Fail 5 2 2 8 moveprev Move to the previous row Calling convention ret moveprev rs Input rs rset object Return 1 Success 0 Fail 5 2 2 9 movenext Move to the next row Calling convention ret movenext rs Input rs rset object Return 1 Success O Fail 30 DBTool User Manual 5 2 2 10 insert Insert a new row into the recordset Calling convention ret insert rs data Input rs rset object data rset data fields in cell Structure not supported Return 1 Success O Fail 5 2 2 11 update Edit and update the current row in the recordset Calling convention ret update rs data Input rs rset class data rset data fields in cell Structure not supported Return 1 Success O Fail 5 2 2 12 delete Delete current row in the recordset Calling convention ret delete rs Input S rset object Return 1 Success 0 Fail 31 DBTool User Manua
13. atabase Database C MATLAB toolbox dbtool test mdb Create Repair Compact System Database Figure 2 12 ODBC Microsoft Access Setup dialog is finished After that click OK and the ODBC data source testaccess is finished 2 3 3 Setup an MySOL ODBC data source Now let s setup a MySQL ODBC data source Before that you need to install MySQL and MyODBC which can be downloaded at http www MySQL com downloads index html After MySQL and MyODBC are installed and setup correctly we need to create a database for test purpose This can be done in MySQL command window or use the GUI MySQL Administration tool WinMySQLadmin Assume you have created a database named test then you can setup a data source with it First click Add button in the Create New Data Source window figure 2 8 In the Create New Data Source window select MySQL in the driver list and click Finish as seen in figure 2 13 DBTool User Manual Create New Data Source ED Select a driver for which you want to set up a data source Figure 2 13 Add a MySQL ODBC data source Then the MySQL ODBC data source configuration window shows up as in figure 2 14 Type in the DSN name for example testmysql and the MySQL server host name IP localhost or your IP or domain name and the MySQL database name test which is mentioned above This is in public domain and comes with NO WARRANTY of any kind Enter a database and options for connect
14. base Database object members Data source name atestaccess 12 DBTool User Manual User name Password handle 0 gt gt 3 2 Open a recordset We have successfully opened a database and the database information is saved in the dbase object db Now type the following command rs rset db select from mytable This will open a recordset where db is the dbase object and the following string is a SQL command In this case the SQL command select from mytable selects all fields in the table mytable Of course you can use any other valid SQL commands to select different fields with some special conditions This command will produce the following output gt gt rs rset db select from mytable Recordset object members handle 1 field count 7 handle of database 1 connect sql string select from mytable field names ID Name Sex gt Age City Date Photo field types long string string short string date blob gt gt The object rs has some properties handle is the handle of the recordset field count indicates there are 7 fields selected in the table handle of database indicates the handle of the corresponding dbase object connect sql string shows the SQL command string of this query The following is field names and field types for all the fields RY If the statement is followed by a there will be on output The output are produce
15. d a folder selection dialog shows up as seen in figure 2 3 Browse for Folder 2 x Add Folder to Path Figure 2 3 Select the DBTool folder Select the folder dbtool and click OK then the Add Folder dialog has the dbtool folder on the top of the list as seen in figure 2 4 DBTool User Manual Set Path 10 x All changes take effect immediately MATLAB search path Add Folder Ma CiMATLABttoolboxidbtoo Add with Subfolders Move to Top Move Up Remove Move Down Move to Bottom PRERFRPRPRRERPRPRER Save hI Close Revert Default Help Figure 2 4 DBTool folder added to MATLAB search path Click Save and then Close and the setup of MATLAB search path is finished 2 2 Standard professional and redistributable version installa tion The standard professional and redistributable version of DBTool are also provided in dbtool zip But you need a license file named license dat to make the non demo version activated 2 2 1 Unpack dbtool zip Unpack all the files in dbtool zip to a folder using WinZIP just as the same as installing the demo version 2 2 2 Copy the license file The license file license dat will be emailed to you as an attachment Just copy the license file into the folder of DBTool as seen in figure 2 5 DBTool User Manual C MATLAB toolbox dbtool 14 object s 295 KB E My Computer Ui Figure 2 5 Install non demo version of DBTool 2 2 3 Update
16. d by the object rs 3 3 Navigating in the recordset Although a SQL query can return several rows of data rset in the old version of DBTool was designed to fetch only one row of data each time This is because when the table is very large reading in the whole table is a waste of memory and time especially when the data source is 13 DBTool User Manual on another machine of the network it may take a long time to load the whole table via the network On the other hand through the ODBC API there is no way to find the total number of rows in a recordset directly But since DBTool version 2 0 multi rows fetching is available this is implemented by calling movenext internally in dbtool dll until the last row is reached or enough rows has been collected and it s pretty faster than calling movenext in m program A method is needed to access different rows for the object rset this is called navigating We can use the following 4 commands to navigate in the recordset e movefirst Moves to the first row e movelast Moves to the last row e movenext Moves to the next row e moveprev Moves to the previous row For example we use the movenext to navigate to the second row because when the recordset is opened it s indicated to the first row gt gt movenext rs gt gt By using movenext or moveprev continuously you can navigate through all the rows of the recordset When movenext or moveprev returns
17. ects 5 2 1 2 hdb The property hdb is the handle of the associated dbase object 5 2 1 3 sql The property sql is the SQL command issued to create the recordset 5 2 1 4 field The property field is a cell array to store the data of a row of the recordset 5 2 1 5 fieldname The property fieldname is a cell array of string to store the field name of the row 26 DBTool User Manual 5 2 1 6 fieldtype The property fieldtype is a cell array of string to store the field type name of the row Available field types are e bool e Char e short e long single e double e date e string e blob 5 2 1 7 fieldcount The property fieldcount is the number of the fields 5 2 2 Methods 5 2 2 1 rset Database recordset object constructor Creates a rset object from the dbase object db and SQL string sql Calling convention rset rset db sql type options blobsize Input db dbase object sql database connect string type open type see below optional options open options see below optional blobsize set the maximum size of BLOB MEMO fileds in KB optional Return rset object Example rset rset db select rset rset db select rset rset db select rset rset db select from mytab type options from mytab from mytab 0 from mytab 2048 27 DBTool User Manual Open Type O dynaset uses SQLExtendedFetch keyset driven cursor 1 sn
18. etup dialog is finished o 9 2 13 Add a MySQL ODBC data source LL 10 2 14 MySQL ODBC data source configuration window oo 10 2 15 ODBC Setup with 2 new data sources e e eee eee 11 O GO 0 44 9 DUKE a 41 Show GIF extracted from BLOB se sa e 5 664 RR e a RRR RR RR T R A 21 42 Data extracted from the BLOB field gt o s soe sa a nba nb NR R we 23 Chapter 1 Introduction 1 1 What s DBTool Besides Database Toolbox released by Mathworks DB Tool is another choice to access database from MATLAB 1 2 DBTool features The kernel of DBTool is a mex file named dbtool dll which is written and compiled in Visual C using the MFC classes CDatabase and CRecordset It s reliable and runs faster than Database Toolbox which is implemented in Java And then a set of m files are written to wrap it into two MATLAB class object dbase and rset MATLAB Database Toolbox does not support binary large object BLOB fields while DBTool can read and write BLOB fields freely For BLOB fields the contents are treated as byte streams All the bytes are read into an MATLAB 1 N double array each element stores a byte And any 1 N double array elements must be 0 255 integer can be written into a BLOB field DBTool has the following features e Faster and reliable easy to use Implemented using MATLAB Class objects Designed to access any database which has an ODBC interface Designed to access a
19. gt gt gt gt gt gt gt db dbase testaccess execsql db create table pet name CHAR 20 birth DATE execsql db drop table pet close db To verify the creating and droping of the new table pet open the file test mdb in Access to check it 4 5 Date Time field We have seen the Date field of the table is a type of date time but when we display the data in xs or xc there is only a number gt gt xs fields rs xs gt gt ID 4 Name Mike Sex male Age 25 City New York Date 7 3123e 005 Photo 1x5099 double How to read the time stored in the field xs Date We can use the MATLAB command datastr to convert it into a string 18 DBTool User Manual gt gt datestr xs Date 10 Jan 2002 14 44 00 gt gt Similarly use MATLAB command datenum to convert time into number And the current time can be obtained by MATLAB command now 4 6 BLOB field 4 6 1 What is BLOB DBTool supports BLOB fields BLOB is binary large object Using BLOB user can store large binary or text data with variable length The data stored as BLOB can be managed by user or by the database manager such as Access In Access BLOB fields are called Packages which can store OLE objects or embed files directly into the table 4 6 2 Reading file from BLOB field of Access database The table mytable in database test mdb has a field named Photo which is a Package Every P
20. hich have rset objects opened else there will be a warining message If you forget which objects are opened just use the command closeall gt gt closeall This will close all opened rset and dbase objects In addition when MATLAB is terminated all rset and dbase objects are closed automati cally this feature is depended on the ODBC driver 15 Chapter 4 Using DBTool 4 1 Editing row To edit an existing row we should use the update method with the following format update rs xc This will update the data of the current row using cell array xc Notice that only cell array is supported in updating structure is not supported The field values are stored in the cell xc the number and sequence of the fields in xc must be the same as in the recordset For example gt gt db dbase testaccess gt gt rs rset db select Name Age from mytable gt gt xc fieldc rs Mike 25 gt gt xc 2 26 gt gt update rs xc gt gt xc ficelde rs xc Mike 26 gt gt close rs gt gt close db gt gt Multi rows updating is not supported 16 DBTool User Manual 4 2 Inserting row Use insert method to insert rows into the recordset insert method is used in the following format insert rs xc For example we copy the data of the first row change the name and age and insert it back into the recordset gt gt db dbase testaccess
21. hoto field is embeded with a gif file There is no difference to read the data from the BLOB fields than other fields For example gt gt db dbase testaccess gt gt rs rset db select from mytable gt gt xs fields rs ID 1 Name Mike Sex male Age 26 City New York Date 7 3123e 005 Photo 1x5099 double gt gt We can see xs Photo is a 1x5099 double array each double word only contains a byte Actually it has a picture file named Abra gif embeded The file Abra gif is 1165 bytes other bytes are OLE information inserted by Access We can find the file content of Abra gif by searching the string GIF8 this is the beginning string of an ordinary GIF file 19 DBTool User Manual gt gt pack xs Photo gt gt offset findstr pack GIF8 offset 184 gt gt And the 4 bytes before the string GIF8 is the length of the file gt gt pack 184 4 184 1 ans 141 4 0 0 gt gt So the length of the file can be calculated gt gt len 141 256x 4 len 1165 gt gt Now we can extract the content of the file and save it to disk gt gt dat pack offset offset len gt gt fout fopen temp gif wb gt gt fwrite fout dat gt gt fclose fout gt gt Now we can open the file temp gif by the Windows Explorer or read it into MATLAB and plot it gt gt xxx map imread temp gif gt gt imshow xxx
22. l 5 2 2 13 display Display rset class members Calling convention display rs or rs Input rs rset object Return Print the properties of the rset object 5 3 Utilities 5 3 1 word2byte Convert 16 bit signed word series into unsigned byte series The lower byte is first and the higher byte is the second For example 0x1234 0x2345 0x3456 0x4567 is converted into 0x34 0x12 0x45 0x23 0x45 0x34 0x67 0x45 This function is used when storing 16 bits signed word array into BLOB fields of the table Calling convention y word2byte x Input x 1xN signed word array range in 32768 32767 Return y 1x2N unsigned byte series 5 3 2 byte2word Convert unsigned byte series into 16 bit signed word series The lower byte is first and the higher byte is the second For example 0x34 0x12 0x45 0x23 0x45 0x34 0x67 0x45 is converted into 0x1234 0x2345 0x3456 0x4567 This function is used when reading unsigned byte series from BLOB fields of the table and restoring 16 bits signed word array from it Calling convention y byte2word x Input x 1x2N unsigned byte series Return y 1xN signed word array range in 32768 32767 32 DBTool User Manual 5 3 3 num2byte Convert double array into unsigned byte series Because a double number is represented by 8 bytes this function unpack the 8 bytes of the elements in the double array This function is used before storing double array
23. num2byte x gt gt inserti rs xc gt gt xs fields rs xs name x 2 rows 3 cols 4 data 1x96 double gt gt y byte2num xs data gt gt y reshape y xs rows xs cols y 0 4447 0 9218 0 4057 0 4103 0 6154 0 7382 0 9355 0 8936 0 7919 0 1763 0 9169 0 0579 gt gt close rs gt gt close db gt gt 4 6 4 Writing and reading BLOB data into MySQL table In this section we give an example of accessing BLOB data with MySQL First create a table named arrays with a field name to save the array s name and a field data to save the array s binary data and the field id is used as the key gt gt db dbase testmysql gt gt execsql db create table arrays id int 10 default 0 not null name char 20 data blob primary key id Now insert a new row into the table manually gt gt execsql db insert into arrays id name data values 0 abc 0 Then open a recordset and write data into the table gt gt rs rset db select from arrays gt gt xc fieldc rs XC 0 abc 48 gt gt 22 DBTool User Manual The data field is 48 this is because 0 is regarded as a character not a number and the ASCII code of 0 is 48 in decimal Now we change the content of xc and update the data in the recordset and then close it gt gt data fix 127 127 sin 1 512 512 2 pi gt gt xc 3 data gt gt update rs xc
24. ny data types including binary large object BLOB Multi rows featching in one statement Directly execute SQL statements e Automatic close all database when MATLAB is closed many others 1 3 Order DBTool DBTool is shareware The demo version of DBTool is free for use without time limitation but the demo version has 3 limitations e Multi rows fetch not enabled DBTool User Manual e You can open only 1 database and 1 recordset each time e The length of BLOB fields is limited to 8192 bytes Besides demo version the standard professional and redistributable version are also avail able The demo standard and professional versions are for personal use only For commercial use please buy the redistributable version Features and prices for different versions are listed in the table below Version Demo Standard Professional Redistributable BLOB Size 8k 256K Unlimited Unlimited dbase objects 1 16 16 16 rset objects 1 16 16 16 multi rows fetch no no yes yes price free USD30 USD60 USD300 If you have a PayPal account please go to the DBTool registration page http energy 51 net matlab share htm and click the PayPal icon in the price table to register If you do not have a PayPal account you can also click the icon you will be guided to setup a new PayPal account for free If you can t pay with PayPal please send the registration fee to BANK OF CHINA BEIJING
25. oe ey AS eee a ee a A ee 17 A4 Exececute SOL directly 2 26445 na Rw Re ba Se eS 18 d Date Time beld cs cor ae OS ARES B S a eS RS 18 db BLOB feld eo eke wee we ee See ae a ee SE ao ee be 6 19 LL Wian e BLOD ok nk dae we Lem Se Par BORG RA ENS 3 19 4 6 2 Reading file from BLOB field of Access database 19 4 6 3 Storing double array in Access e e 21 4 6 4 Writing and reading BLOB data into MySQL table 22 ii DBTool User Manual 5 References 24 ol VIAS scans aa a da ee PAP be e Med AA 24 A A III ee ge a a Be aa 24 BALL Mande ook ak kK ee a ag an a a a ds 24 Ed OW 2 tox foe rs ee oe ee ls ee wo ey 24 5 TH usa rr ok e ia e ke Tana 24 TAIA WOM i a ee oh EY ok ao a ae Se a el a ap an 24 Pele Ta O Ae oe ee ee we 24 A 1 Se ee Se ee A ER ee Re ae we 24 lets CE aa on Sah ee A Re 25 c EROS oscar a ee ee Se oe ee Ae 25 ce AUS or AS Ok Re Aon em Bode Ge Bere ae ade wd Ghee 26 ae MEP aoi A a oh ee my wp Se hee eee ks A Se HOS Sow e aa de Ma da 26 Died Propor eS o ew RA DERE a eA RR Ew 26 EM A A 26 TAr MOI rra rada e e EG dal 26 A Ol oa how ba GS hems eee Se RGR RGR Se ARS OES 26 aid Held vacias he ad ew ea ed ea Bah a 26 Kel DEIA 4 624 546 00 e dla 2 aad a 26 BELO heldtype sesers mn ere riha a na 27 52 17 fedoun 2 0 ke ee ee a 27 5 2 2 Methods lt lt ea K RR RR eB ana 27 A SR ccf Gee na an Ok ee ee ee ge ee eet BO Tai 27 a OO ee Bow a ek ete Tan ek ee a ee oe e
26. s into BLOB fields of the table Calling convention y num2byte x Input x double array in any dimention could be 1xN Nx1 MxN MxNxP etc Return y 1xN unsigned byte series The length of y is 8 times of N which is the total number of elements in x 5 3 4 byte2num Convert unsigned byte series back into 1xN double array where N is 1 of 8 of the size of input This function is used to restore double arrays from BLOB fields of the table Since the dimension information is not saved int the BLOB fields a reshape function must be used to restore dimension Calling convention y byte2num x Input x 1xN double array contains unsigned char values 07255 Return y 1xN 8 double array restored 33
27. ser Manual dsn string of data source name uid string of username pwd string of login password options optional default to 8 Other values 2 Open database read only 4 Use ODBC cursor lib 8 Don t display ODBC Connect dialog default 16 Always display ODBC connect dialog Sum up the options needed or leave blank to use default 8 For example use ODBC cursor lib and don t display ODBC Connect dialog then options should be 4 8 12 Return db dbase object Example db dbase testaccess db dbase testaccess 12 5 1 2 2 close Close an ODBC database Calling convention ret close db Input db a database object Return 1 Success O Failure maybe already closed 1 Failure not all recordsets closed 5 1 2 3 execsql Execute a SQL string Calling convention ret execsql db sql Input db dbase object sql string of SQL command Return 1 Success 0 Fail 25 DBTool User Manual 5 1 2 4 display Display ODBC database members Calling convention display db or db Input db dbase object Return Print the properties of the database object 5 2 rset 5 2 1 Properties The properties of class object rset can t be accessed directly The only way to read it is by using the method display 5 2 1 1 handle The property handle is the handle of the recordset and used to distinguish bewteen different rset obj
28. y 28 Bode WEIR e bo Se ek Pe aS ara PE Wb Re ee BE 28 A Tel 2 2 eed See ee RS ER ee eRe ee a E 29 Gece IONES caca ra ae RE RR Au KOBAR a AA 29 ALZ Moni eeren a aw oR a we a a Be a 29 D227 T T ap kk da butaa e eee ee ke we a 30 Pome REDES e ek A wR doe bee ee eee ole eo 30 ca A ete lg de ads Ries de a owe ge a te wm Se we Ho 30 Ao Al erT oe ay u ee om ER Ree mw ee wR ew 31 Oe WP once ara A He SS 31 Sas ll Sele 2 26546 ke Se EASE ERE Re REA Re Re Re 31 Dada USDA ea bo Ge EME RO be Aa EE Be 32 Sco LUCES 2 2 nk eee Senda oe Bee REA eb eR Ea ee ER ee ee es 32 Dal Wordebytes 42424 54264 ea eho ae eh eR SR ee ee ee 32 Daa e op oa ae oe ms GR he a a a bts Bk a a an aa mo 32 e WYO 6 mo aoe he an an RR Sw ee ee AL dan a 33 Bee re TUN A os ek PO aa ee eB ee BE bui 33 iii List of Figures 2 1 Install deme version of DBTool ooo maa a a an R N 22 The Set Pati dala occ ca a ed A a AA A in 23 select the DB Tool folder gt io e ee a a a ee BU 2 4 DBTool folder added to MATLAB search path 0 o 2 5 Install non demo version of DBTool 2 6 The Control Panel window ia 2 7 The Administrative Tools window 2 8 The ODBC Data Source Administrator window 2 9 The Create New Data Source window o 2 10 The ODBC Microsoft Access Setup dialog o e 2 11 Delect an Access MS oe coda oe E ee Bat A ee 2 12 ODBC Microsoft Access S
Download Pdf Manuals
Related Search