SQL Server, std::thread, error

hansaaa (115)
Hi, i got a method, filestodb()
int filetodb(std::wstring szwf, SQLHANDLESTR *h);


it executes sql commands, such as this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
std::wstring szwComGetCompetition = L"SELECT [CompetitionID],[Country],[Name],[SeasonID],[symID] FROM [a].[dbo].[Competitions] WHERE CompetitionID = '";
 szwComGetCompetition.append(szwcoid);
 szwComGetCompetition.append(L"'");

 std::wostringstream conv;
 conv << szwComGetCompetition.c_str();
 std::wstring wCmd(conv.str());
 WCHAR* wCCmd = const_cast<WCHAR*>(wCmd.c_str());
 
 int retcode = SqlExecSP(h, wCCmd);
 if (retcode != 0)
 {
  assert(false);
  throw(std::exception());
 }


where SqlExecSP calls SqlExecDirect(see http://msdn.microsoft.com/en-us/library/windows/desktop/ms713611(v=vs.85).aspx):

1
2
3
4
5
6
7
8
9
 
extern "C++" {
 __declspec(dllexport) RETCODE __cdecl SqlExecS(SQLHANDLESTR* h, WCHAR* wCCmd)
 {
  RETCODE     RetCode;
  _setmode(_fileno(stdout), _O_WTEXT);
  RetCode = SQLExecDirect(h->hStmt,wCCmd, SQL_NTS);
  return RetCode;
 }
}




so far, i have called filestodb like this:



1
2
3
4
5
6
7
8
9
10
11
 
SQLHANDLESTR *h = SqlConnectDBP();
std::vector<std::wstring> *filesP = new std::vector<std::wstring>;

///
fill filesP (vector)
///

for (int i = 0; i < filesP->size(); i++)
{
      filetodb(filesP->at(i), h);
}




This works, but, however filestodb executes a fair amount, of sql statements, and, also, does other things, that take a long time.


SO, i thought, i may be able benefit of parallelization/threads. So, now, i call filetodb, as:




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
SQLHANDLESTR *h = SqlConnectDBP();
std::vector<std::string>* filesP = new std::vector<std::string>


///
fill vector
/// 

for(int i = 0; i < filesP->size();)
 {
  std::wcout << "parsing file " << i+1 << std::endl;
  std::vector<std::thread*> ts;
  
  int j = 0;
  for (j = 0; j < 10; j++)
  {
   std::wcout << "starting thread " << i+j+1 << std::endl;
   std::thread *thread = new std::thread(filetodb, filesP->at(i+j), h);
   ts.push_back(thread);
  }
  
  for(j = 0; j < 10; j++)
  {
   
   ts.at(j)->join();
   delete ts.at(j);
  }
  i = i+j;
 }




The threads are created, and
it all works, but, now, the SQL statements

causes the SqlStatements to return -1 at:



1
2
3
4
5
6
 
int retcode = SqlExecSP(h, wCCmd);
 if (retcode != 0)
 {
  assert(false);
  throw(std::exception());
 }






This is a long story, i know, but anyone have any ideas, how this can be? is it the SQL Server, that can't handle all the statements, at once, or, what?

Thanks for any and ALL help!

Regards!

C
coder777 (8450)
I'd say that SQLExecDirect() is not thread safe and not intended to be use parallel
hansaaa (115)
I built in a "naïve" semaphore, in form of a bool, and, it SEEMS to work now.

Have to test, however


Cheers

coder777 (8450)
if you use a synchronizing mechanism, you serialize the execution. Then you may better create one thread and a loop for processing the command

See this:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms713563%28v=vs.85%29.aspx

how asynchronous execution works.

You need SQLAllocHandle() to make it work. See the examples there.
You may not even need the asynchronous mode, depends on how you implement it.
I'd suggest that you create one thread and start all SQLExecDirect() asynchron until they're not SQL_STILL_EXECUTING. a thread is a rather expensive resource
Topic archived. No new replies allowed.