connection with ODBC string

[hstijnen]

Hi,
I'm trying to connect to a db on an external computer and have only an ODBC connection string:
"ODBC;DSN=ExtDataSource;DRIVER=;DBQ=.;UID=usr;PWD=pwd1;DOMAIN=;RemoteHost=;RedundantCPU=N;RedundantLAN=N"

The DSN is defined on the external computer as System DSN.

Now I've created a ZEOS connection component and set
Protocol = "ado"
Database = myConnectionString

When connecting to the db I get the error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

What to do?

Thanks for help!

[marsupilami]

Hello hstijnen,

you need to define the DSN on the computer where the program is running. The DSN in turn has to refer to the external computer where tha database server is running.
Best regards,

Jan

[hstijnen]

marsupilami,
my program has to run on an external computer from my customer. And that computer has the DSN defined and is connected with the db server.
A former version of the program runs correctly, but is using another set of db components viz. KaDao. Now I want to replace that components with ZEOS.

The KaDao code is as follows:

Code: Select all

    this->Connection = new TKADaoDatabase(Application);
    this->Connection->ReadOnly = true;
    this->Connection->LoginPrompt = false;
    this->Connection->DatabaseType = "ODBC";
    this->Connection->EngineType = 1;
    DBsettings = "ODBC;DSN=ExtDataSource;DRIVER=;DBQ=.;UID=usr;PWD=pwd1;DOMAIN=;RemoteHost=;RedundantCPU=N;RedundantLAN=N";
    this->Connection->Database = DBsettings;
    this->Connection->Connected = true;

up to now I've made the following code for ZEOS:

Code: Select all

    this->Connection = new TZConnection(Application);
    this->Connection->Protocol = "ado";
    this->Connection->ReadOnly = true;
    this->Connection->LoginPrompt = false;
    this->Connection->Database = DBsettings; // same string as KaDao
    this->Connection->Connect();

But that don't yet work. Are there other settings? Is "ado" the correct choice? Has the string DBsettings the correct format for ZEOS? (e.g. starting with "ODBC" in reference to "ado")

Thanks for help

Henk

[marsupilami]

Hello hstijnen,

when I try to create an ODBC connection string for ADO it always looks somewhat like this:
Provider=MSDASQL.1;Persist Security Info=False;User ID=sysdba;Data Source=Test

So I suggest for you to set the following:
DBsettings = "Provider=MSDASQL.1;Persist Security Info=False;User ID=usr;Data Source=ExtDataSource";
this->Connection->Database = DBsettings;
this->Connection->User = "usr";
this->Connection->Password = "pwd1";

One more suggestion: from this thread it seems like you are trying to connect to an Microsoft SQL Server. There are ADO drivers in the installation package of SQL Server, so you could skip ODBC and use the ADO drivers directly...

Then the connection string could look somewhat like this:
Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=PHANTOMIAS\SQLEXPRESS;Initial File Name="";Server SPN=""
This is for SQL Server native client 10.1

Best regards,

Jan

[hstijnen]

Thanks for your suggestion:
with "Provider=MSDASQL.1;Persist Security Info=False;Data Source=EBIDataSource" I am able to open the connection (connected = true).

But when I open a query on that connection there comes no reaction (program hangs)
Code:
Zqry = new TZQuery(Application);
Zqry->Connection = connection;
Zqry->SQL->Text = "select aField from aTable"
Zqry->Open(); //here program hangs (all was OK in former version with KaDao DB components)

Again any suggestion?

Thanks for help and kind regards,
Henk

[marsupilami]

Hello Henk,

I have no suggestion for this problem. To me it seems like something strange is going on with the ADO-ODBC-Bridge. For testing this I would need more information about your setup - Which Database do you want to connect to (MS SQL, Sybase ASE, ...), which Version of the Database do you use and which ODBC driver do you use?
Best regards,

Jan