Page 1 of 1
Embedded SQL
Posted: Fri 08 Apr 2005 15:19
by 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
Right, I'm somewhere about here now but getting errors
Posted: Fri 08 Apr 2005 15:53
by 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);
going round in circles
Posted: Sat 09 Apr 2005 09:47
by 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 ?
Oh I think I've sussed it
Posted: Sun 10 Apr 2005 09:31
by Spag
It was to do with pre-allocating the bind parameters or summut
Thanks
Spag
...
Posted: Sun 10 Apr 2005 10:42
by coool
Maan this SDAC is soo cool - I love it
YaY
Posted: Mon 11 Apr 2005 07:26
by Ikar
Try to set Param.ParamType = ptInputOutput
Yeah,
Posted: Mon 11 Apr 2005 08:28
by 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
Posted: Mon 11 Apr 2005 11:00
by 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);
Errors
Posted: Mon 11 Apr 2005 12:19
by 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
My Mistake
Posted: Mon 11 Apr 2005 12:25
by 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
Yep,
Posted: Mon 11 Apr 2005 12:29
by 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
Multiple return parameters
Posted: Wed 25 May 2005 06:54
by 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
Posted: Wed 25 May 2005 07:11
by 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
Thanks
Posted: Wed 25 May 2005 12:40
by Spag
Thanks Ikar