BASIC Developer & Support Resources > Scripting Languages

ODBC FetchSchema

(1/1)

John:
I have added a new function to the ScriptBasic ODBC extension module called FetchSchema. This is an example of getting the schema information from the Sage 100 customer table.


--- Code: Script BASIC ---IMPORT odbc.sbi dbh = odbc::RealConnect("SAGE100","","")SQL = "SELECT TOP 1 * FROM AR_Customer"odbc::Query(dbh, SQL)     odbc::FetchSchema(dbh, col) odbc::Close(dbh) FOR x = 0 TO UBOUND(col) STEP 5  PRINT FORMAT("Column Name: %s, Type: %i, Size: %i, Digits: %i, Nullable: %i\n", col[x], col[x + 1], col[x + 2], col[x + 3], col[x + 4])NEXT 

Column Name: ARDivisionNo, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: CustomerNo, Type: 12, Size: 20, Digits: 0, Nullable: 1
Column Name: CustomerName, Type: 12, Size: 50, Digits: 0, Nullable: 1
Column Name: AddressLine1, Type: 12, Size: 40, Digits: 0, Nullable: 1
Column Name: AddressLine2, Type: 12, Size: 40, Digits: 0, Nullable: 1
Column Name: AddressLine3, Type: 12, Size: 40, Digits: 0, Nullable: 1
Column Name: City, Type: 12, Size: 30, Digits: 0, Nullable: 1
Column Name: State, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: ZipCode, Type: 12, Size: 10, Digits: 0, Nullable: 1
Column Name: CountryCode, Type: 12, Size: 3, Digits: 0, Nullable: 1
Column Name: TelephoneNo, Type: 12, Size: 20, Digits: 0, Nullable: 1
Column Name: TelephoneExt, Type: 12, Size: 6, Digits: 0, Nullable: 1
Column Name: TelephoneType, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: FaxNo, Type: 12, Size: 20, Digits: 0, Nullable: 1
Column Name: EmailAddress, Type: 12, Size: 250, Digits: 0, Nullable: 1
Column Name: URLAddress, Type: 12, Size: 50, Digits: 0, Nullable: 1
Column Name: EBMEnabled, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: EBMConsumerUserID, Type: 12, Size: 15, Digits: 0, Nullable: 1
Column Name: BatchFax, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: DefaultCreditCardPmtType, Type: 12, Size: 5, Digits: 0, Nullable: 1
Column Name: ContactCode, Type: 12, Size: 10, Digits: 0, Nullable: 1
Column Name: ShipMethod, Type: 12, Size: 15, Digits: 0, Nullable: 1
Column Name: TaxSchedule, Type: 12, Size: 9, Digits: 0, Nullable: 1
Column Name: TaxExemptNo, Type: 12, Size: 15, Digits: 0, Nullable: 1
Column Name: TermsCode, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: SalespersonDivisionNo, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: SalespersonNo, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: SalespersonDivisionNo2, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: SalespersonNo2, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: SalespersonDivisionNo3, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: SalespersonNo3, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: SalespersonDivisionNo4, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: SalespersonNo4, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: SalespersonDivisionNo5, Type: 12, Size: 2, Digits: 0, Nullable: 1
Column Name: SalespersonNo5, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: Comment, Type: 12, Size: 30, Digits: 0, Nullable: 1
Column Name: SortField, Type: 12, Size: 10, Digits: 0, Nullable: 1
Column Name: TemporaryCustomer, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: CustomerStatus, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: InactiveReasonCode, Type: 12, Size: 5, Digits: 0, Nullable: 1
Column Name: OpenItemCustomer, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: ResidentialAddress, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: StatementCycle, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: PrintDunningMessage, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: UseSageCloudForInvPrinting, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: CustomerType, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: PriceLevel, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: DateLastActivity, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: DateLastPayment, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: DateLastStatement, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: DateLastFinanceChrg, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: DateLastAging, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: DefaultItemCode, Type: 12, Size: 30, Digits: 0, Nullable: 1
Column Name: DefaultCostCode, Type: 12, Size: 9, Digits: 0, Nullable: 1
Column Name: DefaultCostType, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: CreditHold, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: PrimaryShipToCode, Type: 12, Size: 4, Digits: 0, Nullable: 1
Column Name: DateEstablished, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: CreditCardGUID, Type: 12, Size: 32, Digits: 0, Nullable: 1
Column Name: DefaultPaymentType, Type: 12, Size: 5, Digits: 0, Nullable: 1
Column Name: EInvoicePayments, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: EInvoiceContactId, Type: 12, Size: 24, Digits: 0, Nullable: 1
Column Name: EmailStatements, Type: 12, Size: 1, Digits: 0, Nullable: 1
Column Name: PIIEncryptedDate, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: NumberOfInvToUseInCalc, Type: 3, Size: 2, Digits: 0, Nullable: 1
Column Name: AvgDaysPaymentInvoice, Type: 3, Size: 3, Digits: 0, Nullable: 1
Column Name: AvgDaysOverDue, Type: 3, Size: 3, Digits: 0, Nullable: 1
Column Name: CustomerDiscountRate, Type: 3, Size: 12, Digits: 3, Nullable: 1
Column Name: ServiceChargeRate, Type: 3, Size: 12, Digits: 3, Nullable: 1
Column Name: CreditLimit, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: LastPaymentAmt, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: HighestStmntBalance, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: UnpaidServiceChrg, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: BalanceForward, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: CurrentBalance, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: AgingCategory1, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: AgingCategory2, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: AgingCategory3, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: AgingCategory4, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: OpenOrderAmt, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: RetentionCurrent, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: RetentionAging1, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: RetentionAging2, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: RetentionAging3, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: RetentionAging4, Type: 3, Size: 13, Digits: 2, Nullable: 1
Column Name: SplitCommRate2, Type: 3, Size: 8, Digits: 3, Nullable: 1
Column Name: SplitCommRate3, Type: 3, Size: 8, Digits: 3, Nullable: 1
Column Name: SplitCommRate4, Type: 3, Size: 8, Digits: 3, Nullable: 1
Column Name: SplitCommRate5, Type: 3, Size: 8, Digits: 3, Nullable: 1
Column Name: EncryptedVals, Type: -1, Size: 1152, Digits: 0, Nullable: 1
Column Name: DateCreated, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: TimeCreated, Type: 12, Size: 8, Digits: 0, Nullable: 1
Column Name: UserCreatedKey, Type: 12, Size: 10, Digits: 0, Nullable: 1
Column Name: DateUpdated, Type: 91, Size: 10, Digits: 0, Nullable: 1
Column Name: TimeUpdated, Type: 12, Size: 8, Digits: 0, Nullable: 1
Column Name: UserUpdatedKey, Type: 12, Size: 10, Digits: 0, Nullable: 1


This is the Type defines.


--- Code: C ---#define         SQL_UNKNOWN_TYPE 0 #define         SQL_CHAR 1 #define         SQL_NUMERIC 2 #define         SQL_DECIMAL 3 #define         SQL_INTEGER 4 #define         SQL_SMALLINT 5 #define         SQL_FLOAT 6 #define         SQL_REAL 7 #define         SQL_DOUBLE 8 #define         SQL_DATETIME 9 #define         SQL_VARCHAR 12 #define         SQL_TYPE_DATE 91 #define         SQL_TYPE_TIME 92 #define         SQL_TYPE_TIMESTAMP 93  

John:
This is my first pass at generating a CREATE SQL statement from the FetchSchema array. I still need to do the PRIMARY KEY part.


--- Code: Script BASIC ---IMPORT odbc.sbi dbh = odbc::RealConnect("SAGE100","","")odbc::Query(dbh,"SELECT TOP 1 * FROM AR_Customer")odbc::FetchSchema(dbh,col) create_stmt = "CREATE TABLE AR_Customer(\n"FOR x = 0 TO UBOUND(col) STEP 5  create_stmt &= col[x] & " "  IF col[x + 1] = 3 THEN create_stmt &= "DECIMAL(" & col[x + 2] & "," & col[x + 3] & ") "  IF col[x + 1] = 12 THEN create_stmt &= "VARCHAR(" & col[x + 2] & ") "  IF col[x + 1] = 91 THEN create_stmt &= "DATE "  IF col[x + 4] THEN     create_stmt &= "NULL,\n"  ELSE    create_stmt &= "NOT NULL,\n"  END IFNEXT create_stmt = LEFT(create_stmt, LEN(create_stmt) - 2)create_stmt &= ")" PRINT create_stmt, "\n"   odbc::Close(dbh) 
Output

--- Code: SQL ---CREATE TABLE AR_Customer(ARDivisionNo VARCHAR(2) NULL,CustomerNo VARCHAR(20) NULL,CustomerName VARCHAR(50) NULL,AddressLine1 VARCHAR(40) NULL,AddressLine2 VARCHAR(40) NULL,AddressLine3 VARCHAR(40) NULL,City VARCHAR(30) NULL,State VARCHAR(2) NULL,ZipCode VARCHAR(10) NULL,CountryCode VARCHAR(3) NULL,TelephoneNo VARCHAR(20) NULL,TelephoneExt VARCHAR(6) NULL,TelephoneType VARCHAR(1) NULL,FaxNo VARCHAR(20) NULL,EmailAddress VARCHAR(250) NULL,URLAddress VARCHAR(50) NULL,EBMEnabled VARCHAR(1) NULL,EBMConsumerUserID VARCHAR(15) NULL,BatchFax VARCHAR(1) NULL,DefaultCreditCardPmtType VARCHAR(5) NULL,ContactCode VARCHAR(10) NULL,ShipMethod VARCHAR(15) NULL,TaxSchedule VARCHAR(9) NULL,TaxExemptNo VARCHAR(15) NULL,TermsCode VARCHAR(2) NULL,SalespersonDivisionNo VARCHAR(2) NULL,SalespersonNo VARCHAR(4) NULL,SalespersonDivisionNo2 VARCHAR(2) NULL,SalespersonNo2 VARCHAR(4) NULL,SalespersonDivisionNo3 VARCHAR(2) NULL,SalespersonNo3 VARCHAR(4) NULL,SalespersonDivisionNo4 VARCHAR(2) NULL,SalespersonNo4 VARCHAR(4) NULL,SalespersonDivisionNo5 VARCHAR(2) NULL,SalespersonNo5 VARCHAR(4) NULL,Comment VARCHAR(30) NULL,SortField VARCHAR(10) NULL,TemporaryCustomer VARCHAR(1) NULL,CustomerStatus VARCHAR(1) NULL,InactiveReasonCode VARCHAR(5) NULL,OpenItemCustomer VARCHAR(1) NULL,ResidentialAddress VARCHAR(1) NULL,StatementCycle VARCHAR(1) NULL,PrintDunningMessage VARCHAR(1) NULL,UseSageCloudForInvPrinting VARCHAR(1) NULL,CustomerType VARCHAR(4) NULL,PriceLevel VARCHAR(1) NULL,DateLastActivity DATE NULL,DateLastPayment DATE NULL,DateLastStatement DATE NULL,DateLastFinanceChrg DATE NULL,DateLastAging DATE NULL,DefaultItemCode VARCHAR(30) NULL,DefaultCostCode VARCHAR(9) NULL,DefaultCostType VARCHAR(1) NULL,CreditHold VARCHAR(1) NULL,PrimaryShipToCode VARCHAR(4) NULL,DateEstablished DATE NULL,CreditCardGUID VARCHAR(32) NULL,DefaultPaymentType VARCHAR(5) NULL,EInvoicePayments VARCHAR(1) NULL,EInvoiceContactId VARCHAR(24) NULL,EmailStatements VARCHAR(1) NULL,PIIEncryptedDate DATE NULL,NumberOfInvToUseInCalc DECIMAL(2,0) NULL,AvgDaysPaymentInvoice DECIMAL(3,0) NULL,AvgDaysOverDue DECIMAL(3,0) NULL,CustomerDiscountRate DECIMAL(12,3) NULL,ServiceChargeRate DECIMAL(12,3) NULL,CreditLimit DECIMAL(13,2) NULL,LastPaymentAmt DECIMAL(13,2) NULL,HighestStmntBalance DECIMAL(13,2) NULL,UnpaidServiceChrg DECIMAL(13,2) NULL,BalanceForward DECIMAL(13,2) NULL,CurrentBalance DECIMAL(13,2) NULL,AgingCategory1 DECIMAL(13,2) NULL,AgingCategory2 DECIMAL(13,2) NULL,AgingCategory3 DECIMAL(13,2) NULL,AgingCategory4 DECIMAL(13,2) NULL,OpenOrderAmt DECIMAL(13,2) NULL,RetentionCurrent DECIMAL(13,2) NULL,RetentionAging1 DECIMAL(13,2) NULL,RetentionAging2 DECIMAL(13,2) NULL,RetentionAging3 DECIMAL(13,2) NULL,RetentionAging4 DECIMAL(13,2) NULL,SplitCommRate2 DECIMAL(8,3) NULL,SplitCommRate3 DECIMAL(8,3) NULL,SplitCommRate4 DECIMAL(8,3) NULL,SplitCommRate5 DECIMAL(8,3) NULL,EncryptedVals NULL,DateCreated DATE NULL,TimeCreated VARCHAR(8) NULL,UserCreatedKey VARCHAR(10) NULL,DateUpdated DATE NULL,TimeUpdated VARCHAR(8) NULL,UserUpdatedKey VARCHAR(10) NULL) 

Navigation

[0] Message Index

Go to full version