How do you create a SQL query and insert text with carriage returns in
code?
lazarus / 16-Databases

----------------------------------------------------------------------
vfclists · 2012-11-20 17:24

How do you create a string containing carriage returns and line feeds
that can be inserted into an SQL database?
I want to sent it in a function. eg say InsertData() is the function,
so it will be something like:
InsertData(a: integer. b: TDataTime; c: string);
using  a call like InsertData(10, today(), 'this should be a string
with cr and lf in it')

Because the code is for storing configuration defaults  it should be
as readable as plain text. I would even prefer to create the SQL
command directly and pass it to ExecSQL if Pascal had heredoc syntax.

----------------------------------------------------------------------
denver · 2012-11-20 17:44

You can use Parameters:

example :

SQLQuery1.SQL.Add( 'insert into tablemsg ( msgid, textdata ) values (
:id, :textdata ) ' )  ;
SQLQuery1.ParamByName( 'msgid' ).AsString := 'testmessgae' ;
SQLQuery1.ParamByName( 'textdata' ).AsString := Memo1.Lines.Text ;
SQLQuery1.ExecSQL ;

----------------------------------------------------------------------
vfclists · 2012-11-20 20:57

The code is not coming from controls. It should be plain text and
suitable for version control. It is all being gathered in a file. It
is the default configuration code for a program.

Quote from: denver on November 20, 2012, 06:44:52 pmYou can use
Parameters:

example :

SQLQuery1.SQL.Add( 'insert into tablemsg ( msgid, textdata ) values (
:id, :textdata ) ' )  ;
SQLQuery1.ParamByName( 'msgid' ).AsString := 'testmessgae' ;
SQLQuery1.ParamByName( 'textdata' ).AsString := Memo1.Lines.Text ;
SQLQuery1.ExecSQL ;

----------------------------------------------------------------------
goodname · 2012-11-21 03:03

Parameter queries do not depend on controls they require the string or
other data type used to set the parameter. In your case the string
comes from or is saved to a file.

If you really cannot use parameters then I suggest that you find out
how your database engine handles special characters in SQL strings.
Build the SQL string and assign it to TSQLQuery.SQL.Text

----------------------------------------------------------------------
teos · 2012-11-21 09:46

does 'insert into table where line has ' + #10#13 + ' for linebreak'
like I write it here work for you?

----------------------------------------------------------------------
BigChimp · 2012-11-21 10:56

Quote from: vfclists on November 20, 2012, 06:24:13 pmHow do you
create a string containing carriage returns and line feeds that can be
inserted into an SQL database?
What have you tried? A string with carriage return/linefeed in it
should just work, as in teos' example, as far as I know...

(Incidentally, I'd suggest denver's/goodname's parameterized solution
as well. By using hardcoded values you're going to have fun when
you're inserting date values from files... especially if you ever
change databases. Parameterized queries just take away that problem
for you).

Describing what you want to achieve at a higher level of abstraction
may help us give some more suggestions...

Thanks,
BigChimp

----------------------------------------------------------------------
vfclists · 2012-11-21 19:22

It is for storing and creating default configurations in a database.
Having it in code makes it easier to reset the defaults and it should
also be as descriptive as possible. The code that documents the
settings defaults should be the same code that creates them. It will
also make version control easier.  It will also make updating existing
installations easier.
After struggling with using the database approach this way it will be
easier this way. Checking what changes have gone into the default
databases gets harder over time.

Quote from: BigChimp on November 21, 2012, 11:56:25 am
Describing what you want to achieve at a higher level of abstraction
may help us give some more suggestions...

Thanks,
BigChimp

----------------------------------------------------------------------
vrull · 2012-11-21 19:50

I always separate Pascal code from other possible languages. So SQL
queries, HTML templates and other similar stuff is kept in so-called
'SQL library', which is either a table or XML file. There is a single
function to read the query from SQL Library:
Code: [Select]sQuery := GetSQLQuery('queryName');where sQuery and
function type are TString. It is also a single function to insert user
data into query text, you may either fill up parameters or substitute
placeholders with real values, as you like.
Then a single function executes the query and takes care of
exceptions, table locks, writing to a log file/table and so on.

Queries are usually pre-formatted and tested in SQL Management Studio,
look nice and can be of any complexity. If business logic changes,
usually there is no need to change Pascal code and deploy a new
executable, only SQL queries are affected.

This is a concept only, details can be discussed later.

----------------------------------------------------------------------
SunyD · 2012-11-21 22:19

Use LineEnding.
Code: [Select]SomeSQLText:='SELECT * FROM TABLEX '+LineEnding+
                        'WHERE X=10'+LineEnding+
                       'ORDER BY YXD';

----------------------------------------------------------------------
BigChimp · 2012-11-22 07:54

Quote from: vfclists on November 21, 2012, 08:22:36 pmIt is for
storing and creating default configurations in a database. Having it
in code makes it easier to reset the defaults and it should also be as
descriptive as possible. The code that documents the settings defaults
should be the same code that creates them. It will also make version
control easier.  It will also make updating existing installations
easier.
Hmmm, if you're separating out SQL into file and the code that reads
it/applies it you're separating out the comments/documentation as well
I'd think?
Are you saying you want to have the comments in your database in a
separate

An alternate view: what's wrong with putting everything in code and
using a database version number table (with db schema verson and
application version used):
Config will be in version control anyway (surely you use version
control for your application)Commenting will be easierAnyway, I'm sure
you figured it out meanwhile...