topic QV and db mysql in QlikView

QV and db mysql

Fri, 02 Oct 2009 15:28:34 GMT

Hello,
I would to know is if possible to connct QV to a database mysql.
I would to cnow is can I connect the QV to a aour website database based
to mysql and hosted from a Provider. I hope so and in this case how to do?

Thank you./Grazie
Best Regards./Ciao

Sl.

QV and db mysql

Fri, 02 Oct 2009 17:12:16 GMT

Hi,

Yes, you can connect to provider hosted mysql database to pull in data.

You need to download the mysql odbc connector in order to setup you ODBC connection. ( check the version required

as there are different version of MySQL running out there, currently, I am using version 3.51 for the database I connected to.

Let me know if you need further help.

QV and db mysql

biester — Fri, 02 Oct 2009 17:18:53 GMT

Ciao,

I think that will only work if your provider allows database access from non local host - and I can hardly imagine that!

Rgds,
Joachim

QV and db mysql

Fri, 02 Oct 2009 17:40:05 GMT

Hi Biester,

While configuring the MYSQL ODBC connection, you need to provide user id, password, and the port to connect to.

Providing the above mention information will certainly connect you to the database.

Cheers,

QV and db mysql

Fri, 02 Oct 2009 20:55:20 GMT

As mentioned you need to be able to connect the instance (default is port 3306). From there its easy using the ODBC driver that you can download from the mysql site.

If you cannot gain access in this manner, you may have to do something like the following:

1- Using phpmyadmin (as usual thing to have on php/mysql hosting), download the database into csv, xml etc then point QlikView to it

2- Have an automated process that dumps out your tables onto a (S)FTP and then you can download the files.

3- If you consider that you may have performance issues when extracting data from your db you can consider mysql replication. This is easy to setup - you simply have a slave mysql instance elsewhere and you just have to type a few lines into my.ini (including some minor but important data about where to start replicting from, what the db block numbers is etc.). Again you can read about all of this on the mysql site.

QV and db mysql

biester — Fri, 02 Oct 2009 21:28:02 GMT

@QVCON,

of course you also have to also provide the database host. But if the USER table of the mysql schema has only entry for host localhost (or the webserver from where the website is connecting) you certainly won't get connected. It all depends on the good will of your provider. MINE does NOT allow connections from elsewhere then localhost (or from the webserver where my website is located).

Rgds,
Joachim

QV and db mysql

Fri, 02 Oct 2009 22:23:49 GMT

Ok.

I qould to try before by Local connection . I have a the db mySQL in my HDisk. (c:\) .. How to? by oDBC?

thanks, best Regards

Slash

QV and db mysql

biester — Fri, 02 Oct 2009 22:26:56 GMT

Yes. I'd try 3.51 as recommended by qvcon - available here:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

Rgds,
Joachim

QV and db mysql

Sat, 03 Oct 2009 01:13:56 GMT

I downoaded the 5.1 version here:

http://dev.mysql.com/downloads/connector/odbc/5.1.html

In phpmyAmin I create a satabase called MyDatabase

I set the datasource: MyDatabase( is it correct?)

id, server: localhost e nothing for a password.

the test works but when I connect from QV the select table is White.. what is the correct format of database I have to create in phpMyAdmin ?

Some help please?

THANKYOU

Best Regards

Slash

QV and db mysql

Sat, 03 Oct 2009 01:50:33 GMT

The database is:

collation della connessione di MySQL: Collation armscii8_bin armscii8_general_ci ascii_bin ascii_general_ci big5_bin big5_chinese_ci binary cp1250_bin cp1250_croatian_ci cp1250_czech_cs cp1250_general_ci cp1250_polish_ci cp1251_bin cp1251_bulgarian_ci cp1251_general_ci cp1251_general_cs cp1251_ukrainian_ci cp1256_bin cp1256_general_ci cp1257_bin cp1257_general_ci cp1257_lithuanian_ci cp850_bin cp850_general_ci cp852_bin cp852_general_ci cp866_bin cp866_general_ci cp932_bin cp932_japanese_ci dec8_bin dec8_swedish_ci eucjpms_bin eucjpms_japanese_ci euckr_bin euckr_korean_ci gb2312_bin gb2312_chinese_ci gbk_bin gbk_chinese_ci geostd8_bin geostd8_general_ci greek_bin greek_general_ci hebrew_bin hebrew_general_ci hp8_bin hp8_english_ci keybcs2_bin keybcs2_general_ci koi8r_bin koi8r_general_ci koi8u_bin koi8u_general_ci latin1_bin latin1_danish_ci latin1_general_ci latin1_general_cs latin1_german1_ci latin1_german2_ci latin1_spanish_ci latin1_swedish_ci latin2_bin latin2_croatian_ci latin2_czech_cs latin2_general_ci latin2_hungarian_ci latin5_bin latin5_turkish_ci latin7_bin latin7_estonian_cs latin7_general_ci latin7_general_cs macce_bin macce_general_ci macroman_bin macroman_general_ci sjis_bin sjis_japanese_ci swe7_bin swe7_swedish_ci tis620_bin tis620_thai_ci ucs2_bin ucs2_czech_ci ucs2_danish_ci ucs2_esperanto_ci ucs2_estonian_ci ucs2_general_ci ucs2_hungarian_ci ucs2_icelandic_ci ucs2_latvian_ci ucs2_lithuanian_ci ucs2_persian_ci ucs2_polish_ci ucs2_roman_ci ucs2_romanian_ci ucs2_slovak_ci ucs2_slovenian_ci ucs2_spanish2_ci ucs2_spanish_ci ucs2_swedish_ci ucs2_turkish_ci ucs2_unicode_ci ujis_bin ujis_japanese_ci utf8_bin utf8_czech_ci utf8_danish_ci utf8_esperanto_ci utf8_estonian_ci utf8_general_ci utf8_hungarian_ci utf8_icelandic_ci utf8_latvian_ci utf8_lithuanian_ci utf8_persian_ci utf8_polish_ci utf8_roman_ci utf8_romanian_ci utf8_slovak_ci utf8_slovenian_ci utf8_spanish2_ci utf8_spanish_ci utf8_swedish_ci utf8_turkish_ci utf8_unicode_ci <input type="submit" value="Esegui" />

the tavle I create is:

Tabella	Azione	Record ¹	Tipo	Collation	Dimensione	In eccesso

product		2	MyISAM	latin1_swedish_ci	2,1 KiB	-
1 tabella(e)	Totali	2	MyISAM	latin1_swedish_ci	2,1 KiB	0 B

QV and db mysql

Sat, 03 Oct 2009 03:21:36 GMT

Now works but not correctly.

The odbc connection do not show correctli the name of the table.. why? yhe name of table are all blank..

and the query shows something like this:

SWL SELECT

FROM ;

If in this script i correct and I force the value in

SQL SELECT *

From Products;

the table appears correctli in list box.

Now probabily the format of DB and the tables are wrong (ollation). Someone knows something abou? Could to help me?

Thanks

Best Regards, Slash

QV and db mysql

rwunderlich — Sat, 03 Oct 2009 06:21:18 GMT


Slash69 wrote:when I connect from QV the select table is White..

This is a known problem with the MySql 5.1 ODBC Connector. To workaround this problem, use the 3.51 ODBC Connector.

-Rob

QV and db mysql

Sat, 03 Oct 2009 13:10:59 GMT

Hi Joachim,

You are right that the host must allow external access and database host name is needed for the connection.

If your subscription plan with the provider is web hosting and database (e.g. MYSQL) is provided, then you should be able to access and extract data.

regards,

QV and db mysql

Sat, 03 Oct 2009 13:19:05 GMT

Yes it word!

Just problem when I set up the ODBC becouse it crashed. However I fount the way to solve this.

Thnks This solution is very good to menage, outside of php, the db.

Thanks at All and thanks QlikView!

Best Regards

Slash