CODEWITHSUNDEEP
c#mysql
Daniele Sartori
Daniele Sartori

Reputation: 1703

I need to add a tinyint value in the result of a SELECT

It's 2 days that i'm torned with this task and i can't really find a solution since i'm not an expert of MySQL. So basically i have this query

sql.Append("select distinct ");
sql.Append(@"{0}.field1,
             {0}.field2,
             {0}.field3,
             {0}.field4, 
             {0}.field5, 
             ---
             ---
             false as customField1,
             false as customField2,
             {1}.name as customfield3
             from {0} join {1} on {0}.field2check = {1}.id where field2check = ?condition");

this is the text of a select that i'm building in my c# class. This query is then completed and feeded to the database, that give me in response a table that is stored in a DataTable. My problem is that this fields

false as customField1,
false as customField2,
{1}.name as customfield3

are actually obtained after the evaluation of a condition and must return a tinyint(1) to be interpreted as boolean in my c# code, since the bool type don't exist in my sql. If i write directly 0 or 1, this value is casted as BIGINT that is interpreted as Int32 in c#.

I know that i can create a custom function to be stored in my db like 

CREATE FUNCTION myDb.x_cast_to_tinyint(number bigint) RETURNS tinyint(1)
return number;

But this is forbidden in my case (my boss simply don't want to save function in db). Now i was looking to create a temporary table to store the result of this query, but the problem is that i have no clue on how to do that. Something like

create temporary table myDB.res (customfield1 tinyint(1).... ) as (put the select here)

The problems here are 2. First i don't know how to define all the columns of this table, cause it should have the same structure of an existing table plus 3 custom columns. Even if i successfully create this table, how long does it last?. Can i query this table to select * from it?

I am open also to other solutions. If you need other details let me know

EDIT : forgot to mention this. The c# part should remain untouched. I thought to make a converter for those specific column of my datatable but also this solution received a big no in response (and i'm in part good with it, case my c# code should only represent the data, and shouldn't know anything about the structure of the database)

Upvotes: 1

Views: 663

Answers (1)

Cynical
Cynical

Reputation: 9568

You could try something like this, although I think it's more complex than adding a function:

CREATE TEMPORARY TABLE myTempTable LIKE theFirstTable;
ALTER TABLE myTempTable 
    ADD COLUMN customField1 TINYINT(1),
    ADD COLUMN customField2 TINYINT(1),
    ADD COLUMN customField3 TINYINT(1);
INSERT INTO myTempTable 
    SELECT o.*, customField1, customField2, customField3
    FROM originalTable o...

Obviously, you have to customize this approach to your needs, but the advantage is that temporary table will last until the connection is closed or you explicitly drop it.

Upvotes: 1

Related Questions