Recent Posts

Pages: [1] 2 3 ... 10
1
Scripting Languages / Re: ODBC FetchSchema
« Last post by John 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: Script BASIC
  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.  

2
Scripting Languages / ODBC FetchSchema
« Last post by John 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: Script BASIC
  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.  
3
Open Forum / Re: BASIC 911
« Last post by John on July 15, 2021, 05:09:32 pm »
I just received a USB 3.0 to Ethernet adapter for my Lenovo laptop. I'm directly connected to my gigabit (up/dn) fiber switch on a static IP.
 
It's great to be able to run the ScriptBasic Application Server (sbhttpd) on Windows and the outside world have access to it.
4
Open Forum / Re: BASIC 911
« Last post by John on June 29, 2021, 08:20:01 pm »
Your new job seems to have been keeping you pretty busy. You are truly missed here on the forum and your work maintaining the ScriptBasic project. I hope in the near future you will have more time to spend on your personal projects. (JADE, MBC, SB, ...)
5
Open Forum / Re: BASIC 911
« Last post by AIR on June 29, 2021, 03:29:28 pm »
Nope, actually haven't used Basic for anything in a long while.  It doesn't lend itself to sysadmin tasks without having to reinvent the wheel, so to speak.


6
Open Forum / BASIC 911
« Last post by John on June 24, 2021, 12:17:18 am »
I thought I would start an open discussion how / if developers are still using BASIC with their projects.

It would be great to hear from some of the BASIC pros out there and what they have going.

I'm currently focusing my efforts getting ScriptBasic introduced to the Sage 100 ERP community as an alternative scripting engine to VBScript that is hosted in an OLE control container. (WSH)

If you're not a member of the forum send a request to join to support@allbasic.info for posting access.
7
Open Forum / Forum Profile
« Last post by John on June 05, 2021, 04:32:49 am »
I was able to fix the forum Profile access for members. It seems the template file was mysteriously deleted.  :-[

Members should be able to send PM's to each other again.

8
Scripting Languages / Re: SBIDE
« Last post by John on June 01, 2021, 05:15:13 pm »
If you want to get a good feel for what the Scintilla editor can do there is a great demo editor (SciTE) that shows its features
9
Scripting Languages / Re: SBIDE
« Last post by John on May 30, 2021, 01:08:13 am »
I was able to create a simple ScriptBasic based editor with highlighting and intellisense support just adding the scivb2 control and the intellisense support module from the SBIDE project.

Creating a new look with additional functionality shouldn't be that difficult.

10
Scripting Languages / Re: SBIDE
« Last post by John on May 29, 2021, 11:10:02 pm »
I'm seriously thinking about redoing the SBIDE editor / debugger using VBCCR to update the common controls used. I would also like to incorporate some of the additional features of scivb2 like code folding. I would  like a FUNCTION/SUB listbox for quick access as well.

One possibility is to use IUP for the IDE which supports a COM object container which scivb2 could live in. This would allow me to use ScriptBasic as the primary language of design rather than VB6. Another advantage of using IUP is it has a dialog layout feature that would allow me to incorporate forms designer into the IDE.

I really don't want to take on this project alone and looking for others that would like to contribute.

 
Pages: [1] 2 3 ... 10