BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  why we're still using BDE+SQL Links and not dbExpress... Goto page 1, 2, 3  Next          BorlandTalk.com Forum Index -> Delphi Databases (dbExpress) View previous topic :: View next topic   Author Message Randy Magruder Guest Posted: Fri Feb 10, 2006 3:03 pm    Post subject: why we're still using BDE+SQL Links and not dbExpress... I must be expecting too much of dbExpress, because it makes me want to rip my hair out every time I try to do something with it. Maybe someone can tell me what I'm doing wrong here...all this stuff works fine in BDE+SQL Links (legacy code). First, I'm using MS SQL Server 2000. Create this table using SQL Query analyzer: CREATE TABLE [TD01] ( [RequestID] [int] IDENTITY (1, 1) NOT NULL , [RequestTypeID] [int] NULL CONSTRAINT [DF_TD01_RequestTypeID] DEFAULT (0), [CustomerID] [int] NULL , [TD01_BTN] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [TD01_ACCT_ID] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [TD01_TODO_NUM] [numeric](9, 0) NULL CONSTRAINT [DF_TD01_TD01_TODO_NUM] DEFAULT (0), [TD01_SHORT_DESC] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_FULL_DESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_RESOLUTION] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_ASSIGNED_TO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_DUE_DT] [datetime] NULL , [TD01_COMPLETED_DT] [datetime] NULL , [TD01_CHANGE_DT] [datetime] NULL , [TD01_CHANGE_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_CREATE_DT] [datetime] NULL , [TD01_CREATE_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_FOLLOWUP_DT] [datetime] NULL , [TD01_FIELD1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_FIELD2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_FIELD3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TD01_FIELD4] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_TD01] PRIMARY KEY NONCLUSTERED ( [RequestID] ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] Now for the fun part. Drag this table from Data Explorer onto a VCL form. You get your connection and a dataset. Now make an update query for this table (gotta do it by hand ), using the primary key Required ID as the 'where' part. Now try this: with SQLQuery1.Params do begin ParamByName( 'OLD_RequestID' ).AsInteger := keyID; ParamByName( 'RequestTypeID' ).AsInteger := dbo_TD01.FieldByName( 'RequestTypeID' ).AsInteger; ParamByName( 'CustomerID' ).AsInteger := dbo_TD01.FieldByName( 'CustomerID' ).AsInteger; ParamByName( 'TD01_BTN' ).AsString := dbo_TD01.FieldByName( 'TD01_BTN' ).AsString; ParamByName( 'TD01_ACCT_ID' ).AsString := dbo_TD01.FieldByName( 'TD01_ACCT_ID' ).AsString; ParamByName( 'TD01_TODO_NUM' ).AsBCD := dbo_TD01.FieldByName( 'TD01_TODO_NUM' ).AsCurrency; ParamByName( 'TD01_SHORT_DESC' ).AsString := dbo_TD01.FieldByName( 'TD01_SHORT_DESC' ).AsString; ParamByName( 'TD01_FULL_DESC' ).AsMemo := dbo_TD01.FieldByName( 'TD01_FULL_DESC' ).AsString; ParamByName( 'TD01_RESOLUTION' ).AsMemo := dbo_TD01.FieldByName( 'TD01_RESOLUTION' ).AsString; ParamByName( 'TD01_ASSIGNED_TO' ).AsString := dbo_TD01.FieldByName( 'TD01_ASSIGNED_TO' ).AsString; ParamByName( 'TD01_DUE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_DUE_DT' ).AsSQLTimeStamp; ParamByName( 'TD01_COMPLETED_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_COMPLETED_DT' ).AsSQLTimeStamp; ParamByName( 'TD01_CHANGE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_CHANGE_DT' ).AsSQLTimeStamp; ParamByName( 'TD01_CHANGE_ID' ).AsString := dbo_TD01.FieldByName( 'TD01_CHANGE_ID' ).AsString; ParamByName( 'TD01_CREATE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_CREATE_DT' ).AsSQLTimeStamp; ParamByName( 'TD01_CREATE_ID' ).AsString := dbo_TD01.FieldByName( 'TD01_CREATE_ID' ).AsString; ParamByName( 'TD01_FOLLOWUP_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_FOLLOWUP_DT' ).AsSQLTimeStamp; ParamByName( 'TD01_FIELD1' ).AsString := dbo_TD01.FieldByName( 'TD01_FIELD1' ).AsString; ParamByName( 'TD01_FIELD2' ).AsString := dbo_TD01.FieldByName( 'TD01_FIELD2' ).AsString; ParamByName( 'TD01_FIELD3' ).AsString := dbo_TD01.FieldByName( 'TD01_FIELD3' ).AsString; ParamByName( 'TD01_FIELD4' ).AsString := dbo_TD01.FieldByName( 'TD01_FIELD4' ).AsString; end; SQLQuery1.ExecSQL(); Now run it and watch the parameter errors happening. WHY? Why are field types so finicky in dbExpress? Why does dbExpress not seem to properly support field types? Is there an 'expert's guide to making field types work with dbExpress' that I need to buy? ARRGGHHH Randy Back to top Craig Stuntz [TeamB] Guest Posted: Fri Feb 10, 2006 4:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Jan Doggen wrote: Quote: I don't have a Data Explorer in D7 there. D8-D2006 does. -- Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz Useful articles about InterBase development: http://blogs.teamb.com/craigstuntz/category/21.aspx Back to top Jan Doggen Guest Posted: Fri Feb 10, 2006 4:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Hello Randy I don't have a Data Explorer in D7 there. Starting menu option Database/Explorer gave me SQL Explorer which is essentially BDE based, so dragging from there gave me a TTable, which is not DBExpress. Isn't this the confusion? Bye Jan Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 4:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... I'm talking about D2006, which *is* using dbexpress. (You can reproduce it manually - but it illustrates my point on how simple it SHOULD Be) -- Jan Doggen wrote: Quote: Hello Randy I don't have a Data Explorer in D7 there. Starting menu option Database/Explorer gave me SQL Explorer which is essentially BDE based, so dragging from there gave me a TTable, which is not DBExpress. Isn't this the confusion? Bye Jan Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 5:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Well I isolated one of the bugs to: ParamByName( 'TD01_DUE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_DUE_DT' ).AsSQLTimeStamp; Both .as functions return a type of "TSQLTimeStamp". TSQLTimeStampField is the type that dbExpress converts the SQL Server field into when I let it create the meta data. So...why is an apple not an apple here? Randy -- Craig Stuntz [TeamB] wrote: Quote: Jan Doggen wrote: I don't have a Data Explorer in D7 there. D8-D2006 does. Back to top Craig Stuntz [TeamB] Guest Posted: Fri Feb 10, 2006 5:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Randy Magruder wrote: Quote: Well I isolated one of the bugs to: ParamByName( 'TD01_DUE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_DUE_DT' ).AsSQLTimeStamp; Both .as functions return a type of "TSQLTimeStamp". TSQLTimeStampField is the type that dbExpress converts the SQL Server field into when I let it create the meta data. So...why is an apple not an apple here? What is the exact error you get in this case? -- Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz Borland newsgroup denizen Sergio González has a new CD of Irish music out, and it's good: http://tinyurl.com/7hgfr Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 6:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Turns out the bug only occurs when the value is null. So I guess the .As functions don't like Null, even for DateTime fields. Guess It's back to using Variants. Randy -- Craig Stuntz [TeamB] wrote: Quote: Randy Magruder wrote: Well I isolated one of the bugs to: ParamByName( 'TD01_DUE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_DUE_DT' ).AsSQLTimeStamp; Both .as functions return a type of "TSQLTimeStamp". TSQLTimeStampField is the type that dbExpress converts the SQL Server field into when I let it create the meta data. So...why is an apple not an apple here? What is the exact error you get in this case? Back to top Thomas Miller Guest Posted: Fri Feb 10, 2006 6:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Because of bugs like this I stopped using params back in D4. I parse out all my SQL statements. It is slower when you have multiple postings, but you have almost no chance of hitting a Param bug. I was going to suggest dbExpress Plus, but it hasn't been converted for D2006. That could solve a lot of problems for you too. I would also look into 3rd party drivers http://www.crlab.com For one thing, they support multiple version of a database. I don't know if Borland has changed this, but usually they dump the code of the previous version that supported an older version of the database. They are also updated much more often. So for instance with Oracle, the last drive only supported a specific version of Oracle 9. Even though the version before it support 8i. With third party, they leave the support for older databases in their driver. Good luck. Randy Magruder wrote: Quote: Turns out the bug only occurs when the value is null. So I guess the .As functions don't like Null, even for DateTime fields. Guess It's back to using Variants. Randy -- Thomas Miller Chrome Portal Project Manager Wash DC Delphi SIG Chairperson Delphi Client/Server Certified Developer BSS Accounting & Distribution Software BSS Enterprise Accounting FrameWork http://www.bss-software.com http://www.cpcug.org/user/delphi/index.html http://sourceforge.net/projects/chromeportal/ http://sourceforge.net/projects/uopl/ http://sourceforge.net/projects/dbexpressplus Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 7:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Craig, Yeah, I'm in the mode of debating which evil is worse: Using Variants for all my assignments, or adding a whole bunch of IF statements. Neither appeals to me. How big you reckon the Variants overhead is? Randy -- Craig Stuntz [TeamB] wrote: Quote: Thomas Miller wrote: Because of bugs like this I stopped using params back in D4. I parse out all my SQL statements. It is slower when you have multiple postings, but you have almost no chance of hitting a Param bug. This is terrible advice in general -- you not only hurt performance on this statement but you also kill server-wide optimizations like the compiled statement cache -- but especially for this particular issue. You cannot presume that .AsXXX := AsXXX will deal correctly with NULL. Randy is lucky he got an error in this case. Had he not gotten an error his app might have misbehaved badly. For example, doing this with an integer param would result in assigning 0 instead of NULL with no error. Since zero and NULL are not the same, this could cause bugs in the app. If you don't want to use variants, Randy, you can test explicitly: if dbo_TD01.FieldByName( 'TD01_DUE_DT' ).IsNull then begin ParamByName( 'TD01_DUE_DT' ).Clear; ParamByName( 'TD01_DUE_DT' ).Bound := True; end else begin ParamByName( 'TD01_DUE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_DUE_DT' ).AsSQLTimeStamp; end; // if Wrap it up in a helper function if you use it a lot. Back to top Craig Stuntz [TeamB] Guest Posted: Fri Feb 10, 2006 7:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Thomas Miller wrote: Quote: Because of bugs like this I stopped using params back in D4. I parse out all my SQL statements. It is slower when you have multiple postings, but you have almost no chance of hitting a Param bug. This is terrible advice in general -- you not only hurt performance on this statement but you also kill server-wide optimizations like the compiled statement cache -- but *especially* for this particular issue. You *cannot* presume that .AsXXX := AsXXX will deal correctly with NULL. Randy is *lucky* he got an error in this case. Had he not gotten an error his app might have misbehaved badly. For example, doing this with an integer param would result in assigning 0 instead of NULL with no error. Since zero and NULL are not the same, this could cause bugs in the app. If you don't want to use variants, Randy, you can test explicitly: if dbo_TD01.FieldByName( 'TD01_DUE_DT' ).IsNull then begin ParamByName( 'TD01_DUE_DT' ).Clear; ParamByName( 'TD01_DUE_DT' ).Bound := True; end else begin ParamByName( 'TD01_DUE_DT' ).AsSQLTimeStamp := dbo_TD01.FieldByName( 'TD01_DUE_DT' ).AsSQLTimeStamp; end; // if Wrap it up in a helper function if you use it a lot. -- Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz Want to help make Delphi and InterBase better? Use QC! http://qc.borland.com -- Vote for important issues Back to top Craig Stuntz [TeamB] Guest Posted: Fri Feb 10, 2006 7:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Randy Magruder wrote: Quote: How big you reckon the Variants overhead is? Not huge unless you're in a tight loop with a lot of assignments. When using variants with DB stuff, the usual reason to steer away is for more precise control over how conversions are handled since the performance cost is often not significant next to the DB calls. As always, the profiler gives you a definitive answer. -- Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz Please read and follow Borland's rules for the user of their server: http://support.borland.com/entry.jspa?externalID=293 Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 8:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Okay, here's the next followup question - Since I'm losing the TUpdateSQL component that nicely generates SQL update/insert code, am I stuck handcoding all my insert/update statements as well as writing long Param... = value statements to update data in the dbExpress world? Randy -- Craig Stuntz [TeamB] wrote: Quote: Randy Magruder wrote: How big you reckon the Variants overhead is? Not huge unless you're in a tight loop with a lot of assignments. When using variants with DB stuff, the usual reason to steer away is for more precise control over how conversions are handled since the performance cost is often not significant next to the DB calls. As always, the profiler gives you a definitive answer. Back to top Craig Stuntz [TeamB] Guest Posted: Fri Feb 10, 2006 8:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Randy Magruder wrote: Quote: Since I'm losing the TUpdateSQL component that nicely generates SQL update/insert code, am I stuck handcoding all my insert/update statements as well as writing long Param... = value statements to update data in the dbExpress world? Couple things: 1) TUpdateSQL required cached updates with the BDE and hence used Paradox tables under the hood. That made it a non-starter for multi-user use, in my book. If you haven't encountered problems with this yet, consider yourself /exceedingly/ fortunate and be glad you're getting out now. 2) No, you don't have to write the statements yourself. TSQLResolver (which TDatasetProvider uses under the hood) does it for you. Should you want more control -- i.e., if the generated statements are not good enough -- then you can either write a TDSP.BeforeUpdateRecord handler or (what I usually do) write an updateable VIEW in your DB. -- Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz All the great TeamB service you've come to expect plus (New!) Irish Tin Whistle tips: http://learningtowhistle.blogspot.com Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 8:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... Quote: 1) TUpdateSQL required cached updates with the BDE and hence used Paradox tables under the hood. That made it a non-starter for multi-user use, in my book. If you haven't encountered problems with this yet, consider yourself exceedingly fortunate and be glad you're getting out now. Okay - thanks. Quote: 2) No, you don't have to write the statements yourself. TSQLResolver (which TDatasetProvider uses under the hood) does it for you. Should you want more control -- i.e., if the generated statements are not good enough -- then you can either write a TDSP.BeforeUpdateRecord handler or (what I usually do) write an updateable VIEW in your DB. Thinking back to my earlier question about stored-proc based master-detail briefcase model, if I'm getting data from a stored proc that perhaps does joins, groupings, etc...then any attempt by these components to update the data directly would undoubtedly fail. And since it's stored procs, I don't think I can go with the VIEW model (though I'll discuss this with my DBA). So that leaves "BeforeUpdateRecord". can you point me to an example of getting leverage from the BeforeUpdateRecord event? Randy Back to top Randy Magruder Guest Posted: Fri Feb 10, 2006 9:03 pm    Post subject: Re: why we're still using BDE+SQL Links and not dbExpress... What are your thoughts on the advice given by Dan Miser in this article http://bdn.borland.com/article/0,1410,21408,00.html -- Craig Stuntz [TeamB] wrote: Quote: Randy Magruder wrote: Since I'm losing the TUpdateSQL component that nicely generates SQL update/insert code, am I stuck handcoding all my insert/update statements as well as writing long Param... = value statements to update data in the dbExpress world? Couple things: 1) TUpdateSQL required cached updates with the BDE and hence used Paradox tables under the hood. That made it a non-starter for multi-user use, in my book. If you haven't encountered problems with this yet, consider yourself exceedingly fortunate and be glad you're getting out now. 2) No, you don't have to write the statements yourself. TSQLResolver (which TDatasetProvider uses under the hood) does it for you. Should you want more control -- i.e., if the generated statements are not good enough -- then you can either write a TDSP.BeforeUpdateRecord handler or (what I usually do) write an updateable VIEW in your DB. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (dbExpress) All times are GMT Goto page 1, 2, 3  Next Page 1 of 3   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.