The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,839 other subscribers

Archive for the ‘SQL’ Category

« Previous Entries
Next Entries »

Simple query to get .NET connection string from current SSMS connection – via: Database Administrators Stack Exchange

Posted by jpluimers on 2020/09/24

I’m connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?

[WayBack] sql – How to check connection string in SSMS2012? – Database Administrators Stack Exchange

I adopted the SQL statement in the answer to the above question to:

  • use more common parameter names and values
  • embed strings in quotes
select
    -- part names via https://wiert.me/2012/11/07/netc-sqlclient-connectionstring-keys/
    -- prefer SSPI over True via https://wiert.me/2010/10/19/solution-for-ole-db-provider-connecting-to-sql-server-giving-error-multiple-step-ole-db-operation-generated-errors-check-each-ole-db-status-value-if-available-no-work-was-done/
    'Data Source=''' + @@servername + '''' +
    ';Initial Catalog=''' + db_name() + '''' +
    case type_desc
        when 'WINDOWS_LOGIN' 
        then ';Integrated Security=SSPI'
        else ';User ID=''' + suser_name() + ''';Password='''''
    end 
    as DotNetConnectionString,
    -- note the below need to be URI-encoded later on:
    'sqlserver://' + suser_name() + ':password@' + @@servername + '/' + db_name() + '?param1=value&param2=value'
    as GoLangConnectionString
    -- sqlserver://username:password@host/instance?param1=value&param2=value
    -- https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn
from sys.server_principals
where name = suser_name()

You can use this to generate connection strings for use in .NET, OLE DB, Visual Studio Code, go lang and likely many other tools.

Related:

–jeroen

Read the rest of this entry »

Posted in Database Development, Development, Software Development, SQL, SQL Server, SSMS SQL Server Management Studio | Leave a Comment »

FireDAC can do DBMS back-end conditional SQL via Conditional Substitution

Posted by jpluimers on 2020/09/17

Though the field-types mentioned in the problem and solution are equal (so either is wrong), the solution in [WayBack] I have a little problem with FireDAC and the TStringField and TWideStringField design time generation… – Juan C. Cilleruelo – Google+ pointed out by Jeff Weir is interesting: FireDAC supports conditionals that depend on the DBMS back-end, so you can differentiate between them.

The feature is called Conditional Substitution and has been present ever since AnyDAC (which got bought by Embarcadero, transformed into FireDAC, then after Idera bought Embarcadero, the main developer got pink-slipped).

The AnyDAC documentation is in the wayback machine, though you have to disable the onload event in order to read it.

The [Archive.is] XE5: Preprocessing Command Text (FireDAC) – RAD Studio documentation is not much different from the current state [Archive.is].

More background reading is at [WayBack] www.freepascal.org/~michael/articles/anydac2/anydac2.pdf and Cary Jensen covered it in his 2017 course on FireDAC of which you can see the free ToC.

Example from that thread:

SELECT ART.CD_ITEM                ,
       ART.CD_FAMILY              ,
       ART.CD_CATALOGUE           ,
       CAT.DS_CATALOGUE           ,
       FAM.DS_FAMILY              ,
{IF MSSQL}
       CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
          THEN CAST('Y' AS NVARCHAR) 
          ELSE CAST('N' AS NVARCHAR) 
       END HAS_CONFIGURATION      ,
{fi}
{IF FIREBIRD}
       CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
          THEN 'Y'  
          ELSE 'N'  
       END HAS_CONFIGURATION      ,
{fi}
       ART.DS_ITEM                ,
       ART.CD_TAX                 ,
       TAX.DS_TAX                 ,
       TAX.PRC_TAX               ,
...

Given the problem statement, the casts likely should have been VARCHAR instead of NVARCHAR, but the construct can be very powerful.

–jeroen

Posted in Database Development, Delphi, Development, Firebird, InterBase, Software Development, SQL, SQL Server | Leave a Comment »

Joins explained.

Posted by jpluimers on 2020/08/26

Turn your head 90 degrees counter-clockwise around the longitudinal axis [WayBack] Joins explained. – Kristian Köhntopp – Google+

Wait, let me help you:

Read the rest of this entry »

Posted in Database Development, Development, SQL | Leave a Comment »

browser – Clearing old browsing data in Chrome instead of newer data – Super User

Posted by jpluimers on 2020/06/25

[WayBack] browser – Clearing old browsing data in Chrome instead of newer data – Super User had a few possibilities, but eHistory disappeared from the Chrome store (apparently due to some unknown violation), so the way that works best now is to hack the History database which is a SQLite file as mentioned in

[WayBack] How can I delete all web history that matches a specific query in Google Chrome – Super User: For literal values of “query”…You can even query your Chrome history using SQL. (Firefox too: see below. Of course, the appropriate file path will have to be changed).

If you really want you can hack the history frame chrome://history-frame/: [WayBack] How can I delete all web history that matches a specific query in Google Chrome – Super User

–jeroen

Posted in Chrome, Database Development, Development, Google, Power User, Software Development, SQL | Leave a Comment »

In SQL Server use `SET NOCOUNT ON` so tools taking the last modified record count won’t be confused by your trigger.

Posted by jpluimers on 2020/04/07

Interesting read: Time eating bug of the day… – Fabian S. Biehn – Google+.

TL;DR: in SQL Server use SET NOCOUNT ON so tools taking the last modified record count won’t be confused by your trigger.

Source: [WayBack] Time eating bug of the day: I used a TADOQuery.ExecSQL (on Berlin) for an Up…

Related: [WayBack] sql server – ADODB affected rows return trigger’s affected rows – Stack Overflow

–jeroen

Posted in Database Development, Delphi, Development, Office VBA, Scripting, Software Development, SQL, SQL Server | Leave a Comment »

SQL code smells

Posted by jpluimers on 2019/10/31

A while back I bumped into [WayBack] SQL Code Smells – Simple Talk, which is an extensive article covering all sorts of SQL related code smells.

It reminds me that one day I need to dig up some old links on other code smells as well.

–jeroen

via: [WayBack] Martin Fowler on Twitter: “It’s an old anti-pattern, and sadly is still going strong: The Entity Service Antipattern.”

Posted in Database Development, Development, SQL | Leave a Comment »

Public database servers

Posted by jpluimers on 2019/10/30

I could not find any vendors/architectures have public database servers.

So there is no good way to go beyond SQLFiddle (of which I wrote before in SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions and David Rodriguez: a few nice posts on SQL (via: Google+)), that does not provide database access, but allows you to fire SQL statements onto these architectures:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2017

I get the thing (it is very hard to secure an “over the internet” connection to a database server; do NOT do this: [WayBack] connectivity – Connect to SQL Server over Internet – Database Administrators Stack Exchange), so the alternative is to run locally.

If you run locally, there are plenty of example/demo database, like:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »

Some links on SQL parsing

Posted by jpluimers on 2019/10/10

Sometimes you want the opposite of SQL generation (which most, if not all, ORMs do behind the scenes): SQL parsing.

Some links that should get me started:

–jeroen

Posted in Database Development, Development, Software Development, SQL | 2 Comments »

ApexSQL, a free tool (SSMS add-in) for analyzing the execution plan of a SQL server query…

Posted by jpluimers on 2019/10/01

On my research list: ApexSQL PLAN analysis tool released in 2017. It requires SSMS which you can get at [WayBack] Download SQL Server Management Studio (SSMS) | Microsoft Docs.

More info:

Via:

–jeroen

Posted in Database Development, Development, Software Development, SQL, SQL Server | Leave a Comment »

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Posted by jpluimers on 2019/08/29

Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:

Application for testing and sharing SQL queries.

Source: [WayBack] SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

It is a cool site, currently supporting these SQL back-ends:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2014

You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)

Other resources for learning and playing around with SQL:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server | Leave a Comment »

« Previous Entries
Next Entries »