MySQL / ODBC / Linux Question

[Grahame Grieve]

I have a test case that makes a simple use of the odbcsqldyn unit - code below - that succeeds on windows, but fails on linux. On linux, the call to SQLExecDirect fails - it returns a random integer value.

It doesn't fail if I comment out the call to SQLSetStmtAttr, but it's valid so far as I can tell. (it succeeds on both windows and linux). I don't know whether I need to call it before doing SQLExecDirect, but I always have on windows.

Does anyone know whether I'm calling it wrong?

Code: Pascal  [Select][+][-]

1. const
2.   DefaultStringSize = 255;
3.  
4. function makePChar(len : integer) : pchar;
5. begin
6.   result := Getmem(len);
7. end;
8.  
9. function fromPChar(p : PChar; length : integer) : String;
10. begin
11.   result := p;
12. end;
13.  
14. Function odbcError(ARetCode: SQLRETURN; aHandleType: SQLSMALLINT; aHandle: SQLHANDLE): String;
15. Var
16.   ErrorNum: Integer;
17.  
18.   RetCode: SQLRETURN;
19.   State: PChar;
20.   Native: SQLINTEGER;
21.   Message: PChar;
22.   StringLength: SQLSMALLINT;
23. Begin
24.   State := makePChar(DefaultStringSize);
25.   Message := makePChar(DefaultStringSize);
26.   try
27.     Result := '';
28.  
29.     Case ARetCode Of
30.       SQL_ERROR, -24238,
31.       SQL_SUCCESS_WITH_INFO:
32.       Begin
33.         ErrorNum := 0;
34.         Repeat
35.           Inc(ErrorNum);
36.  
37.           RetCode := SQLGetDiagRec(aHandleType, aHandle, ErrorNum, State, Native, Message, DefaultStringSize, StringLength);
38.           If RetCode = SQL_SUCCESS Then
39.             CommaAdd(result, fromPChar(State, 5)+': '+fromPChar(Message, StringLength));
40.         Until RetCode <> SQL_SUCCESS;
41.         If (Result = '') Or (RetCode <> SQL_NO_DATA) Then
42.           result := 'Unable to Retrieve ODBC Error';
43.       End;
44.       Else
45.       Begin
46.         Case ARetCode Of
47.           SQL_INVALID_HANDLE:
48.             result := 'Invalid ODBC Handle';
49.           SQL_NO_DATA:
50.             result := 'No Data Found';
51.           Else
52.             result := 'ODBC Return Code '+IntToStr(ARetCode);
53.         End;
54.       End;
55.     End;
56.   finally
57.     freemem(state);
58.     freemem(message);
59.   end;
60. End;
61.  
62. procedure TFDBTests.odbcTest;
63.   procedure check(retValue : integer; op : String; aHandleType: SQLSMALLINT; aHandle: SQLHANDLE);
64.   begin
65.     if (retValue <> 0) then
66.       raise ELibraryException.create('return value from '+op+' = '+inttostr(retValue)+': '+odbcError(retValue, aHandleType, aHandle));
67.   end;
68. var
69.   env : SQLHENV;
70.   dbc : SQLHDBC;
71.   stmt : SQLHSTMT;
72.   cs, sql : String;
73.   co : pchar;
74.   l : smallint;
75.   np : SQLUINTEGER;
76.   pwd : String;
77. begin
78.   pwd := 'test';
79.  
80.   check(SQLAllocHandle(SQL_HANDLE_ENV, Pointer(SQL_NULL_HANDLE), env), 'SQLAllocHandle', SQL_HANDLE_ENV, env);
81.   check(SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, Pointer(SQL_OV_ODBC3), 0), 'SQLSetEnvAttr', SQL_HANDLE_ENV, env);
82.   check(SQLAllocHandle(SQL_HANDLE_DBC, env, dbc), 'SQLSetEnvAttr', SQL_HANDLE_DBC, dbc);
83.   cs := 'UID=test;PWD='+pwd+';DRIVER=MySQL ODBC 8.0 Unicode Driver;Server=test;Database=test;';
84.   co := makePChar(DefaultStringSize);
85.   try
86.     check(SQLDriverConnect(dbc, 0, pchar(cs), SQL_NTS, co, DefaultStringSize, l, SQL_DRIVER_NOPROMPT), 'SQLDriverConnect', SQL_HANDLE_DBC, dbc);
87.   finally
88.     freemem(co);
89.   end;
90.   check(SQLAllocHandle(SQL_HANDLE_STMT, dbc, stmt), 'SQLAllocHandle', SQL_HANDLE_DBC, dbc);
91.   sql := 'SET time_zone = ''+11:00''';
92.   check(SQLPrepare(stmt, pchar(sql), SQL_NTS), 'SQLPrepare', SQL_HANDLE_STMT, stmt);
93.   np := 0;
94.   check(SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, pointer(np), sizeof(np)), 'SQLPrepare', SQL_HANDLE_STMT, stmt);
95.   check(SQLExecDirect(stmt, pchar(sql), SQL_NTS), 'SQLExecDirect', SQL_HANDLE_STMT, stmt);
96.   check(SQLFreeHandle(SQL_HANDLE_STMT, stmt), 'SQLFreeHandle', SQL_HANDLE_STMT, stmt);
97.   check(SQLDisconnect(dbc), 'SQLDisconnect', SQL_HANDLE_STMT, stmt);
98.   check(SQLFreeHandle(SQL_HANDLE_DBC, dbc), 'SQLFreeHandle', SQL_HANDLE_DBC, dbc);
99.   check(SQLFreeHandle(SQL_HANDLE_DBC, env), 'SQLFreeHandle', SQL_HANDLE_ENV, env);
100. end;
101.              
102.  

[Grahame Grieve]

SQLSetStmtAttr returns -1 on windows, but my code ignores that if np is 0. It returns 0 on linux.

This gets to the question of whether I need to call it at all...

[Thaddy]

Code: Pascal  [Select][+][-]

1. function makePChar(len : integer) : pchar;
2. begin
3.   result := Getmem(len);
4. end;

Really?
- What is your string type?
- Use AllocMem, not GetMem. AllocMem initializes your memory to zero's and make some of the quirky random results go away.
There are more strange things in your code, but without coffee this was the first thing I noticed on this early Sunday morning..

 

[Grahame Grieve]

oh that was a testing hack. It's not the cause of this particular issue anyway - but yes, I would't do that for a string I actually cared about