Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
simple C program to get SQL data via unixODBC
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Portage & Programming
View previous topic :: View next topic  
Author Message
Vieri
l33t
l33t


Joined: 18 Dec 2005
Posts: 901

PostPosted: Tue Sep 17, 2024 12:21 pm    Post subject: simple C program to get SQL data via unixODBC Reply with quote

Hi,

I'm testing the following C code to try to get data via ODBC.
'col1' is varchar and I'm expecting to print out an ASCII string.
Instead, I'm seeing weird chars on the console.


Code:
SQLHDBC con;
SQLHSTMT rs;
SQLRETURN ret;

SQLCHAR mac[50];
SQLLEN cbMac;

SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
SQLHENV env;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &con);

ret = SQLDriverConnect(con, NULL, "DSN=sqlserver;UID=myuser;PWD=mypwd;", SQL_NTS,
                         outstr, sizeof(outstr), &outstrlen,
                         SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) {
        printf("Connected\n");
} else {
        printf("Failed to connect\n");
}

char sql[1024];
strncpy(sql, "select col1 FROM mytable WHERE col2 LIKE '%%2100%%'", sizeof(sql) - 1);

ret = SQLExecDirect(rs, (SQLCHAR *)sql, SQL_NTS);
if (ret == SQL_ERROR) {
        printf("Failed to SQL\n");
}

SQLINTEGER results;
ret = SQLFetch(rs);
if (ret == SQL_NO_DATA) {
        printf("NO SQL DATA\n");
}

// SQLGetData(rs, 1, SQL_C_LONG, &results, 0, NULL);
SQLGetData(rs, 1, SQL_C_CHAR, mac, 50, &cbMac);

printf("results:%lu\n", cbMac);
printf("mac:%s\n", mac);



This is the output:

Code:
Connected
results:140630481905780
mac:▒▒▒▒


(never mind the var names)

What am I missing here?

Thanks!
Back to top
View user's profile Send private message
lars_the_bear
Guru
Guru


Joined: 05 Jun 2024
Posts: 512

PostPosted: Tue Sep 17, 2024 12:47 pm    Post subject: Reply with quote

Did you check the return value from SQLGetData(), to see if it actually succeeded? I'm wondering if the weird value you get for cbMac is just some rubbish that was hanging about on the stack, and the function call didn't actually do anything?

Having said that, I can't see why it wouldn't do anything if you've gotten to the point you have; unless, perhaps, the column type is wrong.

BR, Lars.
Back to top
View user's profile Send private message
Vieri
l33t
l33t


Joined: 18 Dec 2005
Posts: 901

PostPosted: Tue Sep 17, 2024 2:06 pm    Post subject: Reply with quote

Thanks, Lars.
The return value from SQLGetData() is SQL_INVALID_HANDLE, but I still don't see why. I'm using SQLHSTMT rs for the handle.
I'm supposing that if the column type were wrong the return value would be different.
I'll keep looking into this.
Thanks
Back to top
View user's profile Send private message
Genone
Retired Dev
Retired Dev


Joined: 14 Mar 2003
Posts: 9604
Location: beyond the rim

PostPosted: Tue Sep 17, 2024 7:56 pm    Post subject: Reply with quote

Not familiar with that library, but something there seems amiss: you setup your "con" and "env" handles initially, but then the functions to actually do stuff get the "rs" argument which doesn't seem to be initialized or associated with anything at any point. I assume the SQLExecDirect call is already failing with the same "invalid handle"error for that reason. In general with error checking you should check if the return value is an "OK" value (like you do with SQLDriverConnect) instead of checking for a specific error code.
Back to top
View user's profile Send private message
lars_the_bear
Guru
Guru


Joined: 05 Jun 2024
Posts: 512

PostPosted: Wed Sep 18, 2024 7:00 am    Post subject: Reply with quote

Hi

Looking at this example:

https://learn.microsoft.com/en-us/sql/connect/odbc/cpp-code-example-app-connect-access-sql-db?view=sql-server-ver16

the handle argument to SQLExecDirect() is another thing that should be initialized by SQLAllocHandle().

BR, Lars.
Back to top
View user's profile Send private message
Vieri
l33t
l33t


Joined: 18 Dec 2005
Posts: 901

PostPosted: Wed Sep 18, 2024 10:16 am    Post subject: Reply with quote

Thank you very much for the pointers. I rewrote the code so it would check SQLExecDirect like so:

Code:
    ret = SQLExecDirect(rs, (SQLCHAR *)sql, SQL_NTS);
    if (SQL_SUCCEEDED(ret)) {
        printf("Could ExecDirect %d\n", ret);
    } else {
        fprintf(stderr, "Failed to SQL: %s\n", sql);
    }


The previous SQL call clearly connects, but this one fails with:

Code:
Failed to SQL: select col1 from mytable


I'm sure about the SQL statement because I've tested it (with a PHP script for instance).

I also tried adding

Code:
SQLAllocHandle(SQL_HANDLE_STMT, con, &rs);


but I'm getting the same result with SQLExecDirect.

So I'll have to study that link (thanks Lars), or drop C in favor of Rust.

Thanks
Back to top
View user's profile Send private message
lars_the_bear
Guru
Guru


Joined: 05 Jun 2024
Posts: 512

PostPosted: Wed Sep 18, 2024 11:08 am    Post subject: Reply with quote

Hi

I don't think it's the language, but the ancient ODBC API. I think it comes from the Win16 days. All those upper-case named types are just integers, as I recall, so the compiler won't tell you if you're using them wrongly. It's a lot easier to use SQlServer in Java but, of course, you've got to love Java ;)

BR, Lars.
Back to top
View user's profile Send private message
Vieri
l33t
l33t


Joined: 18 Dec 2005
Posts: 901

PostPosted: Thu Oct 31, 2024 12:33 pm    Post subject: Reply with quote

Of all the compilable (non-interpreter) programming languages I've tried, Golang was the easiest way to do what I posted here.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Portage & Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum