SY_SQL

Class SY_SQL

Inherits

SY_Constant, SY_Password

Properties

LOCAL cSettingsFile$="..\home\massql.settings"

PROPERTY DSN$ * Read Only *

PROPERTY Driver$ * Read Only *

PROPERTY Optimize

PROPERTY AnalyzeTrace

Methods

FUNCTION AlterTable(table$, path$, columnActions$)

FUNCTION BracketEncode$(string$)

FUNCTION CloseSQLFiles()

FUNCTION CopySQLTable(srcTable$, srcPath$, dstTable$, dstPath$)

FUNCTION CopySQLTable(srcTable$, srcPath$, dstTable$, dstPath$, applyConstraints)

FUNCTION CopySQLTable(srcTable$, srcPath$, dstTable$, dstPath$, applyConstraints, copyData)

FUNCTION CopySQLTable(srcTable$, srcPath$, dstTable$, dstPath$, applyConstraints, copyData, useDict)

FUNCTION CopySQLTable(srcTable$, srcPath$, dstTable$, dstPath$, applyConstraints, copyData, useDict, useSqlKeys)

Function to create a copy of a SQL table. Used primarily to create an empty table of the same schema as another table (wrk tables), but includes an option to also copy the data.

Parameter	Description
srcTable$	[input, string] Table to copy from
srcPath$	[input, string] Path constant for source table - pthOTHERCOMPANY$+compCode$, pthSYSTEM$, etc.
dstTable$	[input, string] Table to copy to
dstPath$	[input, string] Path constant for destination table - pthOTHERCOMPANY$+compCode$, pthSYSTEM$, etc.
applyConstraints	{optional} [input, boolean] Flag to create indexes (including primary key constraint) to destination table. Default: No indexes are created on destination table, unless useDict flag is set. With useDict set, indexes are always created.
copyData	{optional} [input, boolean] Flag to copy existing rows to destination table. Default: Existing rows are not copied.
useDict	{optional} [input, boolean] Flag to use dictionary schema for destination table. Default: Source table schema is used for destination table (uses INSERT * INTO)
useSqlKeys	{optional} [input, boolean] Flag to not use dictionary key definition for destination table. Default: False. Set to true to use the source table's SQL key definition for the destination table.

Returns

[boolean]

Value	Description
retFAILURE	(0) - if a problem was encountered.
retSUCCESS	(1) - if the table was copied successfully.

FUNCTION CreateDatabase(compCode$)

FUNCTION CreateIndexes(database$, table$)

FUNCTION CreateIndexes(database$, table$, ddfTable$)

FUNCTION CreateIndexes(database$, table$, ddfTable$, useSqlKeys, srcPath$)

Function to create the indexes on a SQL table. Used to rename and copy standard MAS tables.

Parameter	Description
database$	[input, string] Name of database where table exists. Path constants are allowed.
table$	[input, string] Name of table to create indexes for.
ddfTable$	{optional} [input, string] Optional table name when table$ has been renamed, this argument is used to access the table info from the dictionary (ddf).
useSqlKeys	{optional} [input, boolean] Flag to not use dictionary key definition for destination table. Default: False. Set to true to use the source table's SQL key definition for the destination table.
srcPath$	[input, string] Path constant for source table - pthOTHERCOMPANY$+compCode$, pthSYSTEM$, etc.

Returns

[boolean]

Value	Description
retFAILURE	(0) - if a problem was encountered.
retSUCCESS	(1) - if indexes were created successfully.

FUNCTION CreateSQLTable(dbMgr, compCode$, table$, update)

FUNCTION CreateSQLTable(dbMgr, compCode$, table$, update, ddfTableName$)

FUNCTION CreateSQLTable(dbMgr, compCode$, table$, update, ddfTableName$, applyConstraints)

FUNCTION CreateSYSViews(compCode$)

FUNCTION DatabaseExists(compCode$)

FUNCTION DoubleQuoteEncode$(string$)

FUNCTION DropDatabase(compCode$)

FUNCTION DropIndexes(database$, table$)

FUNCTION DropIndexes(database$, table$, ddfTable$)

FUNCTION DropIndexes(database$, table1$, table2$, ddfRenameTable)

Function to drop the indexes on a SQL table. Used to rename and copy standard MAS tables.

Parameter	Description
database$	[input, string] Name of database where table exists. Path constants are allowed.
table$	[input, string] Name of table to drop indexes from.
ddfTable$	{optional} [input, string] Optional table name when table$ has been renamed, this argument is used to access the table info from the dictionary (ddf).
ddfRenameTable	{optional} [input, integer] Setting used to rename indexes when renaming a table. 1 = Renaming a MAS table to a non-standard table name, 2 = Renaming a non-MAS table back to a standard MAS table name

Returns

[boolean]

Value	Description
retFAILURE	(0) - if a problem was encountered.
retSUCCESS	(1) - if indexes were dropped successfully.

FUNCTION DropSQLTable(compCode$, table$)

FUNCTION DropSYSViews(compCode$)

FUNCTION GetApplicationConnectSettings(connectString$, outDsn$, username$, password$, sqlDriver$)

FUNCTION GetConnectString(table$, path$, rtnConnect$, rtnKeyStruct$)

FUNCTION GetConnectString(table$, path$, rtnConnect$, rtnKeyStruct$, class$)

FUNCTION GetConnectString(table$, path$, rtnConnect$, rtnKeyStruct$, class$, rtnCacheMode$)

FUNCTION GetConnectString(table$, path$, rtnConnect$, rtnKeyStruct$, class$, rtnCacheMode$, sqlKeys)

Function to compile and optimize the connection info and options required to open a SQL table.

Parameter	Description
table$	[input, string] Table name
path$	[input, string] Path constant. pthCOMPANY$, pthSYSTEM$
rtnConnect$	[output, string] Returned connection info
rtnKeyStruct$	[output, integer] Returned option info
class$	{optional} [input, string] Alternate class name to search SY_SQLOptimize with for optimization records.
sqlKeys	{optional} [input, boolean] Set to true when opening a table with an index that does not match the dictionary. This flag will use the index info from SQL rather than the Providex.KDF index info.

Returns

[boolean]

Value	Description
retFAILURE	(0) - SQL connection info was not found for the table
retSUCCESS	(1) - The connection info to oppen the table is found

FUNCTION GetDatabaseFileSize$(compCode$)

Returns the file sizes for the database.

Parameter	Description
compCode$	[input, string] Company code to use to identify the database.

Returns

[string] A list of database file names and sizes in mb.

FUNCTION GetDatabaseName$(compCode$)

FUNCTION GetDatabaseName$(compCode$, table$)

FUNCTION GetDatabaseNames$()

Get list of Sage 100 database names

Returns

[string] List of database names

FUNCTION GetDatabaseRecoveryModel$(compCode$)

Returns the recovery model of the database.

Parameter	Description
compCode$	[input, string] The company code for the database.

Returns

[string] The database recovery model.

FUNCTION GetDatabaseTableNames$(database$)

FUNCTION GetDatabaseTableNames$(database$, modCode$)

FUNCTION GetDatabaseTableNames$(database$, modCode$, searchMask$)

Get list of table names in database

Parameter	Description
database$	[input, string] Database to search
modCode$	{optional} [input, string] Module used to restrict the list of tables
searchMask$	{optional} [input, string] Comparison mask to match to add names to list

Returns

[string] List of table names

FUNCTION GetReportConnectSettings(connectString$, outDsn$, username$, password$)

FUNCTION GetReportConnectString(connectString$, password$)

FUNCTION GetRowCount(compCode$, table$)

FUNCTION GetSQLClientVersion(sqlVersion$, sqlClient$)

FUNCTION GetTableCount(compCode$)

FUNCTION GetTableNames$(compCode$)

FUNCTION GetTableNames$(compCode$, modCode$)

Get list of table names in database for company

Parameter	Description
compCode$	[input, string] Company code to use to identify the database
modCode$	{optional} [input, string] Module used to restrict the list of tables

Returns

[string] List of table names

FUNCTION InKdf(table$)

FUNCTION LoginExists(loginName$, exists)

FUNCTION OpenSQLFiles()

FUNCTION QuoteEncode$(string$)

FUNCTION RemoveRecsFromTable(removeTable$, whereClause$)

FUNCTION RemoveRecsFromTable(removeTable$, whereClause$, compCode$)

Removes records matching where clause from specified table

Parameter	Description
removeTable$	[input, string] table to remove records from
whereClause$	[input, string] used to select records to removed from table
compCode$	[input, string] Optional, Company code to use to identify the database

Returns

[boolean]

Value	Description
retFAILURE	(0) - An error occurred executing the command.
retSUCCESS	(1) - No error occurred executing the command.

FUNCTION RenameSQLTable(compCode$, table$, newTable$)

FUNCTION SetApplicationConnectSettings(serverName$, password$, updateSqlServer)

FUNCTION SetConnectString(sqlServerName$, sqlDriver$, isTrustedConnection, sqlLogin$, sqlPassword$, connectString$, openString$)

Function to build the correct SQL connection string based on version and driver being used.

Parameter	Description
sqlServerName$	[input, string]
sqlDriver$	[input, string]
isTrustedConnection	[input, boolean]
sqlLogin$	[input, string]
sqlPassword$	[input, string]
connectString$	[output, string] Returned connection string
openString$	[output, integer] Returned open string

Returns

[boolean]

Value	Description
retFAILURE	(0) - Unable to open SQL connection with the given connection info or no valid SQL client found.
retSUCCESS	(1) - The connection info is valid

FUNCTION SetReportConnectSettings(serverName$, password$, updateSqlServer)

FUNCTION TableExists(table$, compCode$)

FUNCTION TestConnection(opn$, connectString$, inDsn$, username$, password$)

FUNCTION TestCurrentSqlSettings()

FUNCTION TranslateProvidexToSQL(compCode$)

FUNCTION TranslateProvidexToSQL(compCode$, modCode$)

FUNCTION TranslateProvidexToSQL(compCode$, modCode$, isInPlaceInstall)

FUNCTION TruncateTable(table$)

FUNCTION TruncateTable(table$, compCode$)

Properties inherited from: SY_Constant

retFAILURE, retSUCCESS, retNEW, retWARNING, retCANCEL, retYES, retNO, msgYES$, msgNO$, msgCANCEL$, msgOK$, msgRETRY$, msgABORT$, msgIGNORE$, msgNOTONFILE$, msgTIMEOUT$, msgDISABLE, msgACTIVE, msgLinkHELP$, msgLinkMSG$, isFALSE, isTRUE, isYES$, isNO$, isBUSY$, edtNONE, edtEXISTS, edtNEW, ctlDISABLE, ctlENABLE, ctlSHOW, ctlHIDE, ctlLOCK, ctlUNLOCK, ctlLOCKORDISABLE, ctlUNLOCKORENABLE, popHIDE, popDISABLE, popSHOW, popSHOWCHECKED, grdMODEVIEW, grdMODEMAINT, grdMODEADD, filCREATE, filLOCK, filCREATELOCK, filGLOBAL, filCREATEGLOBAL, filLOCKGLOBAL, filCREATELOCKGLOBAL, filCHANNEL, filRAW, filPURGE, filLOAD, filINPUT, filNOOBJECT, filPVX, filREFRESH, filPVX4$, filPVX5$, pthCOMPANY$, pthOTHERCOMPANY$, pthPRCOMPANY$, pthOTHERPRCOMPANY$, pthSYSTEM$, pthUSER$, pthSOA$, pthHOME$, pthROOT$, pthDICTIONARY$, pthTEXTOUT$, pthLOCK$, pthLOCKSYS$, pthPREFIX$, pthARCHIVE$, pthLOGFILES$, folderPROGRAMFILES, folderPROGRAMFILESX86, folderCOMMONAPPLICATIONDATA, folderCOMMONPROGRAMFILES, folderCOMMONPROGRAMFILESX86, errOBJECT, errSYSTEMLOCK, errCONTINUE, errEMBEDDEDIO, errSYSLOCK, extSQLCREATETABLE$, extSQLALLOWNULLDATES$, envSPP$, envDEFAULT$, dbALL$, dbNONE$, dateTOSTRINGFORMAT$, dateLONGMASK$, dateSHORTFMT, dateLONGFMT, dateYEARFIRST, dateMONTHFIRST, dateDAYFIRST, timeMINUTESMASK$, timeMINUTES24MASK$, timeSECONDSMASK$, timeSECONDS24MASK$, idxPRIMARY$, idxBATCH$, idxDISPLAY$, dirFIRST, dirLAST, dirNEXT, dirPREVIOUS, typALPHANUM, typZEROFILL, typCHARNUM, typMASTERNUM, typALPHANUMNF, typMASTERNUMC, typMASTERNUMI, typALPHA$, typNUMBERS$, typALPHANUM$, typALPHANUMNF$, typCHARNUM$, typMASTERNUM$, typMASTERNUMCLEN, modTASKOTFDISALLOW$, logALLACTIVITY$, logACTIVITY$, logERROR$, logACTNEWCUSTOMER$, logACTDATASYNC$, logACTDATAIMPORT$, logACTOPTIONCHANGED$, logACTPROGRAMLAUNCH$, logDFDM$, logSECURITY$, logTASKSCHEDULER$, logWEBERROR$, logVISUALINTEGRATOR$, logEntryIDLength, chkOFF$, chkON$, chkGRAYED$, closeTaskLAUNCHERQUIT, closeTaskSHUTDOWNALL, closeTaskMASTERCONSOLE, closeTaskAUTOLOGOFF, closeTaskDESCRIPTIONS$, secVIEWONLY, secCREATEONLY, secMODIFYONLY, secCREATEMODIFY, secDELETEONLY, secCREATEDELETE, secMODIFYDELETE, secFULLACCESS, secDEFAULTUSER$, secDEFAULTROLE$, secDEFAULTCOMPANY$, regHKEY_CLASSES_ROOT, regHKEY_CURRENT_USER, regHKEY_LOCAL_MACHINE, regHKEY_USERS, regHKEY_CURRENT_CONFIG, regODBC_PATH$, regMAS_PATH$, E3_MYHANDLE, E3_TASK, E3_QUIT, E3_COCHANGE, E3_DATECHANGE, E3_USERCHANGE, E3_SRVRPATH, E3_HELPFILE, E3_TECHSUPPORT, E3_QUERYTOQUIT, E3_SUPERVISOR, E3_CUSTOMIZE, E3_SWITCHMODULE, E3_DISABLEWIN, E3_CURSOR, E3_USERID, E3_PRIVATETASK, E3_CHAT, E3_BROADCAST, E3_RELOAD, E3_PASSTHROUGH, E3_EXECUTE, E3_EXPCOCHANGE, E3_EXPTASK, E3_EXPLAUNCH, E3_SILENT, E3_DIRECT, E3_NODELIST, E3_OCXITEM, E3_MODULEID, E3_CUSTFAV, E3_HOMEPAGE, E3_MINIMIZE, E3_UNDOCKED, E3_DOCKED, E3_PUBLICTASK, E3_LAUNCHURL, E3_BTNCHANGE, E3_STDBTNRELOAD, E3_MYBUSINESS, E3_REGLOADED, E3_LOADED, E3_V4TASK, E3_PUBMODIFY, E3_TASKRELOAD, E3_SETAUTOHIDE, E3_COCHANGE2, E3_TASK_NC, E3_V4TASK_NC, E3_TASK_ALTC, E3_V4TASK_ALTC, E3_PASSWORD, E3_LAUNCHVIEW, E3_LAUNCHBIETASK, E3_CLEANUPBIE, E3_STARTVIEW, E3_BIE_ADMIN, E3_HELPPATH, E3_CUSTOMERID, E3_MASVERSION, E3_LAUNCHEMBEDDED, E3_LAUNCHERMENU, E3_APP_POINTSIZE, E3_LAUNCHERVERSION, E3_DISABLE, E3_ENABLE, E3_SHOW, E3_HIDE, E3_REFRESH, E3_LIBMASTER, WM_COPY, usrDspLOGON$, usrDspNAME$, usrDspCODE$, usrDspEMAIL$, wtsINITIALPROGRAM, wtsAPPLICATIONNAME, wtsWORKINGDIRECTORY, wtsOEMID, wtsSESSIONID, wtsUSERNAME, wtsWINSTATIONNAME, wtsDOMAINNAME, wtsCONNECTSTATE, wtsCLIENTBUILDNUMBER, wtsCLIENTNAME, wtsCLIENTDIRECTORY, wtsCLIENTPRODUCTID, wtsCLIENTHARDWAREID, wtsCLIENTADDRESS, wtsCLIENTDISPLAY, wtsCLIENTPROTOCOLTYPE, thmNORMAL, thmMETAL, thmOLIVE, rptREPORTTYPESTD, rptREPORTTYPELABEL, rptREPORTTYPEMULTCOL, rptISTRUE, rptISFALSE, rptISDEFAULT, rptPORTRAIT, rptLANDSCAPE, rptASCENDING, rptDESCENDING, rptRIGHTALIGN, rptLEFTALIGN, rptHORCENTERALIGN, rptLSSINGLELINE, rptSTSUM, rptSTAVERAGE, rptSTCOUNT, rptDFLTHSPACING, rptDFLTVSPACING, rptDFLTFONTSIZE, rptDFLTLEFT, rptDFLTTOP, rptTWIPSPERCHARW, rptTWIPSPERCHARH, rptSVTREPORTTITLE, rptEDTDISKFILE, rptGCANYVALUE, rptGCDAILY, rptCRDATEFIELD, rptCRDATETIMEFIELD, crEFTCrystalReport, crEFTRichText, crEFTWordForWindows, crEFTExcel, crEFTPortableDocFormat, crEFTHTML32, crEFTHTML40, crEFTExcelRecord, crEFTText, crEFTCharacterSeparatedValues, crEFTTabSeparatedText, crEFTEditableRTF, crEFTXml, crEFTRPTR, crEFTExcelWorkbook, cContextKeyLen1, cContextKeyLen2, cContextKeyLen3, adCmdText, adCmdStoredProc, adClipString, adExecuteNoRecords, adVarChar, adInteger, adParamInput, adParamOutput, adParamInputOutput, adParamReturnValue, adTextNoRecords, adOpenForwardOnly, adOpenStatic, adLockReadOnly, adUseServer, adUseClient, dbTMP$, dbTEMPDB$, dbSYSDB$, dbPVXDDF, dbPVXDB, dbMYSQL, dbADO, dbSQL, sqlSQLOLEDB$, LastErrorMsg$, LastErrorNum$, RoleTypes$, SageCloudAppDesc$, IolNomads$

Methods inherited from: SY_Constant

GetNOMADSIolist$(), GetRoleTypes$(), MaskString(), RawString(), ClassValidateEMailAddress(), dateTODAY$(), Element$(), fmtMODCODE$(), fmtMODPREFIX$(), PERFORM FunctionBreak(), GetExceptionMsg$(), GetFileName(), GetJobTitle$(), GetJobTitles(), GetPrinterProps(), GetRecordCount(), isALPHA(), isDATE(), isNUMERIC(), MsgPath$(), ParseString(), ParseTagField(), Position(), QUO$(), ValidateFormatType(), ZeroFill$()