SQL database expression evaluation for TurboBird, FlameRobin IBexpert
etcc.
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2011-08-03 14:35

Hi all,

I really wonder is there any workaround for the evaluation expression
for these free appliations.
If I use something like this In MSSQL in order to place just prefix
for combination field MSSQL successfully handles it. So to speak
parsing and evaluating the expression .
Code: [Select]select '1,2*'+combination , name from Any_Table

but if attempt to run the same expression in FlameRobin, TurboBird,
IBExpert personal edition I got the
Quote : PrepareStatement :
 -Dynamic SQL Error
 -SQL error code = -206
 -Column unknown
 -1.2*
 -At line 1, column 1
Any workaround for that ?
Regards,

----------------------------------------------------------------------
jixian.yang · 2011-08-03 14:45

In Firebird the SQL is different.
The '+' should be '||':
Quoteselect '1,2*'||combination , name from Any_Table

----------------------------------------------------------------------
Ronan · 2011-08-03 14:58

believe me I've tried with every possible variation single, double
quotes , '+' sign , char concatenation sign '||'. That Combination
field is char(10).

The table doesn't have primary key, Is that error could be related
somehow with that ?

----------------------------------------------------------------------
SteveF · 2011-08-03 16:00

If you were able to write the thing just as a straight string, what
should it look like?  What are the expected data types for all of your
columns? Code concatenation can be a tricky thing sometimes.

Steve

----------------------------------------------------------------------
Ronan · 2011-08-03 19:05

Simply that
Quoteselect '1,2*'+combination , name from Any_Tableand it perfectly
works in MS SQL managment studio , just adds prefix '1.2*' to the
existing fields (combination which is char(10) type )

Expected result is
Combination    Name
1.2*live           Combo1
1.2*dead        Combo2
1.2*self          Combo3

As far as I can understand these applications are not fully-fledged
apps intended for complicated usage and their parsing/evaluation
mechanism doesn't know how to deal with it.

----------------------------------------------------------------------
JimBeam · 2011-08-03 20:08

As Steve told you,first try your query in a Firebird tool before
trying it in code.
+ does not work for string concatenation. in fb, use ||
some wild guesses:
name might be a reserved word, try quoting it.
try an explicit cast of 1,2 to char

Repeating that it works on MS SQL Server doesn't magically let it work
on Firebird...

Away from computer but will try getting this working on fb when back.

----------------------------------------------------------------------
Ronan · 2011-08-04 06:00

Code: [Select]Repeating that it works on MS SQL Server doesn't
magically let it work on Firebird...That's for sure but at least
pointing out that this is applicable in firebird editors as well is a
good entry point and issue to delve deep into for me .

----------------------------------------------------------------------
JimBeam · 2011-08-04 06:35

Glad you took it the way it was meant: positively 

Using FlameRobin & this DDL to create the table:
Code: [Select]CREATE TABLE Any_Table
(
Combination CHAR(10),
Name CHAR(10)
);... inserted some sample data...
then:
Code: [Select]SELECT '1,2'||COMBINATION, NAME
FROM ANY_TABLE
This worked:
Code: [Select]1,2live           Combo1   
1,2dead         Combo2   
1,2dead         Combo3   
1,2comatose     Combo4   
1,2self         Combo5   

By the way, there's also a Firebird mailing list which is very
helpful; if we couldn't figure it out here, the people over there
probably could.
Firebird is a bit different than MSSQL but you might grow to like it!

----------------------------------------------------------------------
Ronan · 2011-08-04 07:09

thanks in advance, that will help