VFP and Informix

[tacs]

I have recently started working with an Informix database (iSql) and need to create a new login routine from within VFP as my manager doesn't want to use ODBC which it is right now. Everything I have tried so far comes back with a Windows screen 'Select Data Source' and if I choose the data source it connects.
I have tried this (with an empty DSNname):
dsn = DSNname;Driver = {Informix 3.3 32 BIT};Host = 99.999.999.99;Server = myserver;Service = myservice;Protocol=olsotcp;Database = dbname;Uid = myuser;Pwd = mypassword

this:
Provider=Ifxoledbc;Data Source=dbname@host;User ID=myuser;Pwd=mypassword;Persist Security Info=true;
In this one the host was a name not an IP address

Both of these result in seeing the select data source screen.
The only way to connect is to use the first option and use the dsn name or to use an even simpler piece of text
dsn=dsnname;uid=myuser;pwd=mypassword;database=dbname

A connection can be made in c# without using dsn, but I need to do this in VFP9. Any suggestions gratefully received

 

[Olaf Doschke]

A DSNless connectionstring still means using an ODBC driver, unless it's the connection string for an ADODB.Connection or you use a cursoradapter with a datasourcetype of "ADO". SQL Passthrough functions only use ODBC drivers. I'd say your manager wants to avoid needing to set a DSN in the odbc manager and rather likes a DSNless connection.

With dsn=DSNname you say you want to use a DSN named DSNname, even if you set a variable DSNname=''.

http://www.connectionstrings.com/informix/

says you should specify DSN='' within the connectionstring, so literally do that or try to drop the DSN option overall.

Bye, Olaf.

 

[tacs]

Thanks for this Olaf
The DSN name used in the string is actually that set by whom ever set up the database x years ago (not actually DSName). I have tried using DSN='' and it always takes me to a Windows screen 'Select Data Source' which has 2 tabs, the second of which shows the Data Source Name list that can be selected and, if I do select, it connects, if I don't it fails. Thanks for the link

 

[Olaf Doschke]

Still, we don't know what code you're executing. It's quite impossible to help without that.

The prompt you see only appears if connection info is insufficient and a connection is refused. To see more details use AERROR().
Besides that you can do SqlSetProp(0,"DispLogin",3) to not get the dialog, but then you get a) -1 as indication of an error and b) need to look into the array AERROR(laError) generates. Most likely something is missing in this connection string.

Bye, Olaf.

 

[tacs]

Obviously I can't use the actual data for some of the lines, but this is the format used and non sensitive information. 'dsnname', is not the value used in the actual string.

This works
cSqlStr = cSqlStr + "dsn = dsnname"+;
";Driver = {Informix 3.3 32 BIT}"+;
";Host = hostname"+;
";Server = servername"+;
";Service = 1526")+;
";Protocol= olsotcp"+;
";Database = databasename"+;
";Uid = username"+;
";Pwd = xxxxxxx"

nRetVal = SQLCONNECT(cSqlStr)

But these fail
cSqlStr = cSqlStr "Driver = {Informix 3.3 32 BIT}"+;
";Host = hostname"+;
";Server = servername"+;
";Service = 1526")+;
";Protocol= olsotcp"+;
";Database = databasename"+;
";Uid = username"+;
";Pwd = xxxxxxx"

nRetVal = SQLCONNECT(cSqlStr)

Also
cSqlStr = cSqlStr + "dsn = ''"+;
";Driver = {Informix 3.3 32 BIT}"+;
";Host = hostname"+;
";Server = servername"+;
";Service = 1526")+;
";Protocol= olsotcp"+;
";Database = databasename"+;
";Uid = username"+;
";Pwd = xxxxxxx"

nRetVal = SQLCONNECT(cSqlStr)

Also

cSqlStr = cSqlstr + "Provider=Ifxoledbc;"
cSqlStr = cSqlstr + "Data Source="+alltrim(databasename)+"@"+alltrim(servername)+";"
cSqlStr = cSqlstr + "User ID=username;"
cSqlStr = cSqlstr + "Password=xxxxxxx;"
cSqlStr = cSqlstr + "Persist Security Info=true;"

nRetVal = SQLCONNECT(cSqlStr)

The result is seeing the same screen I mention earlier asking me to select the DSN. If I don't it fails. If I select the one used in the top example it connects. I have also tried changing the service to various other options, but always with the same result.

Thanks again

 

[Olaf Doschke]

Ah, there you are. Look into the help topic of SQLConnect. Besides the variants it has. it's taking your connection string mainly as a DSN and only a DSN. Since you're not giving that, you get a prompt for a DSN.
All your trial connection strings are determined to be used with [tt]SQLSTRINGCONNECT()[/tt] instead.

And you still haven't comprehended what I said about the types of drivers: You can't use a provider with SQL Passthrough, a provider needs to be used by an ADODB.Connection and/or a cursor with ADO as Datasourcetype.

Bye, Olaf.