« previous next »
Pages: [1]

Author Topic: ODBC FetchSchema  (Read 1904 times)

Offline John

  • Forum Support / SB Dev
  • Posts: 3597
    • ScriptBasic Open Source Project
ODBC FetchSchema
« on: July 16, 2021, 03:06:58 AM »
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: ScriptBasic
  1. IMPORT odbc.sbi
  2.  
  3. dbh = odbc::RealConnect("SAGE100","","")
  4. SQL = "SELECT TOP 1 * FROM AR_Customer"
  5. odbc::Query(dbh, SQL)    
  6. odbc::FetchSchema(dbh, col)
  7. odbc::Close(dbh)
  8.  
  9. FOR x = 0 TO UBOUND(col) STEP 5
  10.   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])
  11. NEXT
  12.  


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
  1. #define         SQL_UNKNOWN_TYPE 0
  2. #define         SQL_CHAR 1
  3. #define         SQL_NUMERIC 2
  4. #define         SQL_DECIMAL 3
  5. #define         SQL_INTEGER 4
  6. #define         SQL_SMALLINT 5
  7. #define         SQL_FLOAT 6
  8. #define         SQL_REAL 7
  9. #define         SQL_DOUBLE 8
  10. #define         SQL_DATETIME 9
  11. #define         SQL_VARCHAR 12
  12. #define         SQL_TYPE_DATE 91
  13. #define         SQL_TYPE_TIME 92
  14. #define         SQL_TYPE_TIMESTAMP 93
  15.  
« Last Edit: July 18, 2021, 10:19:50 PM by John »
Logged

Offline John

  • Forum Support / SB Dev
  • Posts: 3597
    • ScriptBasic Open Source Project
Re: ODBC FetchSchema
« Reply #1 on: July 18, 2021, 10:27:53 PM »
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: ScriptBasic
  1. IMPORT odbc.sbi
  2.  
  3. dbh = odbc::RealConnect("SAGE100","","")
  4. odbc::Query(dbh,"SELECT TOP 1 * FROM AR_Customer")
  5. odbc::FetchSchema(dbh,col)
  6.  
  7. create_stmt = "CREATE TABLE AR_Customer(\n"
  8. FOR x = 0 TO UBOUND(col) STEP 5
  9.   create_stmt &= col[x] & " "
  10.   IF col[x + 1] = 3 THEN create_stmt &= "DECIMAL(" & col[x + 2] & "," & col[x + 3] & ") "
  11.   IF col[x + 1] = 12 THEN create_stmt &= "VARCHAR(" & col[x + 2] & ") "
  12.   IF col[x + 1] = 91 THEN create_stmt &= "DATE "
  13.   IF col[x + 4] THEN
  14.     create_stmt &= "NULL,\n"
  15.   ELSE
  16.     create_stmt &= "NOT NULL,\n"
  17.   END IF
  18. NEXT
  19.  
  20. create_stmt = LEFT(create_stmt, LEN(create_stmt) - 2)
  21. create_stmt &= ")"
  22.  
  23. PRINT create_stmt, "\n"
  24.    
  25. odbc::Close(dbh)
  26.  

Output
Code: SQL
  1. CREATE TABLE AR_Customer(
  2. ARDivisionNo VARCHAR(2) NULL,
  3. CustomerNo VARCHAR(20) NULL,
  4. CustomerName VARCHAR(50) NULL,
  5. AddressLine1 VARCHAR(40) NULL,
  6. AddressLine2 VARCHAR(40) NULL,
  7. AddressLine3 VARCHAR(40) NULL,
  8. City VARCHAR(30) NULL,
  9. State VARCHAR(2) NULL,
  10. ZipCode VARCHAR(10) NULL,
  11. CountryCode VARCHAR(3) NULL,
  12. TelephoneNo VARCHAR(20) NULL,
  13. TelephoneExt VARCHAR(6) NULL,
  14. TelephoneType VARCHAR(1) NULL,
  15. FaxNo VARCHAR(20) NULL,
  16. EmailAddress VARCHAR(250) NULL,
  17. URLAddress VARCHAR(50) NULL,
  18. EBMEnabled VARCHAR(1) NULL,
  19. EBMConsumerUserID VARCHAR(15) NULL,
  20. BatchFax VARCHAR(1) NULL,
  21. DefaultCreditCardPmtType VARCHAR(5) NULL,
  22. ContactCode VARCHAR(10) NULL,
  23. ShipMethod VARCHAR(15) NULL,
  24. TaxSchedule VARCHAR(9) NULL,
  25. TaxExemptNo VARCHAR(15) NULL,
  26. TermsCode VARCHAR(2) NULL,
  27. SalespersonDivisionNo VARCHAR(2) NULL,
  28. SalespersonNo VARCHAR(4) NULL,
  29. SalespersonDivisionNo2 VARCHAR(2) NULL,
  30. SalespersonNo2 VARCHAR(4) NULL,
  31. SalespersonDivisionNo3 VARCHAR(2) NULL,
  32. SalespersonNo3 VARCHAR(4) NULL,
  33. SalespersonDivisionNo4 VARCHAR(2) NULL,
  34. SalespersonNo4 VARCHAR(4) NULL,
  35. SalespersonDivisionNo5 VARCHAR(2) NULL,
  36. SalespersonNo5 VARCHAR(4) NULL,
  37. Comment VARCHAR(30) NULL,
  38. SortField VARCHAR(10) NULL,
  39. TemporaryCustomer VARCHAR(1) NULL,
  40. CustomerStatus VARCHAR(1) NULL,
  41. InactiveReasonCode VARCHAR(5) NULL,
  42. OpenItemCustomer VARCHAR(1) NULL,
  43. ResidentialAddress VARCHAR(1) NULL,
  44. StatementCycle VARCHAR(1) NULL,
  45. PrintDunningMessage VARCHAR(1) NULL,
  46. UseSageCloudForInvPrinting VARCHAR(1) NULL,
  47. CustomerType VARCHAR(4) NULL,
  48. PriceLevel VARCHAR(1) NULL,
  49. DateLastActivity DATE NULL,
  50. DateLastPayment DATE NULL,
  51. DateLastStatement DATE NULL,
  52. DateLastFinanceChrg DATE NULL,
  53. DateLastAging DATE NULL,
  54. DefaultItemCode VARCHAR(30) NULL,
  55. DefaultCostCode VARCHAR(9) NULL,
  56. DefaultCostType VARCHAR(1) NULL,
  57. CreditHold VARCHAR(1) NULL,
  58. PrimaryShipToCode VARCHAR(4) NULL,
  59. DateEstablished DATE NULL,
  60. CreditCardGUID VARCHAR(32) NULL,
  61. DefaultPaymentType VARCHAR(5) NULL,
  62. EInvoicePayments VARCHAR(1) NULL,
  63. EInvoiceContactId VARCHAR(24) NULL,
  64. EmailStatements VARCHAR(1) NULL,
  65. PIIEncryptedDate DATE NULL,
  66. NumberOfInvToUseInCalc DECIMAL(2,0) NULL,
  67. AvgDaysPaymentInvoice DECIMAL(3,0) NULL,
  68. AvgDaysOverDue DECIMAL(3,0) NULL,
  69. CustomerDiscountRate DECIMAL(12,3) NULL,
  70. ServiceChargeRate DECIMAL(12,3) NULL,
  71. CreditLimit DECIMAL(13,2) NULL,
  72. LastPaymentAmt DECIMAL(13,2) NULL,
  73. HighestStmntBalance DECIMAL(13,2) NULL,
  74. UnpaidServiceChrg DECIMAL(13,2) NULL,
  75. BalanceForward DECIMAL(13,2) NULL,
  76. CurrentBalance DECIMAL(13,2) NULL,
  77. AgingCategory1 DECIMAL(13,2) NULL,
  78. AgingCategory2 DECIMAL(13,2) NULL,
  79. AgingCategory3 DECIMAL(13,2) NULL,
  80. AgingCategory4 DECIMAL(13,2) NULL,
  81. OpenOrderAmt DECIMAL(13,2) NULL,
  82. RetentionCurrent DECIMAL(13,2) NULL,
  83. RetentionAging1 DECIMAL(13,2) NULL,
  84. RetentionAging2 DECIMAL(13,2) NULL,
  85. RetentionAging3 DECIMAL(13,2) NULL,
  86. RetentionAging4 DECIMAL(13,2) NULL,
  87. SplitCommRate2 DECIMAL(8,3) NULL,
  88. SplitCommRate3 DECIMAL(8,3) NULL,
  89. SplitCommRate4 DECIMAL(8,3) NULL,
  90. SplitCommRate5 DECIMAL(8,3) NULL,
  91. EncryptedVals NULL,
  92. DateCreated DATE NULL,
  93. TimeCreated VARCHAR(8) NULL,
  94. UserCreatedKey VARCHAR(10) NULL,
  95. DateUpdated DATE NULL,
  96. TimeUpdated VARCHAR(8) NULL,
  97. UserUpdatedKey VARCHAR(10) NULL)
  98.  

« Last Edit: July 18, 2021, 10:43:52 PM by John »
Logged
Pages: [1]
« previous next »