Author Topic: Sage 100 Data Access  (Read 1833 times)

0 Members and 1 Guest are viewing this topic.

John Spikowski

  • Posts: 36
Sage 100 Data Access
« on: November 16, 2017, 04:19:18 PM »
The ProvideX ODBC driver doesn't expose a way via a SQL statement to return a table list as a record set. The following Script BASIC program does just that.




Note:  The GetTableList() function seems to accept an argument which defines the separator being used. The docs say no arguments permitted.

Code: Script BASIC
  1. ' List Sage 100 ODBC Tables
  2.  
  3. IMPORT NT.sbi
  4. IMPORT COM.sbi
  5.  
  6. odbcpath = NT::RegRead("HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\SOTAMAS90\\Directory")
  7. oscript = COM::CreateObject("ProvideX.Script")
  8. COM::CallByName(oscript, "Init", vbMethod, odbcpath & "\\home")
  9. osession = COM::CallByName(oscript, "NewObject", vbMethod, "SY_Session")
  10. ofileobj = COM::CallByName(osession, "oFileObj", vbGet)
  11. tablelist = COM::CallByName(ofileobj, "sGetTableList", vbMethod, ",")
  12. COM::CallByName(osession, "CleanUp", vbMethod)
  13. COM::CallByName(osession, "DropObject", vbMethod)
  14. COM::ReleaseObject(oscript)
  15.  
  16. SPLITA tablelist BY "," TO tablenames
  17. FOR i = 0 TO UBOUND(tablenames)
  18.   PRINT tablenames[i], "\n"
  19. NEXT  
  20.  

Output
Code: [Select]
IM_ItemAuditWrk
IM_ItemMemoWrk
MD_EnhancementReportWrk
IM_ItemListingWrk
SY_UserRegistry
IM_AliasItemListingWrk
IM_OptionsListingWrk
CI_StandardCostAdjustSelection
PO_Options
PO_PurchasesHistory
PO_PurchaseOrderDefaults
SO_RecapByDivision
SY_RoleReportODBCSecurityWrk
GL_PayAccountSelectListingWrk
GL_PayTransactionHeader
GL_PayTransactionDetail
SY_CrystalAnalysisWrk
AP_Division
AP_SalesTax
AP_TermsCode
AP_Vendor
AP_Options
AP_OpenInvoice
AP_InvoiceHeader
AP_InvoiceDetail
AP_ManualCheckHeader
AP_ManualCheckDetail
AP_CheckHeader
AP_CheckDetail
AP_RepetitiveInvoiceHeader
CI_StandardCostAdjustmentWork
CI_MiscItemHistoryByPeriodWrk
BC_Session
BC_OptionsListingWrk
BC_Options
BC_Keys
AP_RepetitiveInvoiceDetail
CM_Entity
CM_Log
CM_Options
CM_UDF
CM_UdfIolist
GL_Account
GL_AccountCategory
GL_AccountFilterFRxWrk
GL_AccountFRxWrk
GL_AccountGroup
GL_AccountHistory
GL_AccountMemo
GL_AccountMemoSettings
GL_AccountMemoWrk
GL_AccountSegment
GL_AccountStructure
GL_AccountType
GL_AllocationDetail
GL_AllocationHeader
GL_AllocationJournalWrk
GL_AllocationSelectionCalc
GL_AllocationSelectionDetail
GL_AllocationSelectionHeader
GL_AllocationSelectionOptions
GL_AllocationSelectionRestart
GL_AnalysisWrk
GL_Audit
GL_AuditWrk
GL_BalanceFRxWrk
GL_Bank
GL_Budget
GL_BudgetAndHistoryWrk
GL_BudgetOptions
GL_BudgetReportWrk
GL_BudgetRevision
GL_BudgetRevisionOptions
GL_BudgetRevisionWrk
GL_ChangeAccounts
GL_ChangeAccountsRestart
GL_ChangeAccountsWork
AR_Options
GL_ChartOfAccountsWrk
GL_CheckMicr
GL_CompanyActiveBatch
GL_CompanyBatchOptions
GL_CompanyMemoManagerSettings
GL_DailyPosting
GL_DailyTransactionRegisterWrk
GL_DailyTransactionRestart
GL_DataExchange
GL_DeleteAccountsWork
GL_DetailBySourceWrk
GL_DetailPosting
GL_DetailReportWrk
GL_ErrorLogWrk
GL_ExceptionReportWrk
GL_Exchange
GL_ExchangeAccountWrk
GL_ExchangeActivityWrk
GL_ExchangeBudgetWrk
GL_ExchangeSelection
GL_ExchangeTransactionWrk
GL_FinancialReport
GL_FinancialReportDetailWrk
GL_FinancialReportGroup
GL_FinancialReportHeaderWrk
GL_FinancialReportSelection
GL_FinancialReportTokenWrk
GL_FinancialTerminology
GL_FiscalYear
GL_FiscalYearDetail
GL_FiscalYearListingWrk
GL_GeneralJournalDetail
GL_GeneralJournalHeader
GL_GeneralJournalHistory
GL_GeneralJournalRestart
GL_GeneralJournalWrk
GL_MainAccount
GL_MainAccountListingWrk
GL_MainAccountMemo
GL_MainAccountMemoSettings
GL_MainAccountMemoWrk
GL_Options
GL_OptionsListingWrk
GL_PeriodBudgetDetail
AR_Customer
GL_PeriodPostingHistory
GL_PostingRecapWrk
GL_RecalculateAccountsWork
GL_RecurringJournalDetail
GL_RecurringJournalHeader
GL_RecurringJournalHistory
GL_RecurringJournalRestart
GL_RecurringJournalUpdate
GL_RecurringJournalWrk
GL_Rollup
GL_SalesTax
GL_SourceJournal
GL_SourceJournalHistory
GL_SourceJournalHistoryWrk
GL_StandardJournalDetail
GL_StandardJournalHeader
GL_StandardJournalListingWrk
GL_SubAccount
GL_SummaryDetailDrillDown
GL_TransactionJournalDetail
GL_TransactionJournalHeader
GL_TransactionJournalHistory
GL_TransactionJournalRestart
GL_TransactionJournalWrk
GL_TrialBalanceWrk
GL_WorksheetWrk
CM_ScriptListingWrk
BC_HeaderRejection
BC_HeaderHistory
BC_Header
BC_DistributionRejection
BC_DistributionHistory
BC_Distribution
BC_DetailRejection
BC_DetailHistory
BC_Detail
SY_LinkPathsWork
CI_StandardCostAdjustmentWrk
CI_StandardCostAdjustRestart
BM_CostedMaterialsWrk
JC1_JobMaster
JC2_JobCostDetail
JC3_TransactionDetail
JCA_JobTypMasterfile
JCC_CostCodeMaster
MD_ModuleListingWrk
BM_ComponentReqrmntWrk
PO_AutoGenerateOrdersDefaults
IM_ItemValuationUpdateWork
SO_DuplicatePOCheckingWrk
CI_ConversionHistory
SY_ConversionDriver
SO_PurchaseControlListingWrk
SO_PurchaseCtrlByCustByItem
AP_JobDistributionDetail
AP_TransactionPaymentHistory
AP_ARClearingHeader
AP_ARClearingDetail
AP_VendorCustomerLink
AP_Vendor1099Payments
AP_Analysis
AP_InvoiceTaxDetail
AP_InvoiceTaxSummary
AP_ManualCheckTaxDetail
AP_ManualCheckTaxSummary
AP_RepetitiveInvoiceTaxDetail
AP_RepetitiveInvoiceTaxSummary
AP_OpenInvoiceTaxSummary
AP_InvoiceHistoryTaxSummary
AP_DeleteChangeVendors
SY0DD1_DataDictionary
SY_Activation
SY_ActivationReq
SY_ActivityLog
SY_BatchManager
SY_Broadcast
SY_Company
SY_CompanyListingWrk
SY_Console
SY_Context
SY_Country
SY_Developer
SY_Enhancement
SY_ExportLink
SY_FinancialReport
SY_FinancialReportColumn
SY_FinancialTerminology
SY_GridDefinition
SY_InstalledModulesListingWrk
SY_MemoDefault
SY_MemoManager
SY_MemoSettingsListingWrk
SY_Menu
SY_Message
SY_Module
SY_ModuleDate
SY_NoMessage
SY_PeriodEndListingWrk
SY_PeriodEndReport
SY_PopupItem
SY_PopupMenu
SY_PopupMenuItem
SY_PopupXref
SY_PrinterPreferences
SY_ReportOption
SY_ReportSelection
SY_ReportSetting
SY_ReportSettingsListingWrk
AR_CashReceiptsHistory
AR_OpenInvoice
AR_TransactionPaymentHistory
AR_InvoiceHeader
AR_InvoiceDetail
AR_CashReceiptsHeader
AR_CashReceiptsDetail
SY_Role
SY_RoleReportWrk
SY_RoleSecurity
SY_SampleAccounts
AR_SalesAnalysisSalespersonWrk
CI_Item
SY_State
SY_Surrogate
SY_SurrogateLog
SY_System
SY_SystemConfigListingWrk
SY_Task
SY_TaskType
SY_Terminal
SY_User
SY_UserSecurity
SY_WizardListingWrk
SY_ZipCode
SY_ZipCodeEdit
VI_DefaultPerform
RA_CustomerRMAWrk
VI_JobExportElements
VI_JobExportSelection
VI_JobHeader
VI_JobImportElements
VI_JobImportSelection
VI_JobPerform
VI_ODBCField
VI_ODBCLink
VI_ODBCTable
WO1_WorkOrderMaster
WOC_RoutingMaster
AP_TaxSummaryWork
AR_Division
SY_EnhancementModule
AR_Salesperson
SO_PurchaseCtrlByStateByItem
SO_PurchaseCtrlByStateByProdLn
AR_SalespersonCommission
SO_PurchaseCtrlByCustByProdLn
SY_ExtendedSolutionUDFConvWork
AP_ACHInterface
AR_TermsCode
AP_ACHInterfaceListingWrk
CI_EnhancementActivation
AP_DeleteChangeVendorsWork
MD_DefaultPerformWrk
IM_InventoryFileComparisonWrk
AR_SalespersonHistory
AR_BillToSoldTo
AP_InvoiceHistoryHeader
AP_ACHCheckEntry
AP_InvoiceHistoryDetail
AP_CheckHistoryHeader
GL_PreMigrationReportsWrk
SY_LockFiles
AR_BillToCustomerListingWrk
IM_CountCardDefaults
SY_RebuildLog
CM_SummaryReportWrk
CM_DetailReportWrk
MD_MenuReportWrk
MD_TaskReportWrk
AR_DailyPostingWork
CM_UDFMaintenanceListingWrk
GL_JournalDrillDownWrk
SY_ReportWizardMain
SY_ReportWizardExpressions
VI_JobLog
SY_ReportWizardFields
SY_ReportWizardFilters
SY_ReportWizardGroups
SY_ReportWizardRoles
SY_ReportWizardSorts
SY_Deferred
SY_ZipCodeListingWrk
GL_GraphicsWrk
GL_ChangeAccountsValidate
SY_Personalize
SY_Workstation
PO_PurchaseOrderHeader
SY_ClassEnhancement
SY_Dialog
SY_DialogSettings
SY_UserReportWrk
SY_Relation
SY_ModuleDateListingWrk
VI_JobExportSort
CM_UdtListingWrk
VI_ImportJobDefinitionWrk
SO_DailyPostingWork
AP_CheckHistoryDetail
AR_SalespersonCommissionWrk
AP_VendorContact
SY_ActivityLogReportWrk
VI_Options
SY_ReportWizardLinks
AR_OpenInvoiceSplitCommissions
AR_FinanceCharge
CI_ItemHistoryByPeriod
AR_RepetitiveInvoiceHeader
AR_RepetitiveInvoiceDetail
SY_ConversionHistory
AR_InvoiceHistoryDetail
AR_CustomerMemo
AR_CashSales
SY_DictionaryConversionHist
AR_SalesTax
AR_CustomerRenumber
AR_Analysis
AR_InvoiceTaxDetail
AR_InvoiceTaxSummary
AR_RepetitiveInvoiceTaxDetail
AR_RepetitiveInvoiceTaxSummary
AR_SalespersonLink
AR_OptionsListingWrk
RA_ReceiptsRestart
BR_EstimatedCashFlowAnlysWrk
AR_CustomerContact
AR_PaymentType
AR_DepositHistory
AR_CustomerShipToTaxExemptions
AR_OpenInvoiceTaxSummary
AR_InvoiceHistoryTaxSummary
AR_CashReceiptsDeposit
AR_InvoiceHistoryTracking
AR_TrackingByItemHistory
AR_CustomerCreditCard
AR_CustomerCreditCardEBMUser
AR_Audit
AR_CashReceiptsRestart
AR_CustomerMemoSettings
AR_CustomerMemoWrk
AR_CustomerSalesHistory
AR_InvoiceHistoryLotSerial
AR_InvoiceRestart
IM_ValuationReportByPeriodWrk
SO_Options
CI_InactiveReasonCode
SO_SalesOrderDetail
AR_CustomerListingWrk
SO_InvoiceDetail
SO_ShippingRateHeader
SO_InvoiceTierDistribution
SO_ShipToAddress
SO_InvoiceTierCostCalcWork
SO_LotSerialHistory
AR_CustomerContactListingWrk
SO_AutoGenerateInvoices
SO_LastPurchaseHistory
AR_InvoiceHistoryHeader
AR_InvoiceHistoryWrk
SO_SalesOrderHistoryDetail
SO_InvoiceHistoryLink
SO_ARInvoiceHistoryLink
SO_CancelReasonCode
SO_AutoGenerateOrders
SO_SalesOrderHistoryTaxDetail
SO_SalesOrderHistoryTaxSummary
SO_SalesOrderTaxDetail
SO_SalesOrderTaxSummary
SO_InvoiceTaxDetail
SO_InvoiceTaxSummary
SO_InvoiceTracking
SO_AdditionalPackingListWrk
SO_CODLabelsWrk
SO_InvoiceHeader
SO_ShipperID
IM_CountCardHeader
SO_PackageTrackingByItem
SO_DailyShipment
SO_DailyShipmentPackage
SO_ShippingOptions
SO_SalesHistory
SO_InvoiceWrk
SO_InvoiceRestart
SO_OpenOrdersByItemWrk
SO_SalesOrderDropShip
SO_OpenOrderDetailByItem
SO_InvoiceDefaults
SO_SalesOrderDefaults
SY_SalesTaxClass
SY_SalesTaxCode
SY_SalesTaxCodeDetail
SY_SalesTaxSchedule
SY_SalesTaxScheduleDetail
SY_SalesTaxScheduleZipXRef
SY_SalesTaxScheduleStateXRef
AR_SalesTaxWrk
AR_SalesJournalWrk
CI_ExtendedDescription
CI_ItemTransactionHistory
SO_CustomerSalesHistoryWrk
SO_SalesJournalRestart
AR_TrialBalanceWrk
SO_OpenOrdersByPromiseDateWrk
AP_VendorContactListingWrk
IM_LotSerialTransactionHistory
CI_Options
IM_AliasItem
AR_AlternateInvoice
IM_AlternateItem
IM_Audit
IM_PeriodPostingHistory
IM_BuyerPlannerCode
SO_RecapByProductLineWhse
SO_RecapByWarehouse
JC_Job
JC_CostCode
PR_Employee
SY_StarShipWork
PO_OptionsListingWrk
AR_CustomerCreditCardListWrk
AR_RepetitiveInvoiceListingWrk
AR_SalespersonListingWrk
AR_DivisionListingWrk
SO_ShippingRateDetail
IM_DeleteChangeItems
PR_Department
PR_EarningsDeduction
SO_CustomerLastPurchaseListWrk
CI_SalesTaxAccountListingWrk
AR_RecapByDivisionWrk
CI_BankCodeListingWrk
AR_PaymentTypeWrk
AR_GrossProfitJournalWrk
AR_TaxJournalWrk
IM_DeleteChangeItemsWork
AR_CreditCardSettlementWrk
AR_CustomerSalespersonHistory
AR_CustomerListingBalWrk
SO_OpenOrdersWrk
AR_CustomerSalesAnalysisWrk
SO_OptionsListingWrk
SO_SalesTaxJournalWrk
SO_DailyBackOrderRecapWrk
SO_DailyDepositRecapWrk
JC_JobBillingHeader
BR_Options
BR_Transaction
AR_DepositHistoryWrk
IT_Options
IT_WebMenu
IT_Customer
IT_Item
IT_HTMLTemplate
IT_ItemCategory
IT_EMail
IT_EMailReasonCode
IT_ShoppingCartDetail
IT_ShoppingCartHeader
IT_ShoppingCartItemsSelected
IT_ShoppingCartTaxDetail
IT_ShoppingCartTaxSummary
IT_UIDCustomerChange
IT_Category
IT_OrderCategoryTree
IT_EMailNotification
IT_EMailToken
SO_RecapWrk
SY_TaskSchedulerListingWrk
CI_ChangeEntityWork
IT_EMailPending
IT_StoreCategoryTree
IT_WebTemplateOptions
IT_ItemAttachment
IT_UIDCustomerChangeRestart
RA_ReceiptsHistoryHeader
RA_ReceiptsHistoryDetail
RA_ReceiptsHistoryLotSerial
RA_InquiryReceiptsHistoryLink
RA_ReceiptsHistoryInvoice
RA_ReturnReasonCode
RA_Options
RA_ReturnHeader
RA_ReturnDetail
RA_ReturnTierDistribution
RA_ReturnPrint
RA_ReceiptsHeader
RA_ReceiptsDetail
RA_ReceiptsTierDistribution
RA_ReceiptsReceiving
RA_GenerateTransactions
RA_RepairDetail
RA_ReturnReasonDetail
RA_ReturnReasonLotSerial
RA_ReturnToAddressCode
RA_CustomerInvoiceSearch
RA_DeleteRenumberMergeXref
SO_AutoGenInvoicesListingWrk
AR_CashReceiptsJournalWrk
SO_AutoGenOrdersListingWrk
IT_CategoryListingWrk
SY_EBusinessManagerUserID
SY_PollingSystem
AR_CashRecPaymentTypeRecapWrk
AR_FinanceChargeJournalWrk
AR_CommissionRegisterWrk
SO_GrossProfitJournalWrk
SO_SalesJournalWrk
SO_DailyJobTransactionWrk
SO_SalesDailyRecapWrk
RA_ReturnToAddressListingWrk
SY_UserTableSecurity
SY_UserHistory
SY_JournalErrorLogWrk
IM_ItemCost
AR_MonthlySalesWrk
AR_CustomerAuditWrk
BM_BillDetail
VI_JobLogWrk
AR_TransactionPosting
RA_ReturnDefaults
RA_ReceiptsDefaults
AR_CashExpectationReportWrk
SO_SalesOrderHeader
AR_MatchCreditsToInvoiceWrk
IT_UserIDListingWrk
AR_FinanceChargeJournalRestart
SO_BatchAuthorizationWrk
SO_SalesOrderHistoryHeader
SY_TaskSchedulerHeader
SY_TaskSchedulerDetail
SO_DailyShipmentWrk
SO_LotSerialHistoryWrk
BM_BillHeader
SO_SalesRecapByProductLineWrk
SO_SalesRecapByWarehouseWrk
SO_SalesRecapByWhseProdLineWrk
SO_SalesRecapByDivisionWrk
RA_OptionsListingWrk
AR_DeleteChangeCustomers
SO_SalesDailyRecapWork
AR_InvoiceWrk
SY_UserTableSecurityReportWrk
AR_StatementWrk
IM_StockStatusWrk
AR_AgedInvoiceReportWrk
AR_InvoiceHistoryMemo
AR_InvoiceHistoryMemoSettings
AR_InvoiceMemo
AR_InvoiceMemoSettings
SO_SalesOrderMemo
SO_SalesOrderMemoSettings
SO_InvoiceMemo
SO_InvoiceMemoSettings
SO_SalesOrderHistMemoSettings
SO_SalesOrderHistoryMemo
RA_ReturnMemo
RA_ReturnMemoSettings
RA_ReceiptsMemoSettings
RA_ReceiptsMemo
RA_ReceiptsHistoryMemo
RA_ReceiptsHistoryMemoSettings
GL_GeneralJournalMemo
GL_TransactionJournalMemo
GL_GeneralJournalMemoSettings
GL_TransJournalMemoSettings
IT_CategoryTreeListingWrk
VI_SortWrk
AR_SalespersonSlsHistByPerWrk
SO_PickingSheetWrk
SO_CustomerSlsHistByItemWrk
SO_SalesOrderPrint
SO_CustomerSlsHistByProdLnWrk
BR_ReconciliationRegisterWrk
SO_PackingListWrk
SO_BackOrderWrk
SO_SalesOrderQuoteHistoryWrk
AR_CustomerMailingLabelsWrk
SO_SalesOrderRecap
SO_ShippingRateListingWrk
SO_SalesOrderWrk
AR_CashReceiptsWrk
SO_ShippingHistoryWrk
SO_ShippingLabelsWrk
AR_CustomerSlsHistByPerWrk
CI_MiscellaneousItemListingWrk
MD_HelpMaintListingWrk
SY_Template
SO_ShipToAddressListingWrk
IT_EmailNotificationWrk
CU_OptionsWrk
SY_LastFormUsed
SY_ReportManagerFormat
SY_ReportManagerTemplate
SY_MultiPartForm
VI_JobImportRecordTypes
IT_OptionsListingWrk
BR_BankRecapWrk
AR_DeleteChangeCustomersWork
RA_DailyRepairWrk
RA_ReturnReasonWrk
SY_ReportWizardSources
RA_GenerateTransactionsWrk
RA_OpenRMAWrk
SY_BaseODBCSecurity
SY_ODBCSecurity
RA_ReceiptsHistoryWrk
AR_AnalysisReportWrk
RA_RMAReceiverWrk
SY_SalesTaxCodeListingWrk
IM_ItemCustomerHistoryByPeriod
SO_DailyDropShipWrk
SY_ReportManagerMaster
MD_MemoMaintenanceListingWrk
AR_ChangeCustomersValidate
SY_SalesTaxScheduleListingWrk
AR_SummaryDrillDownWork
SO_SummaryDrillDownWork
PR1_EmployeeMaster
AP_Audit
AP_VendorMemo
IT_UIDCustomerAcceptanceLogWrk
IT_UIDCustomerRegisterWrk
IT_ShoppingCartRegisterWrk
IT_ShoppingCartAcceptLogWrk
SY_ActivityType
IT_ShoppingCartAutoLogDtl
IT_ShoppingCartAutoLogHdr
IT_UIDCustomerChangeAutoLog
CM_UDFOptions
GL_PayOptions
GL_PayAccount
GL_PayBank
SO_ShippingExceptionWrk
VI_ExportJobDefinitionWrk
MD_EMailNotificationListingWrk
PR_DirectDepositBank
GL_PayActivity
SY_CreditCardProcessXref
GL_PaySyncAccountWork
GL_PaySyncBankCodeWork
SY_EmbeddedIOSettings
SY_URLMaintenance
AP_VendorMemoSettings
AP_ARClearingRestart
AP_CheckRestart
AP_InvoiceHistoryMemo
AP_InvoiceHistoryMemoSettings
AP_InvoiceMemo
AP_InvoiceMemoSettings
AP_ManualCheckRestart
AP_InvoiceRestart
IM_ItemMemo
AP_VendorPurchasesHistory
AP_ChangeVendorsValidate
AP_VendorRemit
AP_ManualCheckInvDetail
AP_RepetitiveInvoiceMemo
AP_RepetitiveInvMemoSettings
AP_RepetitiveInvoiceListingWrk
AP_InvoicePaymentListingWrk
AP_OptionsListingWrk
AP_VendorMailingLabelsWrk
AP_VendorMemoPrintingWrk
AP_CheckHistoryWrk
AP_VendorAuditWrk
IM_CountCardDetail
AP_DivisionListingWrk
AP_VendorCustomerLinkListWrk
AP_VendorListingBalWrk
BR_DailyPostingWork
AP_InvoiceRegisterWrk
AP_TrialBalanceWrk
AP_RecapByDivisionWrk
AP_TaxJournalWrk
JC_CostCodeDetail
JC_JobDetail
AP_DailyPostingWork
CM_Lock
JC_JobType
AP_CheckStubWrk
AP_CheckWrk
AP_SummaryDrillDownWork
SY_1099Setup
IM_ItemMemoSettings
CU_CustomerTemplateWrk
AP_AnalysisWrk
AP_ExtendedStubWrk
AP_AnalysisReportWrk
AP_CheckRegisterWrk
AP_InvoiceHistoryWrk
AP_ManualCheckRegisterWrk
AP_CheckHeaderPosting
AP_CheckDetailPosting
AP_ExpenseByGLAccountWrk
CI_InactivationExceptionWrk
CU_ActivityLogWrk
CU_MAS_Company
AR_InvoiceHistoryPayment
SO_SalesOrderPayment
SO_InvoicePayment
SO_SalesOrderHistoryPayment
IM_WarehouseListingWrk
AP_LastBankUsed
AP_ManualCheckInvDetailWork
AP_AgedInvoiceWrk
AP_PaymentHistoryWrk
AP_CashRequirementsWrk
AP_OpenInvoiceByJobWrk
AP_Form1099PrintingWrk
AP_VendorPurchHistoryByPdWrk
AP_MonthlyPurchaseWrk
MD_ConversionDriverListingWrk
SY_ResourceRoleSecurity
AP_SalesTaxWrk
AP_InvoiceHeaderPosting
AP_InvoiceDetailPosting
AP_VendorPurchaseAnalysisWrk
SY_PersonalizeALE
AP_AssignVendorTaxSchedListWrk
MD_BatchListingWrk
AP_ARClearingRegisterWrk
SY_MemoRelationship
MD_MemoRelationshipListingWrk
AP_ARClearingRecapWrk
MD_ReportManagerMasterWrk
MD_ReportManagerTemplateWrk
AR_AnalysisWrk
SY_FieldMapping
SY_ProcessFlows
IM_ItemValuationSelection
PO_PurchaseOrderMemo
IM_ItemValuationUpdateRestart
IM_ItemVendor
IM_ItemVendorHistoryByPeriod
IM_ItemWarehouse
IM_ItemWhseHistoryByPeriod
IM_Options
IM_Physical
IM_PhysicalRestart
IM_PriceCode
IM_ProductLine
IM_SalesKitDetail
IM_SalesKitHeader
IM_SalesPromotion
IM_ItemTransactionHistory
IM_TransactionHeader
PO_PurchaseOrderMemoSettings
IM_TransactionPosting
IM_TransactionRestart
IM_ItemCostCalcSource
IM_TransactionTierDist
CI_UnitOfMeasure
IM_Warehouse
IM_WarrantyCode
SY_DeveloperManifest
SY_TemplateSetting
AP_SalesTaxCalcErrorLogWrk
PO_PurchaseOrderDetail
GL_PayActivityLogWrk
IM_ProductLineApplyWrk
PO_PurchaseOrderPrint
PO_PurchaseOrderRecap
PO_PurchaseOrderTaxDetail
PO_PurchaseOrderTaxSummary
PO_ReceiptDefaults
PO_AutoGenerateOrders
PO_ReceiptDetail
PO_ReceiptHeader
PO_ReceiptHistoryDetail
PO_ReceiptHistoryLotSerial
PO_AutoReorderSelection
PO_LandedCostAllocation
PO_LandedCostHistory
PO_ReceiptHistoryMemo
PO_ReceiptHistoryMemoSettings
PO_ReceiptHistoryTaxSummary
PO_OpenOrderDetailByItem
PO_LandedCostReceipt
PO_LandedCostType
PO_MaterialReqDefaults
PO_ReceiptMemo
PO_ReceiptMemoSettings
PO_ReceiptRestart
PO_ReceiptTaxAllocation
PO_ReceiptTaxDetail
PO_ReceiptTaxSummary
PO_ReceiptTierDistribution
PO_ReturnDefaults
PO_ReturnDetail
PO_MaterialReqDetail
PO_MaterialReqHeader
POT_TemporaryWork
PO_ReturnHeader
PO_ReturnMatRqTierCostCalcWork
PO_ReturnMemo
PO_ReturnMemoSettings
PO_ReturnRestart
PO_ReturnTaxAllocation
PO_ReturnTaxDetail
PO_MaterialReqRestart
PO_MaterialReqTierDistribution
PO_ReturnTaxSummary
PO_ReturnTierDistribution
PO_ShipToAddress
PO_VendorPriceLevel
PO_VendorPurchaseAddress
PO_ReceiptHistoryHeader
PO_VendPurchAddressListingWrk
IM_ItemListingWithSalesHistWrk
IM_AutoCostPriceChangeWrk
PO_ShipToAddressListingWrk
IM_PriceCodeListingWrk
IM_ReorderReportWrk
PO_LandedCostTypeListingWrk
IM_TurnoverReportWrk
PO_ReceiptReturnMatRqByPO
BM_DailyPostingWork
PO_VendorPriceLevelListingWrk
IM_ProductLineListingWrk
PO_PurchaseOrderRecapWrk
IM_InventoryLabelsWrk
IM_SalesKitWrk
PO_PurchaseOrderWrk
PO_ReturnOrderWrk
PO_OpenPurchaseOrderWrk
PO_PurchasesClearingWrk
PO_OpenOrdersByItemWrk
PO_OpenOrdersByJobWrk
IM_ValuationReportWrk
PO_PurchaseHistoryWrk
PO_ExpectedDeliveryRecapWrk
PO_ExpectedDeliveryWrk
IM_TransactionDetail
PL_Options
PL_JournalPDFLog
PL_PeriodEndPDFLog
PL_ReportPDFLog
PL_JournalRegister
PO_ReceiptOfGoodsInvoiceWrk
PL_JournalRegisterListingWrk
PL_ReportListingWrk
PO_DailyPurchasesJournalWrk
PL_PeriodEndReport
PL_Report
ES_Options
AP_InvoiceHistoryAsset
AP_VendorPDFLog
ES_DashboardSelectionListWrk
ES_ViewSelectionDetail
ES_ViewSelectionHeader
SY_EISViewMaster
PO_ReceiptTaxJournalWrk
SY_FormsEFilingVersion
SY_PayrollEFilingMapping
PL_PeriodEndReportListingWrk
PL_EmailMessage
SY_Token
PL_Form
PL_FormListingWrk
AR_CustomerPDFLog
AP_VendorDocumentContacts
AP_VendorDocuments
AR_CustomerDocumentContacts
AR_CustomerDocuments
IM_DataEntryCostCalcCommit
PR_EmployeePDFLog
PR_EmployeeDocuments
PL_EmailMessageListingWrk
AP_FixedAssetsHistoryWrk
IM_SalesKitWhereUsedWrk
IM_CostedSalesKitWrk
IM_PriceListWrk
IM_TransactionRegisterWrk
CI_BackOrderFillWrk
BM_BillOptionCategory
BM_BillOptionDetail
BM_BillOptionHeader
BM_PickingSheetSelection
BM_BillWhereUsed
BM_ChangeControlDetail
BM_ChangeControlHeader
BM_ChangeControlWhereUsed
BM_ComponentReportSelection
BM_CostOfMaterialsSelection
BM_CostRollUpWork
BM_DisassemblyDetail
BM_DisassemblyHeader
BM_DisassemblyTierDistribution
BM_OptionInteractionDetail
BM_OptionInteractionHeader
BR_TransactionRestart
BM_Options
BM_ProductionDefaults
BM_ProductionDetail
BM_ProductionHistoryDetail
BM_ProductionHistoryHeader
BM_ProductionHistoryTierDist
BM_ProductionTierDistribution
BM_ReplaceDeleteCompDetail
BM_ReplaceDeleteCompHeader
BM_TierCostCalcWork
BM_ProductionHeader
IM_ReceiptsHistoryWrk
PO_ReceiptInvoiceVarianceWrk
IM_SalesPromotionWrk
IM_DetailTransactionWrk
BM_DisassemblyDefaults
WO_WorkOrder
WO_WorkOrderOperationDetail
WO_OperationCode
BM_ProductionRestart
IM_ErrorLogWrk
IM_SalesHistoryWrk
PO_WOTransactionJournalWrk
IM_DailyPostingWork
PO_DailyPostingWork
IM_BalanceQtyOnHandWork
IM_ReceiptLabelsWork
IM_NegativeTierRegisterWork
CI_ChangeItemsValidate
IM_NegativeTierWrk
PO_SummaryDrillDownWork
BM_ProductionRegisterWrk
IM_SalesAnalysisWrk
IM_SalesPromotionApplyWrk
BM_ProductionLabelPrint
IM_LotSerialTransHistWrk
IM_ItemValuationChangeWrk
IM_PhysicalCountVarRegisterWrk
IM_PhysicalCountWrkstWrk
IM_PhysicalDefaults
IM_TrialBalanceWrk
IM_IssueHistoryWrk
PO_ReceiptVarianceCalculation
PO_CashRequirementsWrk
BM_DisassemblyRegisterWrk
BM_CostRollUpRestart
PO_ReturnDebitMemoJournalWrk
PO_ReturnOfGoodsRegisterWrk
PO_ReturnTaxJournalWrk
PO_OpenOrdersByWorkOrderWrk
PO_ReceiptHistoryWrk
PO_MaterialReqIssueRegisterWrk
IM_NegativeTierRestart
BM_DisassemblyRestart
BM_BillValidationWrk
BM_IndentedReportWrk
IM_ItemTransactionRecalc
BM_OptionsListingWrk
CM_Script
CM_ScriptLink
BM_ChangeControlRegisterWrk
BM_ChangeHistoryWrk
BR_TransactionRegisterWrk
PO_AutoOrderSelectWrk
BM_ReplaceDeleteCompWork
CI_MiscItemDetailTransWrk
IM_CountCardWrk
AR_PriceLevelByCustCopyWrk
AR_PriceLevelByCustListingWrk
AR_PriceLevelByCustPriceCode
AR_PriceLevelByCustShipTo
IM_CountCardVarRegisterWrk
SO_SalesOrderTierDistribution
PO_PurchaseOrderNoListingWrk
SO_LineItemCommissionListWrk
PO_ReturnOrderPrint
SY_ContextAFM
BM_ProductionLabelsWrk
BM_ProductionHistWrk
BM_GrossReqrmntWrk
BM_SummarizedReportWrk
BM_IndentedWhereUsedWrk
BM_SingleLevelWhereUsedWrk
BM_SummarizedWhereUsedWrk
BM_PickingSheetWrk
BM_SingleLevelWrk
BM_ChangeControlRestart
PO_AutomaticReorderSelectWrk
CM_ScriptSettings
MD_EISViewMasterReportWrk
SY_PreMigrationCompany
SY_PreMigrationItemCode
SY_PreMigrationWarehouse
AR_CreditCardWork
SY_CreditCardRestart
SO_SalesOrdersThatAreOpenWrk
SY_SQLOptimize
AP_VendorElectronicPayHistory
AP_VendorElectronicPayment
AP_ACHFileWrk
AP_ACHGenerationAddenda
AP_ACHGenerationDetail
AP_ACHGenerationHeader
AP_ElectronicPmtRegisterWrk
AP_PreNoteApprovalRegisterWrk
AP_PreNoteApprovalRestart
AP_PreNoteSelectionRegisterWrk
AP_PreNoteSelectionRestart
AP_VendorListingWrk
BR_AutoCheckDetail
BR_AutoCheckHeader
BR_AutoCheckSettings
BR_AutoCheckWrk
BR_PositivePayDetail
BR_PositivePayExportWrk
BR_PositivePayHeader
BR_PositivePaySelection
BR_PositivePaySettings
SY_SampleAccountsWrk
IM_CountCardRestart
IM_MissingCountCardListingWrk
AR_CustomerPricingWrk
SY_SagePEPTask
AP_ExpenseDistTableDetail
AP_ExpenseDistTableHeader
AP_ExpenseDistTableWrk
SO_LineItemCommission
IM_LotSerialOrderWork
SY_SagePEP
SY_SagePEPSettings
SY_SagePEPTrack
CU_ActivityLog
CU_Address
CU_AddressLink
CU_Company
CU_Country
CU_CountryWrk
CU_CRMServerOptions
CU_CustomerTemplate
CU_Email
CU_EmailLink
CU_Notes
CU_Opportunity
CU_Options
CU_OrderQuoteDetail
CU_OrderQuoteTracking
CU_Person
CU_Phone
CU_PhoneLink
CU_Users
SY_UserAutoComplete
CM_UIScriptLink
CM_UIScriptSettings
PO_PurchaseOrderHistoryMemo
PO_PurchaseOrderHistoryMemoSet
PO_PurchaseOrderHistTaxDetail
PO_PurchaseOrderHistTaxSummary
PO_PurchaseOrderHistoryWrk
PO_CancelReasonCode
PO_PurchaseOrderHistoryDetail
PO_PurchaseOrderHistoryHeader
BC_Rejections

This is an example of getting the column names for the AR_Customer table.

Code: Script BASIC
  1. ' Display AR_Customer column names
  2.  
  3. IMPORT ODBC.sbi
  4.  
  5. dbh = ODBC::RealConnect("SOTAMAS90","","")
  6. ODBC::query(dbh,"SELECT * FROM AR_Customer")
  7. PRINT "AR_Customer\n\n"
  8. ODBC::FetchHash(dbh, column)
  9. FOR x = 0 TO UBOUND(column) STEP 2
  10.   PRINT column[x]
  11.   IF x < UBOUND(column) - 1 THEN PRINT ", "
  12. NEXT
  13. ODBC::CLOSE(dbh)
  14.  

Output

AR_Customer

ARDivisionNo, CustomerNo, CustomerName, AddressLine1, AddressLine2, AddressLine3, City, State, ZipCode, CountryCode, TelephoneNo, TelephoneExt, FaxNo, EmailAddr
ess, URLAddress, EBMEnabled, EBMConsumerUserID, BatchFax, DefaultCreditCardPmtType, ContactCode, ShipMethod, TaxSchedule, TaxExemptNo, TermsCode, SalespersonDiv
isionNo, SalespersonNo, SalespersonDivisionNo2, SalespersonNo2, SalespersonDivisionNo3, SalespersonNo3, SalespersonDivisionNo4, SalespersonNo4, SalespersonDivis
ionNo5, SalespersonNo5, Comment, SortField, TemporaryCustomer, CustomerStatus, InactiveReasonCode, OpenItemCustomer, ResidentialAddress, StatementCycle, PrintDu
nningMessage, UseSageCloudForInvPrinting, CustomerType, PriceLevel, DateLastActivity, DateLastPayment, DateLastStatement, DateLastFinanceChrg, DateLastAging, De
faultItemCode, DefaultCostCode, DefaultCostType, CreditHold, PrimaryShipToCode, DateEstablished, CreditCardGUID, DefaultPaymentType, EmailStatements, NumberOfIn
vToUseInCalc, AvgDaysPaymentInvoice, AvgDaysOverDue, CustomerDiscountRate, ServiceChargeRate, CreditLimit, LastPaymentAmt, HighestStmntBalance, UnpaidServiceChr
g, BalanceForward, CurrentBalance, AgingCategory1, AgingCategory2, AgingCategory3, AgingCategory4, OpenOrderAmt, RetentionCurrent, RetentionAging1, RetentionAgi
ng2, RetentionAging3, RetentionAging4, SplitCommRate2, SplitCommRate3, SplitCommRate4, SplitCommRate5, DateCreated, TimeCreated, UserCreatedKey, DateUpdated, Ti
meUpdated, UserUpdatedKey


ODBC doesn't support the SQL LIMIT option. Here is a way to emulate the functionality with Script BASIC.

Code: Script BASIC
  1. ' Emulate SQL LIMIT 3 in ODBC
  2.  
  3. IMPORT ODBC.sbi
  4. INCLUDE BB.sbi
  5.  
  6. LIMIT = 3
  7. dbh = ODBC::RealConnect("SOTAMAS90", "", "")
  8. ODBC::query dbh,"SELECT * FROM AR_Customer"
  9. PRINT "AR_Customer\n\n"
  10. WHILE LIMIT
  11.   ODBC::FetchHash dbh, column
  12.   FOR x = 0 TO UBOUND(column) STEP 2
  13.     PRINT BB_PAD(column[x], 30, 1, " "), " | ", column[x + 1], "\n"
  14.   NEXT
  15.   LIMIT -= 1
  16.   PRINT "\n"
  17. WEND
  18. ODBC::CLOSE dbh
  19.  

Output
Code: [Select]
AR_Customer

ARDivisionNo                   | 01
CustomerNo                     | ABF
CustomerName                   | American Business Futures
AddressLine1                   | 2131 N. 14th Street
AddressLine2                   | Suite 100
AddressLine3                   | Accounting Department
City                           | Milwaukee
State                          | WI
ZipCode                        | 53205-1204
CountryCode                    | USA
TelephoneNo                    | (414) 555-4787
TelephoneExt                   | 219
FaxNo                          | undef
EmailAddress                   | artie@sage.sample.com
URLAddress                     | www.abf.com
EBMEnabled                     | Y
EBMConsumerUserID              | undef
BatchFax                       | N
DefaultCreditCardPmtType       | undef
ContactCode                    | ARTIE JOHN
ShipMethod                     | UPS BLUE
TaxSchedule                    | WI MILMIL
TaxExemptNo                    | undef
TermsCode                      | 01
SalespersonDivisionNo          | 01
SalespersonNo                  | 0100
SalespersonDivisionNo2         | undef
SalespersonNo2                 | undef
SalespersonDivisionNo3         | undef
SalespersonNo3                 | undef
SalespersonDivisionNo4         | undef
SalespersonNo4                 | undef
SalespersonDivisionNo5         | undef
SalespersonNo5                 | undef
Comment                        | Call Ed for credit approval.
SortField                      | AMER
TemporaryCustomer              | undef
CustomerStatus                 | A
InactiveReasonCode             | undef
OpenItemCustomer               | Y
ResidentialAddress             | N
StatementCycle                 | M
PrintDunningMessage            | Y
UseSageCloudForInvPrinting     | N
CustomerType                   | A2
PriceLevel                     | 1
DateLastActivity               | 2020-05-31
DateLastPayment                | 2020-05-31
DateLastStatement              | 2020-05-31
DateLastFinanceChrg            | 2020-04-30
DateLastAging                  | 2020-05-31
DefaultItemCode                | /WIDGET
DefaultCostCode                | undef
DefaultCostType                | undef
CreditHold                     | N
PrimaryShipToCode              | 2
DateEstablished                | 2019-01-01
CreditCardGUID                 | undef
DefaultPaymentType             | undef
EmailStatements                | N
NumberOfInvToUseInCalc         | 6
AvgDaysPaymentInvoice          | 4
AvgDaysOverDue                 | 0
CustomerDiscountRate           | .000
ServiceChargeRate              | 1.500
CreditLimit                    | 120000.00
LastPaymentAmt                 | 864.25
HighestStmntBalance            | 5732.36
UnpaidServiceChrg              | 43.89
BalanceForward                 | .00
CurrentBalance                 | 3252.38
AgingCategory1                 | 1222.58
AgingCategory2                 | 1257.40
AgingCategory3                 | .00
AgingCategory4                 | .00
OpenOrderAmt                   | 8422.64
RetentionCurrent               | .00
RetentionAging1                | .00
RetentionAging2                | .00
RetentionAging3                | .00
RetentionAging4                | .00
SplitCommRate2                 | .000
SplitCommRate3                 | .000
SplitCommRate4                 | .000
SplitCommRate5                 | .000
DateCreated                    | 2006-06-14
TimeCreated                    | 10.74974
UserCreatedKey                 | 0000000000
DateUpdated                    | 2014-11-04
TimeUpdated                    | 7.52484
UserUpdatedKey                 | 0000000000

ARDivisionNo                   | 01
CustomerNo                     | ABS
CustomerName                   | ABS - Sage cloud for invoices
AddressLine1                   | 1838 Jamboree Rd
AddressLine2                   | undef
AddressLine3                   | Accounting Department
City                           | Newport Beach
State                          | CA
ZipCode                        | 92660
CountryCode                    | USA
TelephoneNo                    | (949) 555-7814
TelephoneExt                   | 327
FaxNo                          | undef
EmailAddress                   | mary@sage.sample.com
URLAddress                     | www.abs.com
EBMEnabled                     | Y
EBMConsumerUserID              | undef
BatchFax                       | N
DefaultCreditCardPmtType       | undef
ContactCode                    | MARY BIRCH
ShipMethod                     | UPS RED
TaxSchedule                    | NONTAX
TaxExemptNo                    | undef
TermsCode                      | 01
SalespersonDivisionNo          | 01
SalespersonNo                  | 0100
SalespersonDivisionNo2         | undef
SalespersonNo2                 | undef
SalespersonDivisionNo3         | undef
SalespersonNo3                 | undef
SalespersonDivisionNo4         | undef
SalespersonNo4                 | undef
SalespersonDivisionNo5         | undef
SalespersonNo5                 | undef
Comment                        | Sage Cloud used for Invoices
SortField                      | AMER
TemporaryCustomer              | undef
CustomerStatus                 | A
InactiveReasonCode             | undef
OpenItemCustomer               | Y
ResidentialAddress             | N
StatementCycle                 | M
PrintDunningMessage            | Y
UseSageCloudForInvPrinting     | Y
CustomerType                   | A2
PriceLevel                     | 1
DateLastActivity               | undef
DateLastPayment                | undef
DateLastStatement              | undef
DateLastFinanceChrg            | undef
DateLastAging                  | undef
DefaultItemCode                | /WIDGET
DefaultCostCode                | undef
DefaultCostType                | undef
CreditHold                     | N
PrimaryShipToCode              | undef
DateEstablished                | 2014-11-06
CreditCardGUID                 | undef
DefaultPaymentType             | undef
EmailStatements                | N
NumberOfInvToUseInCalc         | 0
AvgDaysPaymentInvoice          | 0
AvgDaysOverDue                 | 0
CustomerDiscountRate           | .000
ServiceChargeRate              | 1.500
CreditLimit                    | .00
LastPaymentAmt                 | .00
HighestStmntBalance            | .00
UnpaidServiceChrg              | .00
BalanceForward                 | .00
CurrentBalance                 | .00
AgingCategory1                 | .00
AgingCategory2                 | .00
AgingCategory3                 | .00
AgingCategory4                 | .00
OpenOrderAmt                   | .00
RetentionCurrent               | .00
RetentionAging1                | .00
RetentionAging2                | .00
RetentionAging3                | .00
RetentionAging4                | .00
SplitCommRate2                 | .000
SplitCommRate3                 | .000
SplitCommRate4                 | .000
SplitCommRate5                 | .000
DateCreated                    | 2014-11-06
TimeCreated                    | 17.65796
UserCreatedKey                 | 0000000003
DateUpdated                    | 2014-11-06
TimeUpdated                    | 17.69467
UserUpdatedKey                 | 0000000003

ARDivisionNo                   | 01
CustomerNo                     | AVNET
CustomerName                   | Avnet Processing Corp
AddressLine1                   | 3361 W. Kenosha
AddressLine2                   | Powers Building
AddressLine3                   | Suite 100
City                           | Racine
State                          | WI
ZipCode                        | 53120
CountryCode                    | USA
TelephoneNo                    | (414) 555-2635
TelephoneExt                   | undef
FaxNo                          | undef
EmailAddress                   | tonys@sage.sample.com
URLAddress                     | www.avnet.com
EBMEnabled                     | Y
EBMConsumerUserID              | undef
BatchFax                       | N
DefaultCreditCardPmtType       | undef
ContactCode                    | TONY SCHUL
ShipMethod                     | UPS BLUE
TaxSchedule                    | WI
TaxExemptNo                    | undef
TermsCode                      | 01
SalespersonDivisionNo          | 01
SalespersonNo                  | 0200
SalespersonDivisionNo2         | undef
SalespersonNo2                 | undef
SalespersonDivisionNo3         | undef
SalespersonNo3                 | undef
SalespersonDivisionNo4         | undef
SalespersonNo4                 | undef
SalespersonDivisionNo5         | undef
SalespersonNo5                 | undef
Comment                        | undef
SortField                      | AVNET
TemporaryCustomer              | undef
CustomerStatus                 | A
InactiveReasonCode             | undef
OpenItemCustomer               | Y
ResidentialAddress             | N
StatementCycle                 | M
PrintDunningMessage            | Y
UseSageCloudForInvPrinting     | N
CustomerType                   | A1
PriceLevel                     | 1
DateLastActivity               | 2020-05-31
DateLastPayment                | 2020-05-31
DateLastStatement              | 2020-05-31
DateLastFinanceChrg            | 2020-04-30
DateLastAging                  | 2020-05-31
DefaultItemCode                | undef
DefaultCostCode                | undef
DefaultCostType                | undef
CreditHold                     | N
PrimaryShipToCode              | 2
DateEstablished                | 2019-01-01
CreditCardGUID                 | undef
DefaultPaymentType             | undef
EmailStatements                | N
NumberOfInvToUseInCalc         | 1
AvgDaysPaymentInvoice          | 82
AvgDaysOverDue                 | 52
CustomerDiscountRate           | 5.000
ServiceChargeRate              | 1.500
CreditLimit                    | 7500.00
LastPaymentAmt                 | 1000.00
HighestStmntBalance            | 7377.37
UnpaidServiceChrg              | 44.12
BalanceForward                 | .00
CurrentBalance                 | 5582.25
AgingCategory1                 | 1795.12
AgingCategory2                 | .00
AgingCategory3                 | .00
AgingCategory4                 | .00
OpenOrderAmt                   | .00
RetentionCurrent               | .00
RetentionAging1                | .00
RetentionAging2                | .00
RetentionAging3                | .00
RetentionAging4                | .00
SplitCommRate2                 | .000
SplitCommRate3                 | .000
SplitCommRate4                 | .000
SplitCommRate5                 | .000
DateCreated                    | 2006-06-14
TimeCreated                    | 10.74974
UserCreatedKey                 | 0000000000
DateUpdated                    | 2014-11-04
TimeUpdated                    | 7.52484
UserUpdatedKey                 | 0000000000



« Last Edit: November 19, 2017, 10:36:18 AM by John Spikowski »

John Spikowski

  • Posts: 36
Re: Sage 100 Data Access
« Reply #1 on: January 06, 2018, 05:10:20 PM »
This is an example of using the Script BASIC multi-threaded application server (that runs as a Windows service) and Sage 100 BOI to generate an ABC demo company contact list. The goal is to use the 100 application launcher web control as the UI for scripting BOI on the server.

Code: Script BASIC
  1. ' BOI Customer Contact - Web Page
  2.  
  3. IMPORT cgi.sbi
  4. IMPORT boi.sbi
  5.  
  6. oscript = BOI::CREATE(:SET, "ProvideX.Script")
  7. BOI::CBN oScript, "Init", :CALL, "C:\\Sage\\Sage 100 Advanced ERP\\MAS90\\HOME"
  8. osession = BOI::CBN(oscript, "NewObject", :SET, "SY_Session")
  9. BOI::CBN osession, "nSetUser", :CALL, "JRS", "MyPassword"
  10. BOI::CBN osession, "nsetcompany", :CALL, "ABC"
  11. BOI::CBN osession, "nSetDate", :CALL, "A/R", "20171218"
  12. BOI::CBN osession, "nSetModule", :CALL, "A/R"
  13. ocust = BOI::CBN(oscript, "NewObject", :SET, "AR_Customer_svc", osession)
  14.  
  15. cgi::Header 200,"text/html"
  16. cgi::FinishHeader
  17.  
  18. PRINT """
  19. <html>
  20. <header>
  21. <title>Customer Contacts</title>
  22. </header>
  23. <body>
  24. <table>
  25. <center><h1>ABC - Customer Contacts</h1></center>
  26. <table style="width:100%">
  27.  <tr>
  28.    <th>Customer Number</th>
  29.    <th>Company Name</th>
  30.    <th>Phone Number</th>
  31.  </tr>
  32. """
  33.  
  34. BOI::CBN ocust,"nMoveFirst"
  35. DO UNTIL BOI::CBN(ocust, "nEOF", :GET)
  36.   PRINT "  <tr>\n"
  37.   PRINT "    <td>",BOI::CBN(ocust, "sCUSTOMERNO", :GET),"</td>\n"
  38.   PRINT "    <td>",BOI::CBN(ocust, "sCUSTOMERNAME", :GET),"</td>\n"
  39.   PRINT "    <td>",BOI::CBN(ocust, "sTELEPHONENO", :GET),"</td>\n"
  40.   PRINT "  </tr>\n"
  41.   BOI::CBN ocust, "nMoveNext"
  42. LOOP
  43. PRINT """
  44. </table>
  45. </body>
  46. </html>
  47. """
  48.  
  49. BOI::CBN ocust, "DropObject"
  50. BOI::CBN osession, "DropObject"
  51. BOI::RELEASE oscript
  52.  


« Last Edit: January 15, 2018, 08:28:02 PM by John Spikowski »

John Spikowski

  • Posts: 36
Re: Sage 100 Data Access
« Reply #2 on: December 16, 2018, 03:07:10 PM »
This is an example of using the Sage 100 COM/OLE automation BOI API to read the Customer file.

Note:  The BB_PAD function is from a library I built of common ProvideX specific functions that might be helpful for programmers making the transition to Script BASIC.

Code: Script BASIC
  1. ' Script BASIC BOI - READ AR_Customer RECORD
  2.  
  3. IMPORT COM.sbi
  4.  
  5. oscript = COM::CREATE(:Set, "ProvideX.Script")
  6. COM::CBN oScript,"Init",:CALL,"C:\\Sage\\Sage 100 Standard\\MAS90\\Home"
  7. osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
  8. COM::CBN osession, "nSetUser", :CALL, "JRS", "*PASSWORD*"
  9. COM::CBN osession, "nsetcompany", :CALL, "ABC"
  10. COM::CBN osession, "nSetDate", :CALL, "A/R", "20171218"
  11. COM::CBN osession, "nSetModule", :CALL, "A/R"
  12. ocust = COM::CBN(oscript, "NewObject", :SET, "AR_Customer_svc", osession)
  13.  
  14. COM::CBN ocust,"nMoveFirst"
  15. DO UNTIL COM::CBN(ocust, "nEOF", :GET)
  16.   PRINT COM::BB_PAD(COM::CBN(ocust, "sCUSTOMERNO", :GET), 7, "R", " "), " | ", _
  17.         COM::BB_PAD(COM::CBN(ocust, "sCUSTOMERNAME", :GET), 30, "R", " "), " | ", _
  18.         COM::CBN(ocust, "sTELEPHONENO", :GET), "\n"
  19.   COM::CBN ocust, "nMoveNext"
  20. LOOP
  21.  
  22. COM::CBN ocust, "DropObject"
  23. COM::CBN osession, "DropObject"
  24. COM::RELEASE oscript
  25.  


C:\ScriptBASIC\boi>scriba custlist.sb
ABF     | American Business Futures      | (414) 555-4787
ABS     | ABS - Sage cloud for invoices  | (949) 555-7814
AVNET   | Avnet Processing Corp          | (414) 555-2635
BRESLIN | Breslin Parts Supply           | (414) 555-9654
HILLSB  | Hillsboro Service Center       | (414) 555-6599
INACTIV | Inactive Customer **INACTIVE** | (414) 555--8747
MAVRK   | Maverick Papers                | (312) 861-1200
RSSUPPL | R & S Supply Corp.             | (414) 555-5587
SHEPARD | Shepard Motorworks             | (414) 555-6544
ALLENAP | Allen's Appliance Repair       | (714) 555-3121
AMERCON | American Concrete Service      | (714) 555-2134
ATOZ    | A To Z Carpet Supply           | (714) 555-2231
AUTOCR  | Autocraft Accessories          | (714) 555-0101
BAYPYRO | Bay Pyrotronics Corp.          | (415) 555-9654
CAPRI   | Capri Sailing Ships            | (714) 555-4421
CUSTOM  | Custom Craft Products          | (714) 555-7848
GREALAR | Greater Alarm Company          | (714) 555-5531
JELLCO  | Jellco Packing                 | (714) 555-9451
ORANGE  | Orange Door & Window Co.       | (714) 555-7823

C:\ScriptBASIC\boi>

« Last Edit: December 17, 2018, 02:03:52 AM by John Spikowski »