how to get sql select statement to work with variable

[Spikeman]

Hello,

I'm trying to retrieve data from a table using a string variable "TestId".

I've tried several syntax's and cannot get it to work.

hstmt = DBActivateSQL (hdbc, "SELECT * FROM RESULTS39 WHERE TEST_ID = TestId");  gives a too few of parameters

hstmt = DBActivateSQL (hdbc, "SELECT * FROM RESULTS39 WHERE TEST_ID = '" && TestId && "'");  gives a found int expecting pointer to a char.

The later statement works in VB. I cannot find an example in any of the cvi example using a where clause with a variable. 😞

Thanks

Spike

[Mert_A.]

Hey Spike,

If I understand you correctly, TestId is a string variable like

char TestId[], or
char *TestId.

The reason the latter statement won't compile is that in C the && operator is a logical AND, not a string concatenator.  What is happening is that it is interpreting the addresses of the three strings as integers (booleans), then doing a logical AND of them.  This evaluates to another integer, which is why you're getting the "found int expecting pointer to a char" error.

The function prototype for DBActivateSQL is

int DBActivateSQL (int connectionHandle, char *SQLStatement);

so your first statement should compile.

But to address your real question, in order to use a string variable in your SQL statement, you must use a string manipulation function to plug the value in there.  That is, if the value of TestId is "1234" and you really want your query to be "SELECT * FROM RESULTS39 WHERE TEST_ID = 1234", then you must do something like the following:

/* allocate memory for your query string */
char *sqlStatement = (char*)malloc(sizeof("SELECT * FROM RESULTS39 WHERE TEST_ID = ")
                                          + sizeof(TestId)
                                          + 1);
/* copy your query into the allocated buffer */
sprintf(sqlStatement, "SELECT * FROM RESULTS39 WHERE TEST_ID = %s", TestId);

/* use your query */
hstmt = DBActivateSQL (hdbc, "SELECT * FROM RESULTS39 WHERE TEST_ID = TestId");

/* release the allocated memory */
free(sqlStatement);

If you're still getting compile errors, check your code again to make sure it is the same as what you posted and that your error is actually from that line.

Good luck.

Mert A.
National Instruments

Message Edited by Mert A. on 10-05-2005 01:23 PM

[Spikeman]

Hi Mert,

Thank you for your reply! I plugged in your code example and still get the same error of "Too few parameters. Expected 1." when it gets to the

hstmt = DBActivateSQL (hdbc, "SELECT * FROM RESULTS39 WHERE TEST_ID = TestId");  line of the code.

You are correct that I do want to do essentially want a "SELECT * FROM RESULTS39 WHERE TEST_ID = '1234'", with a variable. Th code does work when using a string but I just cannot get the syntax correct with my variable?

Thanks for your help

Mike

[Mert_A.]

Whoops.  Just to clarify, that line (that I copied and pasted then forgot to change) should have been:

hstmt = DBActivateSQL (hdbc, sqlStatement);

It's still not clear why you would be getting a compiler error about having fewer than one parameter, when there are clearly two needed and two provided.

Mert A.
National Instruments

[Spikeman]

Mert,

I figured it out...needed the single qoute around the string identifier '%s', then the hstmt = DBActivateSQL (hdbc, sqlStatement); worked!

Thank you for your help!!!