ODBC Schema In Powerbuilder
source code from topwiz. I have edit it.
source code example
n_odbcapi from nonvisualobject
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 |
forward global type n_odbcapi from nonvisualobject end type end forward global type n_odbcapi from nonvisualobject autoinstantiate end type type prototypes Function long SQLAllocHandle ( & integer HandleType, & ulong InputHandle, & Ref ulong OutputHandlePtr & ) Library "odbc32.dll" Function long SQLFreeHandle ( & integer HandleType, & ulong Handle & ) Library "odbc32.dll" Function long SQLSetEnvAttr ( & ulong EnvironmentHandle, & long Attribute, & ulong ValuePtr, & long StringLength & ) Library "odbc32.dll" Function long SQLGetDiagRec ( & integer HandleType, & ulong Handle, & integer RecNumber, & Ref string Sqlstate, & Ref long NativeErrorPtr, & Ref string MessageText, & integer BufferLength, & Ref integer TextLengthPtr & ) Library "odbc32.dll" Alias For "SQLGetDiagRec;Ansi" Function long SQLBindCol ( & ulong StatementHandle, & UInt ColumnNumber, & integer TargetType, & Ref string TargetValuePtr, & long BufferLength, & Ref integer StrLen_or_IndPtr & ) Library "odbc32.dll" Alias For "SQLBindCol;Ansi" Function long SQLBindCol ( & ulong StatementHandle, & UInt ColumnNumber, & integer TargetType, & Ref integer TargetValuePtr, & long BufferLength, & Ref integer StrLen_or_IndPtr & ) Library "odbc32.dll" Function long SQLBindCol ( & ulong StatementHandle, & UInt ColumnNumber, & integer TargetType, & Ref long TargetValuePtr, & long BufferLength, & Ref integer StrLen_or_IndPtr & ) Library "odbc32.dll" Function long SQLFetch ( & ulong statementhandle & ) Library "odbc32.dll" Function long SQLDataSources ( & ulong EnvironmentHandle, & UInt Direction, & Ref string ServerName, & integer BufferLength1, & Ref integer NameLength1Ptr, & Ref string Description, & integer BufferLength2 , & Ref integer NameLength2Ptr & ) Library "odbc32.dll" Alias For "SQLDataSources;Ansi" Function long SQLTables ( & ulong StatementHandle, & string CatalogName, & integer NameLength1, & string SchemaName, & integer NameLength2, & string TableName, & integer NameLength3, & string TableType, & integer NameLength4 & ) Library "odbc32.dll" Alias For "SQLTables;Ansi" Function long SQLColumns ( & ulong StatementHandle, & string CatalogName, & integer NameLength1, & string SchemaName, & integer NameLength2, & string TableName, & integer NameLength3, & string ColumnName, & integer NameLength4 & ) Library "odbc32.dll" Alias For "SQLColumns;Ansi" Function long SQLPrimaryKeys ( & ulong StatementHandle, & string CatalogName, & integer NameLength1, & string SchemaName, & integer NameLength2, & string TableName, & integer NameLength3 & ) Library "odbc32.dll" Alias For "SQLPrimaryKeys;Ansi" Function long SQLForeignKeys ( & ulong StatementHandle, & string PKCatalogName, & integer NameLength1, & string PKSchemaName, & integer NameLength2, & string PKTableName, & integer NameLength3, & string FKCatalogName, & integer NameLength4, & string FKSchemaName, & integer NameLength5, & string FKTableName, & integer NameLength6 & ) Library "odbc32.dll" Alias For "SQLForeignKeys;Ansi" Function long SQLProcedures ( & ulong StatementHandle, & string CatalogName, & integer NameLength1, & string SchemaName, & integer NameLength2, & string ProcName, & integer NameLength3 & ) Library "odbc32.dll" Alias For "SQLProcedures;Ansi" Function long SQLGetInfo ( & ulong ConnectionHandle, & integer InfoType, & Ref string InfoValuePtr, & integer BufferLength, & Ref int StringLengthPtr & ) Library "odbc32.dll" Alias For "SQLGetInfo;Ansi" Function long SQLExecDirect ( & ulong StatementHandle, & string StatementText, & long TextLength & ) Library "odbc32.dll" Alias For "SQLExecDirect;Ansi" Function long SQLBrowseConnect ( & ulong ConnectionHandle, & string InConnectionString, & integer StringLength1, & Ref string OutConnectionString, & integer BufferLength, & Ref integer StringLength2Ptr & ) Library "odbc32.dll" Alias For "SQLBrowseConnect;Ansi" Function long SQLSetConnectAttr ( & ulong ConnectionHandle, & integer Attribute, & long ValuePtr, & integer StringLength & ) Library "odbc32.dll" Function long SQLSetConnectAttr ( & ulong ConnectionHandle, & integer Attribute, & string ValuePtr, & integer StringLength & ) Library "odbc32.dll" Alias For "SQLSetConnectAttr;Ansi" end prototypes type variables // return values from functions Constant Long SQL_SUCCESS = 0 Constant Long SQL_SUCCESS_WITH_INFO = 1 Constant Long SQL_NO_DATA = 100 Constant Long SQL_ERROR = -1 Constant Long SQL_INVALID_HANDLE = -2 Constant Long SQL_NEED_DATA = 99 // other values Constant Integer SQL_NTS = -3 Constant Long SQL_NULL_HANDLE = 0 Constant Integer SQL_MAX_DSN_LENGTH = 32 Constant Long SQL_ATTR_ODBC_VERSION = 200 Constant ULong SQL_OV_ODBC3 = 3 // SQLAllocHandle constants Constant Integer SQL_HANDLE_ENV = 1 Constant Integer SQL_HANDLE_DBC = 2 Constant Integer SQL_HANDLE_STMT = 3 Constant Integer SQL_HANDLE_DESC = 4 // SQLTables column numbers constant UInt SQLTAB_TABLE_CAT = 1 Constant UInt SQLTAB_TABLE_SCHEM = 2 Constant UInt SQLTAB_TABLE_NAME = 3 Constant UInt SQLTAB_TABLE_TYPE = 4 Constant UInt SQLTAB_REMARKS = 5 // SQLColumns column numbers constant UInt SQLCOL_TABLE_CAT = 1 Constant UInt SQLCOL_TABLE_SCHEM = 2 Constant UInt SQLCOL_TABLE_NAME = 3 Constant UInt SQLCOL_COLUMN_NAME = 4 Constant UInt SQLCOL_DATA_TYPE = 5 Constant UInt SQLCOL_TYPE_NAME = 6 Constant UInt SQLCOL_COLUMN_SIZE = 7 Constant UInt SQLCOL_BUFFER_LENGTH = 8 Constant UInt SQLCOL_DECIMAL_DIGITS = 9 Constant UInt SQLCOL_NUM_PREC_RADIX = 10 Constant UInt SQLCOL_NULLABLE = 11 Constant UInt SQLCOL_REMARKS = 12 Constant UInt SQLCOL_COLUMN_DEF = 13 Constant UInt SQLCOL_SQL_DATA_TYPE = 14 Constant UInt SQLCOL_SQL_DATETIME_SUB = 15 Constant UInt SQLCOL_CHAR_OCTET_LENGTH = 16 Constant UInt SQLCOL_ORDINAL_POSITION = 17 Constant UInt SQLCOL_IS_NULLABLE = 18 // SQLPrimaryKeys column numbers Constant UInt SQLPK_TABLE_CAT = 1 Constant UInt SQLPK_TABLE_SCHEM = 2 Constant UInt SQLPK_TABLE_NAME = 3 Constant UInt SQLPK_COLUMN_NAME = 4 Constant UInt SQLPK_KEY_SEQ = 5 Constant UInt SQLPK_PK_NAME = 6 // SQLForeignKeys column numbers Constant UInt SQLFK_PKTABLE_CAT = 1 Constant UInt SQLFK_PKTABLE_SCHEM = 2 Constant UInt SQLFK_PKTABLE_NAME = 3 Constant UInt SQLFK_PKCOLUMN_NAME = 4 Constant UInt SQLFK_FKTABLE_CAT = 5 Constant UInt SQLFK_FKTABLE_SCHEM = 6 Constant UInt SQLFK_FKTABLE_NAME = 7 Constant UInt SQLFK_FKCOLUMN_NAME = 8 Constant UInt SQLFK_KEY_SEQ = 9 Constant UInt SQLFK_UPDATE_RULE = 10 Constant UInt SQLFK_DELETE_RULE = 11 Constant UInt SQLFK_FK_NAME = 12 Constant UInt SQLFK_PK_NAME = 13 Constant UInt SQLFK_DEFERRABILITY = 14 // SQLProcedures column numbers Constant UInt SQLSP_PROCEDURE_CAT = 1 Constant UInt SQLSP_PROCEDURE_SCHEM = 2 Constant UInt SQLSP_PROCEDURE_NAME = 3 Constant UInt SQLSP_REMARKS = 7 Constant UInt SQLSP_PROCEDURE_TYPE = 8 // SQL data type codes Constant Long SQL_CHAR = 1 Constant Long SQL_NUMERIC = 2 Constant Long SQL_DECIMAL = 3 Constant Long SQL_INTEGER = 4 Constant Long SQL_SMALLINT = 5 Constant Long SQL_FLOAT = 6 Constant Long SQL_REAL = 7 Constant Long SQL_DOUBLE = 8 Constant Long SQL_DATETIME = 9 Constant Long SQL_VARCHAR = 12 Constant Long SQL_NVARCHAR = 247 Constant Long SQL_TEXT = -1 Constant Long SQL_BINARY = -2 Constant Long SQL_VARBINARY = -3 Constant Long SQL_IMAGE = -4 Constant Long SQL_TINYINT = -6 Constant Long SQL_BIT = -7 Constant Long SQL_WCHAR = -8 Constant Long SQL_WVARCHAR = -9 Long SQL_CHARACTER = SQL_WCHAR // DateTime sub-types Constant Long SQL_CODE_DATE = 1 Constant Long SQL_CODE_TIME = 2 Constant Long SQL_CODE_TIMESTAMP = 3 // FetchOrientation Constant UInt SQL_FETCH_NEXT = 1 Constant UInt SQL_FETCH_FIRST = 2 Constant UInt SQL_FETCH_LAST = 3 Constant UInt SQL_FETCH_PRIOR = 4 Constant UInt SQL_FETCH_ABSOLUTE = 5 Constant UInt SQL_FETCH_RELATIVE = 6 // FetchOrientation for SQLDataSources only Constant UInt SQL_FETCH_FIRST_USER = 31 Constant UInt SQL_FETCH_FIRST_SYSTEM = 32 // SQL Server Constant Integer DEFAULT_RESULT_SIZE = 1024 Constant Integer SQL_COPT_SS_BASE_EX = 1240 Constant Integer SQL_COPT_SS_BROWSE_CONNECT = SQL_COPT_SS_BASE_EX + 1 Constant Integer SQL_COPT_SS_BROWSE_SERVER = SQL_COPT_SS_BASE_EX + 2 Constant Integer SQL_MORE_INFO_NO = 0 Constant Integer SQL_MORE_INFO_YES = 1 Constant Integer SQL_ATTR_LOGIN_TIMEOUT = 103 //Transaction Transaction itran_conn end variables forward prototypes public function integer of_columns (string as_schema, string as_tablename, ref s_columns astr_columns[]) public function integer of_tables (string as_tabletype, ref s_tables astr_table[]) public function integer of_sprocs (ref s_sprocs astr_sprocs[]) public function integer of_sprocsource (string as_schema, string as_sprocname, ref string as_source) public function string of_replaceall (string as_oldstring, string as_findstr, string as_replace) public function integer of_datasources (string as_type, ref string as_name[], ref string as_driver[]) public function integer of_primarykeys (string as_schema, string as_tablename, ref s_primarykeys astr_pkeys[]) public function integer of_foreignkeys (string as_schema, string as_tablename, ref s_foreignkeys astr_fkeys[]) public function integer of_sqlerror (string as_function, integer ai_handletype, long al_handle) public subroutine of_sqlwarning (string as_function, integer ai_handletype, long al_handle) public function integer of_parse (string as_text, string as_sep, ref string as_array[]) public function string of_pbodbini () public subroutine of_settransaction (transaction atran) public function boolean of_isvalid () end prototypes public function integer of_columns (string as_schema, string as_tablename, ref s_columns astr_columns[]);//==================================================================== // Function: n_odbcapi.of_columns() //-------------------------------------------------------------------- // Description: get list of columns //-------------------------------------------------------------------- // Arguments: // value string as_schema // value string as_tablename // reference s_columns astr_columns[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_columns ( string as_schema, string as_tablename, ref s_columns astr_columns[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== ULong ll_handle Long ll_return, ll_position, ll_width, ll_coldecimal Integer li_count, li_strlen, li_coltype String ls_catalog, ls_schema, ls_colname, ls_datatype String ls_nullable, ls_default // null out the search vars SetNull(ls_catalog) SetNull(ls_colname) If as_schema = "" Then SetNull(as_schema) End If If Not of_isvalid() Then MessageBox( "Warning", "Transaction Not Connect", Information!) Return -1 End If // allocate a statement handle ll_return = SQLAllocHandle(SQL_HANDLE_STMT, itran_conn.DBHandle(), ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_Columns", SQL_HANDLE_STMT, ll_handle) End If // get a list of columns ll_return = SQLColumns(ll_handle, ls_catalog, 0, as_schema, Len(as_schema), as_tablename, Len(as_tablename), ls_colname, 0) If ll_return = SQL_ERROR Then Return of_SQLError("of_Columns", SQL_HANDLE_STMT, ll_handle) End If // bind local variables to result set columns SQLBindCol( ll_handle, SQLCOL_ORDINAL_POSITION, SQL_INTEGER, ll_position, 0, li_strlen) ls_colname = Space(128) SQLBindCol( ll_handle, SQLCOL_COLUMN_NAME, SQL_CHARACTER, ls_colname, Len(ls_colname), li_strlen) SQLBindCol( ll_handle, SQLCOL_DATA_TYPE, SQL_SMALLINT, li_coltype, 0, li_strlen) SQLBindCol( ll_handle, SQLCOL_COLUMN_SIZE, SQL_INTEGER, ll_width, 0, li_strlen) SQLBindCol( ll_handle, SQLCOL_DECIMAL_DIGITS, SQL_INTEGER, ll_coldecimal, 0, li_strlen) ls_datatype = Space(128) SQLBindCol( ll_handle, SQLCOL_TYPE_NAME, SQL_CHARACTER, ls_datatype, Len(ls_datatype), li_strlen) ls_nullable = Space(128) SQLBindCol( ll_handle, SQLCOL_IS_NULLABLE, SQL_CHARACTER, ls_nullable, Len(ls_nullable), li_strlen) ls_default = Space(128) SQLBindCol( ll_handle, SQLCOL_COLUMN_DEF, SQL_CHARACTER, ls_default, Len(ls_default), li_strlen) // fetch the first row ll_return = SQLFetch(ll_handle) Do While ll_return = SQL_SUCCESS li_count += 1 astr_columns[li_count].Position = ll_position astr_columns[li_count].Name = Trim(ls_colname) astr_columns[li_count].DataType = Trim(ls_datatype) Choose Case li_coltype Case SQL_DECIMAL, SQL_NUMERIC, SQL_FLOAT astr_columns[li_count].Width = ll_width astr_columns[li_count].Decimal = ll_coldecimal Case SQL_CHAR, SQL_VARCHAR, SQL_WCHAR, SQL_WVARCHAR, SQL_NVARCHAR, SQL_BINARY, SQL_VARBINARY astr_columns[li_count].Width = ll_width End Choose If Upper(ls_nullable) = "YES" Then astr_columns[li_count].Nullable = True End If astr_columns[li_count].Default = Trim(ls_default) // fetch the next row ll_return = SQLFetch(ll_handle) Loop // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, ll_handle) Return li_count end function public function integer of_tables (string as_tabletype, ref s_tables astr_table[]);//==================================================================== // Function: n_odbcapi.of_tables() //-------------------------------------------------------------------- // Description: get list of tables //-------------------------------------------------------------------- // Arguments: // value string as_tabletype // reference s_tables astr_table[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_tables ( string as_tabletype, ref s_tables astr_table[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== ULong ll_handle Integer li_count, li_strlen Long ll_return String ls_catalog, ls_schema, ls_tabname, ls_tabtype // null out the search vars SetNull(ls_catalog) SetNull(ls_schema) SetNull(ls_tabname) If Not of_isvalid() Then MessageBox( "Warning", "Transaction Not Connect", Information!) Return -1 End If // allocate a statement handle ll_return = SQLAllocHandle(SQL_HANDLE_STMT, itran_conn.DBHandle(), ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_Tables", SQL_HANDLE_STMT, ll_handle) End If // get a list of tables ll_return = SQLTables(ll_handle, ls_catalog, 0, ls_schema, 0, ls_tabname, 0, as_tabletype, Len(as_tabletype)) If ll_return = SQL_ERROR Then Return of_SQLError("of_Tables", SQL_HANDLE_STMT, ll_handle) End If // bind local variables to result set columns ls_schema = Space(128) SQLBindCol( ll_handle, SQLTAB_TABLE_SCHEM, SQL_CHARACTER, ls_schema, Len(ls_schema), li_strlen) ls_tabname = Space(128) SQLBindCol( ll_handle, SQLTAB_TABLE_NAME, SQL_CHARACTER, ls_tabname, Len(ls_tabname), li_strlen) ls_tabtype = Space(128) SQLBindCol( ll_handle, SQLTAB_TABLE_TYPE, SQL_CHARACTER, ls_tabtype, Len(ls_tabtype), li_strlen) // fetch the first row ll_return = SQLFetch(ll_handle) Do While ll_return = SQL_SUCCESS li_count += 1 astr_table[li_count].Schema = Trim(ls_schema) astr_table[li_count].Name = Trim(ls_tabname) astr_table[li_count].TableType = Trim(ls_tabtype) // fetch the next row ll_return = SQLFetch(ll_handle) Loop // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, ll_handle) Return li_count end function public function integer of_sprocs (ref s_sprocs astr_sprocs[]);//==================================================================== // Function: n_odbcapi.of_sprocs() //-------------------------------------------------------------------- // Description: get list of stored procedures //-------------------------------------------------------------------- // Arguments: // reference s_sprocs astr_sprocs[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_sprocs ( ref s_sprocs astr_sprocs[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== ULong ll_handle Integer li_count, li_strlen Long ll_return String ls_catalog, ls_schema, ls_sprocname // null out the search vars SetNull(ls_catalog) SetNull(ls_schema) SetNull(ls_sprocname) If Not of_isvalid() Then MessageBox( "Warning", "Transaction Not Connect", Information!) Return -1 End If // allocate a statement handle ll_return = SQLAllocHandle(SQL_HANDLE_STMT, itran_conn.DBHandle(), ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_Sprocs", SQL_HANDLE_STMT, ll_handle) End If // get a list of procedures ll_return = SQLProcedures(ll_handle, ls_catalog, 0, ls_schema, 0, ls_sprocname, 0) If ll_return = SQL_ERROR Then Return of_SQLError("of_Sprocs", SQL_HANDLE_STMT, ll_handle) End If // bind local variables to result set columns ls_schema = Space(128) SQLBindCol( ll_handle, SQLSP_PROCEDURE_SCHEM, SQL_CHARACTER, ls_schema, Len(ls_schema), li_strlen) ls_sprocname = Space(128) SQLBindCol( ll_handle, SQLSP_PROCEDURE_NAME, SQL_CHARACTER, ls_sprocname, Len(ls_sprocname), li_strlen) // fetch the first row ll_return = SQLFetch(ll_handle) Do While ll_return = SQL_SUCCESS li_count += 1 astr_sprocs[li_count].Schema = ls_schema If Pos(ls_sprocname, ";") = 0 Then astr_sprocs[li_count].Name = ls_sprocname Else astr_sprocs[li_count].Name = Left(ls_sprocname, Pos(ls_sprocname, ";") - 1) End If // fetch the next row ll_return = SQLFetch(ll_handle) Loop // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, ll_handle) Return li_count end function public function integer of_sprocsource (string as_schema, string as_sprocname, ref string as_source);//==================================================================== // Function: n_odbcapi.of_sprocsource() //-------------------------------------------------------------------- // Description: get stored procedure source //-------------------------------------------------------------------- // Arguments: // value string as_schema // value string as_sprocname // reference string as_source //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_sprocsource ( string as_schema, string as_sprocname, ref string as_source ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== Constant Integer SQL_DBMS_NAME = 17 Integer li_strlen Long ll_return String ls_dbms, ls_spsource, ls_procsql ULong ll_handle ls_dbms = Space(128) If Not of_isvalid() Then MessageBox( "Warning", "Transaction Not Connect", Information!) Return -1 End If // get dbms name SQLGetInfo(itran_conn.DBHandle(), SQL_DBMS_NAME, ls_dbms, 128, li_strlen) // get stored procedure select syntax ls_spsource = ProfileString(of_pbodbini(), ls_dbms, "PBSyntax", "") ls_procsql = ProfileString(of_pbodbini(), ls_spsource, "PBSelectProcSyntax", "") If ls_procsql = "" Then MessageBox("SQL Warning", "PBSelectProcSyntax not found in pbodb80.ini!") Return -1 End If // replace syntax arguments with values ls_procsql = of_ReplaceAll(ls_procsql, "&ObjectOwner", as_schema) ls_procsql = of_ReplaceAll(ls_procsql, "&ObjectName", as_sprocname) ls_procsql = of_ReplaceAll(ls_procsql, "&ObjectNumber", "1") ls_procsql = of_ReplaceAll(ls_procsql, "''", "'") // allocate a statement handle ll_return = SQLAllocHandle(SQL_HANDLE_STMT, itran_conn.DBHandle(), ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_SprocSource", SQL_HANDLE_STMT, ll_handle) End If // execute the sql statement ll_return = SQLExecDirect(ll_handle, ls_procsql, Len(ls_procsql)) If ll_return = SQL_ERROR Then Return of_SQLError("of_SprocSource", SQL_HANDLE_STMT, ll_handle) End If // bind local variables to result set columns ls_spsource = Space(32768) SQLBindCol(ll_handle, 1, SQL_CHARACTER, ls_spsource, Len(ls_spsource), li_strlen) // fetch the first row ll_return = SQLFetch(ll_handle) Do While ll_return = SQL_SUCCESS // replace LF with CRLF ls_spsource = of_ReplaceAll(ls_spsource, "~n", "~r~n") // add data to output variable as_source += ls_spsource // fetch the next row ls_spsource = Space(32768) ll_return = SQLFetch(ll_handle) Loop // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, ll_handle) Return 1 end function public function string of_replaceall (string as_oldstring, string as_findstr, string as_replace);//==================================================================== // Function: n_odbcapi.of_replaceall() //-------------------------------------------------------------------- // Description: //-------------------------------------------------------------------- // Arguments: // value string as_oldstring // value string as_findstr // value string as_replace //-------------------------------------------------------------------- // Returns: string //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_replaceall ( string as_oldstring, string as_findstr, string as_replace ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== String ls_newstring Long ll_findstr, ll_replace, ll_pos // get length of strings ll_findstr = Len(as_findstr) ll_replace = Len(as_replace) // find first occurrence ls_newstring = as_oldstring ll_pos = Pos(ls_newstring, as_findstr) Do While ll_pos > 0 // replace old with new ls_newstring = Replace(ls_newstring, ll_pos, ll_findstr, as_replace) // find next occurrence ll_pos = Pos(ls_newstring, as_findstr, (ll_pos + ll_replace)) Loop Return ls_newstring end function public function integer of_datasources (string as_type, ref string as_name[], ref string as_driver[]);//==================================================================== // Function: n_odbcapi.of_datasources() //-------------------------------------------------------------------- // Description: get a list of datasources //-------------------------------------------------------------------- // Arguments: // value string as_type // reference string as_name[] // reference string as_driver[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_datasources ( string as_type, ref string as_name[], ref string as_driver[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== ULong ll_handle Integer li_direction, li_count, li_svrlen, li_deslen Long ll_return String ls_name, ls_driver // set the type direction Choose Case Upper(as_type) Case "SYSTEM" li_direction = SQL_FETCH_FIRST_SYSTEM Case "USER" li_direction = SQL_FETCH_FIRST_USER Case Else li_direction = SQL_FETCH_FIRST End Choose // allocate an environment handle ll_return = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_DataSources", SQL_HANDLE_ENV, ll_handle) End If // set the ODBC version ll_return = SQLSetEnvAttr(ll_handle, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0); If ll_return = SQL_ERROR Then Return of_SQLError("of_DataSources", SQL_HANDLE_ENV, ll_handle) End If ls_name = Space(SQL_MAX_DSN_LENGTH) ls_driver = Space(256) // get the datasources Do While SQLDataSources(ll_handle, li_direction, ls_name, SQL_MAX_DSN_LENGTH, li_svrlen, ls_driver, Len(ls_driver), li_deslen) = SQL_SUCCESS li_count += 1 as_name[li_count] = ls_name as_driver[li_count] = ls_driver ls_name = Space(SQL_MAX_DSN_LENGTH) ls_driver = Space(256) li_direction = SQL_FETCH_NEXT Loop If ll_return = SQL_ERROR Then Return of_SQLError("of_DataSources", SQL_HANDLE_ENV, ll_handle) End If // release the statement handle SQLFreeHandle(SQL_HANDLE_ENV, ll_handle) Return li_count end function public function integer of_primarykeys (string as_schema, string as_tablename, ref s_primarykeys astr_pkeys[]);//==================================================================== // Function: n_odbcapi.of_primarykeys() //-------------------------------------------------------------------- // Description: get list of primary keys //-------------------------------------------------------------------- // Arguments: // value string as_schema // value string as_tablename // reference s_primarykeys astr_pkeys[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_primarykeys ( string as_schema, string as_tablename, ref s_primarykeys astr_pkeys[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== ULong ll_handle Integer li_count, li_strlen, li_colseq Long ll_return String ls_catalog, ls_schema, ls_colname, ls_pkname // null out the search vars SetNull(ls_catalog) If as_schema = "" Then SetNull(as_schema) End If If Not of_isvalid() Then MessageBox( "Warning", "Transaction Not Connect", Information!) Return -1 End If // allocate a statement handle ll_return = SQLAllocHandle(SQL_HANDLE_STMT, itran_conn.DBHandle(), ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_PrimaryKeys", SQL_HANDLE_STMT, ll_handle) End If // get a list of primary key columns ll_return = SQLPrimaryKeys(ll_handle, ls_catalog, 0, as_schema, Len(as_schema), as_tablename, Len(as_tablename)) If ll_return = SQL_ERROR Then Return of_SQLError("of_PrimaryKeys", SQL_HANDLE_STMT, ll_handle) End If // bind local variables to result set columns SQLBindCol( ll_handle, SQLPK_KEY_SEQ, SQL_SMALLINT, li_colseq, 0, li_strlen) ls_colname = Space(128) SQLBindCol( ll_handle, SQLPK_COLUMN_NAME, SQL_CHARACTER, ls_colname, Len(ls_colname), li_strlen) ls_pkname = Space(128) SQLBindCol( ll_handle, SQLPK_PK_NAME, SQL_CHARACTER, ls_pkname, Len(ls_pkname), li_strlen) // fetch the first row ll_return = SQLFetch(ll_handle) Do While ll_return = SQL_SUCCESS li_count += 1 astr_pkeys[li_count].ColSeq = li_colseq astr_pkeys[li_count].Colname = Trim(ls_colname) astr_pkeys[li_count].PKName = Trim(ls_pkname) // fetch the next row ll_return = SQLFetch(ll_handle) Loop // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, ll_handle) Return li_count end function public function integer of_foreignkeys (string as_schema, string as_tablename, ref s_foreignkeys astr_fkeys[]);//==================================================================== // Function: n_odbcapi.of_foreignkeys() //-------------------------------------------------------------------- // Description: get list of foreign keys //-------------------------------------------------------------------- // Arguments: // value string as_schema // value string as_tablename // reference s_foreignkeys astr_fkeys[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_foreignkeys ( string as_schema, string as_tablename, ref s_foreignkeys astr_fkeys[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== ulong ll_handle Integer li_count, li_strlen, li_colseq Long ll_return String ls_null, ls_fkname, ls_fkcolumn, ls_pkschema, ls_pktable, ls_pkcolumn SetNull(ls_null) If as_schema = "" Then SetNull(as_schema) End If If Not of_isvalid() Then MessageBox( "Warning", "Transaction Not Connect", Information!) Return -1 End If // allocate a statement handle ll_return = SQLAllocHandle(SQL_HANDLE_STMT, itran_conn.DbHandle(), ll_handle) If ll_return = SQL_ERROR Then Return of_SQLError("of_ForeignKeys", SQL_HANDLE_STMT, ll_handle) End If // get a list of foreign key columns ll_return = SQLForeignKeys(ll_handle, ls_null, 0, ls_null, 0, ls_null, 0, ls_null, 0, as_schema, Len(as_schema), as_tablename, Len(as_tablename)) If ll_return = SQL_ERROR Then Return of_SQLError("of_ForeignKeys", SQL_HANDLE_STMT, ll_handle) End If // bind local variables to result set columns SQLBindCol( ll_handle, SQLFK_KEY_SEQ, SQL_SMALLINT, li_colseq, 0, li_strlen) ls_fkname = Space(128) SQLBindCol( ll_handle, SQLFK_FK_NAME, SQL_CHARACTER, ls_fkname, Len(ls_fkname), li_strlen) ls_fkcolumn = Space(128) SQLBindCol( ll_handle, SQLFK_FKCOLUMN_NAME, SQL_CHARACTER, ls_fkcolumn, Len(ls_fkcolumn), li_strlen) ls_pkschema = Space(128) SQLBindCol( ll_handle, SQLFK_PKTABLE_SCHEM, SQL_CHARACTER, ls_pkschema, Len(ls_pkschema), li_strlen) ls_pktable = Space(128) SQLBindCol( ll_handle, SQLFK_PKTABLE_NAME, SQL_CHARACTER, ls_pktable, Len(ls_pktable), li_strlen) ls_pkcolumn = Space(128) SQLBindCol( ll_handle, SQLFK_PKCOLUMN_NAME, SQL_CHARACTER, ls_pkcolumn, Len(ls_pkcolumn), li_strlen) // fetch the first row ll_return = SQLFetch(ll_handle) Do While ll_return = SQL_SUCCESS li_count += 1 astr_fkeys[li_count].colseq = li_colseq astr_fkeys[li_count].fkname = Trim(ls_fkname) astr_fkeys[li_count].fkcolumn = Trim(ls_fkcolumn) astr_fkeys[li_count].pkschema = Trim(ls_pkschema) astr_fkeys[li_count].pktable = Trim(ls_pktable) astr_fkeys[li_count].pkcolumn = Trim(ls_pkcolumn) // fetch the next row ll_return = SQLFetch(ll_handle) LOOP // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, ll_handle) Return li_count end function public function integer of_sqlerror (string as_function, integer ai_handletype, long al_handle);//==================================================================== // Function: n_odbcapi.of_sqlerror() //-------------------------------------------------------------------- // Description: display sql error message //-------------------------------------------------------------------- // Arguments: // value string as_function // value integer ai_handletype // value long al_handle //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_sqlerror ( string as_function, integer ai_handletype, long al_handle ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== String ls_sqlstate, ls_errtext, ls_title Integer li_textlen Long ll_return, ll_errcode ls_sqlstate = Space(5) ls_errtext = Space(128) ll_return = SQLGetDiagRec(ai_handletype, al_handle, 1, ls_sqlstate, ll_errcode, ls_errtext, 128, li_textlen) If ll_errcode = 0 Then ls_title = as_function + " SQL Error" Else ls_title = as_function + " SQL Error #" + String(ll_errcode) End If MessageBox( ls_title, "SQLSTATE = " + ls_sqlstate + "~r~n" + ls_errtext, StopSign!) // release the statement handle SQLFreeHandle(SQL_HANDLE_STMT, al_handle) Return -1 end function public subroutine of_sqlwarning (string as_function, integer ai_handletype, long al_handle);//==================================================================== // Function: n_odbcapi.of_sqlwarning() //-------------------------------------------------------------------- // Description: display sql warning message //-------------------------------------------------------------------- // Arguments: // value string as_function // value integer ai_handletype // value long al_handle //-------------------------------------------------------------------- // Returns: (none) //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_sqlwarning ( string as_function, integer ai_handletype, long al_handle ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== String ls_sqlstate, ls_errtext, ls_title Integer li_textlen Long ll_return, ll_errcode ls_sqlstate = Space(5) ls_errtext = Space(128) ll_return = SQLGetDiagRec(ai_handletype, al_handle, 1, ls_sqlstate, ll_errcode, ls_errtext, 128, li_textlen) If ll_errcode = 0 Then ls_title = as_function + " SQL Error" Else ls_title = as_function + " SQL Error #" + String(ll_errcode) End If MessageBox( ls_title, "SQLSTATE = " + ls_sqlstate + "~r~n" + ls_errtext, Information!) end subroutine public function integer of_parse (string as_text, string as_sep, ref string as_array[]);//==================================================================== // Function: n_odbcapi.of_parse() //-------------------------------------------------------------------- // Description: //-------------------------------------------------------------------- // Arguments: // value string as_text // value string as_sep // reference string as_array[] //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_parse ( string as_text, string as_sep, ref string as_array[] ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== String ls_empty[], ls_work Long ll_pos, ll_each as_array = ls_empty If IsNull(as_text) Or as_text = "" Then Return 0 ll_pos = Pos(as_text, as_sep) Do While ll_pos > 0 ls_work = Trim(Left(as_text, ll_pos - 1)) as_text = Trim(Mid(as_text, ll_pos + 1)) as_array[UpperBound(as_array) + 1] = ls_work ll_pos = Pos(as_text, as_sep) Loop as_array[UpperBound(as_array) + 1] = Trim(as_text) Return UpperBound(as_array) end function public function string of_pbodbini ();//==================================================================== // Function: n_odbcapi.of_pbodbini() //-------------------------------------------------------------------- // Description: return name of the pbodb###.ini file //-------------------------------------------------------------------- // Arguments: //-------------------------------------------------------------------- // Returns: string //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/09/07 //-------------------------------------------------------------------- // Usage: n_odbcapi.of_pbodbini ( ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== Environment le_env String ls_vmname GetEnvironment(le_env) choose case le_env.PBMajorRevision case 10, 11, 12 choose case le_env.PBMinorRevision case 5 ls_vmname = "pbodb" + String(le_env.PBMajorRevision) + "5.ini" case 6 ls_vmname = "pbodb" + String(le_env.PBMajorRevision) + "6.ini" case else ls_vmname = "pbodb" + String(le_env.PBMajorRevision) + "0.ini" end choose case else ls_vmname = "pbodb" + String(le_env.PBMajorRevision) + "0.ini" end choose Return ls_vmname end function public subroutine of_settransaction (transaction atran);If of_isvalid() Then Disconnect Using itran_conn; End If itran_conn = atran end subroutine public function boolean of_isvalid ();If IsNull(itran_conn) Or Not IsValid(itran_conn) Or IsNull( itran_conn.DBHandle() ) Then Return False End If Return True end function on n_odbcapi.create call super::create TriggerEvent( this, "constructor" ) end on on n_odbcapi.destroy TriggerEvent( this, "destructor" ) call super::destroy end on event constructor;itran_conn = Create Transaction end event event destructor;If of_isvalid() Then Disconnect Using itran_conn; End If If Not IsNull(itran_conn) Then Destroy itran_conn End If end event |
w_main from window
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 |
forward global type w_main from window end type type cb_connect from commandbutton within w_main end type type cb_datasources from commandbutton within w_main end type type ddlb_tables from dropdownlistbox within w_main end type type mle_source from multilineedit within w_main end type type cb_sprocs from commandbutton within w_main end type type lb_stuff from listbox within w_main end type type lb_columns from listbox within w_main end type type lb_tables from listbox within w_main end type type cb_tables from commandbutton within w_main end type type cb_cancel from commandbutton within w_main end type end forward global type w_main from window integer width = 4073 integer height = 2484 boolean titlebar = true string title = "ODBC Schema" boolean controlmenu = true long backcolor = 67108864 string icon = "AppIcon!" boolean center = true cb_connect cb_connect cb_datasources cb_datasources ddlb_tables ddlb_tables mle_source mle_source cb_sprocs cb_sprocs lb_stuff lb_stuff lb_columns lb_columns lb_tables lb_tables cb_tables cb_tables cb_cancel cb_cancel end type global w_main w_main type variables n_odbcapi in_api s_tables istr_tables[] s_sprocs istr_sprocs[] Boolean ib_sprocs end variables on w_main.create this.cb_connect=create cb_connect this.cb_datasources=create cb_datasources this.ddlb_tables=create ddlb_tables this.mle_source=create mle_source this.cb_sprocs=create cb_sprocs this.lb_stuff=create lb_stuff this.lb_columns=create lb_columns this.lb_tables=create lb_tables this.cb_tables=create cb_tables this.cb_cancel=create cb_cancel this.Control[]={this.cb_connect,& this.cb_datasources,& this.ddlb_tables,& this.mle_source,& this.cb_sprocs,& this.lb_stuff,& this.lb_columns,& this.lb_tables,& this.cb_tables,& this.cb_cancel} end on on w_main.destroy destroy(this.cb_connect) destroy(this.cb_datasources) destroy(this.ddlb_tables) destroy(this.mle_source) destroy(this.cb_sprocs) destroy(this.lb_stuff) destroy(this.lb_columns) destroy(this.lb_tables) destroy(this.cb_tables) destroy(this.cb_cancel) end on event open;ddlb_tables.SelectItem(2) end event type cb_connect from commandbutton within w_main integer x = 2377 integer y = 64 integer width = 517 integer height = 100 integer taborder = 50 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = variable! fontfamily fontfamily = swiss! string facename = "Tahoma" string text = "Connect" end type event constructor;this.Enabled = False end event event clicked; String ls_profile Long ll_len Transaction ltran SetPointer(HourGlass!) ls_profile = lb_stuff.SelectedItem() If IsNull(ls_profile) Or ls_profile = "" Then Return End If ll_len = Pos(ls_profile, " - ") ls_profile = Left(ls_profile, ll_len - 1) lb_tables.Reset() lb_columns.Reset() lb_stuff.Reset() mle_source.Text = "" // set connection properties ltran = Create Transaction ltran.DBMS = "ODBC" ltran.DBParm = "ConnectString='DSN=" + ls_profile + "'" // connect to database Connect Using ltran ; If ltran.SQLCode < 0 Then MessageBox("Connect Failed", ltran.SQLErrText) End If This.Enabled = False in_api.of_settransaction( ltran) end event type cb_datasources from commandbutton within w_main integer x = 1792 integer y = 64 integer width = 517 integer height = 100 integer taborder = 40 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = variable! fontfamily fontfamily = swiss! string facename = "Tahoma" string text = "DataSources" end type event clicked;String ls_name[], ls_driver[], ls_additem Integer li_cnt, li_max SetPointer(HourGlass!) ib_sprocs = False lb_stuff.Reset() mle_source.Text = "" lb_stuff.SetRedraw(False) li_max = in_api.of_DataSources("ALL", ls_name, ls_driver) For li_cnt = 1 To li_max ls_additem = ls_name[li_cnt] + " - " + ls_driver[li_cnt] lb_stuff.AddItem(ls_additem) Next lb_stuff.SetRedraw(True) cb_connect.Enabled = True end event type ddlb_tables from dropdownlistbox within w_main integer x = 37 integer y = 64 integer width = 517 integer height = 324 integer taborder = 10 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = variable! fontfamily fontfamily = swiss! string facename = "Tahoma" long textcolor = 33554432 boolean sorted = false boolean vscrollbar = true string item[] = {"All Tables & Views","User Tables","User Views","System Tables","System Views"} borderstyle borderstyle = stylelowered! end type type mle_source from multilineedit within w_main integer x = 1792 integer y = 1312 integer width = 2199 integer height = 1028 integer taborder = 110 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = fixed! fontfamily fontfamily = modern! string facename = "Courier New" long textcolor = 33554432 boolean hscrollbar = true boolean vscrollbar = true borderstyle borderstyle = stylelowered! end type type cb_sprocs from commandbutton within w_main integer x = 1207 integer y = 64 integer width = 517 integer height = 100 integer taborder = 30 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = variable! fontfamily fontfamily = swiss! string facename = "Tahoma" string text = "Stored Procedures" end type event clicked;String ls_additem Integer li_idx, li_max SetPointer(HourGlass!) ib_sprocs = True lb_stuff.Reset() mle_source.Text = "" lb_stuff.SetRedraw(False) li_max = in_api.of_Sprocs(istr_sprocs) For li_idx = 1 To li_max ls_additem = istr_sprocs[li_idx].Schema + "." ls_additem += istr_sprocs[li_idx].Name lb_stuff.AddItem(ls_additem) Next lb_stuff.SetRedraw(True) If li_max > 0 Then lb_stuff.SelectItem(1) lb_stuff.Event SelectionChanged(1) lb_stuff.SetFocus() End If cb_connect.Enabled = False end event type lb_stuff from listbox within w_main integer x = 1792 integer y = 224 integer width = 2199 integer height = 1028 integer taborder = 100 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = fixed! fontfamily fontfamily = modern! string facename = "Courier New" long textcolor = 33554432 boolean vscrollbar = true borderstyle borderstyle = stylelowered! end type event selectionchanged;String ls_schema, ls_sproc, ls_source If Index = 0 Then Return If ib_sprocs Then ls_schema = istr_sprocs[Index].Schema ls_sproc = istr_sprocs[Index].Name mle_source.Text = "" mle_source.SetRedraw(False) If in_api.of_SprocSource(ls_schema, ls_sproc, ls_source) > 0 Then mle_source.Text = ls_source End If mle_source.SetRedraw(True) End If end event type lb_columns from listbox within w_main integer x = 37 integer y = 1312 integer width = 1687 integer height = 1028 integer taborder = 90 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = fixed! fontfamily fontfamily = modern! string facename = "Courier New" long textcolor = 33554432 boolean hscrollbar = true boolean vscrollbar = true boolean sorted = false borderstyle borderstyle = stylelowered! end type type lb_tables from listbox within w_main integer x = 37 integer y = 224 integer width = 1687 integer height = 1028 integer taborder = 80 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = fixed! fontfamily fontfamily = modern! string facename = "Courier New" long textcolor = 33554432 boolean hscrollbar = true boolean vscrollbar = true boolean sorted = false borderstyle borderstyle = stylelowered! end type event selectionchanged;s_columns lstr_columns[] s_primarykeys lstr_pkeys[] s_foreignkeys lstr_fkeys[] Integer li_idx, li_max String ls_schema, ls_table, ls_additem, ls_type If Index = 0 Then Return ls_schema = istr_tables[Index].Schema ls_table = istr_tables[Index].Name lb_columns.Reset() lb_columns.SetRedraw(False) lb_columns.AddItem("Columns:") li_max = in_api.of_Columns(ls_schema, ls_table, lstr_columns) For li_idx = 1 To li_max ls_additem = lstr_columns[li_idx].Name + " - " ls_additem += lstr_columns[li_idx].DataType If lstr_columns[li_idx].Width > 0 Then If lstr_columns[li_idx].Decimal = 0 Then ls_additem += "(" ls_additem += String(lstr_columns[li_idx].Width) ls_additem += ")" Else ls_additem += "(" ls_additem += String(lstr_columns[li_idx].Width) ls_additem += "," ls_additem += String(lstr_columns[li_idx].Decimal) ls_additem += ")" End If End If lb_columns.AddItem(ls_additem) Next lb_columns.SetRedraw(True) ls_type = ddlb_tables.Text Choose Case ls_type Case "User Tables", "System Tables" li_max = in_api.of_PrimaryKeys(ls_schema, ls_table, lstr_pkeys) If li_max > 0 Then lb_columns.AddItem("") lb_columns.AddItem("Primary Keys:") End If For li_idx = 1 To li_max ls_additem = lstr_pkeys[li_idx].pkname + " - " ls_additem += lstr_pkeys[li_idx].colname lb_columns.AddItem(ls_additem) Next li_max = in_api.of_ForeignKeys(ls_schema, ls_table, lstr_fkeys) If li_max > 0 Then lb_columns.AddItem("") lb_columns.AddItem("Foreign Keys:") End If For li_idx = 1 To li_max ls_additem = lstr_fkeys[li_idx].fkname + " - " ls_additem += lstr_fkeys[li_idx].fkcolumn lb_columns.AddItem(ls_additem) Next End Choose end event type cb_tables from commandbutton within w_main integer x = 622 integer y = 64 integer width = 517 integer height = 100 integer taborder = 20 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = variable! fontfamily fontfamily = swiss! string facename = "Tahoma" string text = "Tables" end type event clicked;String ls_additem, ls_type Integer li_idx, li_max ls_type = ddlb_tables.Text Choose Case ls_type Case "User Tables" ls_type = "TABLE" Case "User Views" ls_type = "VIEW" Case "System Tables" ls_type = "SYSTEM TABLE" Case "System Views" ls_type = "SYSTEM VIEW" Case Else ls_type = "" End Choose lb_tables.Reset() lb_columns.Reset() lb_tables.SetRedraw(False) li_max = in_api.of_Tables(ls_type, istr_tables) For li_idx = 1 To li_max If istr_tables[li_idx].Schema = "" Then ls_additem = istr_tables[li_idx].Name Else ls_additem = istr_tables[li_idx].Schema + "." ls_additem += istr_tables[li_idx].Name End If lb_tables.AddItem(ls_additem) Next lb_tables.SetRedraw(True) If li_max > 0 Then lb_tables.SelectItem(1) lb_tables.Event SelectionChanged(1) lb_tables.SetFocus() End If end event type cb_cancel from commandbutton within w_main integer x = 3547 integer y = 64 integer width = 443 integer height = 100 integer taborder = 70 integer textsize = -8 integer weight = 400 fontcharset fontcharset = ansi! fontpitch fontpitch = variable! fontfamily fontfamily = swiss! string facename = "Tahoma" string text = "Cancel" boolean cancel = true end type event clicked;Close(Parent) end event |
s_tables from structure
|
1 2 3 4 5 |
global type s_tables from structure string schema string name string tabletype end type |
s_sprocs from structure
|
1 2 3 4 |
global type s_sprocs from structure string schema string name end type |
s_primarykeys from structure
|
1 2 3 4 5 |
global type s_primarykeys from structure integer colseq string pkname string colname end type |
s_foreignkeys from structure
|
1 2 3 4 5 6 7 8 |
global type s_foreignkeys from structure integer colseq string fkname string fkcolumn string pkschema string pktable string pkcolumn end type |
s_columns from structure
|
1 2 3 4 5 6 7 8 9 |
global type s_columns from structure long position string name string datatype long width long decimal boolean nullable string default end type |
Find Projects On Github click here
Good Luck!
Subscribe
Login
0 Comments
Oldest