[soci-users] Support of MSSQL Server
I need to support MSSQL Server with proper Microsoft UNICODE usage (nvarchar column etc.) from UCS2 to UTF8 and reverse, cause MSSQL Server is the only one database engine, the is still not able to store/retreive UTF8 (even if the actual server is 2012)! My plan is to clone the ODBC backend and come up with a first version of a new MSSQL interface. Later on i would modifiy it to use the native client capabilities for better performance. This also will come up with conversation helper from UTF16 (UCS2) to UTF8 and reverse. But there is a global issue doing so. I need the extends the core with support of #define SQL_WCHAR (-8) #define SQL_WVARCHAR (-9) with a new core data_type dt_wstring and exchanged to std::wstring. Does somebody have had a problem doing so? I'm comming from the Windows world so any objections in terms of Unix world are welcome upfront. Should the support additionally enrolled to any other backend too if the backend is able to support such types using std::wstring? regards Heiko-- This SF.net email is sponsored by Windows: Build for Windows Store. http://p.sf.net/sfu/windows-dev2dev___ soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
Re: [soci-users] Support of MSSQL Server
Hi Sergey, I'm interested into the modified version, you can send me a zip file if you want. In meantime i have done a complete implementation of a second string type (std::wstring) beside the existing std::string type. If have tested it with vectors, use/into and also with row and rowset support. I would prefere to have this as additional type beside the standard one to have full control over converting. Otherwise we have to introduce a new dependency to one of the conversion libs out there. Furthermore the problem at MSSQL Server is: example case 1: varchar(50) column You have to know wether the collation inside is "ISO-8859-1" or any other to deal correctly with the std::string data because it's simply ascii at the given encoding. example case 2: nvarchar(50) column You have to interprete at MSSQL the result as UCS2 coded widestring (std::wstring) and you are now reposible to convert to requested format. I'm not a friend of behind the scenes conversions, because this may always lead to unpredictable results in future or real live systems. I have tested conversions with help of Boost Library as follows: example case 1: varchar(50) column string utf8_string = boost::locale::conv::to_utf(names[i],"ISO-8859-1"); wstring wide_string = boost::locale::conv::to_utf(names[i],"ISO-8859-1"); example case 2: nvarchar(50) column std::string utf8_string = boost::locale::conv::utf_to_utf(names[i]); This works properly as expected. At first case you need to know exactly the collation of the column to get a qualified converted utf8. So if you have to deal with N databases, all having different ANSI encodings (ru,pl,ger, sv, etc) then you need a qualified convert. If anyone have good reasons for doing implicite converts, please let me know. Keep in mind, that above converting is portable and WideCharToMultiByte() stuff isn't. So I would delegate (rarely) necessary convert to the enduser of library. BTW: Some C++ middleware code may internally use always std::wstring management because of sorting, searching, up/downcases and special sorting like Lexicograpical sorting. (lex sorting: as example the german 'ΓΌ' will not be sorted to the end of alphabet but within the 'u', this also happens at svenska and other languages) regards Heiko PS: a push to github will follow during this week, that's my current timeline so far. > Sergey Stepanov hat am 24. Juni 2013 um 15:50 > geschrieben: > > > > Hi, Everyone > > If you really interested, I have fully working version of the ODBC backend > with Unicode fields implemented. > I can share the code, but it would be really cool if someone will help me > pushing those changes to SOCI repository. > In my implementation I used UTF8 based columns support with translation to > MSSQL`s UCS2 and back.-- This SF.net email is sponsored by Windows: Build for Windows Store. http://p.sf.net/sfu/windows-dev2dev___ soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
[soci-users] unsupported BLOB vector support @ ODBC
Beside the implementation of MSSQL unicode support over std::string I stumbled over the unsupported BLOB capabilities of SOCI in terms of ODBC. We would use SOCI as replacement ina 3.2M codelines middleware project but we have to check carefully all capabilities upfront. SOCI is still within the scope (beside other libraries) but after solving partly MS unicode things we have to deal with blobs too. It's not possible to fetch them over ODBC by vectors, any other way is not suiteable because of performance. So I did some tests to provide BLOB reading/writing using vectors and it seems, that it can be handled same straight forward than currently done with std::wstring (but with special handling to buffer lazy allocation after knowing the real size per BLOB). In the MSSQL world I can handle the BLOB field with SQL_C_BINARY but I'm afraid, this is only half the story. I would prefere some more input in terms of BLOB utilization at other systems using ODBC prior to define it fix for a 2nd additional base type: x_binary (beside the existing x_blob) with a more straight forward class wrapping an unsigned char vector as content holder. I haven't looked yet though the other backends (beside ODBC) but I think, it could be handled there more or less similar at the end. I appreciate any input for BLOB handling to avoid getting a small tunnel view :-) regards Heiko -- This SF.net email is sponsored by Windows: Build for Windows Store. http://p.sf.net/sfu/windows-dev2dev___ soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
Re: [soci-users] unsupported BLOB vector support @ ODBC
Currently I have found a possible solution for ODCB layer.
But it has a limitation while bulk fetching blob data. Any known C++ bulk blob
select I have seen so far works only with fix sizes of blobs.
Thats the only way to bind it into vectors successfully.
example APP code from my local working version:
records = 0;
int rows_per_fetch = 50;
vector primary(rows_per_fetch);
vector sequence(rows_per_fetch);
vector name(rows_per_fetch);
vector content(rows_per_fetch, 32000); //32000 bytes per blob =
1.52 MB in total for blob buffer
vector ind(rows_per_fetch);
statement st2 = (sql.prepare << "select rc_sequence_id, sequence, name,
content from rc_sequence order by rc_sequence_id, sequence DESC",
into(primary),
into(sequence),
into(name,ind),
into(content)
);
st2.execute();
while (st2.fetch())
{
//because last roundtrip may have shrinked the vector(s)
for (size_t i=0; i an can be used like an ordinary vector of bytes.
It also permits the definition of fix sized blobs by utilize the resize (fill)
constructor. Internally the buffer will be defined using the vector items sizes.
For simple blob reads this should deliver the full size of blob without such
limitation. (fetching row by row, complete blob size for very large blobs).
It also avoids to put the session into the blob constructor as the current
backend class does.
I will have to check, if it's possible to do this for all scenarios/backends and
managing the special blob handling (oracle etc.) behind the scenes and not at
construction.
In my opinion no data represention object (string, blob, integer etc.) have to
know its session, this is not the right place of responsibility.
I know, that this bulk usage may truncate very large blobs. But if the DB / APP
systems works with max limited BLOB's in sequences (like we do), it's suitable
to
speedup fetching using fixed size blob reads.
Any response welcome, fork of 3.2.1 version follows soon with my modifications
(directly inside ODBC layer for now).
regards
Heiko
> "[email protected]" hat am 25. Juni 2013
> um 17:58 geschrieben:
>
> Beside the implementation of MSSQL unicode support over std::string I
> stumbled over the unsupported BLOB capabilities of SOCI in terms of ODBC.
> We would use SOCI as replacement ina 3.2M codelines middleware project but we
> have to check carefully all capabilities upfront.
> SOCI is still within the scope (beside other libraries) but after solving
> partly MS unicode things we have to deal with blobs too.
> It's not possible to fetch them over ODBC by vectors, any other way is not
> suiteable because of performance.
>
> So I did some tests to provide BLOB reading/writing using vectors and it
> seems, that it can be handled same straight forward than currently done with
> std::wstring (but with special handling to buffer lazy allocation after
> knowing the real size per BLOB).
> In the MSSQL world I can handle the BLOB field with SQL_C_BINARY but I'm
> afraid, this is only half the story.
> I would prefere some more input in terms of BLOB utilization at other systems
> using ODBC prior to define it fix for a 2nd additional base type: x_binary
> (beside the existing x_blob) with a more straight forward class wrapping an
> unsigned char vector as content holder.
>
> I haven't looked yet though the other backends (beside ODBC) but I think, it
> could be handled there more or less similar at the end.
>
> I appreciate any input for BLOB handling to avoid getting a small tunnel view
> :-)
>
> regards
>
> Heiko
>
>
--
This SF.net email is sponsored by Windows:
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev___
soci-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/soci-users
Re: [soci-users] Basic SELECT not working.
st.got_data() simply returns the current member and doesn't any action.
If your request expects only 1 record returned back, than you are at this
secanios:
1.) only st.got_data() -> false because nothing executed so far
2.) st.fetch(); st.got_data() -> false too because fetch() returned true for the
one record and got_data() still false because it's no bulk.
try this:
st.execute();
if (st.fetch())
{
printf("well done!");
}
else
{
printf("sucks again!");
}
> Nicolas Deroche hat am 26. Juni 2013 um 16:16
> geschrieben:
>
> And the version like this, isn't working either:
> sql.once << "";
> if(!sql.got_data())
> {
> /// throws
> }
>
>
> 2013/6/26 Nicolas Deroche mailto:[email protected]> >
>> >
> >Well using the fetch function right after execute is not working either
> >
> >dbs::Server* server = new dbs::Server();
> >soci::statement st =
> >(sql.prepare << "SELECT linkname, name, engine, listening_ip,
> > listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
> > FROM `" << Server::table_name << "` WHERE `linkname`=':ii'",
> >soci::use(linkname, "ii"),
> >soci::into(server->linkname),
> >soci::into(server->name),
> >soci::into(server->engine_type),
> >soci::into(server->listening_ip),
> >soci::into(server->listening_port),
> >soci::into(server->auth_id),
> >soci::into(server->auth_pw),
> >soci::into(server->max_clients),
> >soci::into(server->parent_linkname),
> >soci::into(server->childs),
> >soci::into(server->id));
> >st.execute();
> >st.fetch();
> >
> >if(!st.got_data())
> >{
> >std::cout << "SQL = SELECT linkname, name, engine, listening_ip,
> > listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
> > FROM `" << Server::table_name << "` WHERE `linkname`='" << linkname << "'"
> > << std::endl;
> >delete server;
> >throw Server::does_not_exist();
> >
> >}
> >else
> >{
> >Server::gets().insert({server->linkname, server});
> >server->init_load();
> >return *server;
> >}
> >
> >
> >2013/6/26 Mateusz Loskot mailto:[email protected]>
> > >
> > > > > On 26 June 2013 15:02, Nicolas Deroche < [email protected]
> > > > > > wrote:
> > > > The query should return only one row, is t really necessary ?
> > > >
> > > Please, check the docs. It's all there what calls are necessary.
> > >
> > > Best regards,
> > > --
> > > Mateusz Loskot,
> > >
> > >
> > >
> > > --
> > > This SF.net email is sponsored by Windows:
> > >
> > > Build for Windows Store.
> > >
> > >
> > >
> > > ___
> > > soci-users mailing list
> > > [email protected]
> > >
> > >
> > >> > >
--
This SF.net email is sponsored by Windows:
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev___
soci-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/soci-users
