virtual table and mysql

[Phjr]

Hi, seem to have a upper/lower case problem.
defined a virtual table as follows
mysqlconn='Driver={MySQL ODBC 5.1 Driver};';
+'Server=10.231.92.232;';
+'Database=protodata;';
+'User=proto;';
+'Password=proto;'

open database odbcdev
create virtualtable t1 alias 't1' connstr mysqlconn as select * from protodata.t1
close database

From console
open database odbcdev
list tables
use t1
close database

I get
Tables in Database: odbcdev
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ t1 /opt/lianja/data/odbcdev/t1.dbf VirtualTable
| connstr 'Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;'
| alias 't1'
| as select * from protodata.t1

and from use t1
Wed Jul 31 12:05:05 2013
**** Lianja error ****
use 't1'
^
File 'T1' does not exis

Lianja runs on Centos 6.4 64bit and an ODBC import from Lianja runs successfully.

Regards

[yvonne.milne]

Hi,
With the odbcdev database open, if you look in the Data Workspace at the Tables listing, does it show the record count for t1? Also, if you open the database and virtualtable after issuing SET DEBUG ON in the Console, do you get any information in the /opt/lianja/debug/ debug files after exiting?

Thanks,
Yvonne

[Phjr]

It does'nt show any record, it says "encrypted".

Found /opt/lianja/debug/debug_client.txt
Class Cursoradaptor Object OBJECT(96263152,0) Method: name='CONSTRUCTOR'
File rco_cursoradaptor.cpp at line 393 alias=T1 basetable=protodata.t1 command=select * from protodata.t1
File rco_cursoradaptor.cpp at line 2453 setTables(97613584) table=protodata.t1
File rco_cursoradaptor.cpp at line 2853 setDataSource() value=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;@@basetable=@@primarykey=@ @properties=
File rco_cursoradaptor.cpp at line 2859 setDataSource() value=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;@@basetable=@@primarykey=@ @properties=
File rco_cursoradaptor.cpp at line 594 datasource=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto; username= password= _allowupdate=1
File rco_cursoradaptor.cpp at line 162 database type is ''
Class Column Object OBJECT(96263344,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96263536,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96263728,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96263920,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96264112,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96264304,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96264496,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96264688,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96264880,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96269680,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96265264,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96265072,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96251040,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96265648,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96250272,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96265456,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(65628048,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96250656,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(65627856,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96265840,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96267952,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96267760,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96267568,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96267376,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96267184,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96266992,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96266800,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96266608,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96266416,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96266224,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96266032,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96270448,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96270256,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96270064,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96269872,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96269488,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96269296,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96269104,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96268912,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96268720,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96268528,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96268336,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96268144,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96270640,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96270832,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96271024,0) Method: name='CONSTRUCTOR'
Class Column Object OBJECT(96271216,0) Method: name='CONSTRUCTOR'
Class Header Object OBJECT(96271408,0) Method: name='CONSTRUCTOR'
Class Cursoradaptor Object OBJECT(96271792,0) Method: name='CONSTRUCTOR'
File rco_cursoradaptor.cpp at line 393 alias=T1 basetable=protodata.t1 command=select count(*) from protodata.t1
File rco_cursoradaptor.cpp at line 2453 setTables(106363008) table=protodata.t1
File rco_cursoradaptor.cpp at line 2853 setDataSource() value=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;@@basetable=@@primarykey=@ @properties=
File rco_cursoradaptor.cpp at line 2859 setDataSource() value=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;@@basetable=@@primarykey=@ @properties=
File rco_cursoradaptor.cpp at line 594 datasource=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto; username= password= _allowupdate=1
File rco_cursoradaptor.cpp at line 162 database type is ''
Class Cursoradaptor Object OBJECT(96271984,0) Method: name='CONSTRUCTOR'
File rco_cursoradaptor.cpp at line 393 alias=T1 basetable=protodata.t1 command=select * from protodata.t1
File rco_cursoradaptor.cpp at line 2453 setTables(97662464) table=protodata.t1
File rco_cursoradaptor.cpp at line 2853 setDataSource() value=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;@@basetable=@@primarykey=@ @properties=
File rco_cursoradaptor.cpp at line 2859 setDataSource() value=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto;@@basetable=@@primarykey=@ @properties=
File rco_cursoradaptor.cpp at line 594 datasource=Driver={MySQL ODBC 5.1 Driver};Server=10.231.92.232;Database=protodata;Us er=proto;Password=proto; username= password= _allowupdate=1
File rco_cursoradaptor.cpp at line 162 database type is ''

Regards

[barrymavin]

Its not connecting. Use the ODBC console tab to test this first.

Also recreate the virtualtable without the alias specified.

[Phjr]

I can connect from ODBC console and even import, using the datasource.
The connect string used in the script is then incorrect ...

mysqlconn='Driver={MySQL ODBC 5.1 Driver};';
+'Server=10.231.92.232;';
+'Database=protodata;';
+'User=proto;';
+'Password=proto;'
will try various combinations

[barrymavin]

Ok then remove the alias it may be confusing it.

[yvonne.milne]

I can connect from ODBC console and even import, using the datasource.
The connect string used in the script is then incorrect ...

mysqlconn='Driver={MySQL ODBC 5.1 Driver};';
+'Server=10.231.92.232;';
+'Database=protodata;';
+'User=proto;';
+'Password=proto;'
will try various combinations

Try removing the {} from around the Driver.

Regards,
Yvonne

[Phjr]

That was it (again) !

connection string below works on Centos 6.4

mysqlcs='Driver=MySQL ODBC 5.1 Driver;Server=10.231.92.232;Database=protodata;Use r=proto;Password=proto;'

Tables in Database: odbcdev
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ t1 /opt/lianja/data/odbcdev/t1.dbf VirtualTable
| connstr 'Driver=MySQL ODBC 5.1 Driver;Server=10.231.92.232;Database=protodata;Use r=proto;Password=proto;'
| alias 'T1'
| as select * from protodata.t1
Structure for table : _000012bf00dd.dbf
Alias : t1
Number of records : 1
Date of creation : 08/02/2013
Date of last update : 08/02/2013
DES3 Encrypted : No
CursorAdaptor : Yes
Field Field Name Type Width Dec Description
1 NAME Character 10
** Total ** 11


Best regards

[yvonne.milne]