Author Topic: SQL Mirroring  (Read 328 times)

0 Members and 1 Guest are viewing this topic.

John Spikowski

  • Posts: 41
SQL Mirroring
« on: June 21, 2021, 10:34:24 pm »
With the NEWS that DSD is retiring their SQL Mirroring solution as of the end of the year, I wanted to mention that I wrote a SQL mirroring solution for Sage 100 using the open source and free Postgres SQL 64 bit server and ScriptBasic.

DSD's recommendation is to upgrade to Sage 100 Premium. The  problem with Premium is all data access is via an ODBC emulation of ProvideX file I/O. What you gain in reporting speed being SQL native you lose in overall performance. Reading NOMADS screens via SQL is painfully slow as is other routine tasks like updating.

This is a screen shot of the SQL Mirroring package running on a Windows Server.



Features
  • Postgres SQL 64 bit server integration. (MS SQL Server coming soon)
  • Updating tables just takes a few seconds.
  • Multi-threaded - You can do selective on demand syncing while auto mode is running in a thread.
  • Setting up new tables just takes seconds and create the SQL tables from Sage 100 definitions directly.
  • Easy to use GUI front end that is intuitive to use.

SQL Mirroring sells for $1,250.00 and comes with installation and setup assistance. Updates are free for a year.

If you would like to see a live demo via a Team Viewer session, send me an e-mail or give me a call.

John Spikowski - JAR Productions LLC
e-mail@johnspikowski.com
360-941-0452

« Last Edit: June 22, 2021, 01:29:16 pm by John Spikowski »

John Spikowski

  • Posts: 41
Re: SQL Mirroring
« Reply #1 on: July 31, 2021, 04:38:19 pm »
I'm working on a new version of SQL mirroring that uses the ScriptBasic application server as it's base. The mirror process runs as a fault tolerant thread with intelligent scheduling keeping the SQL server in sync. The application server provides a web service interface to hide the complexity of BOI scripting with an easy to use endpoint interface. A live BI feature is include using Google charts API for the presentation. The application server can provide dynamic content as a traditional web server as well.

UPDATE:

I just fell in love with Zoho BI with its amazing user interface, ease of use and affordability.



I created a schema based set of tools to create SQL statements easier. A few examples how it works.

Table Column Schema

Code: Script BASIC
  1. SCHEMA::COLUMNS("DSN=SAGE100","AR_Customer")
  2.  
  3. FOR i = 0 TO UBOUND(SCHEMA::COLUMN) STEP 2
  4.   PRINT FORMAT("Name: %s, Type: %i, Length: %i, Precision: %i, Scale: %i, Nullable: %s\n", _
  5.   SCHEMA::COLUMN[i+1][3], _
  6.   SCHEMA::COLUMN[i+1][11], _
  7.   SCHEMA::COLUMN[i+1][13], _
  8.   SCHEMA::COLUMN[i+1][15], _
  9.   SCHEMA::COLUMN[i+1][16], _
  10.   SCHEMA::COLUMN[i+1][10])
  11. NEXT
  12.  

Output

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


The column information can also be accessed by column name and property constants.

Code: Script BASIC
  1. SCHEMA::COLUMNS("DSN=SAGE100","AR_Customer")
  2.  
  3. PRINT SCHEMA::COLUMN{"CustomerName"}[DATA_TYPE],"\n"
  4. PRINT SCHEMA::COLUMN{"CustomerName"}[CHARACTER_MAXIMUM_LENGTH],"\n"
  5. PRINT SCHEMA::COLUMN{"CustomerName"}[IS_NULLABLE],"\n"
  6.  

Output

129
50
True


Table names can be returned using an optional mask or all tables in the database if not used.

Code: Script BASIC
  1. SCHEMA::TABLES("DSN=SAGE100","ap")      
  2.                                          
  3. FOR i = 0 TO UBOUND(SCHEMA::TABLE) STEP 2
  4.   PRINT SCHEMA::TABLE[i],"\n"            
  5.   ' PRINT SCHEMA::TABLE[i+1][2],"\n"    
  6. NEXT                                    
  7.  

Output

AP_ACHCheckEntry
AP_ACHFileWrk
AP_ACHGenerationAddenda
AP_ACHGenerationDetail
AP_ACHGenerationHeader
AP_ACHInterface
AP_ACHInterfaceListingWrk
AP_AgedInvoiceWrk
AP_Analysis
AP_AnalysisReportWrk
AP_AnalysisWrk
AP_ARClearingDetail
AP_ARClearingHeader
AP_ARClearingRecapWrk
AP_ARClearingRegisterWrk
AP_ARClearingRestart
AP_AssignVendorTaxSchedListWrk
AP_Audit
AP_CashRequirementsWrk
AP_ChangeVendorsValidate
AP_CheckDetail
AP_CheckDetailPosting
AP_CheckHeader
AP_CheckHeaderPosting
AP_CheckHistoryDetail
AP_CheckHistoryHeader
AP_CheckHistoryWrk
AP_CheckRegisterWrk
AP_CheckRestart
AP_CheckStubWrk
AP_CheckWrk
AP_DailyPostingWork
AP_DeleteChangeVendors
AP_DeleteChangeVendorsWork
AP_Division
AP_DivisionListingWrk
AP_ElectronicPmtRegisterWrk
AP_ExpenseByGLAccountWrk
AP_ExpenseDistTableDetail
AP_ExpenseDistTableHeader
AP_ExpenseDistTableWrk
AP_ExtendedStubWrk
AP_FixedAssetsHistoryWrk
AP_Form1099PrintingWrk
AP_InvoiceAppliedPrepayPosting
AP_InvoiceDetail
AP_InvoiceDetailPosting
AP_InvoiceHeader
AP_InvoiceHeaderPosting
AP_InvoiceHistoryAppliedPrepay
AP_InvoiceHistoryAsset
AP_InvoiceHistoryDetail
AP_InvoiceHistoryHeader
AP_InvoiceHistoryMemo
AP_InvoiceHistoryMemoSettings
AP_InvoiceHistoryTaxSummary
AP_InvoiceHistoryWrk
AP_InvoiceMemo
AP_InvoiceMemoSettings
AP_InvoicePaymentListingWrk
AP_InvoiceRegisterWrk
AP_InvoiceRestart
AP_InvoiceTaxDetail
AP_InvoiceTaxSummary
AP_JobDistributionDetail
AP_LastBankUsed
AP_ManualCheckDetail
AP_ManualCheckHeader
AP_ManualCheckInvDetail
AP_ManualCheckInvDetailWork
AP_ManualCheckRegisterWrk
AP_ManualCheckRestart
AP_ManualCheckTaxDetail
AP_ManualCheckTaxSummary
AP_MonthlyPurchaseWrk
AP_OpenInvoice
AP_OpenInvoiceByJobWrk
AP_OpenInvoiceTaxSummary
AP_Options
AP_OptionsListingWrk
AP_PaymentHistoryWrk
AP_PreNoteApprovalRegisterWrk
AP_PreNoteApprovalRestart
AP_PreNoteSelectionRegisterWrk
AP_PreNoteSelectionRestart
AP_RecapByDivisionWrk
AP_RepetitiveInvMemoSettings
AP_RepetitiveInvoiceDetail
AP_RepetitiveInvoiceHeader
AP_RepetitiveInvoiceListingWrk
AP_RepetitiveInvoiceMemo
AP_RepetitiveInvoiceTaxDetail
AP_RepetitiveInvoiceTaxSummary
AP_ReverseCheckDetail
AP_ReverseCheckHeader
AP_ReverseCheckRegisterWrk
AP_ReverseCheckRestart
AP_ReverseCheckWork
AP_SalesTax
AP_SalesTaxCalcErrorLogWrk
AP_SalesTaxWrk
AP_SummaryDrillDownWork
AP_TaxJournalWrk
AP_TaxSummaryWork
AP_TermsCode
AP_TransactionPaymentHistory
AP_TrialBalanceWrk
AP_Vendor
AP_Vendor1099Payments
AP_VendorAuditWrk
AP_VendorContact
AP_VendorContactListingWrk
AP_VendorCustomerLink
AP_VendorCustomerLinkListWrk
AP_VendorDocumentContacts
AP_VendorDocuments
AP_VendorElectronicPayHistory
AP_VendorElectronicPayment
AP_VendorListingBalWrk
AP_VendorListingWrk
AP_VendorMailingLabelsWrk
AP_VendorMemo
AP_VendorMemoPrintingWrk
AP_VendorMemoSettings
AP_VendorPDFLog
AP_VendorPurchaseAnalysisWrk
AP_VendorPurchasesHistory
AP_VendorPurchHistoryByPdWrk
AP_VendorRemit


This example returns indexes defined for the table.

Code: Script BASIC
  1. SCHEMA::INDEXES("DSN=SAGE100","AR_Customer")                                  
  2.                                                                              
  3. FOR i = 1 TO UBOUND(SCHEMA::INDEX)                                            
  4.   PRINT FORMAT("Index Name: %s, Segment: %i, Unique: %s, Column Name: %s\n", _
  5.      SCHEMA::INDEX[i][5], _                                                  
  6.      SCHEMA::INDEX[i][16], _                                                  
  7.      SCHEMA::INDEX[i][7], _                                                  
  8.      SCHEMA::INDEX[i][17])                                                    
  9. NEXT                                                                          
  10.  

Output

Index Name: KEY0, Segment: 1, Unique: True, Column Name: ARDivisionNo
Index Name: KEY0, Segment: 2, Unique: True, Column Name: CustomerNo
Index Name: KEY1, Segment: 1, Unique: False, Column Name: CustomerName
Index Name: KEY1, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY1, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY2, Segment: 1, Unique: False, Column Name: SortField
Index Name: KEY2, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY2, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY3, Segment: 1, Unique: False, Column Name: ARDivisionNo
Index Name: KEY3, Segment: 2, Unique: False, Column Name: SalespersonNo
Index Name: KEY3, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY4, Segment: 1, Unique: False, Column Name: CustomerType
Index Name: KEY4, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY4, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY5, Segment: 1, Unique: False, Column Name: ZipCode
Index Name: KEY5, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY5, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY6, Segment: 1, Unique: False, Column Name: TelephoneNo
Index Name: KEY6, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY6, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY7, Segment: 1, Unique: False, Column Name: CustomerNo
Index Name: KEY8, Segment: 1, Unique: False, Column Name: AddressLine1
Index Name: KEY8, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY8, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY9, Segment: 1, Unique: False, Column Name: AddressLine2
Index Name: KEY9, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY9, Segment: 3, Unique: False, Column Name: CustomerNo
Index Name: KEY10, Segment: 1, Unique: False, Column Name: AddressLine3
Index Name: KEY10, Segment: 2, Unique: False, Column Name: ARDivisionNo
Index Name: KEY10, Segment: 3, Unique: False, Column Name: CustomerNo


This function returns the Primary Key for the table.

Code: Script BASIC
  1. SCHEMA::PRIMARY_KEY("DSN=SAGE100","AR_Customer")
  2.  
  3. FOR i = 1 TO UBOUND(SCHEMA::PKEY)
  4.   PRINT FORMAT("Column Name: %s, PK_Name: %s, Segment: %i\n", _
  5.     SCHEMA::PKEY[i][3], _
  6.     SCHEMA::PKEY[i][7], _
  7.     SCHEMA::PKEY[i][6])
  8. NEXT
  9.  

Output

Column Name: ARDivisionNo, PK_Name: KEY0, Segment: 1
Column Name: CustomerNo, PK_Name: KEY0, Segment: 2

« Last Edit: August 03, 2021, 11:16:12 pm by John Spikowski »