Hi,
I'm having serious trouble getting prepared statements with bound parameters
in the where clause to work over the C API with MySQL 4.1.3. The Bugtracker on
mysql.com has similar bugs for 4.1.2, which are marked as closed and fixed in
4.1.3 so I wanted to make sure that I'm doing things correctly in my code
before fileing an official bug report. Operating system is Linux 2.6.
The query is declared as
"SELECT UserID FROM users WHERE Login=? AND Password=?".
I should be getting exactly one row back from mysql, but mysql_stmt_fetch()
just returns MYSQL_NO_DATA.
The same prepared query but with explicitly hardcoded parameters works.
Thanks for any help.
The table in question looks like the following:
mysql> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`UserID` bigint(20) unsigned NOT NULL auto_increment,
`Login` char(64) NOT NULL default '',
`Password` char(128) NOT NULL default '',
PRIMARY KEY (`UserID`,`Login`,`Password`),
UNIQUE KEY `Login` (`Login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And the code looks like this (altered from the example in section 21.2.7.5):
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <mysql/mysql.h>
#define STRING_SIZE 50
#define SELECT_SAMPLE "SELECT UserID, Login, Password FROM users WHERE Login=? AND
Password=?"
int main(void)
{
MYSQL *mysql;
MYSQL_STMT *stmt;
MYSQL_BIND bind[3]; /* results */
unsigned long length[3];
int row_count;
long long int int_data;
char login[STRING_SIZE];
char password[STRING_SIZE];
my_bool is_null[3];
MYSQL_BIND pbind[2]; /* parameters */
unsigned long plength[2];
char *pdata[2];
my_bool p_is_null[2];
mysql = mysql_init(NULL);
mysql_real_connect( mysql, "localhost", "myuser", "mypassword",
"mydb", 0, "/tmp/mysql-4.1.sock", 0 );
/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, " prepare, SELECT successful\n");
plength[0] = plength[1] = STRING_SIZE * sizeof(char);
p_is_null[0] = p_is_null[1] = 0;
pdata[0] = (char*)malloc( STRING_SIZE * sizeof(char) );
pdata[1] = (char*)malloc( STRING_SIZE * sizeof(char) );
/* STRING PARAMETER */
pbind[0].buffer_type= MYSQL_TYPE_STRING;
pbind[0].buffer= (char *)pdata[0];
pbind[0].buffer_length=STRING_SIZE * sizeof(char);
pbind[0].is_null= &p_is_null[0];
pbind[0].length= &plength[0];
/* STRING PARAMETER */
pbind[1].buffer_type= MYSQL_TYPE_STRING;
pbind[1].buffer= (char *)pdata[1];
pbind[1].buffer_length= STRING_SIZE * sizeof(char);
pbind[1].is_null= &p_is_null[1];
pbind[1].length= &plength[1];
if( mysql_stmt_bind_param( stmt, pbind ) ) {
fprintf( stderr, " mysql_stmt_bind_param() failed\n" );
fprintf( stderr, " %s\n", mysql_stmt_error( stmt ) );
exit(0);
}
mysql_real_escape_string( mysql, pdata[0], "testuser1",
strlen("testuser1")*sizeof(char) );
plength[0] = strlen( pdata[0] ) + 1;
mysql_real_escape_string( mysql, pdata[1], "bla", strlen("bla")*sizeof(char) );
plength[1] = strlen( pdata[1] ) + 1;
printf( "Executing query with parameters %s and %s\n", pdata[0], pdata[1] );
/* Execute the SELECT query */
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute(), failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Bind the result buffers for all 4 columns before fetching them */
/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONGLONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= &is_null[0];
bind[0].length= &length[0];
/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)login;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= &is_null[1];
bind[1].length= &length[1];
/* STRING COLUMN */
bind[2].buffer_type= MYSQL_TYPE_STRING;
bind[2].buffer= (char *)password;
bind[2].buffer_length= STRING_SIZE;
bind[2].is_null= &is_null[2];
bind[2].length= &length[2];
/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, " mysql_stmt_store_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Fetch all rows */
row_count= 0;
fprintf(stdout, "Fetching results ...\n");
while (!mysql_stmt_fetch(stmt))
{
row_count++;
fprintf(stdout, " row %d\n", row_count);
/* column 1 */
fprintf(stdout, " column1 (bigint) : ");
if (is_null[0])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %lld(%ld)\n", int_data, length[0]);
/* column 2 */
fprintf(stdout, " column2 (string) : ");
if (is_null[1])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %s(%ld)\n", login, length[1]);
/* column 3 */
fprintf(stdout, " column3 (string) : ");
if (is_null[2])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %s(%ld)\n", password, length[2]);
}
/* Validate rows fetched */
fprintf(stdout, " total rows fetched: %d\n", row_count);
/* Close the statement */
if (mysql_stmt_close(stmt))
{
fprintf(stderr, " failed while closing the statement\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
return 0;
}
--
---------------------Trigital-
Sven Riedel
. Tel: +49 511 1236364
. Fax: +49 511 1690746
. email: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
