Description:
I have developed an application using xHarbour and FiveWin GUI Library with the following MySQL Query
Select UserName,User_Id, Active,Password,
case
When Active = 'T' Then 'Yes'
else 'No' "
end as Enabled
from users where Branch_ID=1
This was working fine in my application with MySQL ODBC Driver 3.51
Today I uninstalled ODBC Driver 3.51 and installed the new ODBC Driver 5.1
Unfortunately after installing the new Driver 5.1 I get the follwoing error message when I run my application
"Data provider or other service returned an E_FAIL status"
This error is not there If I change the ODBC Driver back to 3.51
I tried this in different PC's but it seems that the problem exists only when ODBC Driver 5.1
The MySql Server Ver is 6.0 is running on a Windows XP (PC)
My Operating System is Windows XP (SP3)
Application is developed using xHarbour and FiveWin (GUI Library)
The Connection string in my applications is
For Ver ODBC 3.51
"Driver={MySQL ODBC 3.51 Driver};Server=192.168.0.170;Port=3306;Database=mytest;User=root;Password=123;Option=3;"
For ver ODBC 5.1
"Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.170;Port=3306;Database=mytest;User=root;Password=123;Option=3;"
The problem is only with that particular SQL statement which is working fine in ODBC Driver Ver 3.51 but not in ODBC Driver Ver 5.1
If I change the sql statement from
Select UserName,User_Id, Active,Password,
case
When Active = 'T' Then 'Yes'
else 'No' "
end as Enabled
from users where Branch_ID=1
To
Select UserName,User_Id,Active,Password from users where Branch_ID=1
Then there is no problem and everything works fine
I have created a Error TraceLog file SQL.Log file Generated from ODBC Connection manager from Windows ODBC Manager. Once you give me the permission to upload the log file I shall attach the file.
Regards
Anser
How to repeat:
*--------- My Code *--------------------------------*
// Creating Connection
oConnection:=CreateObject("ADODB.Connection")
oConnection:ConnectionString:="Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.170;Port=3306;Database=mydata;User=root;Password=123;Option=3;"
// Opening Connection
oConnection:Open()
// Creating Record Set
oRecSet:= CreateObject("ADODB.RecordSet")
cSql:="Select UserName,User_Id, Active,Password, "
cSql+="case "
cSql+=" When Active = 'T' Then 'Yes'"
cSql+=" else 'No' "
cSql+="end as Enabled "
cSql+="from users where Branch_ID=1"
// Recordset Properties
oRecSet:CursorLocation := adUseClient
oRecSet:LockType := adLockOptimistic
oRecSet:CursorType := adOpenDynamic
oRecSet:Source :=cSQL
oRecSet:ActiveConnection(oConnection)
oRecSet:Open() /* Here I get the above said error */
*--------- End of My Code *--------------------------------*
*----- Sql Script to create the Table and Data ---------------*
USE pdms;
CREATE TABLE `users` (
`Branch_ID` int(10) unsigned NOT NULL,
`User_ID` int(11) NOT NULL,
`UserName` varchar(30) NOT NULL,
`Password` varchar(10) DEFAULT NULL,
`User_Level` int(11) NOT NULL,
`Active` enum('T','F') NOT NULL DEFAULT 'T',
PRIMARY KEY (`Branch_ID`,`User_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,1,'Admin','admin',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,2,'Anser','Anser',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,3,'User 3','Anil',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,4,'User 4','Abhilash',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,5,'User 5','Rajesh',1,'F');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,6,'User 6','Usha',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,7,'User 7','Sunil',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,8,'User 8','Babu',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,9,'User 9','Hari',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,10,'User 10','Saju',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,11,'User 11','Simon',1,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,12,'User 12','Antony',2,'F');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,13,'User 13','testuser',2,'T');
insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,14,'User 14','testuser2',2,'F');
Description: I have developed an application using xHarbour and FiveWin GUI Library with the following MySQL Query Select UserName,User_Id, Active,Password, case When Active = 'T' Then 'Yes' else 'No' " end as Enabled from users where Branch_ID=1 This was working fine in my application with MySQL ODBC Driver 3.51 Today I uninstalled ODBC Driver 3.51 and installed the new ODBC Driver 5.1 Unfortunately after installing the new Driver 5.1 I get the follwoing error message when I run my application "Data provider or other service returned an E_FAIL status" This error is not there If I change the ODBC Driver back to 3.51 I tried this in different PC's but it seems that the problem exists only when ODBC Driver 5.1 The MySql Server Ver is 6.0 is running on a Windows XP (PC) My Operating System is Windows XP (SP3) Application is developed using xHarbour and FiveWin (GUI Library) The Connection string in my applications is For Ver ODBC 3.51 "Driver={MySQL ODBC 3.51 Driver};Server=192.168.0.170;Port=3306;Database=mytest;User=root;Password=123;Option=3;" For ver ODBC 5.1 "Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.170;Port=3306;Database=mytest;User=root;Password=123;Option=3;" The problem is only with that particular SQL statement which is working fine in ODBC Driver Ver 3.51 but not in ODBC Driver Ver 5.1 If I change the sql statement from Select UserName,User_Id, Active,Password, case When Active = 'T' Then 'Yes' else 'No' " end as Enabled from users where Branch_ID=1 To Select UserName,User_Id,Active,Password from users where Branch_ID=1 Then there is no problem and everything works fine I have created a Error TraceLog file SQL.Log file Generated from ODBC Connection manager from Windows ODBC Manager. Once you give me the permission to upload the log file I shall attach the file. Regards Anser How to repeat: *--------- My Code *--------------------------------* // Creating Connection oConnection:=CreateObject("ADODB.Connection") oConnection:ConnectionString:="Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.170;Port=3306;Database=mydata;User=root;Password=123;Option=3;" // Opening Connection oConnection:Open() // Creating Record Set oRecSet:= CreateObject("ADODB.RecordSet") cSql:="Select UserName,User_Id, Active,Password, " cSql+="case " cSql+=" When Active = 'T' Then 'Yes'" cSql+=" else 'No' " cSql+="end as Enabled " cSql+="from users where Branch_ID=1" // Recordset Properties oRecSet:CursorLocation := adUseClient oRecSet:LockType := adLockOptimistic oRecSet:CursorType := adOpenDynamic oRecSet:Source :=cSQL oRecSet:ActiveConnection(oConnection) oRecSet:Open() /* Here I get the above said error */ *--------- End of My Code *--------------------------------* *----- Sql Script to create the Table and Data ---------------* USE pdms; CREATE TABLE `users` ( `Branch_ID` int(10) unsigned NOT NULL, `User_ID` int(11) NOT NULL, `UserName` varchar(30) NOT NULL, `Password` varchar(10) DEFAULT NULL, `User_Level` int(11) NOT NULL, `Active` enum('T','F') NOT NULL DEFAULT 'T', PRIMARY KEY (`Branch_ID`,`User_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,1,'Admin','admin',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,2,'Anser','Anser',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,3,'User 3','Anil',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,4,'User 4','Abhilash',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,5,'User 5','Rajesh',1,'F'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,6,'User 6','Usha',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,7,'User 7','Sunil',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,8,'User 8','Babu',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,9,'User 9','Hari',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,10,'User 10','Saju',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,11,'User 11','Simon',1,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,12,'User 12','Antony',2,'F'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,13,'User 13','testuser',2,'T'); insert into `users`(`Branch_ID`,`User_ID`,`UserName`,`Password`,`User_Level`,`Active`) values (1,14,'User 14','testuser2',2,'F');