Recent

Author Topic: MySQL / ODBC / Linux Question  (Read 864 times)

Grahame Grieve

  • Full Member
  • ***
  • Posts: 211
MySQL / ODBC / Linux Question
« on: October 10, 2021, 04:16:54 am »
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

  • Full Member
  • ***
  • Posts: 211
Re: MySQL / ODBC / Linux Question
« Reply #1 on: October 10, 2021, 04:20:16 am »
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

  • Hero Member
  • *****
  • Posts: 10991
Re: MySQL / ODBC / Linux Question
« Reply #2 on: October 10, 2021, 09:18:51 am »
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..

 
« Last Edit: October 10, 2021, 09:21:36 am by Thaddy »
The average programmer productivity is 4-5 hours per day. Peak performance 72 hours for short bursts. MTBF is 1 second or less.

Grahame Grieve

  • Full Member
  • ***
  • Posts: 211
Re: MySQL / ODBC / Linux Question
« Reply #3 on: October 10, 2021, 11:17:46 am »
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

 

TinyPortal © 2005-2018