ALTIBASE开发培训.ppt

1 ALTIBASE开发培训 天津南开创元信息技术有限公司韩国Altibase公司 2 CONTENTS 1 AltibaseCLI 3 1 AltibaseCLI CLIOverviewCLIAPIGroupsBasicProgrammingStepsNoteswhenimplementingProgramsSample Basic 4 Overview 1 CallLevelInterfaceCLIIsStandard AltibaseCLIcomplieswithX OPENCLIstandardspecification Soyoucanfindeasilyusefulsampleanddocumentsex Altibase MSDN ODBC DB2 Informix Postgres Enablesyoutowritedatabaseindependentapplications 5 Overview 2 DifferencebetweenCLIandEmbeddedSQL Ex SQLExecDirect stmt deletefromt1 SQL NTS EXECSQLdeletefromT1 E SQL SESC ProC needsprecompilestep Belowtwothingsismypersonalopinions E SQLismoreeasyindevelopmentandsourcereadability InC Object OrientedStyledevelopmethod CLIcanbemoregoodchoicethanE SQL 6 Overview 3 CLIAPIFunctionsGroupsEnvironment ConnectionManagementex SQLAllocEnv SQLDriverConnect SQLStatementProcessingex SQLPrepare SQLExecute AttributeProcessingex SQLSetConnectAttr SQLSetStmtAttr MetaInformationProcessingex SQLTables CLIAPIGroups 1 Environment ConnectionManagement SQLStatementProcessing SQLAllocEnv SQLAllocConnect SQLAllocStmt SQLFreeEnv SQLFreeConnect SQLFreeStmt SQLDriverConnectSQLConnectSQLDisconnect SQLPrepare SQLBindParameter SQLExecute SQLNumResultColsSQLDescribeColSQLRowCountSQLGetData SQLBindColSQLFetchSQLExecDirectSQLEndTran 8 AttributeProcessing MetaInformationProcessing SQLSetEnvAttr SQLSetConnectAttr SQLSetStmtAttr ex ArrayInsert Update SQLTablesSQLColumnsSQLProcedures SQLPrimaryKeysSQLForeignKeysSQLStatistics CLIAPIGroups 2 SQLProcedureColumnsSQLSpecialColumnsSQLTablePrivilegs 9 SQLRETURNSQLAllocEnv SQLHENV env rc SQLAllocEnv Allocatesanenvironmenthandleandassociatedresources if rc SQL SUCCESS Environment ConnectionManagement 1 SQLRETURNSQLAllocConnect SQLHENVenv SQLHDBC dbc rc SQLAllocConnect henv Allocatesaconnectionhandleandassociatedresourcesif rc SQL SUCCESS SQLRETURNSQLAllocStmt SQLHDBCDBC SQLHSTMT stmt rc SQLAllocStmt Allocatesanewstatementhandleand associatesitwiththeconnectionspecifiedbytheconnectionhandle if rc SQL SUCCESS 10 SQLRETURNSQLFreeEnv SQLHENVenv SQLRETURNSQLFreeConnect SQLHDBCdbc SQLRETURNSQLFreeStmt SQLHSTMTstmt SQLSMALLINTfOption rc SQLFreeStmt hstmt SQL DROP Freesastatementhandleif rc SQL SUCCESS rc SQLDisconnect hdbc Closestheconnectionassociatedwiththedatabaseconnectionhandle if rc SQL SUCCESS printf DisconnectError n SQLFreeConnect hdbc Invalidatesandfreestheconnectionhandle SQLFreeEnv henv Invalidatesandfreestheenvironmenthandle Environment ConnectionManagement 2 11 SQLRETURNSQLDriverConnect SQLHDBCdbc SQLHWNDwindowHandle NotUsed SQLCHAR InConnectionString SQLSMALLINT length1 SQLCHAR OutConnectionString SQLSMALLINTbufferLength SQLSMALLINT strlength2Ptr SQLSMALLINT DriverCompletion charconnstr 100 sprintf connstr DSN s UID s PWD s CONNTYPE 1 PORT NO s server uid pwd port rc SQLDriverConnect hdbc NULL connstr SQL NTS NULL 0 NULL SQL DRIVER NOPROMPT if rc SQL SUCCESS Environment ConnectionManagement 3 12 sprintf sql SELECTENAMEFROMEMPLOYEEWHERENAME rc SQLPrepare hstmt1 sql SQL NTS SQLStatementProcessingExamples 1 SQLRETURNSQLPrepare SQLHSTMTstmt SQLCHAR sql SQLINTEGERsqlLength SQLRETURNupd execute intpos if chk stmt u NOT YET PREPARED rc upd prepare performancefactor if SQLExecute stmt u SQL SUCCESS unicom upd ins cli app1 cpp 13 SQLRETURNSQLBindParameter SQLHSTMTStatementHandle SQLSMALLINTParameterNumber SQLSMALLINTpType SQLSMALLINTcType SQLSMALLINTsqlType SQLINTEGERcolumnSize SQLSMALLINTscale numeric columnSize scale SQLPOINTERParameterValuePtr SQLINTEGERBufferLength sizeof ParameterValuePrt SQLINTEGER StrLen or IndPtr when 1meansNULLvalueSQL NULL DATA 1 SQLStatementProcessingExamples 2 rc SQLBindParameter hstmt1 1 SQL PARAM INPUT SQL C CHAR SQL VARCHAR ID LEN 0 id sizeof id NULL if rc SQL SUCCESS 14 Correcttypematchingismajorperformancefator integer smallint bigintdatatypesshowfasterperformancethannumeric Speciallyintheindexcolumn numerictypehavebigtypeconversioncost SQLStatementProcessingExamples 3 15 SQLRETURNSQLBindCol SQLHSTMTstmt SQLSMALLINTColumnNumber SQLSMALLINTTargetType SQLPOINTERTargetValuePtr SQLINTEGERBufferLength SQLINTEGER StrLen or IndPtr SQLRETURNSQLFetch SQLHSTMTstmt SQLStatementProcessingExamples 4 16 SQLExecDirect Thisisjustsample Don tuseSQLExecDirectforperformancehstmt2 selectid namefromemployee SQL NTS if SQL SUCCESS SQLBindCol hstmt2 1 SQL C CHAR id 8 NULL if SQL SUCCESS SQLBindCol hstmt2 2 SQL C CHAR name 30 SQLStatementProcessingExamples 5 BasicProgrammingSteps 1 CLIApplicationcontains InitializationTransactionProcessingTerminationDiagnosticMessageHandling 18 STEP1 CONNECTSQLAllocHandle env SQLAllocEnvSQLSetEnvAttrSQLAllocHandle dbc SQLAllocConnectSQLDriverConnectSQLSetConnectAttr setnon autocommit BasicProgrammingSteps STEP2 INITIALIZESQLAllocHandle stmt SQLAllocStmtSQLSetStmtAttr whenarraybinding BasicProgrammingSteps 2 19 STEP3 ExecuteMetaCatalogfunctionOrSQLExecute SQLExecDirect 1 SelectorMetaCatalog STEP4a FetchResultsSQLNumResultColsSQLDescribeColSQLBindColSQLFetchSQLGetData STEP4b FetchRowCountSQLRowCount Non array affectedresultnum 2 Update Delete Insert 3 Other BasicProgrammingSteps 3 20 STEP5 TRANSACTSQLEndTran STEP6 DISCONNECTSQLFreeHandle stmt SQLFreeStmtSQLDisconnectSQLFreeHandle dbc SQLFreeConnectSQLFreeHandle env SQLFreeEnv BasicProgrammingSteps 4 21 NotesWhenImplementingPrograms 1 Step1 Formulti threadprogram thefunctionideAllocErrorSpace shouldbecalledinthreadmainfunctionbeforeotherDBfunctions Formulti threadprogram thesameconnectionhandlemustnotbeusedonothertheads CLIAPIisnotsafeinSignal Don tuseSignal ifitisneeded thenusethread if 0 pthread create sample SQLCLI demo mt cpp 22 NotesWhenImplementingPrograms 2 Step2 InSQLBindCol orSQLBindParameter thelastparametervalueLength lStrLen or IndPtr isindicatorvariabletocheck NULLorNotNULL Step3 WhenyouexitaprogramwithoutexecutingtheCOMMITstatementinAUTOCOMMITOFFsession SQLstatementsthatyoucannotfinishareallROLLBACKed ButiftheprogramexitsafterexecutingtheSQLDisconnect theCOMMITstatementisperformed 23 Step4 InSQLFreeStmt RememberthedifferencebetweenSQL DROPandSQL CLOSE SQL DROPReleaseallresourceofthestatement Forreuse firstlySQLAllocStmtisneeded SQL CLOSECheckdemo ex2 cpp Thisisusedforreleasenon endedfetchresultonly NotesWhenImplementingPrograms 3 24 SQLFreeStmt stmt SQL CLOSE Ifacursorfetchedthelastrecordofaresultset SQL CLOSEcanbeomitted Howerver iftherearesomerecordstobefetchedbySQLFetch andSQLExecute isre executed invalidcusorerroroccurs sample SQLCLI demo ex2 cpp NotesWhenImplementingPrograms 4 25 include include include defineSQL LEN1000 defineMSG LEN1024SQLHENVenv Allocatesanenvironmenthandle SQLHDBCdbc Allocatesanconnectionhandle intconn flag intexecute type SQLRETURNalloc handle SQLRETURNdb connect voidfree handle SQLRETURNexecute select voidexecute err SQLHDBCaCon SQLHSTMTaStmt char q Sample Basic 1 26 intmain SQLRETURNrc env SQL NULL HENV dbc SQL NULL HDBC conn flag 0 execute type 1 Allocatesthehandle rc alloc handle if rc SQL SUCCESS free handle exit 1 rc db connect if rc SQL SUCCESS free handle exit 1 Sample Basic 1 Tasks switch execute type case1 rc execute select break case1 rc execute insert break case1 rc execute update break case1 rc execute delete break if rc SQL SUCCESS printf Successexecute free handle 27 voidexecute err SQLHDBCaCon SQLHSTMTaStmt char q SQLINTEGERSQLCODE SQLCHARSQLSTATE 7 SQLSMALLINTmsgLength