Need help with connectiion string from VC++ to CSQL using ODBC .

Hi,
I am trying to connect my VC++ program to CSQL running on VMware Ubuntu  on the same computer.

I am using the C program from the example given by CSQL (attached).
How do I write the connection string when I´m trying to connect to another IP?
Assuming the CSQL IP machine is 134.23.14.1 and the Port is 5678 What do I need to change?

ret = SQLConnect (dbc,
                   (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
                   (SQLCHAR *) "root",
                   (SQLSMALLINT) strlen ("root"),
                   (SQLCHAR *) "manager",
                   (SQLSMALLINT) strlen (""));


There is also a link in Wiki but I did not know how to change the string.
http://wiki.csqldb.com/index.php/ProgrammerGuide/ODBCAndCSQL

Thanks
/**************************************************************************
 *   Copyright (C) 2008 by www.databasecache.com                          *
 *   Contact : praba_tuty@databasecache.com				  *
 *                                                                        * 
 *   THis program is free software; you can redistribute it and/or modify *
 *   it under the terms of the GNU General Public License as Published by * 
 *   the Fre Software Foundation;either version 2 of the License, or      *
 *   (at your option)any later version.                                   *
 *                                                                        *
 *   This program is distributed in the hope that will be useful,         *
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of       *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, See the         *
 *   GNU General License for more details.                                *
 *                                                                        *
 **************************************************************************/
#include <windows.h>
#include<stdio.h>
#include<stdlib.h>
#include <sql.h>
#include<sqlext.h>
#include<string.h>

void checkrc(int rc,int line)
{
    if(rc)
    {
	printf("Error %d at line %d\n", rc, line);
	exit(1);
    }
}

int main()
{
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret;
    SQLCHAR outstr[1024];
    SQLSMALLINT outstrlen;

    //Allocate an environment handle
    ret = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
    checkrc(ret,__LINE__);

    //we need ODBC3 support
    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
	
    //Allocate a Connection handle
    ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
    checkrc(ret,__LINE__);


    //connect to Data source

	
	ret = SQLConnect (dbc,
                   (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
                   (SQLCHAR *) "root",
                   (SQLSMALLINT) strlen ("root"),
                   (SQLCHAR *) "manager",
                   (SQLSMALLINT) strlen (""));



	/*
    //connect using unixODBC Driver Manager
    ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*) 
                     "DSN=mycsql;USER=root;PASSWORD=manager;", SQL_NTS, 
                      outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
*/
    
	
	
	if(SQL_SUCCEEDED(ret))
    { 
        printf("Connected to CSQL\n");
    }
    else
    {
        printf("error in connection\n");
        ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
        checkrc(ret,__LINE__);
        ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
        checkrc(ret,__LINE__);
        return 2;
    }

    // Allocation of  statement handle for DDL nad DML Operation
    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
    checkrc(ret,__LINE__);
 
    // create table 'T1' with two fields, F1 INTEGER AND F2 CHAR.
    SQLCHAR table[200]= "CREATE TABLE T1(F1 INT,F2 CHAR(20))";
   
    ret = SQLPrepare(stmt,table,SQL_NTS);
    checkrc(ret,__LINE__);
   	
    ret = SQLExecute(stmt);
    checkrc(ret,__LINE__);
   	
    printf("Table T1 created\n");

    //Insert 10 Tuples into the table 'T1'
    int id=10;
    char name[20]="THIRU";
    char names[10][20]={"Gopal", "Aruna", "Kanchana", "Vijay", "Ganga",
                       "XieLiang", "Rajesh", "Steve", "Veda", "Jitendra" };


    SQLINTEGER slen = SQL_NTS;
	
    ret  = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?);",SQL_NTS);
    checkrc(ret,__LINE__);

    ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id,0,NULL);
    checkrc(ret,__LINE__);

    ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)name,0,&slen);
    checkrc(ret,__LINE__);

    int i,count=0;
    for(i=0;i<10;i++)
    {
        id++;
        strcpy(name,names[i]);
    
        ret = SQLExecute(stmt);
        checkrc(ret,__LINE__);

        ret = SQLTransact(env,dbc,SQL_COMMIT);
        checkrc(ret,__LINE__);
        count++;
    }
    printf("%d Rows inserted\n",count);

	
    //Fetch rows from the table 'T1'
    int id1=10;

    ret = SQLPrepare(stmt,(unsigned char*)"SELECT * FROM T1;",SQL_NTS);
    checkrc(ret,__LINE__);

    ret = SQLBindCol(stmt,1,SQL_C_SLONG,&id1,0,NULL);
    checkrc(ret,__LINE__);
 	
    ret = SQLBindCol(stmt,2,SQL_C_CHAR,name,sizeof(name),NULL);
    checkrc(ret,__LINE__);
	
    count=0;
    ret = SQLExecute(stmt);
    checkrc(ret,__LINE__);
	
    printf("Fetching starts on table T1 :\n");
    while(SQL_SUCCEEDED(ret=SQLFetch(stmt)))
    {
        printf("F1:%d\tF2:%s\n",id1,name);
        count++;
    }
    ret = SQLCloseCursor(stmt);
    checkrc(ret,__LINE__);

    ret = SQLTransact(env,dbc,SQL_COMMIT);
    checkrc(ret,__LINE__);

    printf("%d rows fetched\n",count);

    //Delete all the rows from the table 'T1'
    ret = SQLPrepare(stmt,(unsigned char*)"DELETE FROM T1 WHERE F1=?;",SQL_NTS);
    checkrc(ret,__LINE__);

    ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id1,0,NULL);
    checkrc(ret,__LINE__);


    count=0;
    for(i=0;i<10;i++)
    {
       id++;
       ret = SQLExecute(stmt);
       checkrc(ret,__LINE__);
       count++;
    }

    ret = SQLTransact(env,dbc,SQL_COMMIT);
    checkrc(ret,__LINE__);
    printf("%d Rows deleted\n",count);

	
    // drop the table 'T1'
    SQLCHAR drop[50]="DROP TABLE T1";
    ret = SQLPrepare(stmt,drop,SQL_NTS);
    checkrc(ret,__LINE__);

    ret = SQLExecute(stmt);
    checkrc(ret,__LINE__);
    printf("Table T1 dropped\n");

    //Free the statement handle
    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
    checkrc(ret,__LINE__);

    //Disconnect from the Data source
    ret = SQLDisconnect(dbc);
    checkrc(ret,__LINE__);
    printf("Disconnected from CSQL\n");
   	
    //Free the connection handle
    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
    checkrc(ret,__LINE__);

    //Free the environment handle
    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
    checkrc(ret,__LINE__);
    return 0;
}

Open in new window

star90Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sarabandeCommented:
you could try driver connect with connection string

Server=134.23.14.1;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Sara
0
star90Author Commented:
Hi sarabande,
I don´t have a driver for CSQL for ODBC.
(I know for example how to connect to MYSQL with the ODBC driver for vc++.)
0
sarabandeCommented:
with 'driver connect' i mean the call SQLDriverConnect where you can pass a connection string. your code now has both a call SQLConnect and SQLDriverConnect where you should comment the first one.

Sara
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sarabandeCommented:
note, the datasource 'mycsql' must be defined with the odbc applet in the system settings. alternatively you can start it with odbcad32.exe from commandline or 'Run'.

instead of DSN= you could do DRIVER= where you would need choose the (exact) name of the mysql driver as it was shown in the odbcad32.

Sara
0
Todd GerbertIT ConsultantCommented:
@sarabande
I think one of those function calls, SQLDriverConnect, is already commented - that's what the /* ... */ means. ;)

@star90
Well, that documentation you linked to doesn't really seem to explain the parameters very well, but (keep in mind I'm not familiar with CSQL) I'd guess the first one is the computer and port, so perhaps:
ret = SQLConnect (dbc,
                   (SQLCHAR *) "134.23.14.1:5678",  // Server address
                   (SQLSMALLINT) strlen ("134.23.14.1:5678"),
                   (SQLCHAR *) "root",  // User name
                   (SQLSMALLINT) strlen ("root"),
                   (SQLCHAR *) "manager",  // Password
                   (SQLSMALLINT) strlen (""));  // Not sure why this is empty in the example, maybe should be strlen("manager")?

Open in new window

0
Todd GerbertIT ConsultantCommented:
I figured you meant to comment the other one instead, I just couldn't resist poking a little fun. >:)
0
Todd GerbertIT ConsultantCommented:
This seems to have much better information than the wiki: http://www.csqldb.com/download/ProgrammerGuide.pdf
0
star90Author Commented:
Hi
Thanks for the answers.

1) Sarabande,
I don´t have any drivers for the CSQL, they only supply the C file that I attached.
Do you mean to do something like this?

http://www.developer.com/db/article.php/3712461/Installing-a-Custom-ODBC-Driver-on-Windows-for-SQL-Anywhere.htm

I don´t have any dll files for the CSQL ODBC.

2)  tgerbert & Sarabande
I tried the suggested string and I get the printf("error in connection\n");
with ret=0
I used the string below:
ret = SQLConnect (dbc,
                   (SQLCHAR *) "152.168.18.139:5678",  // Server address
                   (SQLSMALLINT) strlen ("152.168.18.139:5678"),
                   (SQLCHAR *) "root",  // User name
                   (SQLSMALLINT) strlen ("root"),
                   (SQLCHAR *) "manager",  // Password
                   (SQLSMALLINT) strlen ("manager"));  //

I gor the IP address from the Ubuntu by typing "ifconfig" and copying the inet addr
as the IP.
There are other "similar numbers" that appear in the Vmware Virtual network editor.
Any suggestions how to investigate the reason for the connection error?
The CSQL is alive and running on the Ubuntu machine.

0
star90Author Commented:
Thanks tgerbert,
I read this manual but did not find anything about the connection string.
I ran the example (that comes with the installation) from the  CSQL on the linux (on the same machine that CSQL is running)
and its running great but it run with the  CSQLConnect on the same Ubuntu  machine.

ret = SQLConnect (dbc,
                   (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
                   (SQLCHAR *) "root",
                   (SQLSMALLINT) strlen ("root"),
                   (SQLCHAR *) "manager",
                   (SQLSMALLINT) strlen (""));

0
sarabandeCommented:
the SQLDriverConnect is supposed to connect via connection string passing datasource info directly to the odbc driver (which must have been installled at windows for each dbms).

the SQLConnect does connection to a data source name where the datasource was defined with the odbc administrator in the control panel and where all info already was stored with that name (the info includes what ODBC driver to use and which database to connect to.).

Sara

0
sarabandeCommented:
star90, did you add a datasource for the csql at your windows machine? did you install an odbc driver for mysql at the windows machine?

Sara
0
star90Author Commented:
sarabande,
For MYSQL there is an ODBC connector for example that I can install.
Please explain how to do it with the CSQL.
I don´t have other files except for the C file that you see above.
How can I add the CSQL ODBC driver to  odbcad32 if I don´t have any dll files?
0
star90Author Commented:
I don´t know how to add a Datasource for the  CSQL.
ODBC driver is installed for MYSQL (MYSQL not CSQL)
0
Todd GerbertIT ConsultantCommented:
>> SQLConnect does connection to a data source name where the datasource was defined with the odbc administrator
No, SQLConnect() is for connecting without ODBC.

>> SQLDriverConnect is supposed to connect via connection string passing datasource info directly to the odbc driver (which must have been installled at windows for each dbms).
Uhh, kind of, except that it's intended purpose is for use with unixODBC, not Windows.

@star90
There don't seem to be any readily available drivers for use with Windows clients, and (as you seem to have discovered) the documentation doesn't seem to indicate how, or if, you can explicitly specify a server name or port number using SQLConnect().
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Todd GerbertIT ConsultantCommented:
>> How can I add the CSQL ODBC driver to  odbcad32 if I don´t have any dll files?
You can't. Perhaps a different database server might better suit your needs (if that's an option).
0
Todd GerbertIT ConsultantCommented:
>> No, SQLConnect() is for connecting without ODBC.
Perhaps I jumped the gun, according to the documentation you can pass an ODBC-style connection string as the second parameter SQLConnect() - though that'd still be a unixODBC thing, and not Windows.

Poking around on Google a bit and I'm not convinced it's possible to connect to a CSQL database from Windows, unless you care to write your own ODBC driver (that doesn't mean it's truly impossible, I just couldn't find any references to or examples of such).
0
star90Author Commented:
Hi tgerbert,
Thanks for the answer.

Do you know any In memory databases that can work with VC++
(this was the reason I installed the CSQL on a Vmware machine)
0
Todd GerbertIT ConsultantCommented:
There's a list on the Wikipedia page: http://en.wikipedia.org/wiki/In-memory_database#Products

I don't know off the top of my head which ones support Windows clients, but I would guess that products like Oracle, MySQl and SQLite  will all have some support for Windows-based clients.
0
sarabandeCommented:
tgerbert, all SQL calls are ODBC. i made since 1994 i really know.

Sara
0
Todd GerbertIT ConsultantCommented:
Maybe something's getting lost in translation...ODBC is a system that acts as a middle-man between the client application and the database driver. It allows the client to connect to well-known interfaces in the ODBC driver, which in turn communicates with the DBMS in it's native format.  The ODBC driver, by definition, must be able to communicate with it's database without ODBC - so what makes you think it's not possible a database system could have published an API allowing client applications to work drirectly with the database - just as the driver does, thereby bypassing ODBC?

ODBC wasn't developed until 1992. SQL databases date back to 1970. I can assure you that for those 22 years people were able to access their databases without the aid of ODBC. I'm also certain some database vendors (Oracle, Informix, Sybase, DB2, SAP, MySQL) might still provide proprietary interfaces that do not conform to nor rely upon ODBC. Apache Derby is just one that definitely provides non-ODBC interfaces.  From http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ah35004_.htm: Derby is a native Java™ database engine without ODBC support.

Maybe you meant Microsoft's SQL Server under the Windows platform, but this question pertains to a non-Microsoft database running on a non-Windows platform, plus I would dispute such a statement anyway - for example the Microsoft .Net System.Data.SqlClient namespace provides a non-ODBC method to access SQL Server. From http://msdn.microsoft.com/en-us/library/a6cd7c08(v=vs.71).aspx: The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer.

So no, all SQL calls are most definitely not ODBC.
0
star90Author Commented:


Perhaps I jumped the gun, according to the documentation you can pass an ODBC-style connection string as the second parameter SQLConnect() - though that'd still be a unixODBC thing, and not Windows.

How would you write it in the SQLConnec?
Assuming its IP=152.168.18.139:5678
Thanks

ret = SQLConnect (dbc,
                   (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
                   (SQLCHAR *) "root",
                   (SQLSMALLINT) strlen ("root"),
                   (SQLCHAR *) "manager",
                   (SQLSMALLINT) strlen (""));
0
Todd GerbertIT ConsultantCommented:
The documentation for this product is lacking, to say the least (and that's being generous), so I'm not sure. I'm not in front of a computer right now so I can't check, but I'll look later if you haven't already found it.

I suspect, however, this DB might require unixODBC, which - to my knowledge - is not available for Windows, which means you're still out of luck with a Windows client.
0
sarabandeCommented:
tgerbert, what i mean with SQL functions are the functions declared in sql.h and sqlext.h which all belong to odbc. i don't mean sql commands or native api to rdbms.

the SQLConnect and SQLDriverConnect are both ODBC functions. the difference is that with the latter you can do a connect via an odbc driver directly while for the first you need a datasource defined with the odbc administrator (odbcad32.exe) and stored in windows registry.

note odbc is also on unix platforms. the ibm db2 database uses odbc as api for their dbms. the odbc on unix and windows are compatible (odbc has an own standard independent of os) but could use different header files.

star90, any access to a rdbms via odbc needs an odbc driver specially written for the os and the dbms. it must be installed prior to connect and it would be then in the list of odbc drivers you can choose when calling the odbc administrator.

for getting an odbc driver for csql you might check the following site:

http://wiki.csqldb.com/index.php/ProgrammerGuide/ODBCAndCSQL 

Sara
0
Todd GerbertIT ConsultantCommented:
>> i mean with SQL functions are the functions declared in sql.h and sqlext.h which all belong to odbc
To say that these two functions are both ODBC is a world of difference from saying that all SQL calls are ODBC. You'll see that I had already noticed that they were both ODBC anyway (http:#a35326054).


>> note odbc is also on unix platforms
Yes, we know. There were already a few references to unixODBC in this thread.


>> for getting an odbc driver for csql you might check the following site
The site you suggested is just a wiki (kind of) with only a handful of pages of documentation, and not a single download among them. The better place to check is http://www.csqldb.com/, which I have done and there is no mention of Windows ODBC drivers. Google doesn't turn up anything either.

Their website does indicate, though, that a port to windows is slated for a future release, so presumably a Windows ODBC driver is forthcoming. For the moment, however, there doesn't appear to be any support for Windows-based clients - unless, of course, you care to attempt to port the unixODBC driver yourself.
0
sarabandeCommented:
sorry, but that was the two functions i had explained before and where you said the first one is not odbc. i think it is not good for the questioner when we were discussing off-topic and i am sorry when i tried to make it (too) short because of that ...

the site i suggested described odbc access to csql. such a site wouldn't make any sense if there is no odbc driver available. as it is open source the odbc driver was free also, i hadn't time to read all details.

when you try to connect from vc (windows!) to a csql (server on arbitrary os) you need a windows odbc driver which hardly can be derived from unixOdbc.

Sara
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual C++.NET

From novice to tech pro — start learning today.