///<summary>Queries information_schema.COLUMNS and returns all column names of given table.</summary> public static List <string> GetColumnNamesFromTableMySql(string tableName) { return(Db.GetListString(@" SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" + DataConnection.GetDatabaseName() + "' AND TABLE_NAME='" + tableName + "'")); }
///<summary>Returns serialized DbInfo object as JSON string of database info from both the preference table and non preferernce table info.</summary> private string GetDbInfoJSON(long patNum, string moduleName) { _info = new BugSubmission.SubmissionInfo(); try { //This list is not in a separate method because we want to ensure that future development related to bug submissions don't try to make assumptions //on which preferences are in an object at any given time. //Ex. Let's say in version 17.4, the list doesn't contain the payplan version preference, but 17.5 does. //If we called the method that retrieves the used preferences from WebServiceMainHQ which in this example is on version 17.5, // it would think all bugsubmission rows contain the payplan version preference when that is not the case. List <PrefName> listPrefs = new List <PrefName>() { PrefName.AtoZfolderUsed, PrefName.ClaimSnapshotEnabled, PrefName.ClaimSnapshotRunTime, PrefName.ClaimSnapshotTriggerType, PrefName.CorruptedDatabase, PrefName.DataBaseVersion, PrefName.EasyNoClinics, PrefName.LanguageAndRegion, PrefName.MySqlVersion, PrefName.PayPlansVersion, PrefName.ProcessSigsIntervalInSecs, PrefName.ProgramVersionLastUpdated, PrefName.ProgramVersion, PrefName.RandomPrimaryKeys, PrefName.RegistrationKey, PrefName.RegistrationKeyIsDisabled, PrefName.ReplicationFailureAtServer_id, PrefName.ReportingServerCompName, PrefName.ReportingServerDbName, PrefName.ReportingServerMySqlUser, PrefName.ReportingServerMySqlPassHash, PrefName.ReportingServerURI, PrefName.WebServiceServerName }; foreach (PrefName pref in listPrefs) { _info.DictPrefValues[pref] = Prefs.GetOne(pref).ValueString; } _info.CountClinics = Clinics.GetCount(); _info.EnabledPlugins = Programs.GetWhere(x => x.Enabled && !string.IsNullOrWhiteSpace(x.PluginDllName)).Select(x => x.ProgName).ToList(); _info.ClinicNumCur = Clinics.ClinicNum; _info.UserNumCur = Security.CurUser.UserNum; _info.PatientNumCur = patNum; _info.IsOfficeOnReplication = (ReplicationServers.GetCount() > 0 ? true : false); _info.IsOfficeUsingMiddleTier = (RemotingClient.RemotingRole == RemotingRole.ClientWeb ? true : false); _info.WindowsVersion = MiscData.GetOSVersionInfo(); _info.CompName = Environment.MachineName; List <UpdateHistory> listHist = UpdateHistories.GetPreviouUpdateHistories(2); //Ordered by newer versions first. _info.PreviousUpdateVersion = listHist.Count == 2 ? listHist[1].ProgramVersion : ""; //Show the previous version they updated from _info.PreviousUpdateTime = listHist.Count > 0 ? listHist[0].DateTimeUpdated : DateTime.MinValue; //Show when they updated to the current version. _info.ModuleNameCur = moduleName; _info.DatabaseName = DataConnection.GetDatabaseName(); } catch (Exception ex) { ex.DoNothing(); } return(JsonConvert.SerializeObject(_info)); }
///<summary>Gets the names of tables in InnoDB format, comma delimited (excluding the 'phone' table). Returns empty string if none.</summary> public static string GetInnodbTableNames() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetString(MethodBase.GetCurrentMethod())); } //Using COUNT(*) with INFORMATION_SCHEMA is buggy. It can return "1" even if no results. string command = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables " + "WHERE TABLE_SCHEMA='" + POut.String(DataConnection.GetDatabaseName()) + "' " + "AND TABLE_NAME!='phone' " //this table is used internally at OD HQ, and is always innodb. + "AND ENGINE NOT LIKE 'MyISAM'"; DataTable table = Db.GetTable(command); string tableNames = ""; for (int i = 0; i < table.Rows.Count; i++) { if (tableNames != "") { tableNames += ","; } tableNames += PIn.String(table.Rows[i][0].ToString()); } return(tableNames); }
///<summary>Turns "pooling" off, then opens the current database connection, adds that connection to the dictionary of MySqlConnections, then returns the unique ServerThread. ///The returned ServerThread can then be used later in order to stop the query in the middle of executing. ///A non-pooled connection will NOT attempt to re-use connections to the DB that already exist but are idle, ///rather it will create a brand new connection that no other connection can use. ///This is so that user queries can be safely cancelled if needed. ///Required as a first step for user queries (and ONLY user queries). ///Not currently Oracle compatible.</summary> public static int GetServerThread(bool isReportServer) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetInt(MethodBase.GetCurrentMethod(), isReportServer)); } MySqlConnection con = new MySqlConnection(); if (isReportServer) { if (ODBuild.IsWeb() && PrefC.ReportingServer.Server != "" && PrefC.ReportingServer.Database != DataConnection.GetDatabaseName()) { //Security safeguard to prevent Web users from connecting to another office's database. throw new ODException("Report server database name must match current database."); } con = new MySqlConnection( DataConnection.GetReportConnectionString( PrefC.ReportingServer.Server , PrefC.ReportingServer.Database , PrefC.ReportingServer.MySqlUser , PrefC.ReportingServer.MySqlPass) + ";pooling=false"); } else { //Use the database user with lower permissions when in Middle Tier since this method is explicitly designed for the User Query window. string connectStr = (RemotingClient.RemotingRole == RemotingRole.ServerWeb ? DataConnection.GetLowConnectionString() : DataConnection.GetCurrentConnectionString()); //all connection details are the same, except pooling should be false. con = new MySqlConnection(connectStr + ";pooling=false"); } con.Open(); int serverThread = con.ServerThread; //If the dictionary already contains the ServerThread key, then something went wrong. Just stop and throw. if (_dictCons.ContainsKey(serverThread)) { con.Close(); throw new ApplicationException("Critical error in GetServerThread: A duplicate connection was found via the server thread ID."); } lock (_lockObj) { _dictCons[serverThread] = con; } return(serverThread); }
///<summary>Returns true if the database has at least one table in InnoDB format. Default db is DataConnection.GetDatabaseName().</summary> public static bool HasInnoDbTables(string dbName = "") { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetBool(MethodBase.GetCurrentMethod(), dbName)); } //Using COUNT(*) with INFORMATION_SCHEMA is buggy. It can return "1" even if no results. string command = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables " + "WHERE TABLE_SCHEMA='" + POut.String(string.IsNullOrEmpty(dbName) ? DataConnection.GetDatabaseName() : dbName) + "' " + "AND ENGINE NOT LIKE 'MyISAM'"; return(Db.GetTable(command).Rows.Count > 1); }