Well I tried the SQL Server Management Studio solution to connect to a MySQL database then lost my way wondering what was I trying to achieve?

I've written this article because this is how I connected to a MySQL database from within the Business Intelligence Development Studio (BIDS) IDE from Microsoft.

Required:
  • Windows XP (ODBC Data Source Administrator)
  • MS Business Intelligence Development Studio 2008
  • Test/Sample MySQL Database to connect to (server and username + password).
  • Admin Access to the reporting server if you plan on deploying the report to it.
Scenario:
  • We want to report on a MySQL database
  • MySQL login information is a user who exists as a MySQL user and a server user.
  • Reporting server is remote as well as the MySQL database.
  • BIDS is on the client machine.


Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.

One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).


The following describes how to setup a database user with read-only access to the AdventureWorks database.

Using SQL Server Management Studio 2008:
  1. Connect to your database server.
  2. Expand Security > Logins.
  3. Right-click on the user who will be set as having read-only access (in this example "adventureworksro").
  4. Select Properties.
  5. Select User Mapping.
  6. Map the login to the database they will have access to.
  7. Tick the boxes for role membership next to public and db_datareader.
  8. Confirm by clicking OK.
You should get something like the following:
Login Properties - Adventureworksro

The scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across the tables of the local database which had valid content to extract keywords from.

The following is a MYSQL query that displays the structure of all the columns in all the databases of the localhost:
copyraw
SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME
  1.  SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME 


The following is a MYSQL query that finds all columns (displayed as "tablename.columnname") that had the data_type TEXT across all databases:
copyraw
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME
  1.  SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME 


The following is a MYSQL query that finds all columns that had the data_type TEXT across a specified database (eg. sample_db):
copyraw
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME
  1.  SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME 


Note that the user launching this SQL query would need the SELECT privilege as a minimum on "information_schema" (arguable).
Category: MySQL :: Article: 305

The title of this article implies something rather odd and upcoming considering that Sun Microsystems bought MySQL and Oracle bought Sun. But in fact, this is just a quick list of some regular commands in MySQL that I need in Oracle:


Well I find myself again the dummie of the Internet.  I basically came across this error and STFW'd for ages following complex T-SQL Timestamp conversions but to no avail.  Not saying that you shouldn't try their solutions but just check you haven't done this silly mistake.

I'm only guessing the same error would happen in Report Builder 2.0 which I've stopped using as my day job wants us to use Business Intelligence Development Studio for all our SQL Server Analysis Service (SSAS) and Reporting Service (SSRS) projects/solutions.  The features and interface are very similar when developing reports though.

The error I'd get was

An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the dataset DataSet1.
Conversion failed when converting date and/or time from character string.

Ok is it just me who does everything slowly and badly until someone comes along and says why are you doing it like that?  My justification is that the job has to be done no matter what.

If you've ever sat there with phpMyAdmin or a MySQL Administration Tool (like Navicat or SQLYog), and still there at night editing each field so that it displays correctly... well that's usually me.

Found it a bit boring but here's a short bit of code to speed it up which I now use:

copyraw
update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace');
  1.  update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace')

Source: http://www.mediacollege.com/computer/database/mysql/find-replace.html

 

Category: MySQL :: Article: 248

Suppose you have a column in your table that you use as a counter (storing the value of the counter - eg. times an article has been displayed).

Basically what I used to do is something similar to the following:

  1. SELECT counter_field_value FROM table1 WHERE column1='this_article'
  2. Add 1 to counter_field_value
  3. UPDATE table1 SET counter_field_value=<new_counter_field_value> WHERE column1='this_article'

Combined with a PHP script this could be a few lines for something really small.

 

The quick trick to this is to do it all in one query:

copyraw
UPDATE table1 SET counter_field_value=counter_field_value+1 WHERE column1='this_article'
  1.  UPDATE table1 SET counter_field_value=counter_field_value+1 WHERE column1='this_article' 
Category: MySQL :: Article: 243

A data type reference table. If you're designing a database then you don't need me to tell you what this is.

My personal opinion is to always try to use the minimal type and length of the value required. For example, a comment of 500 words should only be TEXT (~64Kb) rather than LONGTEXT (~4Gb).


 I've been looking into this for a friend and going through forums to investigate this error.  After you've checked your database connection details, I find what FisherC said below is the most probable cause for the error.

FisherC says:

I have the same problem, but I'm not sure for the same reasons.  I installed the standard 1.5 installation package. I have uninstalled and reinstalled once just to make sure everything was OK. Basically after I use my website (using the default Joomla welcome template with NO modifications whatsoever) I start to get the "cannot connect to MYSQL." I contacted my webhost, and the problem is the number of MYSQL database queries. It goes up to about 200,000 in the first few minutes of use and then after that every page load is a few hundred to few thousand queries (per page). The webserver limits me to 50,000 queries an hour and I am blowing past that quickly with just 1 person (me!) on the site so they just refuse the connnection after a while.  Obviously something is not right. I am searching the forums for an answer and if I don't find one I'll repost. I just thought I'd share this with you in case this your problem too.

Source: Joomla Forum: http://forum.joomla.org/viewtopic.php?f=433&t=198257

My Solution

I have over 20 joomla websites on my virtual server and I haven't had this problem.  

  1. I setup a website with about 20 3rd-party components installed, 15 modules and about 15 plugins (excluding the core modules/plugins) and managed to recreate this error.  
  2. I disabled half of these but still got the error.  
  3. Cleared the expired cache but still got the error.
  4. I then uninstalled half of these (including Kunena and Agora forums), specifically database intensive plugins and the error happened less...
  5. I then went through the menus and installation screen to get rid of anything that was not visibly used on the website.  The website was working fine and I was able resume my click-happy habits.

It's a given that in my test, there was only 1 user (me).  There are reports though that some people set up websites with NO 3rd-party items whatsoever... For those of you with this issue, I'd double-check your website host allows Joomla and if they don't, look for either an upgrade or a different host.

If your website has been LIVE for a while and it's returning this error despite having little installed, then check your logs to see if anyone has been trying to hack your website.  There are a number of components I have found that even with captcha features, still get hacked with viagra emails and what have you (eg. JoomlaBook).  If you don't want to go through the logs, try setting up a test joomla site with a different database and user and see if that behaves the same way.

Virtual Hosts

I used to have an account with 1&1 (oneandone.co.uk) and I would strongly urge Joomla developers to avoid them like the plague.  They have been less than helpful and when I only had one Joomla site with them, they still would block me thinking I was trying to hack into their servers because of the number of queries Joomla does and the number of times I refresh the page and upload files.  Why they think that developers should not be able to send a 1000 queries a minute or why a developer has to refresh the page, make a change, upload, etc.  well it's beyond a joke and no one should waste their time with such companies.


Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

work   version   system   note   create   website   order   client   name   zoho   case   creator   value   where   google   function   windows   database   uploaded   added   page   server   need   date   would   following   parameter   source   using   error   file   mysql   data   used   report   list   deluge   time   display   table   form   first   files   script   joomla   find   license   code   field   user   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.