C++

Forum

 
Null result from SQL causing error

p38fln (4)
I'm not good at all with C++ - I'm trying to teach it to myself using some real life examples where I work.
So, I'm trying to make a simple console program that will pull employee passport information from a database.
I got it to the point where it will prompt the user for an employee code and return the passport number and employee name to the console - but if the employee doesn't have a passport, it gives some amazing results. I tried putting if != SQL_SUCCESS but it appears to completely ignore that.
I played a lot with different [] sizes so if anything looks crazy i just forgot to set it back the way it should be.

Here's the section where it goes nuts:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
char PassportNo[64];
	  char DriverName[64];
	  
	  char DriverType[32];
	  
	  unsigned long int DriverMiles;
	  unsigned long int DriverPhone1;
	  unsigned long int DriverPhone2;
	  unsigned long int DriverPhone3;
	  
	  
	
	  if(SQLFetch(sqlstatement)==SQL_SUCCESS){
		    
		  if(SQLGetData(sqlstatement,1,SQL_C_CHAR,&DriverName,64,0)!=SQL_SUCCESS)
			  {
				  char DriverName[12]="Not on File";
		  }
		  if( SQLGetData(sqlstatement,2,SQL_C_CHAR,&DriverType,32,0)!=SQL_SUCCESS)
		  {
			  char DriverType[6]="Error";
		  }
		  if(SQLGetData(sqlstatement,4,SQL_C_ULONG,&DriverMiles,64,0)!=SQL_SUCCESS)
		  {
			  char DriverMiles[6]="Error";
		  }
		  if(SQLGetData(sqlstatement,5,SQL_C_ULONG,&DriverPhone1,11,0)!=SQL_SUCCESS)
		  {
			  DriverPhone1=0;
		  }
		  if(SQLGetData(sqlstatement,6,SQL_C_ULONG,&DriverPhone2,11,0)!=SQL_SUCCESS)
			  {
				  DriverPhone2=0;
		  }

		  if(SQLGetData(sqlstatement,7,SQL_C_ULONG,&DriverPhone3,11,0)!=SQL_SUCCESS)
			  { 
				  DriverPhone3=0;
		  }
		  if(SQLGetData(sqlstatement,10,SQL_C_CHAR,&PassportNo,64,0)!=SQL_SUCCESS)
		  {
			 char PassportNo[64]="None";
		  }
	  }
	  gotoxy(0,1);
	  cout << "FullName: "<< DriverName;
	  gotoxy(0,2);
	  cout << "Com/IC: " << DriverType;
	  gotoxy(0,3);
	  cout << "Miles Last 30 days: "<< DriverMiles;
	  gotoxy(40,1);
	  cout << "Driver Phone 1:" << DriverPhone1;
	  gotoxy(40,2);
	  cout << "Driver Phone 2:" << DriverPhone2;
	  gotoxy(40,3);
	  cout << "Driver Phone 3:" << DriverPhone3;
	  cout << endl;
	  gotoxy(0,5);
      cout << "Passport Number:" << PassportNo << endl;


And here's what it does when I put in an employee with no passport- the same query on the database server just shows the word "NULL" in that field.
Note that if the employee did have a passport, it would properly display the passport number.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Passport Number:╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠
╠╠╠╠╠╠╠╠SELECT     CASE WHEN manpowerprofile.mpp_middlename IS NULL  THEN manpow
erprofile.mpp_firstname + ' ' + manpowerprofile.mpp_lastname ELSE manpowerprofil
e.mpp_firstname + ' ' + manpowerprofile.mpp_middlename + ' ' + manpowerprofile.m
pp_lastname END AS 'Fullname', manpowerprofile.mpp_type1, manpowerprofile.mpp_ty
pe2, SUM(stops.stp_lgh_mileage) AS lgh_miles, manpowerprofile.mpp_currentphone,
manpowerprofile.mpp_alternatephone, manpowerprofile.mpp_homephone, manpowerprofi
le.mpp_id,  passport.pass_exp, passport.pass_no, accident_tbl.TotalAcc, TicketTb
l_1.TotalTic, TicketTbl_2.TotalTic AS TotalTic1QAgo FROM       stops INNER JOIN
legheader ON stops.lgh_number = legheader.lgh_number INNER JOIN manpowerprofile
ON legheader.lgh_driver1 = manpowerprofile.mpp_id OR legheader.lgh_driver2 = man
powerprofile.mpp_id LEFT OUTER JOIN (SELECT     COUNT(*) AS TotalTic, INCIDENT_1
.inc_MppOrEeID  FROM          SAFETYREPORT AS S2 INNER JOIN
 INCIDENT AS INCIDENT_1 ON S2.srp_ID = INCIDENT_1.srp_ID        WHERE       (S2.
srp_SafetyType1 <> 'Yes')  GROUP BY INCIDENT_1.inc_MppOrEeID) AS TicketTbl_2 ON
manpowerprofile.mpp_id = TicketTbl_2.inc_MppOrEeID LEFT OUTER JOIN     (SELECT
   COUNT(*) AS TotalTic, INCIDENT.inc_MppOrEeID    FROM          SAFETYREPORT AS
 S2 INNER JOIN     INCIDENT ON S2.srp_ID = INCIDENT.srp_ID  WHERE       (S2.srp_
SafetyType1 <> 'Yes')  GROUP BY INCIDENT.inc_MppOrEeID) AS TicketTbl_1 ON manpow
erprofile.mpp_id = TicketTbl_1.inc_MppOrEeID LEFT OUTER JOIN      (SELECT     CO


The other crazy thing - the SQL_SUCCESS check works fine on the phone numbers, and properly sets them to zero if there isn't a phone number.
Last edited on
ne555 (10692)
Your problem is scope. 
1
2
3
4
 
		  if( SQLGetData(sqlstatement,2,SQL_C_CHAR,&DriverType,32,0)!=SQL_SUCCESS)
		  {
			  char DriverType[6]="Error";
		  }
You've got two variables that are named DriverType. The one that it's assigned "Error" only lives inside that if block.
So you are not assigned that to the one that you are printing.
Because you don't initialize them, they hold garbage.
kbw (9488)
You don't seem to check for NULL values coming back from the database.

I think there's an example here:
http://techmythoughts.blogspot.co.uk/2009/01/how-to-fetch-null-data-into-c-odbc.html
Last edited on
p38fln (4)
Thanks for the quick replies - I don't know how to check for NULLs coming back, so I changed the SQL query to send something else instead -
It got the job done, in any case :) Although that is one HUGE query at this point

, ISNULL(CONVERT(character, passport.pass_exp, 121),'NA') as pass_exp, ISNULL(CONVERT(character, passport.pass_no),'NA') as pass_no

Although --- How do you check for NULLs? Something i definitely want to know
Last edited on
p38fln (4)
Another fun thing i learned the hard way - you have to query the database in order. Can't skip around the columns. Has to go 1, 2 ,3 ,4 ,5. Or 1,3,4,5. It can't go 1,3,2,4,5 or you get the same result as above, but for a different reason.
It does say this at the end of the MSDN article on SQLGetData - my mistake for not reading the whole thing the first time through.
Texan40 (645)
a NULL return is just a zero. something like if(!myFunc()) would return true if a NULL was returned from myFunc().
kbw (9488)
a NULL return is just a zero.
We're not talking about NULL pointers, we're talking about NULL values in a database row.
p38fln (4)
Thought i had it but i just realized i had added a ; to an if statement... *smack head*

Actually looks like it does work, I fixed the extra ; and it gives the correct results -
This is a mix of kbw showing me where to find the function and ne555 correcting my attempts at changing the variable.

1
2
3
4
 
		if(SQLGetData(sqlstatement,15,SQL_C_CHAR,&address2,64,0)==SQL_NULL_DATA)
		{
			strcpy (address2, "ERROR");
		}
Last edited on
Topic archived. No new replies allowed.