Embedded SQL

[Spag]

I'm knocking up a program in Borland C++ Builder.

I'm trying to keep Database calls to a minimum to keep things fairly fast

MSSQL->SQL->Add("UPDATE student SET accessed = (accessed + 1) WHERE Code='" + Edit1->Text + "'" );
MSSQL->Execute();

But at this point I also want to retrieve a field, and return it to the user


I feel I want to do something like :

MSSQL->SQL->Add("UPDATE student SET accessed = (accessed + 1) WHERE Code='" + Edit1->Text + "' Score=:score" );
MSSQL->Execute();
ShowMessage(score);


any ideas anyone ?

Thanks - Spag

[Spag]

MSSQL->SQL->Add("UPDATE student SET accessed = (accessed + 1) WHERE IN (SELECT Score INTO :Score FROM URLs WHERE Code='" + Edit1->Text + "' " );
MSSQL->Execute();

ShowMessage(MSSQL->ParamByName("Score")->AsString);

[Spag]

So Gave up trying to update the record at the same time and am just trying to retrieve a parameter. Got here, getting nothing in the ShowMessage box - though MSSQL->Params->Count is reporting that 1 parameter is returned.


MSSQL->SQL->Add("SET :Score= (SELECT Score FROM class WHERE student='" + Edit1->Text + "')");

MSSQL->Execute();

for(int i = 0 ; i Params->Count ; i++)
ShowMessage(MSSQL->Params->Items->AsString) ;


Anyone got any ideas ?

[Spag]

It was to do with pre-allocating the bind parameters or summut

Thanks
Spag

[coool]

Maan this SDAC is soo cool - I love it

YaY

[Ikar]

Try to set Param.ParamType = ptInputOutput

[Spag]

That's it entirely Ikar, you are right (as usual ).

But I went the whole hog and did :

MSSQL->Params->CreateParam(ftVariant, "Student", ptResult);
MSSQL->SQL->Add("SET :Student = (SELECT Student FROM Class WHERE (Code=123))");
MSSQL->Execute();
ShowMessage(MSSQL->Params-FindParam("Student")->AsString);

kinda

works, but dunno if it's the right way

Spag

[Ikar]

You'd better use:

Code: Select all

 MSSQL->SQL->Add("SET :Student = (SELECT Student FROM Class WHERE (Code=123))");
 MSSQL->Params[0]->ParamType = ptInputOutput;
 MSSQL->Params[0]->DataType = ftInteger;
 MSSQL->Execute();
 ShowMessage(MSSQL->Params-FindParam("Student")->AsString);

[Spag]

Both the

MSSQL->Params[0]->ParamType = ptInputOutput;
MSSQL->Params[0]->DataType = ftString;

Generate :

[C++ Error] MForm.cpp(52): E2288 Pointer to structure required on left side of -> or ->*
[C++ Error] MForm.cpp(53): E2288 Pointer to structure required on left side of -> or ->*

Changing it to :

MSSQL->Params->Items[0]->ParamType = ptInputOutput;
MSSQL->Params->Items[0]->ParamType = ftString;

generates :

[C++ Warning] MForm.cpp(53): W8018 Assigning TFieldType to TParamType

in the second line.

dunno
Spag

[Spag]

MSSQL->Params->Items[0]->ParamType = ptInputOutput;
MSSQL->Params->Items[0]->ParamType = ftString;

Should be

MSSQL->Params->Items[0]->ParamType = ptInputOutput;
MSSQL->Params->Items[0]->DataType = ftString;

compiles perfectly then

[Spaggie]

Thats cool Ikar, what you said works great once I put the Params object in properly (Delphi/Builder VCL addressing differences)

So exactly what you suggested but :

MSSQL->SQL->Add("SET :Student = (SELECT Student FROM Class WHERE (Code=123))");
MSSQL->Params->Items[0]->ParamType = ptInputOutput;
MSSQL->Params->Items[0]->DataType = ftInteger;
MSSQL->Execute();
ShowMessage(MSSQL->Params-FindParam("Student")->AsString);


Lovely
Spag

[Spag]

Code: Select all

MSSQL->SQL->Add("SET :Student = (SELECT Student FROM Class WHERE (Code=123))"); 
 MSSQL->Params[0]->ParamType = ptInputOutput; 
 MSSQL->Params[0]->DataType = ftInteger; 
 MSSQL->Execute(); 
 ShowMessage(MSSQL->Params-FindParam("Student")->AsString);

Hi,

Sorry to ask this but I cant work it out.

What's the syntax for multiple returns ?

Is it something along the lines of :

Code: Select all

MSSQL->SQL->Add("SET :Student :Course= (SELECT Student, Course FROM Class WHERE (Code=123))"); 
 MSSQL->Params[0]->ParamType = ptInputOutput; 
 MSSQL->Params[0]->DataType = ftInteger; 
 MSSQL->Params[1]->ParamType = ptInputOutput; 
 MSSQL->Params[1]->DataType = ftInteger; 
 MSSQL->Execute(); 
 ShowMessage(MSSQL->Params-FindParam("Student")->AsString);

Thanks
Spag

[Ikar]

Code: Select all

MSSQL->SQL->Add("SET :Student= (SELECT Student FROM Class WHERE (Code=123));"); 
MSSQL->SQL->Add("SET :Course= (SELECT Course FROM Class WHERE (Code=123))"); 

Read MSDN for details

[Spag]

Thanks Ikar