ODBC and MSSQL lazarus / 13-General ---------------------------------------------------------------------- Dane · 2009-01-28 07:36 What I have: 1. Lazarus 0.9.26 + FPC 2.2.2 2. MSSQL Server 2005 3. ODBC MSSQL Server driver What I Need: 1. Connect to MSSQL through ODBC 2. Select some data from different databases 3. Insert some selected data to another database Some code: Code: [Select]MSDBConnector := TODBCConnection.Create(nil);    with MSDBConnector do    begin       Driver := MSSQLServerIni.DocumentElement.FindNode('driver').TextContent;       UserName := MSSQLServerIni.DocumentElement.FindNode('username').TextContent;       Password := MSSQLServerIni.DocumentElement.FindNode('password').TextContent;       Params.Add('database='+MSSQLServerIni.DocumentElement.FindNode(' database').TextContent);       Params.Add('server='+MSSQLServerIni.DocumentElement.FindNode('se rver').TextContent);       Connected := True;    end;    MSDBTrans := TSQLTransaction.Create(nil);    MSDBTrans.Database := MSDBConnector;    MSDBQuery := TSQLQuery.Create(nil);    with MSDBQuery do    begin       Database := MSDBConnector;       Transaction := MSDBTrans;       PacketRecords := -1;    end;    //MSSQL server    Application.ProcessMessages;    if PriceRef.Checked then    begin       ...       MSDBQuery.SQL.Clear;       MSDBQuery.SQL.Add('SELECT Inc, CONVERT(datetime, CheckIn, 20) CheckIn, Nights, Tour, Price, PeopleCount, Hotel, HtPlace, Meal, room FROM [wwwbron].[dbo].[Cat_Claim] WHERE(Inc IS NOT NULL)');       MSDBQuery.Open; //This part working fine       while not MSDBQuery.EOF do       begin          ...          MSDBQuery.Next;          ...       end;       MSDBQuery.Close;    end;    if SprRef.Checked then    begin       TotalProgress.Position := 0;       Application.ProcessMessages;       ...       MSDBQuery.SQL.Clear;       MSDBQuery.SQL.Add('SELECT inc, [name], lname, star, partner, town, address, phprefix, phones, faxes, email, meal, www FROM [SamoTour].[dbo].[hotel] WHERE ([name] IS NOT NULL AND [name]!='''');');       MSDBQuery.Open; //Show an ODBC error: Could not retrieve primary key metadata...     ...    end; Problem is:    If programm runs the first trigger (PriceRef.Checked := true) MSDBQuery.Open works fine without any error. If programm runs second trigger (SprRef.Checked := true) it's terminated with an ODBC error that could not retrieve primary key metadata.    Both MSSQL databases is allowed to access by username and password that using in configuration. If make a queries in MS Studio - they works fine. All using databases and tables are exists and checked for errors. Any ideas? ---------------------------------------------------------------------- Loesje · 2009-02-01 11:25 The solution to this problem is written everywhere: ParseSQL:=False. Or, make sure that a primary key is defined for the table. And then you probably have to update your fpc-version. ---------------------------------------------------------------------- Lacak2 · 2009-02-17 13:44 I have the same problem with error message "Could not retrieve primary key metadata for table ... Connection is busy with results for another hstmt;" when I am using TODBCConnection with TSQLQuery (with simple select * from table). When I set ParseSQL:=false all works ok, isn't it a bug ? My second problem is, that this dataset is not updatable (I have not set UpdateSQL, but for simple select there is no need to set it ... as stated in sqldb/examples : // With these lines commented out, TSQLQuery creates the update, delete and insert // queries itself. see in sqldb.pp ... InitialiseModifyQuery(FUpdateQry,ModifyRecQuery); ) And my third problem is with the same example but on Windows Vista (on Windows98 it works), where always error "Could not close ODBC statement cursor. ... Invalid handle passed to SQLGetDiagRec/Field" is returned, when I set Active:=true Can anyone help, pls ? -Laco. ---------------------------------------------------------------------- Loesje · 2009-02-17 16:57 When you set ParseSQL to False, you always have to provide your own update/insert/delete sql-statements. (That's what the ParseSQL property is for, together with server-side filtering) You could also set UsePrimaryKeyAsKey to false, and then set the field which is the primary key yourself. Or you could use upda]"]>Blockedde := upWhereAll. And try to update to a more recent fpc-version (2.2.4rc1 or a snapshot/svn version) Your last problem is probably a broken odbc-setup. But the error- message isn't very clear. Check your password etc. ---------------------------------------------------------------------- Lacak2 · 2009-02-18 09:32 Quote from: Loesje on February 17, 2009, 05:57:42 pmWhen you set ParseSQL to False, you always have to provide your own update/insert/delete sql-statements. (That's what the ParseSQL property is for, together with server-side filtering) Yes you are right, I missed "if not FParseSQL then break;" in TCustomSQLQuery.SQLParser Quote from: Loesje on February 17, 2009, 05:57:42 pmYou could also set UsePrimaryKeyAsKey to false, and then set the field which is the primary key yourself. Yes I must do it this way, else I always get error mesage : "Could not retrieve primary key metadata for table ... using SQLPrimaryKeys. ODBC error details: ... Connection is busy with results for another hstmt;" So i think, that this should be reported as bug ...??? Quote from: Loesje on February 17, 2009, 05:57:42 pmOr you could use upda]"]>Blockedde := upWhereAll. Yes, when I set "ParseSQL:=true" and "Upda]"]>Blockedde:=upWhereAll" and "UsePrimaryKeyAsKey:=false" it works ok. (no error and dataset is updatable) Quote from: Loesje on February 17, 2009, 05:57:42 pmAnd try to update to a more recent fpc-version (2.2.4rc1 or a snapshot/svn version) I have tried Lazarus 0.9.27 with fpc 2.2.3, but above mentioned error does not disapears ... Quote from: Loesje on February 17, 2009, 05:57:42 pmYour last problem is probably a broken odbc-setup. But the error-message isn't very clear. Check your password etc. No, this in new installed PC with 32bit Windows Vista Business, but seems, that error "Could not close ODBC statement cursor. ... Invalid handle passed to SQLGetDiagRec/Field" occured only in IDE not at runtime So after all I catch another error "Parameter ... of type smallint , which not supported yet." (in TODBCConnection.SetParameters ... I think that, this can be fixed easy) TIA -Laco. ---------------------------------------------------------------------- Loesje · 2009-02-18 13:15 Quote from: Lacak2 on February 18, 2009, 10:32:57 amYes I must do it this way, else I always get error mesage : "Could not retrieve primary key metadata for table ... using SQLPrimaryKeys. ODBC error details: ... Connection is busy with results for another hstmt;" So i think, that this should be reported as bug ...??? Could you try this again with fpc 2.3.1? There are some experimental changes there which could solve this problem. If you think that using fpc 2.3.1 is too much of a hassle, you could download the latest version of odbcconn.pas from svn.  Then place it somewhere in your project-directory so that the compiler can find it. This way you can try if this version solves your problem, and if you want you can modify and debug it. QuoteSo after all I catch another error "Parameter ... of type smallint , which not supported yet." (in TODBCConnection.SetParameters ... I think that, this can be fixed easy) Yes, in TODBCConnection.SetParameters. You could try to fix it yourself. Probably you only have to add 'ftSmallInt' to the same case- statement as 'ftInteger' ---------------------------------------------------------------------- Lacak2 · 2009-02-19 08:55 Quote from: Loesje on February 18, 2009, 02:15:53 pmCould you try this again with fpc 2.3.1? There are some experimental changes there which could solve this problem. Sorry, I am not able to set up Lazarus to work with new version of fpc /tons of file not found messages/, so I can not confirm ... Quote from: Loesje on February 18, 2009, 02:15:53 pmyou could download the latest version of odbcconn.pas from svn.  Then place it somewhere in your project-directory so that the compiler can find it. This way you can try if this version solves your problem, and if you want you can modify and debug it. When I do so, I get error messages : odbcconn.pas(519,17) Error: identifier idents no member "FSchemaType" odbcconn.pas(587,19) Error: identifier idents no member "FSchemaType" odbcconn.pas(588,17) Error: Constant and CASE types do not match odbcconn.pas(589,17) Error: Constant and CASE types do not match odbcconn.pas(590,17) Error: Constant and CASE types do not match odbcconn.pas(591,17) Error: Constant and CASE types do not match QuoteYes, in TODBCConnection.SetParameters. You could try to fix it yourself. Probably you only have to add 'ftSmallInt' to the same case- statement as 'ftInteger' In latest odbcconn.pas is added only support for BLOBs, I will report FR in bug-tracker to include support for ftSmallInt=SQL_SMALLINT, ftLargeInt=SQL_BIGINT, ftFloat=SQL_DOUBLE, ftDateTime=SQL_TIMESTAMP http://mantis.freepascal.org/view.php?id=13206 -Laco. ---------------------------------------------------------------------- Loesje · 2009-02-22 17:46 Quote from: Lacak2 on February 19, 2009, 09:55:52 amQuote from: Loesje on February 18, 2009, 02:15:53 pmyou could download the latest version of odbcconn.pas from svn.  Then place it somewhere in your project- directory so that the compiler can find it. This way you can try if this version solves your problem, and if you want you can modify and debug it. When I do so, I get error messages : odbcconn.pas(519,17) Error: identifier idents no member "FSchemaType" In that case you should also add sqldb.pp to your compiler-path and try again. I don't have a odbc/windows setup and since i'm the one who fixes most db-bugs, the fastest way to fix this bug is by supplying a patch by your own. ---------------------------------------------------------------------- Lacak2 · 2009-02-23 10:05 Quote from: Loesje on February 22, 2009, 06:46:24 pmIn that case you should also add sqldb.pp to your compiler-path and try again. now: Unit sqlscript not found, and I can not find this unit in Lazarus (sub)directory Quote from: Loesje on February 22, 2009, 06:46:24 pmI don't have a odbc/windows setup and since i'm the one who fixes most db-bugs, the fastest way to fix this bug is by supplying a patch by your own. is there a possibility, that you will apply folowing fix, and I will download "some daily snapshot of whole Lazarus" and test it ? 1. Please add ftSmallInt in case of ftInteger 2. Add following case (+var FloatVal: cdouble):       ftFloat:         begin           Buf:=GetMem(Sizeof(cdouble));           FloatVal:=AParams[ParamIndex].AsFloat;           Move(FloatVal,Buf^,Sizeof(cdouble));           ODBCCursor.FParamBuf:=Buf;           ODBCCheckResult(             SQLBindParameter(ODBCCursor.FSTMTHandle, // StatementHandle                              i+1,                    // ParameterNumber                              SQL_PARAM_INPUT,        // InputOutputType                              SQL_C_DOUBLE,             // ValueType                              SQL_DOUBLE,            // ParameterType                              15,                     // ColumnSize                              0,                      // DecimalDigits                              Buf,                    // ParameterValuePtr                              0,                      // BufferLength                              nil),                   // StrLen_or_IndPtr             SQL_HANDLE_STMT, ODBCCursor.FSTMTHandle, 'Could not bind (float) parameter %d.',           );         end; If it will be ok, we can later add suport for TIMESTAMP -Laco. ---------------------------------------------------------------------- Loesje · 2009-02-23 15:35 Quote from: Lacak2 on February 23, 2009, 11:05:33 amnow: Unit sqlscript not found, and I can not find this unit in Lazarus (sub)directory You can find it here. Note that you can also browse through the files through this web-interface. But I admit that at first it's difficult to find your way through the file-structure. Quoteis there a possibility, that you will apply folowing fix, and I will download "some daily snapshot of whole Lazarus" and test it ? Well, this are all fpc-related issues. So you have to use a updated fpc-snapshot. But when I commit something it will be in fpc 2.3.1, and I just read in another post from you that there's no lazarus-snapshot including fpc 2.3.1. And without any tests is can't be merged to fpc 2.2.3. But as it seems that you're not afraid of looking at someone else's code, you could also consider using svn directly... ---------------------------------------------------------------------- Lacak2 · 2009-02-24 11:28 Quote from: Loesje on February 18, 2009, 02:15:53 pmQuote from: Lacak2 on February 18, 2009, 10:32:57 amYes I must do it this way, else I always get error mesage : "Could not retrieve primary key metadata for table ... using SQLPrimaryKeys. ODBC error details: ... Connection is busy with results for another hstmt;" So i think, that this should be reported as bug ...??? Could you try this again with fpc 2.3.1? There are some experimental changes there which could solve this problem. I have tried it with Lazarus-0.9.27-18813-fpc-2.3.1-20090224-win32.exe but error stil exists ---------------------------------------------------------------------- Lacak2 · 2009-02-24 12:24 Quote from: Loesje on February 23, 2009, 04:35:35 pmQuote from: Lacak2 on February 23, 2009, 11:05:33 amnow: Unit sqlscript not found, and I can not find this unit in Lazarus (sub)directory You can find it here. Note that you can also browse through the files through this web-interface. But I admit that at first it's difficult to find your way through the file-structure. After all attempts (adding missing source units to project directory and adding paths to compiler options) I definitelly end with error : \lcl\calendar.pp(29,6) Error: Illegal unit name: Calendar QuoteWell, this are all fpc-related issues. So you have to use a updated fpc-snapshot. But when I commit something it will be in fpc 2.3.1, and I just read in another post from you that there's no lazarus-snapshot including fpc 2.3.1. As pointed me Vincent, there are snapshots with fpc 2.3.1, so I can download and test it ... And of course I would like to make changes locally and test, but I am not able (as I write) compile when I make any change to odbcconn.pas ... is there a simple way how to recompile units of fpc ? (Rebuilding lazarus does not help, becasue it expect already compiled fpc units) ---------------------------------------------------------------------- Lacak2 · 2009-03-18 13:47 Quote from: Loesje on February 23, 2009, 04:35:35 pmBut when I commit something it will be in fpc 2.3.1 Can you please look at: http://mantis.freepascal.org/view.php?id=13206