| View previous topic :: View next topic |
| Author |
Message |
Vieri
l33t
Joined: 18 Dec 2005
Posts: 901
|
 Posted: Tue Sep 17, 2024 12:21 pm Post subject: simple C program to get SQL data via unixODBC |
 |
|
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 |
|
 |
lars_the_bear
Guru
Joined: 05 Jun 2024
Posts: 517
|
| Posted: Tue Sep 17, 2024 12:47 pm Post subject: |
|
|
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 |
|
|
Vieri
l33t
Joined: 18 Dec 2005
Posts: 901
|
| Posted: Tue Sep 17, 2024 2:06 pm Post subject: |
|
|
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 |
|
|
Genone
Retired Dev
Joined: 14 Mar 2003
Posts: 9607
Location: beyond the rim
|
| Posted: Tue Sep 17, 2024 7:56 pm Post subject: |
|
|
| 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 |
|
|
lars_the_bear
Guru
Joined: 05 Jun 2024
Posts: 517
|
|
| Back to top |
|
|
Vieri
l33t
Joined: 18 Dec 2005
Posts: 901
|
| Posted: Wed Sep 18, 2024 10:16 am Post subject: |
|
|
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 |
|
|
lars_the_bear
Guru
Joined: 05 Jun 2024
Posts: 517
|
| Posted: Wed Sep 18, 2024 11:08 am Post subject: |
|
|
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 |
|
|
Vieri
l33t
Joined: 18 Dec 2005
Posts: 901
|
| Posted: Thu Oct 31, 2024 12:33 pm Post subject: |
|
|
| 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 |
|
|
|
|
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
|
|
Portage & Programming
