Thread: foxpro, odbc, data types and unnecessary convertions
Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = "some sql" + alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = "some_column = ?foxpro_variable ". The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do? Thanks.
Fernando Moreno <azazel.7@gmail.com> writes:
> For numbers, I have to convert them first to string and then remove
> the spaces, the code looks like this: sql_string = "some sql" +
> alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and
> str in a third function but it's still tricky. A shorter and
> presumably better way to do the same is: sql_string = "some_column =
> ?foxpro_variable ". The problem with the last option is that, watching
> the pgsql log, values are sent this way: '12345'::float(8), so for
> every numeric value, no matter its type, I'm sending 12 characters
> more and the server is doing convertions that I don't need.
> Having a lot of foreign keys and other numeric data, I think this
> behaviour is not so good for network (remote and poor connection) and
> server performance. I'm almost decided to keep doing the trim/str
> thing, but my question is: am I exaggerating? what would you do?
You're obsessing over an issue that is almost certainly not going to
make a measurable difference. You can probably improve your application
performance a lot more by expending the same effort somewhere else.
regards, tom lane
Fernando Moreno wrote: <blockquote cite="mid:b1c45530902251340r6b62cd0u9da123e0d91e9356@mail.gmail.com" type="cite"> Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. i like foxpro it has its quirks as do all languages. Only concern if this is a new app Foxpro has been killed by MS. Version 9 is the last so no 64 bit support and at the mercy of MS to keep 32 bit support working down the road <blockquote cite="mid:b1c45530902251340r6b62cd0u9da123e0d91e9356@mail.gmail.com" type="cite"> For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = "some sql" + alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and Why the alltrim ? white spaces don't hurt. take a look at Text EndText with TextMerge it has a few gotchas here and there but on complex sql string it makes life allot easier. <blockquote cite="mid:b1c45530902251340r6b62cd0u9da123e0d91e9356@mail.gmail.com" type="cite"> str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = "some_column = ?foxpro_variable ". don't use ? its from the DOS days, it does some odd conversions because it is a hold over <blockquote cite="mid:b1c45530902251340r6b62cd0u9da123e0d91e9356@mail.gmail.com" type="cite"> The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do
Completely forgot take a look at Cursor Adapter Class, also any cursor in foxpro can be made be updateable with CURSORSETPROP( ) function removing the problem of writing Update's and Inserts Fernando Moreno wrote: > Hi all, I'm using visual foxpro 9 -not my decision- for a client > application. Statements are writen as the typical sql string and sent > through ODBC. > > For numbers, I have to convert them first to string and then remove > the spaces, the code looks like this: sql_string = "some sql" + > alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and > str in a third function but it's still tricky. A shorter and > presumably better way to do the same is: sql_string = "some_column = > ?foxpro_variable ". The problem with the last option is that, watching > the pgsql log, values are sent this way: '12345'::float(8), so for > every numeric value, no matter its type, I'm sending 12 characters > more and the server is doing convertions that I don't need. > > Having a lot of foreign keys and other numeric data, I think this > behaviour is not so good for network (remote and poor connection) and > server performance. I'm almost decided to keep doing the trim/str > thing, but my question is: am I exaggerating? what would you do? > > Thanks. > >
Thank you very much for your advice, I guess I'm wasting my time in this 'problem'. I'm going to check that class, it seems pretty useful. And by the way...yes, this is a born-dead app (at least on the client side) and it's likely to be ported to .NET in the future, but like I said before, it's not my call. Cheers.