Home

LabWindows/CVI SQL Toolkit Reference Manual

Contents

1. If you decide to install the ODBC Database Driver files into a directory other than a directory specified in your current PATH statement you must add your new driver directory to your PATH statement in your AUTOEXEC BAT file You must then reboot your computer for the new path to take effect Note The LabWindows CVI SQL Toolkit ODBC Database Driver files consist of Closed Drivers in that they can only be used by LabWindows CVI The installation program will install two driver files QECD LIC and QELIB DLL that enable the LabWindows CVI SQL Toolkit to use the supplied Closed Drivers Some older versions of Microsoft Excel and some other database products may also use their own versions of these driver files This may prevent the LabWindows CVI SQL Toolkit from functioning properly If an older QEL1B file exists on your system and it occurs earlier within the defined search path for DLLs you may have to remove it or place the ODBC Database Driver files earlier with your PATH statement Overview The LabWindows CVI SQL Toolkit is an add on package for accessing databases The toolkit contains a set of high level functions for performing the most common database tasks The following list describes the main features of the LabWindows CVI SQL Toolkit e Works with any database driver that complies with Microsoft s Open Database Connectivity ODBC standard e Maintains a high level of portability In man
2. drop space result dropspace WHERE meas meas2 WHERE meas lt gt meas2 WHERE meas gt meas2 WHERE meas lt meas2 WHERE uut_num IN 2860A123 2860A1234 WHERE result NOT IN FAILED RETEST WHERE uut_num ANY SELECT WHERE measl BETWEEN 0 0 AND 1 0 WHERE EXISTS SELECT WHERE uut_num LIKE TEK continues A 5 LabWindows CVI SQL Toolkit Manual SQL Reference Appendix A Table A 4 SQL Operators Continued Operator Class Description Examples and Operators Comparison Existence of at least WHERE uut_num NOT NULL cont one row character NOT NULL pattern match empty Date Add subtract testdate 5 result new date testdate 2 8 60 result number of days Logical O Precedence WHERE res1 AND res2 OR res3 AND NOT Negation res4 AND WHERE NOT uut_num IN SELECT OR Or WHERE meas1 lt 0 0 AND meas gt 1 0 WHERE meas1 lt 0 0 OR meas2 lt 1 0 Set UNION Set of all rows from all SELECT UNION SELECT individual distinct queries Other All columns SELECT FROM testres COUNT Count of all rows SELECT COUNT FROM testres DISTINCT Only non duplicate SELECT DISTINCT FROM rows LabWindows CVI SQL Toolkit Manual A 6 National Instruments Corporation Appendix A SQL Reference SQL Functions Table A 5 lists the functions you can use in SQL stat
3. Chapter 4 SQL Toolkit Function Descriptions DBCreateRecord int resCode DBCreateRecord int statementHandle Purpose Creates the buffer that will be used for a new record All column values are initially set to NULL You can put values into the buffer by copying the values into bound variables You can then insert the record in the database by calling DBPut Record Parameters statementHandle _ integer Handle to the SQL statement returned by DBActivateSQL or DBActivateMap Return Value Result code See DBError for a list of result codes DBCreateTableFromMap int resCode DBCreateTableFromMap int mapHandle char tableName Purpose Creates a database table based on a map This process involves constructing a SQL CREATE TABLE statement from the map and table name and then executing the statement Parameters mapHandle integer The handle to the map returned by DBBeginMap tableName string Name of the database table to which the map will be applied Return Value Result code See DBError for a list of result codes National Instruments Corporation 4 17 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBDatabases int statementHandle DBDatabases int connectionHandle Purpose Creates and activates a SELECT statement that returns information about the available databases on a connection You can then use the DBBindCol and DBFetch functions to retri
4. DBColumnName char columnName DBColumnName int statementHandle int columnNumber Purpose Returns the name of a column Parameters statementHandle integer Handle to the SQL statement returned by DBAct ivateSQL or DBActivateMap columnNumber integer The column number for which the name will be returned The first column number is 1 Return Value columnName string Pointer to the returned column name The SQL Toolkit maintains a buffer that stores the string You must copy the string out of this buffer before you call another toolkit function because the next function may use the same buffer The nn column name is if the column is an expression in the SQL statement O National Instruments Corporation 4 13 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions DBColumnType Chapter 4 int dataType DBColumnType int statementHandle int columnNumber Purpose Returns the SQL Toolkit data type for a column in the SQL SELECT statement Notice that this data type may not be the same as the type in the underlying database Parameters statementHandle columnNumber integer Handle to the SQL statement returned by DBAct ivateSQL or DBActivateMap integer The column number for which the data type will be returned The first column number is 1 Return Value dataType integer LabWindows CVI SQL Toolkit Manual The returned data type Possible values are
5. DBError void Purpose Returns the text associated with the error or warning generated by the last SQL Toolkit function you called Parameters None Return Value errorMessage string Pointer to the returned error message The SQL Toolkit maintains a buffer that stores the string You must copy the string out of this buffer before you call another toolkit function becuase the next function may use the same buffer LabWindows CVI SQL Toolkit Manual 4 22 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBF etchNext int resCode DBFetchNext int statementHandle Purpose Retrieves the next record from the database The program places the column values in the variables previously specified by the variable binding or mapping functions You can use DBFetchNext with either automatic SQL or explicit SQL When DBFetchNext attempts to fetch a record beyond the last record returned by the SELECT statement it returns a result of DB_EOF 5 Parameters Input statementHandle integer Handle to the database connection returned by DBConnect Return Value Result code See DBError for a list of result codes O National Instruments Corporation 4 23 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBF etchPrev int resCode DBFetchPrev int statementHandle Purpose Retrieves the previous record from the database The program places the column v
6. GROUP BY must uut_num HAVING be specified first measl lt 0 ORDER BY sort_expr SELECT Specifies row order in SELECT FROM DESC ASC the active set of rows testres ORDER BY uut_num DESC FOR UPDATE OF SELECT Locks columns in SELECT FROM col_name col_name selected rows for testres FOR updates for deletion UPDATE OF measl meas2 LabWindows CVI SQL Toolkit Manual A 4 National Instruments Corporation Appendix A SQL Operators SQL Reference Table A 4 lists the operators you can use in SQL statements Table A 4 SQL Operators Operator Class Description Examples and Operators Constants Numeric constant Character constant Date time constant LE Logical constant Numeric O Operator precedence Sign E Multiply divide Add subtract ERN Exponentiation M NN NN Character Concatenate keep trailing blanks Concatenate drop trailing blanks Comparison Equal Not equal Greater than or equal Less than or equal ANY ALL BETWEEN EXISTS NOT LI Compare with list of rows Within value range National Instruments Corporation Contained in the set 1234 1234 5678 PASSED CVI 2 8 60 16 59 59 Es measl meas2 meas3 meas4 meas meas meas2 measl meas2 meas1 meas2 meas1 meas2 meas1 power meas1 2 keep space result keep space
7. 3 11 statements SQL See SQL statements Structured Query Language SQL See SQL Structured Query Language T tables data types supported by SQL Toolkit table 2 2 deleting 3 8 purpose and use 2 1 technical support D 1 third party ODBC Database Drivers 2 4 time format strings table C 2 to C 3 transaction functions DBBeginTran 3 10 4 7 DBCommit 3 10 4 15 DBRollback 3 10 4 34 function tree table 4 2 purpose 4 3 transactions 3 10 U UPDATE command 2 2 A 2 updating records 3 7 See also insert update delete records functions W Warnings See DBWarning function WHERE clause SQL table A 3 LabWindows CVI SQL Toolkit Manual
8. 5 WHERE UUT_NUM 2860B456 if hstmt 0 goto Error Other operations in the transaction Ask if user wants to commit the transaction response ConfirmPopup Transaction Example Commit the transaction if response 1 make the changes permanent DBCommit hdbc else discard the changes DBRollback hdbc Error Checking The SQL Toolkit functions return one of three types of values result codes handles and data You can compare a result code with DB_SUCCESS to determine if an error occurred Handles refer to such items as database connections or activated SQL statements If a function returns a handle a value of zero indicates an error You can then call DBError to determine the error number You can also call DBErrorMessage to get the text of the error message For functions that return data such as DBColumnName you should call DBError to determine if an error occurred hdbc DBConnect hdbc if hdbc 0 errorCode DBError errorMsg DBErrorMsg printf Error number d n s n errorCode errorMsg LabWindows CVI SQL Toolkit Manual 3 10 National Instruments Corporation Chapter 3 Using the SQL Toolkit Creating A Standalone Executable File When executing a LabWindows CVI standalone executable file that contains functions from the SQL Toolkit Instrument Driver you must copy the instrument driver s
9. Toolkit table 2 2 database sessions 2 5 to 2 6 National Instruments Corporation activating SQL statements 2 5 connecting to databases 2 5 3 2 deactivating SQL statements 2 6 disconnecting from databases 2 6 processing SQL statements 2 5 to 2 6 steps in database sessions 2 5 databases concepts 2 1 to 2 2 data types supported by SQL Toolkit table 2 2 tables 2 1 date operators SQL table A 5 date time format strings table C 2 to C 3 DBActivateMap function activating maps 3 3 description 4 4 DBActivateSQL function description 4 5 example 3 4 DBAllowFetchAnyDirection function description 4 6 example 3 5 DBBeginMap function description 4 6 example 3 2 DBBeginTran function description 4 7 example 3 10 DBBindColChar function description 4 8 example 3 4 DBBindColDouble function description 4 9 example 3 4 DBBindColFloat function description 4 10 example 3 4 DBBindCollInt function description 4 11 example 3 4 DBBindColShort function description 4 12 example 3 4 DBColumnName function description 4 13 LabWindows CVI SQL Toolkit Manual Index example 3 9 DBColumnType function description 4 14 example 3 9 DBColumn Width function 4 15 DBCommit function description 4 15 example 3 10 DBConnect function description 4 16 example 3 2 DBCreateRecord function description 4 17 example 3 6 DBCreateTableFromMap function 4 17 DBDatabases function description 4 18 pur
10. a result code for example DBColumnName Parameters None Return Value errorCode integer Result code of the last SQL Toolkit function called Possible values are DB_HSTMT_BUSY 7 Returned when a thread of execution attempts to use a statement handle that is currently in use by another thread of execution DB_LOCK_NO_REC 6 Attempted a lock but either no record was selected by the primary key the record was deleted by another user or another user changed the value of a key field DB_EOF 5 EOF returned by DBFetchNext DBFetchPrev or DBFetchRandom when there is no record to return DB_USER_CANCELED 4 User canceled out of the box DB_OUT_OF_MEMORY 3 Not enough memory in Windows This is usually fatal DB_SUCCESS 0 Success DB_SUCCESS_WITH_INFO 1 Success with warning continues National Instruments Corporation 4 21 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 Return Value Continued errorCode integer DB_NO_DATA_WITH_INFO 2 EOF with additional information usually occurs if you hit ESC during a fetch DB_DBSYS_ERROR 4 Database system error DB_LIBSYS_ ERROR 5 Returned when the system cannot locate the DataDirect Dynamic Link Library lt other errors gt 1000 Call DBErrorMsg to get the text of the error message DBErrorMessage char errorMessage
11. do not need to use formatting use the empty string as the format string See Appendix C Format Strings for a description of format strings You must bind all columns in the SELECT statement to variables and you must bind the columns in the order they appear in the SELECT statement When finished with the SQL statement call for DB DBI DeactivateSQL consists of the statement handle from DeactivateSQL to free system resources The only parameter DBActivateSOL Execute a SELECT statement hstmt DBActivateSQL hdbc SELECT UUT_NUM MEAS1 MEAS2 FROM TESTRES WHERE MEAS2 gt 1 0 if hstmt 0 goto Error Bind the columns in the SELECT statement to program variables resCode DBBindColChar hstmt 1 11 uutNum suutStat if resCode DB_SUCCESS goto Error resCode DBBindColDouble hstmt 2 amp measl amp measlStat if resCode DB_SUCCESS goto Error resCode DBBindColDouble hstmt 3 amp meas2 amp meas2Stat if resCode DB_SUCCESS goto Error Fetching or other operations resCode DBDeactivateSQL hstmt if resCode DB_SUCCESS goto Error If the SQL statement does not require further processing in other words most non SELECT DB statements you can use LabWindows CVI SQL Toolkit Manual ImmediateSQL to activate and deactivate the stateme
12. does not have a separate error code DBNumberOfColumns int numCols DBNumberOfColumns int statementHandle Purpose Returns the number of columns selected by a SQL SELECT statement Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL or DBActivateMap Return Value LabWindows CVI SQL Toolkit Manual 4 32 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBNumberOfModifiedRecords int numModRecs DBNumberOfModifiedRecords int statementHandle Purpose Returns the number of records updated by the last function that changed the database Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL or DBActivateMap Return Value numModRecs integer Number of modified records Returns 0 if the statement is nota SELECT statement DBNumberOfRecords int numRecs DBNumberOfRecords int statementHandle Purpose Returns the number of records chosen by the SELECT statement To determine the number of records selected the SQL Toolkit fetches all rows from the result set If you have not enabled backward fetching calling DBNumbe rOfRecords causes an error to be returned If you have selected a large number of records this function may work slowly and create large temporary log files Parameters statementHandle integer Handle to the SQL statement returned by DBActiv
13. in bytes Pointer to the variable that will to receive the null terminated character string value for the column when the program fetches a record Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_ TRUNCATION 1 or DB_NULL_ DATA 2 Used to control formatting for dates and numbers Result code See DBError for a list of result codes LabWindows CVI SOL Toolkit Manual O National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBBindColDouble int resCode DBBindColDouble int statementHandle int columnNumber double locationForValue int locationForStatus Purpose Specifies the value and status variables in your program that will receive the column s value and length each time the program fetches a record You must bind all columns in the SELECT statement You must bind the columns in the order in which they occur in the statement Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL columnNumber integer The column number containing specified variables The first column number is 1 locationForValue Pointer to the variable that will receive the double value for the column when the program fetches a record locationForStatus integer Pointer to the va
14. letter abbreviation initial cap Day of the month s three letter abbreviation uppercase Day of the month s full name lowercase tuesday Day of the month s full name initial cap Tuesday Day of the month s full name uppercase TUESDAY Last two digits of year 60 Four digit year 1960 Hour of the day without leading zero use am pm 12 5 symbol for 12 hour style Hour of the day with leading zero use am pm symbol 12 05 for 12 hour style Minute of the hour without leading zero 57 5 Minute of the hour with leading zero 57 05 continues LabWindows CVI SQL Toolkit Manual C 2 O National Instruments Corporation Appendix C Format Strings Table C 2 Symbols for Date Time Format Strings Continued Example Symbol Description Output S Second of the minute without leading zero 57 5 SS Second of the minute with leading zero 57 05 SS SSSSSS Second of the minute with fractional seconds up to six 57 123456 s symbols after the decimal point am pm am or pm string lowercase forces 12 hour clock am AM PM AM or PM string uppercase forces 12 hour AM clock 6699 6699 a or p string forces 12 hour clock A or P string uppercase forces 12 hour clock Julian value for date time Output the character lt space gt lt character gt Output the character following the V character G M T is GMT lt string gt Output the s
15. name and remarks If you use DBDatabases with a flat file database the program will not return any records e DBTables returns information about the available tables Its parameters contain the connection handle a qualifier pattern a user name pattern a table name pattern and a flags parameter You can use or in the patterns to match with anything The flags parameter chooses the type of table The SELECT statement returns the table qualifier table user table name table type and remarks The following example puts the table names from DBTables into a list box You can find this example as well as examples of DBSources and DBDatabases in pick_src c hstmt DBTables hdbc 2 2 2 DB_TBL_TABLE if hstmt lt 0 goto Error resCode DBBindColChar hstmt 1 127 qual amp qualStat if resCode DB_SUCCESS goto Error resCode DBBindColChar hstmt 2 127 user amp userStat if resCode DB_SUCCESS goto Error resCode DBBindColChar hstmt 3 127 name amp nameStat if resCode DB_SUCCESS goto Error resCode DBBindColInt hstmt 4 amp type amp typeStat if resCode DB_SUCCESS goto Error resCode DBBindColChar hstmt 5 255 rem amp remStat if resCode DB_SUCCESS goto Error whil resCode DBFetchNext hstmt DB_EOF if resCode DB_SUCCESS ShowError goto Error i
16. recordNumber integer The record number to be read The first record is 1 Return Value Result code See DBError fora list of result codes National Instruments Corporation 4 25 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBImmediateSQL int resCode DBImmediateSQL int connectionHandle char SQLStatement Purpose Executes a SQL statement immediately Equivalent to DBAct ivat eSQL followed by DBDeactivateSQL This function is useful for any SQL statement that does not require further processing such as CREATE TABLE INSERT and UPDATE Because this function also ends statement execution it is not useful for SELECT statements Parameters connectionHandle integer The handle to the database connection returned by DBConnect SQLStatement string The SQL statement to be executed Return Value Result code See DBError for a list of result codes LabWindows CVI SQL Toolkit Manual 4 26 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBMapColumnToChar int resCode DBMapColumnToChar int mapHandle char columnName int maxLen char locationForValue int locationForStatus char formatString Purpose Specifies a column to be selected and the value and status variables that will receive the column s value and status each time the program fetches a record Parameters mapHandle integer Handle to t
17. the toolkit Columns must be retrieved in increasing order The value of a column can only be retrieved once Maximum length parameter of DBBindColToChar and DBMapColToChar must be zero if the underlying data type is not a character string Attempt to bind column after records have been fetched All columns in the SELECT statement must be bound to variables Connected database does not support transactions Attempt to commit or rollback a transaction without beginning a transaction Transaction already active Attempt to execute SQL statement without specifying SQL statement Tracing already active Invalid trace filename Tracing is not active Attempt to call DBA11owFetchAnyDirection after binding variables or fetching records Attempt to fetch previous or random record without enabling fetching in any direction Function is not valid for non SELECT statements Evaluation copy expired Function is not valid if there is no active record Evaluation copy will expire within 15 days You cannot change this column s value Attempt to get column attribute that does not exist for this table Dictionary query is not allowed for this function Invalid option specified Use DBErrorMessage for more information Statement has not been executed or is not positioned on a row Row to be locked has changed Multiple rows locked continues LabWindows CVI SOL Toolkit Manual B 4 O National Instruments Corporat
18. 1 A fetched value was truncated because the value size exceeded the buffer LabWindows CVI SOL Toolkit Manual 4 40 O National Instruments Corporation Appendix A SQL Reference This appendix briefly explains SQL commands operators and functions This version of SQL is included in the ODBC standard and is applies to all ODBC compliant databases SQL Commands Table A 1 lists the SQL commands you can use with DBImmediatesSQL CREATE TABLE DELETE Table A 1 SQL Commands SQL Command Syntax Description Example CREATE TABLE table name column def column def DELETE table name WHERE where clause INSERT table_name options col_name col_name VALUES expr expr O National Instruments Corporation Drop Table table_name A 1 Creates a new database table Removes rows from a database table The where clause selects specific rows to delete Removes a database table Creates a new record places data values into its columns The VALUES clause specifies the values DBActivateSQL and CREATE TABLE testres uut_num char 10 NOT NULL meas NUMBER 10 2 meas2 NUMBER 10 2 DELETE testres WHERE measl lt 0 0 DROP TABLE testres INSERT testres uut_num measl meas2 VALUES 2860C890 0 4 0 6 continues LabWindows CVI SQL Toolkit Manual SQL Reference Appendix A Table A 1 SQL Commands C
19. 4 7 DB Bind Ol CWA o a as e los 4 8 DBBind olDouble iii AAA AAA 4 9 DBBindColElO timon de dis 4 10 DBBIRdCEO Mutant eae ate 4 11 DBBindCOL Dorama ia ooo ias 4 12 DBColumaN dle esineen e anrea E E EES caida 4 13 DBCOl MATY Pessin erenn EEE AA Ein 4 14 DECIAN WY TUE asis natina a a n ete 4 15 DBCOMMIt asenwisih adv na n a a dida 4 15 DBC GMC Ct irsi insierto tie acts le a ee aan auaa 4 16 DBCreateRe cord ai lia S R A ao Ae 4 17 DBCreateTableFromMap a 4 17 DBD abastos ln ae 4 18 WBC ACL abs PD S ange deacon ease eee 4 19 DBDe activateS OL A SEER Reise EAE I 4 19 DBDeleteRecordissisconk oll encnl ian sew alten E aariseah 4 20 DBDISCOMNECE Samanta sabia 4 20 DB EOL ia 4 21 DBBETOrMesSs Iustel e a ieaie 4 22 DBEFetehNEXt sanie a a tees E has 4 23 DISI rE A A TE toast ohh A E E ested 4 24 DBFetchRandoM incida diana 4 25 DBImmediates OL oie iai escola 4 26 DB MapColumin To Chicas di rilaan conocida opens 4 27 DB Map olumin ToDou Bless ina a 4 28 DBMapCotumntToBloat ont ada 4 29 DBMapC olumn Toit aii a Geta eax 4 30 DBMapColumm PoShort 2 2 5 5 c04 24 nse Gan AeA Gah eS aes 4 31 DBNativeEtTO E orita A A 4 32 DBNumberOfCoOlUIMNS ooooooooononcnnnnnnnnnnonnnnnananonononnnnnonononononnonannononnnnnnnos 4 32 DBNumberOfModifiedRecordS oococccnnonnoononnnonocnonnananananononcononnnnanono 4 33 DBNumberOfRECOFAS rrota cados a eera as ak 4 33 DBP utRecord usina ad EEEE A aS 4 34 DBRollback ci ia a A a a aa 4 35 DBSetDatabaS Osses il ad
20. B_S MapColu DB_S apColu DB_S UCCESS m m CCESS UCCESS the ta colu SOL SE mns to tiva teMap goto or o Deac if resCode You can also use DB_SUCCESS nToChar nToDouble nToDouble ECT stat the previously specified variables hmap Error hma goto h goto h goto ns to be selected and the Pr map for table testres variables where column UUT_NUM 11 uutNum Error map Error map Error MEAS1 amp measl amp measlStat MEAS2 amp meas2 amp meas2Stat ay ET men ESTRI th bind the A xecut statement peed ES tivateMap hmap goto ther operations Error DBCreateTableFromMap to create a table The toolkit will use the same parameters as DBActivateMap the map handle and the table name After creating the table you can still use the map with DBActivateMap For example you can use the map to create the initial records for the table You can find the following example in new_tabl prj begin map for constructed SOL statement hmap DBBeginMap hdbc if hmap lt 0 goto Error specify the columns to be selected and the variables where column values will be placed resCode DBMapColumnToChar hmap UUT_NUM 11 uutNum amp uutNumStat if resCode DB
21. DBError for a list of result codes O National Instruments Corporation 4 29 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBMapColumnToInt int resCode DBMapColumnTolInt int mapHandle char columnName int locationForValue int locationForStatus Purpose Specifies a column to be selected and the value and status variables that will receive the column s value and status each time the program fetches a record Parameters mapHandle integer Handle to the map returned by DBBeginMap columnName string The name of the containing specified variables locationForValue integer Pointer to the variable that will receive the int value for the column when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_TRUNCATION 1 or DB_NULL_DATA 2 Return Value Result code See DBError for a list of result codes LabWindows CVI SOL Toolkit Manual 4 30 O National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBMapColumnToShort int resCode DBMapColumnToShort int mapHandle char columnName short locationFor Value int locationForStatus Purpose Specifies a column to be selected and the value and sta
22. DB_CHAR 1 Fixed length character string DB_VARCHAR 2 Variable length character string DB_DECIMAL 3 Decimal Number BCD DB_INTEGER 4 Long Integer 4 byte DB_SMALLINT 5 Integer 2 byte DB_FLOAT 6 Floating point number 4 byte DB_DOUBLEPRECISION 7 Double precision floating point number 8 byte DB_DATETIME 8 Date time 26 byte char string 4 14 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBColumnWidth int colWidth DBColumnWidth int statementHandle int columnNumber Purpose Returns the width of a column The width consists of the size in bytes of the longest value that can be stored in the column Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL or DBActivateMap columnNumber integer The column number for which the width will be returned The first column number is 1 Return Value colWidth integer The size of the longest value which can be stored in the column in bytes DBCommit int resCode DBCommit int connectionHandle Purpose Saves all changes that you have made using the SQL statements INSERT UPDATE or DELETE since calling DBBeginTran You must call DBBeginTran to begin a transaction before you can call DBCommit to save all changes Parameters Input connectionHandle string The handle to the database connection returned by DBC
23. L_SYSTABLE 0x0008 System table names DB_TBL_SYNONYM 0x0010 Synonym names DB_TBL_DATABASE 0x0080 Database names Except for DB_TBL_ DATABASE you can combine these constants by adding them together or joining them with an or clause Returned handle to the statement execution This value identifies the statement and is a parameter statementHandle integer to other functions If 0 the statement could not be executed National Instruments Corporation 4 39 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBWarning int warningCode DBError void Purpose Returns the warning generated by the last SQL Toolkit or DataDirect function you called DBWarning is usually called after DBError to determine if the database system or the last function returned any warnings Parameters None Return Value resCode integer Result code either a warning code returned by the database system or one of the following The possible variables are DB_LOCK_CHANGE_REC 8 A lock was obtained but the record has been changed since 1t was originally read This occurs only for database systems requiring a log file DB_LOCK_MULTI_REC 7 A lock was obtained but more than one record was locked This occurred because the primary key fields caused more than one record to be selected DB_NULL DATA 2 The fetched value was NULL DB_TRUNCATION
24. LabWindows CVI SQL Toolkit Reference Manual May 1995 Edition Part Number 320960A 01 Copyright 1995 National Instruments Corporation All rights reserved National Instruments Corporate Headquarters 6504 Bridge Point Parkway Austin TX 78730 5039 512 794 0100 Technical support fax 800 328 2203 512 794 5678 Branch Offices Australia 03 9 879 9422 Austria 0662 45 79 90 0 Belgium 02 757 00 20 Canada Ontario 519 622 9310 Canada Qu bec 514 694 8521 Denmark 45 76 26 00 Finland 90 527 2321 France 1 48 14 24 24 Germany 089 741 31 30 Hong Kong 2645 3186 Italy 02 48301892 Japan 03 5472 2970 Korea 02 596 7456 Mexico 5 202 2544 Netherlands 03480 33466 Norway 32 84 84 00 Singapore 2265886 Spain 91 640 0085 Sweden 08 730 49 70 Switzerland 056 20 51 51 Taiwan 02 377 1200 U K 01635 523545 Limited Warranty The media on which you receive National Instruments software are warranted not to fail to execute programming instructions due to defects in materials and workmanship for a period of 90 days from date of shipment as evidenced by receipts or other documentation National Instruments will at its option repair or replace software media that do not execute programming instructions if National Instruments receives notice of such defects during the warranty period National Instruments does not warrant that the operation of the software shall be uninterrupted or error free A Return Material Authorization RMA n
25. LabWindows CVI SQL Toolkit Installation Install SQL Toolkit SQL Toolkit Install Directory CACVINDATABASE Database Driver Install Directory CAWINDOWS SYSTEM Database Drivers to Install Btrieve Paradox 4 5 HP ALLBASE SQL HP IMAGE SQL UJ Paradox 5 Figure 1 1 LabWindows CVI SQL Toolkit Installation Dialog Box When the installation dialog box appears on the screen you can change the default directories for the toolkit and the ODBC drivers The LabWindows CVI SQL Toolkit installation program contains the following two groups of installation files e SQL Toolkit files Contains library file function panels and LabWindows CVI example programs for database communication The default installation directory for the SQL Toolkit files is the DATABASE subdirectory within the LabWindows CVI base directory e ODBC Database Driver files Contains database specific DLLs required to communicate with each database The ODBC Database Driver files default installation directory is the WINDOWS SYSTEM directory The installation program also installs the following files in the Windows system directory ODBC DLL e ODBCINST DLL e ODBCCURS DLL e CTL3DV2 DLL LabWindows CVI SOL Toolkit Manual 1 2 O National Instruments Corporation Chapter 1 Introduction In addition the driver setup program modifies or creates CONTROL INI ODBC INI and ODBCINST INI in the Windows directory
26. Number 320960A 01 Please comment on the completeness clarity and organization of this manual If you find errors in this manual please record the page numbers and describe the errors Thank you for your help Name Title Company Address Phone Mail to Technical Publications Fax to Technical Publications National Instruments Corporation National Instruments Corporation 6504 Bridge Point Parkway MS 53 02 MS 53 02 Austin TX 78730 5039 512 794 5678 Index A activating SQL statements 2 5 3 2 to 3 4 automatic SQL maps functions activating SQL statements 2 5 DBActivateMap 3 3 4 4 DBBeginMap 3 2 4 6 DBCreateTableFromMap 4 17 DBDeactivateMap 4 19 DBMapColumnToChar 3 2 4 26 DBMapColumnToDouble 3 2 4 27 DBMapColumnToFloat 4 28 DBMapColumnTolInt 4 29 DBMapColumnToShort 4 30 function tree table 4 1 generating SQL statements 3 2 to 3 4 purpose 4 3 C character operators SQL table A 4 clauses SQL table A 3 to A 4 commands SQL 2 2 A 1 to A 2 comparison operators SQL table A 5 connecting to databases 2 5 3 2 connection functions DBConnect 3 2 4 16 DBDisconnect 3 2 4 20 DBSetDatabase 4 35 function tree table 4 1 purpose 4 3 constant operators SQL table A 4 CREATE TABLE command 2 2 A 1 creating standalone executables 3 11 customer communication x D 1 D Data Sources Dialog Box 2 3 data types supported by SQL
27. QL or DBImmediateSQL as in the following example from new_rec prj resCode DBImmediateSQL hdbc INSERT INTO TESTRES VALUES 2860B456 0 4 0 6 You can also insert a record with DBCreateRecord and DBPutRecord First you activate a map or SQL statement in the same manner as for fetching records You then call DBCreateRecord with the statement handle as the only parameter Next copy the desired values into the bound variables for the SELECT statement Finally call DBPut Record to copy the new record into the database You can find this example in new_rec prj Activate a map or SQL statement Create the new record resCode DBCreateRecord hstmt if resCode DB_SUCCESS goto Error Put values into the bound variables strcpy uutNum 2860B456 measl 07 meas2 1 1 Insert the record into the database resCode DBPutRecord hstmt if resCode DB_SUCCESS goto Error Updating a Record You can update a record with a SQL UPDATE statement and DBActivateSQL or DBImmediateSQL The following example comes from update prj hstmt DBActivateSQL hdbc UPDATE TESTRES SET MEAS2 500 0 WHERE UUT_NUM 2860B456 You can also update a record with DBPutRecord The process is similar to inserting a record and works with either automatic SQL or explicit SQL After activating a map or
28. SQL statement you must then fetch the record you wish to update Next copy the desired values into the bound variables Finally call DBPutRecord to copy the updated record into the database Activate a map or SQL statement LabWindows CVI SQL Toolkit Manual 3 6 National Instruments Corporation Chapter 3 Using the SQL Toolkit Fetch the record to update whil resCode DBFetchNext hstmt DB SUCCESS if strcmp uutNum 2860B456 0 break if resCode DB_EOF printf record not found n if resCode DB_SUCCESS goto Error Change the value of meas2 meas2 0 5 copy the updated record back to the database resCode DBPutRecord hstmt if resCode DB_SUCCESS goto Error Deleting a Record You can delete a record with a SQL DELETE statement and DBActivateSQL or DBImmediateSQL You can find the following example in del_rec prj H H resCode DBImmediateSOL hdbc DELE UUT_NUM 2860B567 E FROM TESTRES WHERE You can also delete a record with DBDeleteRecord After activating a map or SQL statement you must then fetch the record you wish to delete Then call DBDeleteRecord to delete the record from the database Activate a map or SOL statement find and delete the record whil resCode DBFetchNext hstmt if strcmp uutNum 2860B567 resCode DBDeleteRecord
29. TTe conoonooncnnnnonnnnononnnnncnoncanannccnanancnononnnnss 4 1 Table 4 2 Columns Contained in Each Record oocoocccnnncccnococoncccnnaccnnnncnnn conan ccnnn noo 4 18 Table 4 3 Columns Contained in Each Record ooonocccnncccnoncccnnccconaccnnncnnnnonnnncnnn noo 4 37 Table 4 4 Columns Contained in Each Record ooooocccnonccnnnccconccconancnnnconanncnnnccrnnnoos 4 38 National Instruments Corporation vil LabWindows CVI SOL Toolkit Manual Contents Table A 1 Table A 2 Table A 3 Table A 4 Table A 5 Table B 1 Table C 1 Table C 2 Table C 3 Table 3 1 SOU Command s iia ana A 1 UA A dae ance Waleed daca vp Packt a pubes EET A 3 S Ole Clases faerie a hee he Lal he A Ne Le Ne NN ce le asst a A 4 SOL Operators O io A 5 SOL FuUncionssiialco nado A 7 Fror Codes 2 220 a cei ne eee E ea eee eae ein B 1 Example Format SUM id iiO C 1 Symbols for Date Time Format Strings cooococonnccnoncccononcnonnccnoncconnnccnnncnos C 2 Symbols for Numeric Format Strings oooooconcccnonocononcccnnnnncnnncnonnnnanonnncnnns C 3 sample Database Table ad indeed 3 1 LabWindows CVI SOL Toolkit Manual viii O National Instruments Corporation About This Manual The LabWindows CVI SQL Toolkit Reference Manual describes the LabWindows CVI add on package you can use for database operations Organization of This Manual The LabWindows CVI SQL Toolkit Reference Manual is organized as follows e Chapter 1 Introduction describe
30. VI SQL Toolkit Manual 4 4 National Instruments Corporation Chapter 4 DBActivateSQL SQL Toolkit Function Descriptions int statementHandle DBActivateSQL int connectionHandle Purpose Activates a SQL statement Parameters connectionHandle SQLStatement char SQLStatement integer The handle to the database connection returned by DBConnect string The SQL statement to activate Return Value statementHandle integer Returned handle to the statement execution This value identifies the statement and is a parameter to other functions If 0 the statement could not be executed National Instruments Corporation 4 5 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBAllowFetchAnyDirection int resCode DBAllowFetchAnyDirection int connectionHandle int enable Purpose Enables or disables fetching SELECT statement results in either direction for a database connection If you use this function you must call it before using DBAct ivat eSQL or DBActivateMap Parameters connectionHandle integer The handle to the database connection returned by DBConnect enable integer Using enables fetching in any direction using 0 disables fetching in any direction Return Value Result code See DBError for a list of result codes DBBeginMap int mapHandle DBBeginMap int connectionHandle Purpose Begins a set of column to varia
31. _SUCCESS goto Error resCode DBMapColumnToDouble hmap MEAS1 amp measl amp measlStat National Instruments Corporation 3 3 LabWindows CVI SQL Toolkit Manual Using the SQL Toolkit if resCode DB_SUCCESS goto resCode DBMapColumnToDouble h if resCode DB_SUCCESS goto resCode DBCreateTableFromMap if resCode DB_SUCCESS goto Optionally activate the map a resCode DBDeactivateMap hmap if resCode DB_SUCCESS goto Chapter 3 Error map MEAS2 amp meas2 amp meas2Stat Error hmap TESTRES Error nd create records Error Using Explicit SQL Statements The following example from readtab2 pr 3 executes a SQL SELECT statement Notice that the supplied SELECT statement contains a WHERE clause which is not possible using the mapping functions First database connection handle from DBActivateSQL executes the SQL statement The parameters consist of the DBConnect and the SQL statement The return value acts as a handle to the executed SQL statement and is a parameter to other toolkit functions Next the All DBBindCol functions bind the column values to program variables DBBindCo1 functions use the statement handle column number the address of the target variable and the address of the status variable as parameters DBBindColChar has two additional parameters the size of the buffer and a format string If you
32. a value Fixing bind and set is not allowed for multiple value parameters End of results Fetching no longer allowed on this statement probably due to previous update or delete Enabling logging will probably fix the error Attempt to change the data type of a parameter Use DBErrorMessage for more information continues LabWindows CVI SQL Toolkit Manual B 6 National Instruments Corporation Appendix B Error Codes Table B 1 Error Codes Continued Error Code Description Cause Inserting specified row is illegal with current row Use DBErrorMessage for more information Attempt to insert too many characters into a column Use DBErrorMessage for more information Cannot access drive Cannot open file Use DBErrorMessage for more information Error during file I O Cannot rename file Use DBErrorMessage for more information Not enough memory for this command The maximum number of files are already open Reserved file name cannot be opened Use DBErrorMessage for more information Out of memory National Instruments Corporation B 7 LabWindows CVI SQL Toolkit Manual Appendix C Format Strings This appendix describes the format strings that you can use with DBMapColumnToChar and DBBindColChar Format Strings Format strings consist of symbols that describe how a value should be formatted Table C 1 shows some example format strings The symbols used in these examples
33. able 4 2 transaction functions DBBeginTran 3 10 4 7 DBCommit 3 10 4 15 DBRollback 3 10 4 34 function tree table 4 2 G GROUP BY clause SQL table A 3 H HAVING clause SQL table A 4 I include files for SQL Toolkit functions 4 3 information functions data source information 3 8 to 3 9 DBColumnName 3 9 4 13 DBColumnType 3 9 4 14 DBColumn Width 4 15 DBDatabases 3 8 4 18 DBNumberOfColumns 3 9 4 31 DBNumberOfModifiedRecords 3 9 4 32 DBNumberOfRecords 3 9 4 32 DB Sources 3 8 4 36 DBTables 3 8 4 37 to 4 38 function tree table 4 2 purpose 4 3 SELECT statement information 3 9 to 3 10 INSERT command 2 2 A 1 insert update delete records functions LabWindows CVI SQL Toolkit Manual DBCreateRecord 3 6 4 17 DBDeleteRecord 3 7 4 20 DBPutRecord 3 6 3 7 4 33 function tree table 4 2 purpose 4 3 inserting records 3 6 installation of LabWindows CVI SQL Toolkit 1 1 to 1 3 installation files 1 2 procedure 1 1 to 1 2 L LabWindows CVI SQL Toolkit See SQL Toolkit logical operators SQL table A 5 M manual See documentation N numeric format strings table C 4 to C 5 numeric operators SQL table A 4 O objects SQL table A 2 to A 3 ODBC Administrator 2 3 to 2 4 ODBC Database Drivers installation files 1 2 setting up with ODBC Administrator 2 3 to 2 4 third party drivers 2 4 updating ODBC database driver information 1 3 ODBC Open Databa
34. aia 4 36 LabWindows CVI SOL Toolkit Manual vi O National Instruments Corporation Contents DB SOURCES 2 iia 4 37 DBA lirica cel 4 38 DO WO ia 4 40 Appendix A SOL References Re RE ET olle oia lle re poden A 1 SOLE COMME soa Ea E A 1 SOL ODJECTS uvas dani ciao A 3 SO E E siii iii ice risa A 4 A S as anode coins gsaco a a a sua sae Gece cout cole veined A 5 SOL PUNCHONS id telah eee adel i hene dh A a A A 7 Appendix B MAA A e ete eh cote B 1 Appendix C Format Strings ookee de do tc C 1 Format SIMS baaa alaba abou C 1 Date Time Format SINO iii olor load C 2 Numeric Format Stings cicsicssaissseeisansavaasasessactassnddedasoovtancsvickaceonseeseadonsae C 4 Appendix D Customer Communication 0 eee ccesesseseeseseseseseeeeeesesnenereeeeaesneneeeeseees D 1 TRUONG cic ha le i Se aA Mi tel T aoe tg casa Mie hac I 1 Figures Figure 1 1 LabWindows CVI SQL Toolkit Installation Dialog Box ee 1 2 Figure 2 1 Data Sources Dialog BOX ceccceeecceesseceeseceessecenseceeaecesaeeeenaeeesaeeeenaeeees 2 3 Figure 2 2 ODBC dBASE Driver Setup Dialog BOX ooooconnocccconccccnonccononaconancconnncn ns 2 4 Figure 3 1 The SQL Toolkit Functions 24 nts ade di 3 1 Tables Table 2 1 Sample Test Sequence Results ooooooononocinnoconcccnonanonnccnonononononnnconncannn cnn 2 1 Table 2 2 Data Types Supported by the SQL Toolkit eee ceeseesseeeeeeeeneeeeneees 2 2 Table 3 1 Sample Database Table uti dd 3 1 Table 4 1 The SQL Toolkit Function
35. alues in the variables previously specified by the variable binding or mapping functions You can use DBFetchPrev with either automatic SQL or explicit SQL You can use this function only if you have previously called DBA11owFetchAnyDirection to allow fetches in both directions When DBF et chPrev attempts to fetch a record before the first record returned by the SELECT statement it returns a result of DB_EOF 5 Parameters Input statementHandle integer Handle to the database connection returned by DBConnect Return Value Result code See DBError fora list of result codes LabWindows CVI SQL Toolkit Manual 4 24 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBFetchRandom int resCode DBFetchRandom int statementHandle int recordNumber Purpose Retrieves the designated record from the database The program places the column values in the variables previously specified by the variable binding or mapping functions You can use DBFetchRandom with either automatic SQL or explicit SQL You can use this function only if you have previously called DBA11owFetchAnyDirection to allow fetches in both directions When DBFet chRandom attempts to fetch a record not contained in the result set returned by the SELECT statement it returns a result of DB_EOF 5 Parameters statementHandle integer Handle to the database connection returned by DBConnect
36. ame format for positive and negative numbers For formats with two sections use the second section as the format for negative numbers Table C 3 lists the symbols you can use in numeric format strings Table C 3 Symbols for Numeric Format Strings Outputs the currency string from the international section of the Windows control panel Outputs the decimal point character from the international section of the Windows control panel Outputs the thousands separator character from the international section of the Windows control panel Outputs a digit If there is no digit in the position outputs nothing Outputs a digit If there is no digit in the position outputs a zero Outputs a digit If there is no digit in the position outputs a space Outputs the value as a percent The value is multiplied by 100 and the character is output Outputs in scientific notation shows exponent sign only if negative Outputs in scientific notation always shows exponent sign E E Outputs uppercase analogs of e and e Outputs the character lt space gt lt character gt Outputs the character following the Y character lt string gt Outputs the string lt string gt GN General format for numbers This is the default if no format string is given Note you can only combine GN with symbols that are enclosed in brackets such as US GF General fixed format for numbers from the international sectio
37. are described later in this appendix Table C 1 Example Format Strings Format String Formatted Value mm dd yy Mar 14 1995 03 14 95 dd mm yy Mar 14 1995 14 03 95 Stephen Hawkins born Mar 14 1995 Stephen Hawkins born March 14 Mmmm d yyyy 1995 hh mm ss 3 47 42 PM 15 47 42 hh mm ss AM PM 3 47 42 PM 03 47 42 PM 0 00 210 6 210 60 S 0 00 S 0 00 210 6 210 60 156 20348 156 20 GN 153 153 1 875 1 875 O S 1000 12567 12 199 0 National Instruments Corporation C 1 LabWindows CVI SQL Toolkit Manual Format Strings Appendix C Date Time Format Strings Date time format strings control which parts of the date or time are converted or retrieved the order of the parts and how the months and days are abbreviated Table C 2 lists the symbols you can use in date time format strings Table C 2 Symbols for Date Time Format Strings Example Symbol Description Output Month s number without leading zero Month s number with leading zero if applicable Month s three letter abbreviation lowercase Month s three letter abbreviation initial cap Month s three letter abbreviation uppercase Month s full name lowercase Month s full name initial cap Month s full name uppercase Day of the month s number without leading zero Day of the month s number with leading zero if applicable Day of the month s three letter abbreviation lowercase Day of the month s three
38. ateSQL or DBActivateMap Return Value National Instruments Corporation 4 33 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBPutRecord int resCode DBPutRecord int statementHandle Purpose Places the current record in the database You can use DBPut Record with new records created by DBCreateRecord or with existing records fetched from a SELECT statement You can call DBNumberOfModifiedRecords to determine the number of records affected by acall to DBPutRecord Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL or DBActivateMap Return Value Result code See DBError for a list of result codes LabWindows CVI SQL Toolkit Manual 4 34 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBRollback int resCode DBRollback int connectionHandle Purpose Discards all changes that you have made using the SQL statements INSERT UPDATE or DELETE since you called DBBeginTran You must call DBBeginTran to begin a transaction before you can call DBRol1lback to undo all changes Note The discarded changes include any saved changes on records other than the current record any records created by calling DBCreateRecord and any new values placed in the current record by calls to DBPutRecord After calling DBRollback the SQL Toolkit will be position
39. ble mappings The map describes which columns will be selected and the variables that will receive column values when the program fetches a record You can also use the map to define a new table for creation with DBCreateTableFromMap Parameters connectionHandle integer The handle to the database connection returned by DBConnect Return Value mapHandle integer Handle to the new map The functions DBActivateMap and DBCreateTableFromMap use this value LabWindows CVI SQL Toolkit Manual 4 6 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBBeginTran int resCode DBBeginTran int connectionHandle Purpose Begins a transaction on a database connection After a transaction begins the SOL INSERT UPDATE and DELETE statements that you execute using DBActivateSQL DBActivateMap or DBImmediateSQL cannot be saved to the database until you call DBCommit DBCommit saves the changes that have been made since the DBBeginTran call and frees all database locks Alternately DBRollback discards the changes that you have made since calling DBBeginTran and frees all database locks If you execute an INSERT UPDATE or DELETE statement without first calling DBBeginTran the toolkit automatically saves the database changes and frees all database locks You cannot have more than one simultaneous transaction active on a database connection After
40. command Cannot set current working directory to specified directory Use DBErrorMessage for more information Insufficient disk space Invalid file handle Access denied to specified file Use DBErrorMessage for more information Specified file not found Use DBErrorMessage for more information Specified path not found Use DBErrorMessage for more information You must run SHARE when locking is enabled or you must set Locking NONE in your ODBC INT file Part or all of the region is already locked Unable to unlock specified record Lock failed because SHARE buffers exceeded You can only login to this database once Cannot load specified dynamic link library Use DBErrorMessage for more information Specified connection string is missing DSN lt driver name gt Use DBErrorMessage for more information This Database driver does not support transaction processing Specified token is too big Use DBErrorMessage for more information Specified number is too large Use DBErrorMessage for more information continues National Instruments Corporation B 1 LabWindows CVI SOL Toolkit Manual Error Codes Appendix B Table B 1 Error Codes Continued Error Code Description Cause Invalid character in number Use DBErrorMessage for more information Unmatched quote character Use DBErrorMessage for more information Error parsing connect string Use DBErrorMessage
41. cvi_db pth file into the same directory as your executable file This allows the executable file to resolve the DLL filename that the SQL Toolkit functions need to implement the database functions You will also find a copy of the cvi_db pth file in the same directory as the SQL Toolkit Instrument Driver National Instruments Corporation 3 1 LabWindows CVI SQL Toolkit Manual Chapter 4 SQL Toolkit Function Descriptions This chapter describes the functions in the LabWindows CVI SQL Toolkit The SQL Toolkit Overview section contains general information about the SQL Toolkit functions The SQL Toolkit Function Reference section contains an alphabetical list of function descriptions SQL Toolkit Overview This section contains general information about the SQL Toolkit functions SQL Toolkit Function Panels The SQL Toolkit function panels are grouped in a tree structure according to the types of operations they perform Table 4 1 shows the SQL Toolkit function tree The bold headings in the tree indicate the names of function classes and subclasses Function classes and subclasses contain groups of related function panels The headings in plain text indicate the names of individual function panels Each function panel generates one function call You will find the names of the corresponding function calls in bold italics to the right of the function panel names Table 4 1 The SQL Toolkit Function Tree SQL Toolkit Connection Open Conn
42. directory You can use the ODBC Administrator icon on your Control Panel to configure a driver to make it available as a data source for your applications Your system will save all changes made within the ODBC Administrator into the ODBC INT file By double clicking on the ODBC Administrator icon on the Control Panel the Data Sources dialog box located in Figure 2 1 appears Data Sources Driver CVYI_Paradox_4 L W C I Paradox 4 db CVI_Paradox_5 LW CV I Paradox 5 db C l_PROGRESS LW C I PROGRESS CVI_SOLBase LW C I SQLBase C L_ SQLServer LW C I SQL Server CVI_Sybase LW CVI Sybase CVI_Text_Files LW 2CWI Text Files CVI_XDB LW CVI XDB MS Access Databases Access Data mdb Options Figure 2 1 Data Sources Dialog Box The Data Sources dialog box lists all the registered ODBC data sources The list may include non LabWindows CVI SQL Toolkit ODBC Drivers such as the Microsoft Access ODBC Driver You can use the Add or Setup buttons to display a driver specific dialog box that enables you to configure a new or an existing data source The system then saves the configuration for the data source in the ODBC INT file National Instruments Corporation 2 3 LabWindows CVI SQL Toolkit Manual Getting Started Chapter 2 Figure 2 2 displays the ODBC dBASE Driver Setup dialog box Data Source Name cvi_s amples Description Data Source for SQL T
43. e DBErrorMessage for more information No SQL statement given for execution continues National Instruments Corporation B 5 LabWindows CVI SQL Toolkit Manual Error Codes Appendix B Table B 1 Error Codes Continued Error Code Description Cause Cannot update or delete record no primary key Operation aborted Duplicate table names in FROM clause Use DBErrorMessage for more information Unable to lock record Modified or deleted by another user Unable to insert record into database Number of parameters supplied does not match number of parameter markers in statement Declared parameter names do not match the statement parameters Attempt to delete the current record from a query containing a join Attempt to insert a record into a query containing a join Another break cannot be added to the specified field Use DBErrorMessage for more information Attempt to read backwards without a log file Unable to build SELECT list Attempt to modify read only query Specified field number is too large Use DBErrorMessage for more information Case insensitive search requested for a non character column Operation requires an executed statement Internal Error invalid ODBC handle Table or table alias name exceeds limit Use DBErrorMessage for more information The specified parameter number is too large Use DBErrorMessage for more information At least one parameter has not been supplied
44. eMap 4 19 DBMapColumnToChar 3 2 4 26 DBMapColumnToDouble 3 2 4 27 DBMapColumnToFloat 4 28 DBMapColumnTolInt 4 29 DBMapColumnToShort 4 30 function tree table 4 1 connection functions DBConnect 3 2 4 16 DBDisconnect 3 2 4 20 DBSetDatabase 4 35 function tree table 4 1 error functions DBError 3 11 4 21 to 4 22 DBErrorMessage 3 11 4 22 DBNativeError 4 31 DBWarning 4 39 function tree table 4 2 error reporting 4 3 explicit SQL functions DBActivateSQL 3 4 4 5 DBBindColChar 3 4 4 8 DBBindColDouble 3 4 4 9 DBBindColFloat 3 4 4 10 DBBindColInt 3 4 4 11 DBBindColShort 3 4 4 12 DBDeactivateSQL 3 4 4 19 DBImmediateSQL 3 6 4 25 function tree table 4 2 fetch records functions DBAllowFetchAnyDirection 3 5 4 6 DBFetchNext 3 5 4 23 DBFetchPrev 3 5 4 23 to 4 24 DBFetchRandom 3 5 4 24 function tree table 4 2 function classes 4 3 function summary figure 3 1 function tree table 4 1 to 4 2 include files 4 3 information functions DBColumnName 3 9 4 13 DBColumnType 3 9 4 14 DBColumnWidth 4 15 DBDatabases 3 8 4 18 DBNumberOfColumns 3 9 4 31 LabWindows CVI SQL Toolkit Manual Index DBNumberOfModifiedRecords 3 9 4 32 DBNumberOfRecords 3 9 4 32 DB Sources 3 8 4 36 DBTables 3 8 4 37 to 4 38 function tree table 4 2 insert update delete records functions DBCreateRecord 3 6 4 17 DBDeleteRecord 3 7 4 20 DBPutRecord 3 6 3 7 4 33 function tree t
45. ection DBConnect Close Connection DBDisconnect Set Database DBSetDatabase Automatic SQL maps Begin Map DBBeginMap Map Column to String DBMapColumnToChar Map Column to Short Integer DBMapColumnToShort Map Column to Integer DBMapColumnToInt Map Column to Float DBMapColumnToFloat Map Column to Double DBMapColumnToDouble Create Table From Map DBCreateTableFromMap Activate Map DBActivateMap Deactivate Map DBDeactivateMap continues O National Instruments Corporation 4 1 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 Table 4 1 The SQL Toolkit Function Tree Continued Explicit SQL Immediate SQL Statement Activate SQL Statement Bind Column to String Bind Column to Short Integer Bind Column to Integer Bind Column to Float Bind Column to Double Deactivate SQL Statement Fetch Records Fetch Next Record Fetch Previous Record Fetch Random Record Allow Previous or Random Fetch Insert Update Delete Records Create New Record Put Record Delete Record Information Functions Data Source Information Available Sources Available Databases Available Tables Select Information Number of Records Number of Columns Column Name Column Width Column Type Number of Modified Records Transactions Begin Transaction Commit Transaction Rollback Transaction Errors Error Code Warning Code Native Error Code Error Warning Text LabWindows CVI SQL Toolkit Manual DBImmediateSQL DBActivateSQL DBBindColC
46. ed between what was the last current record in the transaction and the next record in the statement handle Before you perform any operations against the records call one of the DBFet ch functions to position on a valid record Parameters Input connectionHandle integer The handle to the database connection returned by DBConnect Return Value Result code See DBError for a list of result codes National Instruments Corporation 4 35 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBSetDatabase int statementHandle DBSetDatabase int connectionHandle char databaseName Purpose Sets the default database in systems that allow tables to be stored in separate databases A limited number of database systems support this function Parameters connectionHandle integer Handle to the database connection previously returned by DBConnect databaseName string Name of the new default database Return Value statementHandle integer Result code returned by DBDisconnect Result codes are the same as those returned by DBError LabWindows CVI SQL Toolkit Manual 4 36 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBSources int statementHandle int DBSources int option Purpose Creates and activates a SELECT statement that returns information about the available database sources You can then use the DBBindCol and DBFetch func
47. ements Table A 5 SQL Functions ROUND num_exprl num_exprl rounded to num_expr2 decimal places num_expr2 CHR num_expr Character having ASCII value num_expr LOWER char_expr Change all characters in char_expr to lower case LTRIM char_expr Strip leading spaces from char_expr LEFT char_expr Leftmost character of char_expr RIGHT char_expr Rightmost character of char_expr SPACE num_expr Construct a string with num_expr blanks IFF logical_expr Return True_Value if logical_expr is true otherwise return True_ Value False_ Value False_Value STR num_expr width Converts num_expr to string of width characters with prec optional prec fractional digits STRVAL expr Converts any expr to a character string TIME Returns time of day as a character string LEN char_expr Number of characters in char_expr AVG column_name Average of all non null values in column_name must be numeric column COUNT Number of rows in table MAX col_expr Maximum value of col_expr MAX num_exprl Maximum of num_exprl and num_expr2 num_expr2 MIN num_exprl Minimum of num_expr1 and num_expr2 num_expr2 SUM col_expr Sum of values in col_expr continues O National Instruments Corporation A 7 LabWindows CVI SOL Toolkit Manual SQL Reference Appendix A Table A 5 SQL Functions Continued DTOC date_expr Convert date_expr to character string using fmt and optional fmt_value separator_char separator_char fmt_valu
48. ers answer your questions more efficiently Be sure to fax copies of your AUTOEXEC BAT and CONFIG SYS files as well If one or more National Instruments hardware products are involved in this problem include the Hardware Configuration form from each hardware product s user manual Include additional pages as necessary Name Company Address Fax Phone Computer brand Model Processor Coprocessor Operating system Version Bus XT AT ISA Micro Channel or EISA Speed MHz CPU BUS RAM Extended Expanded Video Board Mouse Yes No Mouse Type Mouse Driver Version __ Other adapters installed Base I O Address Level of Other Boards Interrupt Level of Other Boards Hard disk capacity Brand Instruments used National Instruments hardware product models Revision Configuration Base I O Address of Board s Interrupt Level of Board s LabWindows CVI Version Number Size and date of CVI EXE file Other National Instruments software product Version Programming Language and Version The problem is List any error messages The following steps will reproduce the problem Documentation Comment Form National Instruments encourages you to comment on the documentation supplied with our products This information helps us provide quality products to meet your needs Title LabWindows CVI SQL Toolkit Reference Manual Edition Date May 1995 Part
49. es are 0 MM DD YY 1 DD YY MM 2 YY MM DD 10 MM DD YYYY 11 DD MM YYYY 12 YYYY MM DD USERNAME Returns name of current user not supported by all databases MOD num_exprl Remainder of num_expr1 divided by num_expr2 num_expr2 MONTH date_expr Returns month from date_expr as a number DAY date_expr Returns day from date_expr as a number YEAR date_expr Returns year from date_expr as a number POWER num_exprl Returns num_exprl raised to num_expr2 power num_expr2 INT num_expr Returns integer part of num_expr NUMVAL char_expr Converts char_expr to number If char_expr is not a valid VAL char_expr number returns zero DATEQ Returns today s date TODAY DATEVAL char_expr Converts char_expr to a date CTOD char_expr fmt Converts char_expr to date format using fmt template LabWindows CVI SQL Toolkit Manual A 8 O National Instruments Corporation Appendix B Error Codes This appendix describes the error codes returned by functions in the LabWindows CVI SQL Toolkit In many cases you can obtain additional information about errors by using DBErrorMessage Table B 1 Error Codes Error Code Description Cause Error on menu operation Resources may be getting low Not enough memory for data transfer Message truncated Cannot create specified file Use DBErrorMessage for more information Cannot delete specified file Use DBErrorMessage for more information Not enough memory for this
50. esult code See DBError for a list of result codes O National Instruments Corporation 4 11 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions DBBindColShort Chapter 4 int resCode DBBindColShort int statementHandle int columnNumber short locationForValue int locationForStatus Purpose Specifies the value and status variables in your program that will receive the column s value and length each time the program fetches a record You must bind all columns in the statement You must bind the columns in the order in which they occur in the statement Parameters statementHandle columnNumber locationFor Value locationForStatus Return Value LabWindows CVI SQL Toolkit Manual integer integer short integer integer Handle to the SQL statement returned by DBActivateSQL The column number containing specified variables The first column number is 1 Pointer to the variable that will receive a short int value for the column when the program fetches a record Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB TRUNCATION 1 or DB NULL DATA 2 Result code See DBError for a list of result codes National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions
51. eturned table Table User Char 128 User name table based source Table Name Char 128 Table name table based source Directory Name Char 128 Directory name file based source File Name Char 128 File name file based source Table Type Short Type of table DB_TBL_TABLE DB_TBL_VIEW DB_TBL_SYNONYM DB_TBL_PROCEDURE or DB_TBL_SYSTABLE Remarks Char 256 Remarks may be NULL Parameters connectionHandle integer The handle to the database connection returned by DBConnect qualifierPattern string Pointer to a string containing a qualifier or path for the tables to be selected continues LabWindows CVI SOL Toolkit Manual 4 38 O National Instruments Corporation Chapter 4 Parameters Continued userPattern string tablePattern integer Return Value SQL Toolkit Function Descriptions Pointer to a string containing the pattern for selecting users If NULL the system returns tables for the current user If the pattern is or returns tables for all users File based databases will ignore this parameter Pointer to a string containing a pattern for selecting tables or files To select all tables use a pattern of or ok Specifies the type s of table s for which information will be returned You can use any of the following values DB_TBL_TABLE 0x0001 Table names DB_TBL_VIEW 0x0002 View names DB_TBL_PROCEDURE 0x0004 Stored procedure names DB_TB
52. eve the information Table 4 2 shows the two columns that each record contains Table 4 2 Columns Contained in Each Record Database Char 128 Database name Remarks Char 256 Remarks may be NULL Note If you use DBDatabases with a flat file database it will not select any records Parameters connectionHandle integer The handle to the database connection returned by DBConnect statementHandle integer Handle to the SQL statement execution If 0 the statement could not be executed Return Value LabWindows CVI SQL Toolkit Manual 4 18 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBDeactivateMap int resCode DBDeactivateMap int mapHandle Purpose Ends activation of amap You should use DBDeactivateMap to free system resources when you finish using the map Parameters Input mapHandle integer The handle to the map returned by DBBeginMap Return Value resCode Result code See DBError for a list of result codes DBDeactivateSQL int resCode DBDeactivateSQL int statementHandle Purpose Deactivates a SQL statement You should use DBDeact ivateSQL to free system resources when you finish using the statement Parameters Input statementHandle integer Handle to the SQL statement returned by DBActivateSQL Return Value resCode Result code See DBError for a list of result codes O National Instruments Corpora
53. f nameStat DB_NULL DATA InsertListItem pan SELTABLE_TABLES 0 name name LabWindows CVI SQL Toolkit Manual 3 8 National Instruments Corporation Chapter 3 Using the SQL Toolkit SELECT Statement Information The SQL Toolkit contains several functions that return information about SELECT statements You will find these functions useful when you access tables without prior knowledge of the table structure Use the following functions to return SELECT statement information DBNumberOfRecords DBNumberOfColumns e DBColumnName e DBColumnType e DBNumberOfModifiedRecords The first parameter for all these functions consists of the statement handle DBColumnName and DBColumnType also have a second parameter for the column number DBNumberOfRecords DBNumberOfColumns and DBNumberOfModi fiedRecords return the number of items in a table DBColumnName returns a pointer to the column name string Because the toolkit will reuse the buffer containing the column name you must copy the string before you call another toolkit function DBColumnType returns the data type of the column You can find the following example in sel_info prj We will be calling DBNumberOfRecords so fetching in any direction must be enabled resCode DBAllowFetchAnyDirection hdbc 1 Execute a SELECT statement hstmt DBActivateSQL hdbc selectStmt Get information about the column
54. for more information Error parsing string Use DBErrorMessage for more information Attribute specified more than once Use DBErrorMessage for more information Attribute specified twice by different keywords Use DBErrorMessage for more information Invalid hex character during conversion Quicksort stack overflow Too many sort keys Invalid specified license file Use DBErrorMessage for more information Beta period expired Evaluation period expired Beta period will expire in less than 15 days Evaluation period will expire in less than 15 days String too large Limit is 65 500 bytes Initialization file is not open Not for resale version Could not create trace window Format mask too long Limit is 79 characters A sign character must follow the E format character for example 0 00E 00 One or more digits for the exponent must follow the E format character for example 0 00E 00 Quoted string in format mask is missing closing quote for multiply or for divide must follow the scale command for example S 1000 continues LabWindows CVI SQL Toolkit Manual B 2 National Instruments Corporation Appendix B Error Codes Table B 1 Error Codes Continued Error Code Description Cause Power of 10 must follow the scale command for example S 10 A command in format mask is missing the end command character Partial values cannot be formatted or converted Atte
55. har DBBindColShort DBBindColInt DBBindColFloat DBBindColDouble DBDeactivateSOL DBFetchNext DBFetchPrev DBFetchRandom DBAllowFetchAnyDirection DBCreateRecord DBPutRecord DBDeleteRecord DBSources DBDatabases DBTables DBNumberOfRecords DBNumberOfColumns DBColumnName DBColumn Width DBColumnType DBNumberOfModifiedRecords DBBeginTran DBCommit DBRollback DBError DBWarning DBNativeError DBErrorMessage National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions The following list describes the function classes in the tree e The Connection functions control connecting to and disconnecting from a database The Automatic SQL Maps functions define construct and execute SQL statements automatically The Explicit SQL functions execute SQL statements provided by the programmer e The Fetch Records functions retrieve SELECT statement results from a database table e The Insert Update Delete Records functions allow inserting new rows updating existing rows and deleting rows e The Information functions provide information about tables databases and active SQL statements e The Transactions functions allow database operations to be grouped into transactions e The Errors functions support error checking and reporting Include Files The SQL Toolkit contains an include file named cvi_db h which consists of function declarations and defined constants for all the toolkit routines Yo
56. he map returned by DBBeginMap columnName string The name of the column containing specified variables maxLen integer Size of the value variable in bytes locationFor Value Pointer to the variable that will receive the null terminated character string value for the column when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_ TRUNCATION 1 or DB_NULL_DATA 2 formatString Used to control formatting for dates and numbers Return Value Result code See DBError for a list of result codes National Instruments Corporation 4 27 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBMapColumnToDouble int resCode DBMapColumnToChar int mapHandle char columnName double locationForValue int locationForStatus Purpose Specifies a column to be selected and the value and status variables that will receive the column s value and status each time the program fetches a record Parameters mapHandle integer Handle to the map returned by DBBeginMap columnName string The name of the column containing specified variables locationForValue double Pointer to the variable that will precision receive the double value for the column
57. hstmt DBUSUCCESS 0 F Deleting a Table You can delete a table with a SQL DROP TABLE statement and DBActivatesQL or DBImmediateSQL resCode DBImmediateSQL hdbc DROP TABLE TESTRES Note You can only delete a table using SQL statements National Instruments Corporation 3 7 LabWindows CVI SQL Toolkit Manual Using the SQL Toolkit Chapter 3 Information Functions The SQL Toolkit includes several information functions These functions fall into two categories available data source information and SELECT statement information Data Source Information The SQL Toolkit contains three functions that return information about data sources DBSources DBDatabases and DBTables These functions all execute SELECT statements and return a statement handle that you can use to fetch the information e DBSources returns information about the available data source names With the single parameter you can choose all available data sources DB_SRC_AVAILABLE or only currently connected sources DB_SRC_CONNECTED The SELECT statement that DBSources executes returns four columns the source name file extension possibly NULL connection handle and remarks e DBDatabases returns information about the available databases for a connection The only parameter consists of the connection handle The two columns that the SELECT returns consist of the database
58. ion Appendix B Error Codes Table B 1 Error Codes Continued Error Code Description Cause No rows locked Specified column is not searchable No database source specified Invalid specified parameter number Use DBErrorMessage for more information Specified field number is invalid Use DBErrorMessage for more information Missing key word Use DBErrorMessage for more information Specified statement has not been executed Execution required for this operation DBBeginTran required before locking records No query save file specified Cannot insert row For this database system the row must be within the fetched rows or immediately following the last row fetched Use DBErrorMessage for more information Parameter type not in range 1 to 6 Use DBErrorMessage for more information Native database error Use DBErrorMessage for more information Native database warning Use DBErrorMessage for more information Statement handle was not invalidated at end of transaction Not positioned on any row Attempt to fetch before committing or rolling back transaction Query does not have a valid connection handle Operation only allowed with deferred auto update Missing key word Use DBErrorMessage for more information Unexpected text at end of SQL query Use DBErrorMessage for more information Empty SQL clause Missing in comment Improper select list in SELECT statement Us
59. ll as a form you can use to comment on our products and manuals O National Instruments Corporation ix LabWindows CVI SOL Toolkit Manual About This Manual Conventions Used in This Manual The following conventions are used in this manual bold Bold text denotes menus menu items or dialog box buttons or options italic Italic text denotes emphasis a cross reference or an introduction to a key concept bold italic Bold italic text denotes a note caution or warning monospace Lowercase text in this font denotes text or characters that you should literally enter from the keyboard This font is also used for the proper names of disk drives paths directories programs subprograms subroutines device names functions variables filenames and extensions and for statements and comments taken from program code italic Italic text in this font denotes that you must supply the appropriate words monospace or values in the place of these items lt gt Angle brackets enclose the name of a key on the keyboard for example lt PageDown gt A hyphen between two or more key names enclosed in angle brackets denotes that you should simultaneously press the named keys for example lt Control Alt Delete gt lt Control gt Key names are capitalized Related Documentation The following documents contain information that you may find helpful as you read this manual e Getting Started with LabWindows CVI e LabWindows CVI Use
60. mpt to format or convert an invalid date value Overflow when converting to single precision floating point Overflow when converting to short integer Overflow when converting to binary coded decimal Exponent too large when converting value Overflow when converting to long integer Invalid year in date Invalid month in date Invalid day in date Invalid hour in date Invalid minute in date Invalid second in date Invalid fractional seconds in date Invalid character in date format string Use DBErrorMessage for more information Invalid character in number format string Use DBErrorMessage for more information Invalid character in general format string Use DBErrorMessage for more information Specified string cannot be converted to a number Use DBErrorMessage for more information Could not convert date to value Use DBErrorMessage for more information Overflow when converting to double precision floating point Limit on number of connection and statement handles exceeded Invalid connection statement or query handle LIKE or NOT LIKE invalid for non character data type Invalid column number continues National Instruments Corporation B 3 LabWindows CVI SQL Toolkit Manual Error Codes Appendix B Table B 1 Error Codes Continued Error Code Description Cause Column information requested does not apply to column because of column data type Too many active programs using
61. n contract or tort including negligence Any action against National Instruments must be brought within one year after the cause of action accrues National Instruments shall not be liable for any delay in performance due to causes beyond its reasonable control The warranty provided herein does not cover damages defects malfunctions or service failures caused by owner s failure to follow the National Instruments installation operation or maintenance instructions owner s modification of the product owner s abuse misuse or negligent acts and power failure or surges fire flood accident actions of third parties or other events outside reasonable control Copyright Under the copyright laws this publication may not be reproduced or transmitted in any form electronic or mechanical including photocopying recording storing in an information retrieval system or translating in whole or in part without the prior written consent of National Instruments Corporation Trademarks Product and company names listed are trademarks or trade names of their respective companies WARNING REGARDING MEDICAL AND CLINICAL USE OF NATIONAL INSTRUMENTS PRODUCTS National Instruments products are not designed with components and testing intended to ensure a level of reliability suitable for use in treatment and diagnosis of humans Applications of National Instruments products involving medical or clinical treatment can create a potential for acciden
62. n of the Windows control panel Note you can only combine GF with symbols that are enclosed in brackets such as US continues LabWindows CVI SQL Toolkit Manual C 4 National Instruments Corporation Appendix C Format Strings Table C 3 Symbols for Numeric Format Strings Continued sana General currency format for numbers from the international section of the Windows control panel Note you can only combine GC with symbols that are enclosed in brackets such as US Scales divides the number by a power of 10 before output n must be a power of 10 Scales multiplies the number by a power of 10 before output n must be a power of 10 Ignores the information in the international section of the Windows control panel Substitutes the United States defaults instead National Instruments Corporation C 5 LabWindows CVI SOL Toolkit Manual Appendix D Customer Communication For your convenience this appendix contains forms to help you gather the information necessary to help National Instruments solve technical problems you might have as well as a form you can use to comment on the product documentation Filling out a copy of the LabWindows CVI Technical Support Form before contacting National Instruments enables us help you better and faster National Instruments provides comprehensive technical assistance around the world In the U S and Canada you can reach our applications engineers Monday through Frida
63. n the rest of this chapter are based on Table 3 1 Sample Database Table UUT_NUM MEAS1 MEAS2 20860B456 E 0 6 20860B456 20860B 123 0 7 20860B789 0 6 20860B789 Connecting to a Database Use DBConnect to connect to a data source The only parameter is a connection string which must contain the data source name and any other attributes the database requires DBConnect returns a handle to the database connection that other functions in the toolkit use Call DBDi sconnect to close the database connection passing in the database connection handle from DBConnect You can find the following example in connect prj int hdbc 0 Handle to database connection Connect to CVI_Samples data source hdbc DBConnect DSN CVI Samples if hdbc lt 0 goto Error Disconnect from database resCode DBDisconnect hdbc if resCode DB_SUCCESS goto Error Using Automatic SQL Maps The following example uses the SQL Toolkit mapping functions to automatically generate and execute a SQL SELECT statement See readtab prj for the complete program To define a map first call DBBeginMap The only parameter consists of the connection handle from DBConnect The return value acts as a handle to the map Next you must map the columns of interest to program variables with the DBMapColumnTo functions in this case DBMapColumnToChar and DBMapColumnToDouble All the DBMapColumnTo functi
64. necting to the Databases il ai 2 5 Activating SQL Statements pdas 2 5 Processing SOL Statements 00 A ees 2 5 Deactivating SQL Statements a a GReese heed eens 2 6 Disconnecting from the Database aa 2 6 Chapter 3 Using OIT 3 1 A etic a uatis tu gages a i a ai 3 1 Connecting toa Database 3 2 Using Automatic SQL Maps ii 3 2 Using Explicit SQL Statements 5 c cisaccseasedeais iapecea eg saseacteavasnscaas soeeaneesunaeazees sees 3 4 o A ON 3 5 Inserting a Recorrido renis 3 6 os a R cord A A O 3 6 Delete a Record ui ata RE 3 7 Delemaa Table initial arenga 3 7 A UA Ri 3 8 DataSource Information sises aea id 3 8 SELECT Statement Information arena 3 9 Transactions da a e os eas ac aa vd ee ese E ERA 3 10 A A 3 10 Creating A Standalone Executable Pile aiii 3 11 National Instruments Corporation v LabWindows CVI SQL Toolkit Manual Contents Chapter 4 SQL Toolkit Function Descriptions 0 0 0 0 cceeeeseseeeeeeeeeeeeteeeeeees 4 1 SOL TOOK OVELVIE WA AS ERIS te RE 4 1 SOL Toolkit Function Panels nai ici iia aces ai aci 4 1 A duit since ciowd at a A aap aa n aAa 4 3 Reporting Error ninio EE e EES 4 3 SQL Toolkit Function Reference ooocoonoonooncnonononnnnnnonnnnnnonononnnnnonoconnnn nn nncccnnannnonos 4 4 DBActivate Map unta cdas a a cio dores 4 4 DBActivate SOL contada dadas ticas 4 5 DBAllowFetchAnyDirectiON ocooocccoonccononcnonnnonnnncnononononncnonnnonnnnncnonaconnnos 4 6 IDB Bee iMac 4 6 MOB BG Sir talc A A O apais
65. nt in one step National Instruments Corporation Chapter 3 Using the SQL Toolkit This example from new_tabl prj executes a SQL CREATE TABLE statement Notice that you do not need to bind variables in this example resCode DBImmediateSQL hdbc CREATE TABLE TESTRES UUT_NUM CHAR 10 MEAS1 NUMERIC 10 2 MEAS2 NUMERIC 10 2 if resCode DB_SUCCESS goto Error I T Fetching Records Fetching records uses the same functions for automatic SQL and explicit SQL Typically you fetch records from first to last with DBFetchNext The parameter for DBFetchNext consists of the statement handle from DBExecuteMap or DBActivatesQL The return value consists of a result code A DB_EOF result code indicates that no more records can be fetched You can use the following code fragment with either of the previous examples to fetch the selected records You can find this example in either readtab prj or readtab2 prj Fetch the values column values are placed in the previously bound variables while resCode DBFetchNext hstmt DB_SUCCESS printf Serial Number s measurement 1 f measurement 2 2fin uutNum measl meas2 if resCode DB_SUCCESS amp amp resCode DB_EOF goto Error You can also fetch the previous record with DBFet chP rev or a specified record with DBFetchRandom Use the same parameters as for DBFet chNe
66. numbers can easily overflow or underflow the precision available for a BCD e Restrictions on column names vary among database systems For maximum portability limit column names to 10 uppercase characters without embedded spaces e Some database systems do not support date time or date and time data types LabWindows CVI SQL Toolkit Manual 1 4 National Instruments Corporation Chapter 2 Getting Started This chapter introduces the basic concepts of database interactions using the LabWindows CVI SQL Toolkit It also describes the Structured Query Language SQL the Open Database Connectivity ODBC Standard and the Database Session Database Concepts A database consists of an organized collection of data While the underlying details may vary most modern Database Management Systems DBMS store data in tables The tables are organized into rows also known as records and columns also known as fields Every table in a database must have a unique name Similarly every column within a table must have a unique name The database tables have many uses Table 2 1 is an example table that you could use with a simple test executive program to record test sequence results It contains columns for the Unit Under Test UUT number the test name the test result and two measurements Table 2 1 Sample Test Sequence Results UUT_NUM TEST_NAME RESULT MEAS1 MEAS2 20860B456 20860B456 20860B 123 20860B789 20860B789 The da
67. o determine whether the fetch retrieved truncated or NULL data DB_TRUNCATION 1 or DB_NULL_DATA 2 Return Value Result code See DBError for a list of result codes LabWindows CVI SOL Toolkit Manual 4 10 O National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBBindColInt int resCode DBBindColInt int statementHandle int columnNumber int locationForValue int locationForStatus Purpose Specifies the value and status variables in your program that will receive the column s value and length each time the program fetches a record Converts data to a 4 byte integer a long int or int in LabWindows CVI You must bind all columns in the statement You must bind the columns in the order in which they occur in the statement Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL columnNumber integer The column number containing specified variables The first column number is 1 locationForValue integer Pointer to the variable that will receive the int value for the column when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_TRUNCATION 1 or DB_NULL_DATA 2 Return Value R
68. of a widely supported standard for database access You can use the SQL commands to manipulate the rows and columns in database tables The following list describes some of the most useful SQL commands e CREATE TABLE Creates a new table specifying the name and data type for each column e SELECT Retrieves all rows in a table that match specified conditions e INSERT Adds a new record to the table You can then assign values for the columns e UPDATE Changes values in specified columns for all rows that match specified conditions e DELETE Deletes all rows that match specified conditions See Appendix A SOL Reference for a complete list of SQL commands The ODBC Standard Microsoft Corp developed the Open Database Connectivity ODBC Standard as a uniform method for applications to access databases The standard includes a driver packaging standard a method for maintaining Data Source Names and a SQL implementation based on ANSI SQL Because the LabWindows CVI SQL Toolkit and the supplied drivers comply with the ODBC standard you can port LabWindows CVI database applications to other supported databases with minimal changes LabWindows CVI SOL Toolkit Manual 2 2 O National Instruments Corporation Chapter 2 Getting Started The ODBC Administrator On Microsoft Windows 3 x systems ODBC drivers consist of 16 bit DLL drivers Any ODBC driver that you use must be registered into the ODBC INT text file located in the Windows
69. olumns to variables in your program You can then use the fetch functions to retrieve the selected rows Each time you call a fetch function the SQL Toolkit copies the column values into the bound variables You also use the bound variables when updating a row or creating a new row That is when updating or creating a row you copy the new values into bound variables and then call the appropriate function For more details on variable binding see Chapter 3 Using the SQL Toolkit You can also get information about an active SELECT statement such as the number of columns selected the name and data type of a given column and the number of rows selected You will find this information especially useful when selecting all columns SELECT in an unfamiliar table or when creating a program such as a database browser that must access a variety of tables Deactivating SQL Statements After you finish using a statement you should deactivate the statement to free system resources This deactivation is especially important when fetching in any direction so that the toolkit properly closes and deletes temporary log files Disconnecting from the Database At the end of a database session disconnect from the database to free system resources Note To prevent problems in cases of program failure the SQL Toolkit will deactivate any remaining SQL statements and disconnect any remaining connections when program execution ends However for the bes
70. on Database interactions occur within a database session A simple session consists of the following steps 1 Connect to the database 2 Activate SQL statements 3 Process SQL statements 4 Deactivate SQL statements 5 Disconnect from the database Connecting to the Database Before you can execute SQL statements you must establish a connection to a database The SQL Toolkit supports multiple simultaneous connections to a single database or to multiple databases Activating SQL Statements With the SQL Toolkit you can use two methods for activating statements automatic SQL and explicit SQL e Automatic SQL constructs the statement for you Automatic SQL can only construct simple SELECT and CREATE TABLE statements e Explicit SQL must have the statement passed into the function Use explicit SQL for more complex SELECT statements or other types of statements For more details on automatic SQL and explicit SQL see Chapter 3 Using the SQL Toolkit Processing SQL Statements In general only SQL SELECT statements require further processing SELECT statements are important components of the SQL Toolkit You use SELECT statements for the following database operations e Retrieving rows from a table e Updating rows in a table e Creating new rows in a table National Instruments Corporation 2 5 LabWindows CVI SQL Toolkit Manual Getting Started Chapter 2 To use a SELECT statement you must bind the selected c
71. onnect Return Value Result code See DBError for a list of result codes National Instruments Corporation 4 15 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions DBConnect Chapter 4 int connectionHandle DBConnect char connectionString Purpose Opens a connection to a database system so you can execute SQL statements Parameters connectionString Return Value connectionHandle integer Identifies the database system and any additional login information The connection string has the form DSN lt data source name gt lt attribute gt lt value gt lt attribute gt lt value gt The following list consists of the most commonly used attributes For additional attributes see the documentation for the particular driver DSN Name of the data source defined in the ODBC INT file DLG When enabled DLG 1 displays a dialog box that allows user input of connection string information UID The user ID or name PWD The password MODIFYSQL Set to 1 support ODBC compliant SQL Set to 0 support native SQL of the underlying database The returned handle to the database connection This value identifies the connection and acts as a parameter to other functions If the handle is 0 the connection could not be opened LabWindows CVI SQL Toolkit Manual National Instruments Corporation
72. ons use the map handle the column name the address of the target variable and the address of the status variable as parameters DBMapColumnToChar has two additional parameters the size of the buffer and a format string If you do not need to use LabWindows CVI SOL Toolkit Manual 3 2 O National Instruments Corporation Chapter 3 Using the SQL Toolkit formatting use the empty string as the format string See Appendix C Format Strings for a description of format strings After the program maps all the columns call DBAct ivat eMap to construct the SQL SELECT statement execute the statement and bind the columns to the variables DBActivateMap has two parameters the connection handle and the name of the table The return value acts as a handle to the executed SQL statement and is a parameter to other SQL Toolkit functions When you finish using the SQL statement call DBI parameter for DB DeactivateMap to free system resources The only DeactivateMap consists of the map handle from DBActivateMap begin map for constructed SQL statement hmap if DBBeg hmap lt specify t values wi inMap hdbc 0 goto Error he colu ll be p m laced resCode DB uutNumSt if resCode resCode DB if resCode resCode DB if resCode Activate Construc selected yx hstmt DBAc if hstmt Fetching resCode DB apColum at bea I D
73. ontinued SQL Command a Description Example SELECT SELECT DISTINCT Query specifies SELECT uut_num al ie eee columns from tables meas FROM testres FROM from clause WHERE measl lt 0 WHERE where_clause ORDER BY GROUP BY group uut_num DESC clause HAVING having clause UNI ALL SELECT ORDER BY order_clause FOR UPDATE OF col_expr UPDATE table_name Sets columns in UPDATE testres options SET col_name existing rows to new SET meas2 expr WHERE values meas1 0 1 where_clause where meas1 lt 0 LabWindows CVI SQL Toolkit Manual A 2 National Instruments Corporation Appendix A SQL Objects Table A 2 lists SQL objects which are the building blocks for SQL statements table_name col_name col_expr sort_expr data_type constraint column_defn char_expr date_expr num_expr logical_expr expr National Instruments Corporation SQL Reference Table A 2 SQL Objects Object Describes the target table name of the operation for file based databases may include full path Refers to a column in a table Some databases restrict column names Specifies a single column name or a complex combination of column names operators and functions Any column expression Specifies a column s data type Constrains the contents of a column Describes a column to create in a new table Consists of col_name data_t
74. oolkit Sa Database Directory C CVINDATABASE SAMPLES Optional Settings Create Type dBASE5 Locking NONE Lock Compatibility dBASE File Open Cache fo Cache Size 256 International Sort Perform OEM to ANSI Translation Figure 2 2 ODBC dBASE Driver Setup Dialog Box Many of the LabWindows CVI SQL Toolkit ODBC drivers require DLLs from other vendors For more information on system requirements and configuring the supplied ODBC data source please refer to the Help on Individual Drivers section in the online driver help file in the LabWindows CVI SQL Toolkit program group Note If you attempt to use an ODBC driver without the required DLLs or if you incorrectly configure an ODBC driver the following message appears The setup routines for the ODBC driver could not be loaded You may be low on memory and need to quit a fewapplications Third Party ODBC Database Drivers The LabWindows CVI SQL Toolkit supplied ODBC Database Drivers offer you a wide range of support for industry accepted database formats Because the LabWindows CVI SQL Toolkit complies with the ODBC standard you can also use other ODBC compliant database drivers Please refer to your third party vendor documentation for more information on registering your specific database drivers with the ODBC Administrator LabWindows CVI SQL Toolkit Manual 2 4 National Instruments Corporation Chapter 2 Getting Started The Database Sessi
75. pose 3 8 DBDeactivateMap function 4 19 DBDeactivateSQL function description 4 19 freeing system resources 3 4 DBDeleteRecord function description 4 20 example 3 7 DBDisconnect function description 4 20 example 3 2 DBError function description 4 21 to 4 22 example 3 11 DBErrorMessage function description 4 22 example 3 11 DBFetchNext function description 4 23 example 3 5 DBFetchPrev function description 4 24 example 3 5 DBFetchRandom function description 4 25 example 3 5 DBImmediateSQL function description 4 26 example 3 6 DBMapColumnToChar function description 4 27 example 3 2 LabWindows CVI SOL Toolkit Manual DBMapColumnToDouble function description 4 28 example 3 2 DBMapColumnToFloat function 4 29 DBMapColumnTolInt function 4 30 DBMapColumnToShort function 4 31 DBNativeError function 4 32 DBNumberOfColumns function description 4 32 example 3 9 DBNumberOfModifiedRecords function description 4 33 example 3 9 DBNumberOfRecords function description 4 33 example 3 9 DBPutRecord function description 4 34 inserting records 3 6 updating records 3 7 DBRollback function description 4 35 example 3 10 DBSetDatabase function 4 36 DBSources function description 4 37 purpose 3 8 DBTables function description 4 38 to 4 39 purpose 3 8 DBWarning function 4 40 deactivating SQL statements 2 6 DELETE command 2 2 A 1 deleting See also insert update delete reco
76. r Manual Customer Communication National Instruments wants to receive your comments on our products and manuals We are interested in the applications you develop with our products and we want to help if you have problems with them To make it easy for you to contact us this manual contains comment and configuration forms for you to complete You will find these forms in Appendix D Customer Communication at the end of this manual LabWindows CVI SQL Toolkit Manual x National Instruments Corporation Chapter 1 Introduction This chapter describes the installation procedure and lists the main features of the SQL Toolkit Installation This section contains instructions for installing the LabWindows CVI SQL Toolkit on the Windows platform Installing the SQL Toolkit Software Insert LabWindows CVI SQL Toolkit disk one into the 3 5 in disk drive and run the SETUP EXE program using one of the following methods e From Windows select Run from the File menu of the Program Manager In the dialog box that appears type X SETUP where X denotes the proper drive designation e From Windows launch the File Manager Double click on the drive icon that contains the installation disk Find SETUP EXE in the list of files on that disk and double click on it National Instruments Corporation 1 1 LabWindows CVI SOL Toolkit Manual Introduction Chapter 1 Figure 1 1 shows the LabWindows CVI SQL Toolkit installation dialog box
77. rds functions records 3 7 to 3 8 tables 3 8 disconnecting from databases 2 6 3 2 documentation conventions used in manual x organization of manual ix related documentation x DROP TABLE command A 1 O National Instruments Corporation E error checking 3 11 error codes B 1 to B 8 error functions DBError 3 11 4 21 to 4 22 DBErrorMessage 3 11 4 22 DBNativeError 4 31 DBWarning 4 39 function tree table 4 2 purpose 4 3 error reporting by SQL Toolkit functions 4 3 explicit SQL functions activating SQL statements 2 5 3 4 to 3 5 DBActivateSQL 3 4 4 5 DBBindColChar 3 4 4 8 DBBindColDouble 3 4 4 9 DBBindColFloat 3 4 4 10 DBBindColInt 3 4 4 11 DBBindColShort 3 4 4 12 DBDeactivateSQL 3 4 4 19 DBImmediateSQL 3 6 4 25 function tree table 4 2 purpose 4 3 F fax technical support D 1 fetch records functions DBAllowFetchAnyDirection 3 5 4 6 DBFetchNext 3 5 4 23 DBFetchPrev 3 5 4 23 to 4 24 DBFetchRandom 3 5 4 24 function tree table 4 2 purpose 4 3 fetching records 3 5 to 3 6 FOR UPDATE clause SQL table A 4 format strings date time table C 2 to C 3 examples table C 1 numeric table C 4 to C 5 FROM clause SQL table A 3 functions SQL table A 6 to A 7 functions for SQL Toolkit automatic SQL maps functions National Instruments Corporation Index 3 Index DBActivateMap 3 3 4 4 DBBeginMap 3 2 4 6 DBCreateTableFromMap 4 17 DBDeactivat
78. riable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_ TRUNCATION 1 or DB_NULL_DATA 2 Return Value Result code See DBError for a list of result codes National Instruments Corporation 4 9 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBBindColFloat int resCode DBBindColFloat int statementHandle int columnNumber float locationForValue int locationForStatus Purpose Specifies the value and status variables in your program that will receive the column s value and length each time the program fetches a record When fetched the program converts data to a single precision floating point value You must bind all columns in the statement You must bind the columns in the order in which they occur in the statement Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL columnNumber integer The column number containing specified variables The first column number is 1 locationForValue Pointer to the variable that will receive the float value for the column when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable t
79. s and rows in the SELECT numCols DBNumberOfColumns hstmt numRecs DBNumberOfRecords hstmt printf Executed Ss n selectStmt printf Sd rows and d columns selected n numRecs numCols for i 1 i lt numCols i columnName DBColName hstmt i columnType DBColType hstmt i printf column d name s type number d n i colName colType O National Instruments Corporation 3 9 LabWindows CVI SOL Toolkit Manual Using the SQL Toolkit Chapter 3 Transactions You can use the SQL Toolkit to group database changes into transactions A transaction consists of a set of database operations that you can either commit save or roll back discard The toolkit uses the following transaction functions DBBeginTran DBCommit and DBRollback These functions all have one parameter consisting of the database connection handle from DBConnect You begin a transaction by calling DBBeginTran After you have made changes you can call DBCommit to make the changes permanent or DBRollback to discard the changes Each connection can have one active transaction This example from transact prj starts a transaction updates a record and then prompts the user to either commit or rollback the transaction Begin transaction resCode DBBeginTran hdbc if resCode DB_SUCCESS goto Error Execute SQL Statement hstmt DBImmediateSQL hdbc UPDATE TESTRES SET MEAS2 0
80. s the installation procedure and lists the main features of the SQL Toolkit e Chapter 2 Getting Started introduces the basic concepts of database interactions using the LabWindows CVI SQL Toolkit It also describes the Structured Query Language SQL the ODBC Standard and the Database Session e Chapter 3 Using the SQL Toolkit describes how to use the SQL Toolkit functions for common types of database operations and contains example code for performing each operation e Chapter 4 SOL Toolkit Function Descriptions describes the functions in the LabWindows CVI SQL Toolkit The SQL Toolkit Overview section contains general information about the SQL Toolkit functions The SQL Toolkit Function Reference section contains an alphabetical list of function descriptions e Appendix A SOL Reference briefly explains SQL commands operators and functions This version of SQL is included in the ODBC standard and applies to all ODBC compliant databases e Appendix B Error Codes describes the error codes returned by functions in the LabWindows CVI SQL Toolkit In many cases you can obtain additional information about errors by using DBErrorMessage e Appendix C Format Strings describes the format strings that you can use with DBMapColumnToChar and DBBindColChar e Appendix D Customer Communication contains forms to help you gather the information necessary to help National Instruments solve technical problems you might have as we
81. se Connectivity Standard 2 2 operators SQL table A 4 to A 6 ORDER BY clause SQL table A 4 National Instruments Corporation P processing SQL statements 2 5 to 2 6 R records See also insert update delete records functions deleting 3 7 to 3 8 fetching 3 5 to 3 6 inserting 3 6 updating 3 7 S SELECT command 2 2 A 2 SELECT statement functions for returning information 3 9 to 3 10 processing 2 5 to 2 6 set operators SQL table A 5 SQL Structured Query Language clauses table A 3 to A 4 commands 2 2 A 1 to A 2 definition 2 2 functions table A 6 to A 7 objects table A 2 to A 3 operators table A 4 to A 5 SQL statements See also specific statements activating 2 5 automatic SQL 2 5 3 2 to 3 4 explicit SQL 2 5 3 4 to 3 5 deactivating 2 6 processing 2 5 to 2 6 SQL Toolkit automatic SQL maps 3 2 to 3 4 connecting to databases 3 2 creating standalone executables 3 11 deleting records 3 7 to 3 8 tables 3 8 error checking 3 11 National Instruments Corporation Index explicit SQL statements 3 4 to 3 5 features 1 3 to 1 4 fetching records 3 5 to 3 6 functions See functions for SQL Toolkit information functions data source information 3 8 to 3 9 SELECT statement information 3 9 to 3 10 inserting records 3 6 installation 1 1 to 1 3 overview 1 3 to 1 4 portability issues 1 4 transactions 3 10 updating records 3 7 standalone executables creating
82. t use of resources you should explicitly deactivate statements and connections after completing each session LabWindows CVI SQL Toolkit Manual 2 6 National Instruments Corporation Chapter 3 Using the SQL Toolkit This chapter describes how to use the SQL Toolkit functions for common types of database operations and contains example code for performing each operation Function Summary Figure 3 1 shows how the major SQL Toolkit functions relate to each other The rest of this chapter describes the steps in this illustration DBConnect ro DBBeginMap tivatesQoL DBImmediateSQL Y DBMapColumnToChar DBMapColumnToShort DBMapColumnTolnt DBMapColumnToFloat DBMapColumnToDouble l y DBBindColChar DBBindColShort DBBindColInt DBBindColFloat DBBindColDouble DBCreateTableFromMap DBActivateMap DBCreateRecord y Assign Values y DBPutRecord y DBFetchNext DBFetchPrev gt DBFetchRandom y y Use Values Assign New Values y DBPutRecord y Y ies activateMap DBDeactivateSOL DBDisconn ct la Figure 3 1 The SQL Toolkit Functions O National Instruments Corporation 3 1 LabWindows CVI SOL Toolkit Manual Using the SQL Toolkit Chapter 3 Table 3 1 provides the data that the examples i
83. ta in the table are not inherently ordered Ordering grouping and other manipulations of the data occur when you use a SELECT statement to retrieve the data from the table A row can have empty columns which means that the row contains NULL values Notice that the NULL values in a table row are not the same as NULL values in the C programming language Each column in a table has a data type The available data types vary depending on the DBMS The LabWindows CVI SQL Toolkit uses a set of common data types The SQL Toolkit automatically maps these data types into the appropriate type in the underlying database By using the common data types the SQL Toolkit program can access a variety of databases with little or no modification National Instruments Corporation 2 1 LabWindows CVI SQL Toolkit Manual Getting Started Chapter 2 Table 2 2 lists the data types supported by the SQL Toolkit Table 2 2 Data Types Supported by the SQL Toolkit Type Code Type Constant Name Data Type Description DB_CHAR Fixed length character string DB_ VARCHAR Character string DB_DECIMAL Binary Coded Decimal BCD DB_INTEGER Long integer DB_SMALLINT Short integer DB_FLOAT Single precision floating point DB_DOUBLEPREC Double precision floating point DB_DATETIME Date time YYYY MM DD HH MM SS SSSSSS 1 2 3 4 5 6 7 8 The Structured Query Language SQL The Structured Query Language SQL consists
84. tal injury caused by product failure or by errors on the part of the user or application designer Any use or application of National Instruments products for or involving medical or clinical treatment must be performed by properly trained and qualified medical personnel and all traditional medical safeguards equipment and procedures that are appropriate in the particular situation to prevent serious injury or death should always continue to be used when National Instruments products are being used National Instruments products are NOT intended to be a substitute for any form of established process procedure or equipment used to monitor or safeguard human health and safety in medical or clinical treatment Contents About This AMA do ix Organization of This Manual 0 0 00 ceccesscessecessecessecesseceeseceseeceeeaceeeaeeeeaeessaeeeaees ix Conventions Used in This Manual iden datan ade ad odia x Related Documentations seica eriin e i iEn a E iE oa a aa T aS x Customer Communicati iia eeo ans x Chapter 1 Introduction dunas 1 1 Installation a tia add 1 1 Installing the SQL Toolkit Software ooooonnocononccnnoccnonoconnccnoncconnncnnncnnnncnn 1 1 O NN 1 3 Chapter 2 GENE Sal e ee o 2 1 Database CONCE 2 1 The Structured Query Language SOD ivan niente beds 2 2 The ODBE Sad iS O O 2 3 The ODBC Administrator nenni nerien ni i exacesaadevanead condal 2 3 Third Party ODBC Database Drivers usarla 2 4 The Database Session A E A A 2 5 Con
85. tion 4 19 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBDeleteRecord int resCode DBDeleteRecord int statementHandle Purpose Deletes the current record Parameters statementHandle integer Handle to the SQL statement returned by DBActivateSQL or DBActivateMap Return Value Result code See DBError for a list of result codes Note After deleting a record the current record will be positioned between the previous record and the next record in the buffer You must call DBFet chNext after deleting a record to position on the next record DBDisconnect int resCode DBDeactivateMap int connectionHandle Purpose Closes a connection to a database system You should close all connections before your program terminates to free system resources used by the connection DBDisconnect will end execution of any active SQL statements on the connection Parameters Input connectionHandle integer Handle to the database connection returned by DBConnect Return Value Result code See DBError for a list of result codes LabWindows CVI SQL Toolkit Manual 4 20 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBError int errorCode DBError void Purpose Returns the result code of the last SQL Toolkit function you called You should call DBError immediately after calling any other SQL Toolkit function that does not return
86. tions to retrieve the information Table 4 3 shows the columns contained in each record Table 4 3 Columns Contained in Each Record Name Char 32 Source name Extension Char 32 File Extension may be NULL Connection Handle Short integer Connection handle of connection if connected to this source Returns 0 if not connected Remarks Char 256 Remarks may be NULL Parameters option integer Determines which sources will be returned The possible values are DB_SRC_AVAILABLE 1 which returns all sources and DB_SRC_CONNECTED 2 which returns only currently connected sources Return Value statementHandle integer Returned handle to the statement execution This value identifies the statement and is a parameter to other functions If 0 the statement could not be executed National Instruments Corporation 4 37 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBTables int StatementHandle DBTables int connectionHandle char qualifierPattern char userPattern char tablePattern int flags Purpose Creates and activates a SELECT statement that returns information about the available tables on a connection You can then use the DBBindCol and DBFet ch functions to retrieve the information Table 4 4 shows the columns contained in each record Table 4 4 Columns Contained in Each Record Table Qualifier Char 128 Qualifier for r
87. tring GMT is lt string gt GMT GD General format for dates the Short Date Format in the international section of the Windows control panel Do not combine other format symbols with GD except US General format for dates with times the Time Format in the international section of the Windows control panel 1s appended to the Short Date Format Do not combine other format symbols with GDT except US General long format for dates the Long Date Format in the international section of the Windows control panel Do not combine other format symbols with GL except US General long format for dates with times the Time Format in the international section of the Windows control panel is appended to the Long Date Format Do not combine other format symbols with GLT except US General format for time the Time Format in the international section of the Windows control panel Do not combine other format symbols with GT Combine with GD GDT GL GLT GT to override the international section of the Windows control panel and use the United States defaults instead National Instruments Corporation C 3 LabWindows CVI SQL Toolkit Manual Format Strings Appendix C Numeric Format Strings You can use numeric format strings to format numbers in a variety of ways Numeric formats can have either one or two sections separated by a semicolon For formats with one section use the s
88. tus variables that will receive the column s value and status each time the program fetches a record Parameters mapHandle integer Handle to the map returned by DBBeginMap columnName string The name of the column containing specified variables locationForValue Pointer to the variable that will receive the short int value for the column when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_TRUNCATION 1 or DB_NULL_DATA 2 Return Value Result code See DBError for a list of result codes O National Instruments Corporation 4 31 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 DBNativeError int nativeErrCode DBNativeError void Purpose Returns the result code from the underlying database system for the last SQL Toolkit function you called You can call DBNativeError when DBError returns a value of DB_DBSYS_ERROR 4 to determine the native error code Parameters None Return Value nativeErrCode integer Native error code in the underlying database system If 0 no database system error was reported If DBError returned DB_DBSYS_ERROR 4 DBNativeError may return 0 In this case the underlying database system
89. u must include this file in all code modules that reference the SQL Toolkit Reporting Errors Most of the functions in the SQL Toolkit return an integer code containing the result of the call A negative return code indicates that an error occurred Otherwise the function completed successfully The remaining functions return a handle or other values A handle value of zero indicates that an error occurred You can call DBError to get the error code for the most recent SQL Toolkit function National Instruments Corporation 4 3 LabWindows CVI SQL Toolkit Manual SQL Toolkit Function Descriptions Chapter 4 SQL Toolkit Function Reference This section describes each function in the SQL Toolkit The functions are arranged alphabetically DBActivateMap int statementHandle DBActivateMap int mapHandle char tableName Purpose Activates a map This process includes constructing a SQL SELECT statement based on the map and table name executing the statement binding program variables to the resulting columns and freeing resources used by the map Parameters mapHandle integer The handle to the map returned by DBBeginMap tableName string Name of the database table the map will be applied to Return Value statementHandle integer Returned handle to the statement execution This value identifies the statement and is a parameter to other functions If 0 the statement could not be executed LabWindows C
90. umber must be obtained from the factory and clearly marked on the outside of the package before any equipment will be accepted for warranty work National Instruments will pay the shipping costs of returning to the owner parts which are covered by warranty National Instruments believes that the information in this manual is accurate The document has been carefully reviewed for technical accuracy In the event that technical or typographical errors exist National Instruments reserves the right to make changes to subsequent editions of this document without prior notice to holders of this edition The reader should consult National Instruments if errors are suspected In no event shall National Instruments be liable for any damages arising out of or related to this document or the information contained in it EXCEPT AS SPECIFIED HEREIN NATIONAL INSTRUMENTS MAKES NO WARRANTIES EXPRESS OR IMPLIED AND SPECIFICALLY DISCLAIMS ANY WARRANTY OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE CUSTOMER S RIGHT TO RECOVER DAMAGES CAUSED BY FAULT OR NEGLIGENCE ON THE PART OF NATIONAL INSTRUMENTS SHALL BE LIMITED TO THE AMOUNT THERETOFORE PAID BY THE CUSTOMER NATIONAL INSTRUMENTS WILL NOT BE LIABLE FOR DAMAGES RESULTING FROM LOSS OF DATA PROFITS USE OF PRODUCTS OR INCIDENTAL OR CONSEQUENTIAL DAMAGES EVEN IF ADVISED OF THE POSSIBILITY THEREOF This limitation of the liability of National Instruments will apply regardless of the form of action whether i
91. when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_TRUNCATION 1 or DB_NULL_DATA 2 Return Value Result code See DBError for a list of result codes LabWindows CVI SQL Toolkit Manual 4 28 National Instruments Corporation Chapter 4 SQL Toolkit Function Descriptions DBMapColumnToFloat int resCode DBMapColumnToFloat int mapHandle char columnName float locationForValue int locationForStatus Purpose Specifies a column to be selected and the value and status variables that will receive the column s value and status each time the program fetches a record Parameters mapHandle integer Handle to the map returned by DBBeginMap columnName string The name of the column containing specified variables locationForValue Pointer to the variable that will receive the float value for the column when the program fetches a record locationForStatus integer Pointer to the variable that will receive the column s status when the program fetches a record After fetching a record you can use this variable to determine whether the fetch retrieved truncated or NULL data DB_TRUNCATION 1 or DB_NULL_DATA 2 Return Value Result code See
92. xt except you must also pass the record number to DBFet chRandom To use DBFet chPrev or DBFet chRandom you must enable fetching in any direction by calling DBAl lowFet chAnyDirection before activating the statement or map The following example first fetches the last record notice the use of DBNumberOfRecords and then fetches the remaining records in reverse order You can find this example as part of readtab3 prj allow DBFetchPrev and DBFetchxRandom resCode DBAllowFetchAnyDirection hdbc 1 if resCode DB_SUCCESS goto Error activate explicit or automatic SQL statement Fetch the last record numRecs DBNumberOfRecords hstmt resCode DBFetchRandom hstmt numRecs if resCode DB_SUCCESS goto Error printf Serial Number s measurement 1 f measurement 2 f n uutNum measl meas2 Fetch the records in reverse order Notice that as each record is fetched the column values are placed in the specified variables while resCode DBFetchPrev hstmt DB_SUCCESS printf Serial Number s measurement 1 f measurement 2 f n uutNum measl meas2 National Instruments Corporation 3 5 LabWindows CVI SQL Toolkit Manual Using the SQL Toolkit Chapter 3 if resCode DB_SUCCESS amp amp resCode DB_EOF goto Error Inserting a Record You can insert a record with a SQL INSERT statement and DBAct ivateS
93. y cases you can port your application to another database by simply changing the connection string passed to the DBConnect function e Converts database column values from native data types to eight standard SQL Toolkit data types further enhancing portability e Permits the use of SQL statements with all supported database systems even non SQL systems e Includes functions to retrieve the name and data type of a column returned by a SELECT statement e Scrolls backward and forward through records returned by a SELECT statement even in databases that do not support backward scrolling e Creates tables and selects inserts updates and deletes records without using SQL statements e Allows the use of transactions which group database operations together so that they can be committed or canceled as a unit with databases that do not support transactions LabWindows CVI SOL Toolkit Manual 1 3 National Instruments Corporation Introduction Chapter 1 Because of the wide range of databases the SQL Toolkit works with some portability issues remain You should consider the following when choosing your database system e Some database systems particularly the flat file databases such as dBase do not support floating point numbers In such cases the SQL Toolkit converts floating point numbers to the nearest equivalent usually Binary Coded Decimal BCD before storing them in the database Very large or very small floating point
94. y from 8 00 a m to 6 00 p m central time In other countries contact the nearest branch office You may fax questions to us at any time Corporate Headquarters 512 795 8248 Technical support fax 800 328 2203 512 794 5678 Branch Offices Phone Number Australia 03 9 879 9422 Austria 0662 45 79 90 0 Belgium 02 757 00 20 Canada Ontario 519 622 9310 Canada Quebec 514 694 8521 Denmark 45 76 26 00 Finland 90 527 2321 France 1 48 14 24 24 Germany 089 741 31 30 Hong Kong 2645 3186 Italy 02 48301892 Japan 03 5472 2970 Korea 02 596 7456 Mexico 5 202 2544 Netherlands 03480 33466 Norway 32 84 84 00 Singapore 2265886 Spain 91 640 0085 Sweden 08 730 49 70 Switzerland 056 20 51 51 Taiwan 02 377 1200 U K 01635 523545 O National Instruments Corporation Fax Number 03 9 879 9179 0662 45 79 90 19 02 757 03 11 519 622 9311 514 694 4399 45 7671 11 90 502 2930 1 48 14 24 14 089 714 60 35 2686 8505 02 48301915 03 5472 2977 02 596 7455 5 520 3282 03480 30673 32 84 86 00 2265887 91 640 0533 08 730 43 70 056 20 51 55 02 737 4644 01635 523154 LabWindows CVI SQL Toolkit Manual LabWindows CVI Technical Support Form Photocopy this form and update it each time you make changes to your software or hardware Use your completed copy of this form as a reference for your current configuration Completing this form accurately before contacting National Instruments for technical support helps our applications engine
95. you call DBBeginTran you must call either DBCommit or DBRollback before you call DBBeginTran again on the same database connection After you call DBBeginTran you must call either DBCommit or DBRollback before you call DBDisconnect Calling DBDisconnect with an active transaction results in an error Parameters connectionHandle integer The handle to the database connection returned by DBConnect Return Value Result code See DBError for a list of result codes O National Instruments Corporation 4 7 LabWindows CVI SOL Toolkit Manual SQL Toolkit Function Descriptions DBBindColChar Chapter 4 int resCode DBBindColChar int statementHandle int columnNumber int maxLen char locationForValue int locationForStatus char formatString Purpose Specifies the value and status variables in your program that will receive a column s value and length each time the program fetches a record You must bind all columns in the SELECT statement You must bind the columns in the order in which they occur in the statement Parameters statementHandle columnNumber maxLen locationFor Value locationForStatus formatString Return Value integer integer integer integer string Handle to the SQL statement returned by DBActivateSQL The column number containing specified variables The first column number is 1 Size of the value variable
96. ype and optional constraint Any expression that yields a character data type Any expression that yields a date data type Any expression that yields a number data type Any expression that yields a logical data type Any expression A 3 testres c cvi database testres dbf uut_num meas 1 uut_num meas meas2 LOWER uut_num CHAR 30 NUMBER 10 5 NOT NULL uut_num CHAR 10 NOT NULL measl NUMBER 10 5 PASSED STR 42 6 10 2 DATE measl meas2 LabWindows CVI SOL Toolkit Manual SQL Reference Appendix A SQL Clauses Table A 3 lists the types of clauses you can use in SQL statements Table A 3 SQL Clauses Applicable Name Syntax Commands Description Examples FROM table_name SELECT Specifies table name SELECT FROM options table alias DELETE may be a full path name testres for file based databases WHERE exprl SELECT Specifies conditions that SELECT FROM comparison_oper expr2 DELETE apply to each row in the testres WHERE logical_oper expr3 table to determine an meas1 lt 0 0 and comparison_oper expr4 UPDATE active set of rows meas2 gt 1 0 GROUP BY col_expr SELECT Specifies column s to SELECT FROM col_expr apply to group active set testres GROUP BY rows meas HAVING exprl SELECT Specifies conditions to SELECT FROM comparison_oper expr2 used with apply to group active set testres GROUP BY GROUP BY rows

Download Pdf Manuals

Related Search

Related Contents

Biothrine® Flow  Panasonic AG-HPX172 Camera Accessories User Manual    GE 19234 User's Manual  Lion alcolmeter SD-400  ADTRAN NETVANTA 3120 User's Manual  長期除電性能が安定。 除電エリアが広がる バタフライルーバー！ 独自の  

Copyright © All rights reserved. Failed to retrieve file