Any SQL builder for generating queries available?

At the moment we have a lot of simple but hardcoded queries that need
rewriting because of the introduction of mysql  (reserved words and the
need for the backtick/quotes or simple for adjusting the
top/first/limit or 'emulating' *ci_ai collation on db's that dont fully
support a collation system like postgreSQL). It would be so much more
elegant than hardcoded queries.

Before trying to build such ourselves (which would be a rather big
undertaking) are there any SQL builders/toolboxes available that
(partly) take care of writing SQL statements at runtime for multiple
database systems and taking into account the optional schema and
escaping/quoting system of that particular database?


Sofar I have only seen GpSQLBuilder from Primoz Gabrijelcic (a step in
the right direction but not very readable or suitable as there is no
escape possibilities and no knowledges of any database). But i'm sure
there are probably more out there that i have overlooked (google
returned way to much hits)


We are running Delphi XE, with the following databases: Firebird,
MsSql, MySql and PostgreSQL (and perhaps Oracle in the future)


Would some sort of LINQ systems be suitable for this?
0
Marius
5/10/2015 9:18:45 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

7 Replies
548 Views

Similar Articles

[PageSpeed] 28

Not exactly what you are looking for and is not available separately from FireDAC, but
the FireDAC SQL preprocessor is aimed to solve the same tasks:
http://docwiki.embarcadero.com/RADStudio/XE8/en/Preprocessing_Command_Text_(FireDAC)

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
5/11/2015 5:26:47 AM
Hello Dmitry

>Not exactly what you are looking for and is not available separately
>from FireDAC, but the FireDAC SQL preprocessor is aimed to solve the
>same tasks:

That is interesting, there is however no information on that page
except the following (seems to be a rather generic problem on docwiki)

"There is currently no text in this page. You can search for this page
title in other pages, or search the related logs, but you do not have
permission to create this page."


Do you have another page with some more info?

Marius
0
Marius
5/11/2015 12:24:34 PM
While I have never used it, I wonder if our Rapid SQL product 
(http://www.embarcadero.com/products/rapid-sql) would do what you want 
here? Rapid SQL is part of DB PowerStudio 
(http://www.embarcadero.com/products/db-powerstudio), which is part of 
the Ultimate edition of Delphi/Rad Studio.

Marius. wrote:
> At the moment we have a lot of simple but hardcoded queries that need
> rewriting because of the introduction of mysql  (reserved words and the
> need for the backtick/quotes or simple for adjusting the
> top/first/limit or 'emulating' *ci_ai collation on db's that dont fully
> support a collation system like postgreSQL). It would be so much more
> elegant than hardcoded queries.
>
> Before trying to build such ourselves (which would be a rather big
> undertaking) are there any SQL builders/toolboxes available that
> (partly) take care of writing SQL statements at runtime for multiple
> database systems and taking into account the optional schema and
> escaping/quoting system of that particular database?
>
>
> Sofar I have only seen GpSQLBuilder from Primoz Gabrijelcic (a step in
> the right direction but not very readable or suitable as there is no
> escape possibilities and no knowledges of any database). But i'm sure
> there are probably more out there that i have overlooked (google
> returned way to much hits)
>
>
> We are running Delphi XE, with the following databases: Firebird,
> MsSql, MySql and PostgreSQL (and perhaps Oracle in the future)
>
>
> Would some sort of LINQ systems be suitable for this?
>
0
quinn
5/11/2015 3:41:23 PM
>Do you have another page with some more info?

Got it, i missed the last ) in the url..
0
Marius
5/11/2015 4:58:53 PM
quinn wildman wrote:

>While I have never used it, I wonder if our Rapid SQL product 
>(http://www.embarcadero.com/products/rapid-sql) would do what you
>want here? Rapid SQL is part of DB PowerStudio 
>(http://www.embarcadero.com/products/db-powerstudio), which is part
>of the Ultimate edition of Delphi/Rad Studio.

Thanks Quinn!

From the first looks both are full blown IDE's that need a bit of
investigation. So far I have been looking for runtime solutions but who
knows where this solution can lead to.

Marius
0
Marius
5/11/2015 5:17:29 PM
These two third party components have LINQ like support:
http://www.devart.com/entitydac/
https://www.tmssoftware.com/site/aurelius.asp

UniDAC does "Server-Independent SQL":
http://www.devart.com/unidac/

Lastly you can use Navicat as a SQL server management studio and query builder.
0
Eli
5/11/2015 10:26:07 PM
Eli M wrote:

>UniDAC does "Server-Independent SQL":
>http://www.devart.com/unidac/

Since we use UniDac we decided to go with UniDac. Entitydac is also
interesting, but at this moment it is not an option because it does not
support Fpc/Lazarus (hence the choice for UniDac instead of
AnyDac/FireDac).


select 
{if InterBase} first 10 {ENDIF} 
{if PostgreSQL} top 10 {ENDIF}
{if SQL Server} top 10 {ENDIF}

{fn YEAR("datestart")} as datestartyear,
{fn MONTH("datestart")} as datestartmonth,
{fn DAY("datestart")} as datestartday

from "ACTION"
{if MySql} limit 10 {ENDIF}

http://www.devart.com/unidac/docs/#unisql.htm

The double quotes are automaticly replaced with backticks (mysql) or []
(mssql). In effect we only need to quote and uppercase the (unexpected)
reserved words from mysql. Nice solution.

Thanks Eli!
0
Marius
5/12/2015 11:31:39 AM
Reply:

Similar Artilces:

custom query builder in asp against a sql database
Hello Is there any sample code out there to show how to create a custom query builder using web controls - querying a sql database. Thanks Andi I'm not aware of any examples out there. Best of luck in finding them! Are you familiar with Sql Injection attacks and how to stop them?  If not, you need to read up on them.  Assume that any sample custom query builder you find is vulnerable to this type of hacking until proven otherwise.  ...

LDAP queries linked to SQL database queries
Hi, I am wondering whether it is common practice to use the AD domain users information as a link table for user information in database queries. My first thought is that it would be too slow since it can potentially query the AD many times depending on the number of records returned. Any insight is appreciated, sivilianMy Weblogs...

Problems whith SQL query. I need to store ' char in sql database
Hi,Im trying to store a sql query in sql, see example below. But it keeps bugging me around 'Adobe' in the example. Anyone who know how to store the stringSelect app_Number as [Applikationsnummer], App_status as [Applikationstatus], Collector as [Insamlare] from Applikations WHERE App_Producer='Adobe'In a sql database?   INSERT INTO [View] (View_Name, View_Query) VALUES ('Henriks','Select app_Number as [Applikationsnummer], App_status as [Applikationstatus], Collector as [Insamlare] from Applikations WHERE App_Producer='Adobe'') Thanks in advance Henrik Alstersjo You n...

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

Generate Database SQL
Can SQL Anywhere generate the create sql statements for all tables in a database? Thanks Yes! Use DBUNLOAD.EXE to extract the structure as SQL statements. Don't assume that it is correct, though. Give it a quick look just to make sure it got everything. In the past I've had problems with extracting stored procedures. -- Jim Egan [TeamPS] Dealer Solutions, LLC Houston, TX Stephen & Suzan Curtin <curtins@highway1.com> wrote in article <335FCD9E.1B32@highway1.com>... > Can SQL Anywhere generate the create sql statements for all tables in a > d...

Problem inserting sql query into database float datatype field using SQL Transaction
I have this problem of inserting my query into database field. My code is as of below. The @AVERAGESCORE parameter is derived from Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0) and the value returned is  (averagescore.toString("0.00")) However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?   Try Dim i As Integer ...

Need to back up & restore a SQL server database when only SQL 2000 and 2005 are available.
I am maintaining a .net 1.0 asp.net web application that accesses a SQL server 2000 database. I need to backup the database and then restore it on a laptop. Currently it resides on a server. The problem is that on the server they are using sql server 2005. If I backup using 2005 and then try and restore this database using SQL server 2000 or 2005 on the laptop, I get an error message. "Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally. What can I do to restore the database? Must it be backed up with SQL server 2000 ...

Generating xml file from table in sql database with desired structure(How to Generate it ,please explain with Source code or url)
Structure of table MenuID MenuName MainMenu CommandName URL 1 AR (Root  element)       2 Order Processing(Sub Item 1) AR     3 Enter & View Contract(Sub Item11) Order Processing Enter & View Contract ../../EnterpriseASPAR/ContractsHeader/ContractsHeaderList.aspx 4 Enter And View Quotes Order Processing Enter And View Quotes ../../EnterpriseASPAR/OrderHeader/QuoteHeaderList.aspx 17 Service Processing AR     18 Enter And View Service Orders Service Processing Enter And View Service Orders .....

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use not
Hello,          I receive this error  "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'&...

Querying AD and SQL Database
I am new to Active Directory and LDAP.  In the past I have always used forms based authentication and have the users in a database table, and now I am trying to write an application using ASP.NET 2.0 in C# that will use windows authentication and have all the users in Active Directory.Let’s say my application is a Helpdesk program and when a user enters a ticket, what would I use as the unique key for the user that entered the ticket when I add it to the database. In my previous experience I would just use the id from users table.  Now when I go to display the information abou...

Button Generate SQL query
hi all, i want to generate sql query on button click. i have a multiple table that s join together plz tell me how to do it ?? thanks in advance Can you please post the issue that you are facing or more details on what you are trying to achieve?   Thanks and RegardsS.K.Vignesh----------------------Do remember to "Mark as answer" for this post or the post that helped you solve the issue hi skvignesh , i have a criteria i.e table fields checkboxes dropdownlist textbox where user select it then user button press for generate sql query Plz help me Better that you s...

Running SQL Queries on Database
Does anyone know how i can extract the First Name, Last Name and email address from my DNN Database of anyone registered?  I need to do a mass email to my customers through another marketing program.  How do i run an SQL query? Thanks, Pete Try this... select    u.username,     u.firstname,    u.lastname,    m.Email  from     users U,     aspnet_membership m,     aspnet_users auwhere     U.username = au.username    and au.Userid = m.UseriD...

checkboxes to query SQL database
I am currently working on a project for school.  This project involves using checkboxes to query data. I am using an arraylist to get the checkboxes that are selected and therefore perform the query.  My problem is I don't know a lot about arraylists and I am struggling with the code. I hope someone can help me with this.  All I need is a bit of a jump start with a code sample. Thanks. I am not sure about check boxes but try the link below for sample ArrayList code.  Hope this helps.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemc...

Web resources about - Any SQL builder for generating queries available? - embarcadero.delphi.database

Formulas for generating Pythagorean triples - Wikipedia, the free encyclopedia
The following method is a direct algebraic manipulation of the Euclid equations. Start with any even integer , and use the relation from the ...

Ivanpah Solar Electric Generating System - Spring 2013 - a set on Flickr
... Commons Getty Collection Galleries World Map App Garden Camera Finder The Weekly Flickr FlickrBlog Upload Sign In Ivanpah Solar Electric Generating ...

Mario Lives! An Adaptive Learning AI Approach for Generating a Living and Conversing Mario Agent - YouTube ...
Mario Lives! An Adaptive Learning AI Approach for Generating a Living and Conversing Mario Agent Stephan Ehrenfeld, Fabian Schrodt, & Prof. Dr. ...

Generating media attention was the aim of New York Times attack: Akamai
Cyber attacks, such as the recent one on the New York Times, are more about gaining attention than causing damage, according to Akamai.

FourPlay review: Generating momentum with improvisation and surprise
From string quartet into a rock band 19 years ago and FourPlay is presenting `interesting compositions, with finely detailed arrangements', John ...

Men In Kilts generating cash and kudos
The Men In Kilts window cleaning concept might seem like a gimmick, but Tressa Wood's business smarts have the company cleaning up, and landed ...

Live blog of MagNet2013 Conference: Generating Publicity—The Right Way
Live blog of MagNet2013 Conference: Generating Publicity—The Right Way

Generating interest in homegrown power
Generating interest in homegrown power People's Daily Online ... are dealing with the preliminary paperwork. "The method was absolutely new ...

IHG generating over $20 million a month in revenue from mobile bookings
InterContinental Hotels' emphasis is on developing booking apps and mobile features to engage with guests in every step of their journey.

TIMELINE: Shutdowns at San Onofre Nuclear Generating Station over 13 years
This timeline of events looks at shutdowns at the San Onofre Nuclear Generating plant over the past 13 years.

Resources last updated: 1/21/2016 5:01:45 PM