SQL ToString() Command

[Mike Reed]

Hi,
I have this short routine to try to put data into a string 'without' the Tab character.
Commands for pulling info the mysql table are:

QRY = "SELECT * FROM 1099T"
cn_exec = cn.Execute(qry)
rs = cn.ResultSet
datastring = rs.ToString()

This all works, BUT, the data string looks like this:
T 2015 861111111 88888
I need it to look like this:
T201586111111188888

H

Anyone know how do get rid of the 'tab' character in the string?

Thanks,
Mike

[JerryBrightbill]

Re: SQL ToString() Command

Try

cn.resultset.toString(-1,-1,.F.,"")

[GGandhi]

Re: SQL ToString() Command

you could use remspecial too
datastring =remspecial(rs.ToString())
should work.

[Mike Reed]

Re: SQL ToString() Command

Thanks,
Neither of the options will work because they delete out all the spaces. The spacing is important. This is a file going to the IRS. Fields are defined by which column they start in.

In the sample below here is how it works
Column 1 = Record Type
Column 2-5 = Calendar Year
Column 6 = blank
Column 7-37 = Business name
etc.

T2009 86111111137888 BUSINESS NAME DBA BUSINESS NAME BUSINESS NAME DBA

The tables are all character fields so that leading zeros can be in place, the field type is C and each one is exactly the length is needs to be for each field. So I have to be able to take each field in its entirety and place it into a text file to send the IRS.

Thanks for your advice.

Mike

[GGandhi]

Re: SQL ToString() Command

This all works, BUT, the data string looks like this:
T 2015 861111111 88888
I need it to look like this:
T201586111111188888

Neither of the options will work because they delete out all the spaces

they both answered the question you asked, not what you wanted.
however if the table contains the data you want and the result set to string may not be the one you need but you can fashion an output from result set itself
for example
dim txtToSendToIRS as c
txtToSendToIRS = rs.data(1) + " " + rs.data(2) + " " + rs.data(3) ... etc
or
txtToSendToIRS = rs.data("company") + " " + rs.data("field_name") + .... etc.,
since the data coming to you is exactly the length you want then you don't need to pad it or you can pad the desired length and format the string.

[lvasic]

Re: SQL ToString() Command

Can you pull it out of SQL like this? "Select concat(RecordType,CalYear,space(1),'86111111137888',space(1),BusinessName)"

[peteconway]

Re: SQL ToString() Command

I love your attention to detail Govindan

[Mike Reed]

Re: SQL ToString() Command

Thank you all for replying. There are actually 6 different files that I have to pull information from and most of them have upwards of 50 fields. So I could use the methods described above. But because spacing is critical it makes it difficult to get everything perfect. When the IRS does file format changes, then it means a lot of work to reformat. Add to that some very long code sequences. So what I decided to do, was to import the data into .dbf tables. Each field in the table is the exact length it needs to be for the file. From there I can use the simple export to table operation and get exactly what I needed.

Didn't really want to do that, as I wanted to keep everything in MySQL tables. But the old .dbf tables along with some Alpha functions made the process much easier to write and to maintain for the future.

Thanks again,

[GGandhi]

Re: SQL ToString() Command

I love your attention to detail Govindan

thanks, pete, very nice of you.
have a great day.

[GGandhi]

Re: SQL ToString() Command

Thank you all for replying. There are actually 6 different files that I have to pull information from and most of them have upwards of 50 fields. So I could use the methods described above. But because spacing is critical it makes it difficult to get everything perfect. When the IRS does file format changes, then it means a lot of work to reformat. Add to that some very long code sequences. So what I decided to do, was to import the data into .dbf tables. Each field in the table is the exact length it needs to be for the file. From there I can use the simple export to table operation and get exactly what I needed.

Didn't really want to do that, as I wanted to keep everything in MySQL tables. But the old .dbf tables along with some Alpha functions made the process much easier to write and to maintain for the future.

Thanks again,

i am glad that you got it sorted out, but still to me it is confusing. the computers are here to do the boring job of doing repeated tasks, it looks like you are doing all manual. while it is your decision i am wondering.
the result set is coming from the sql query.
and you know you can convert that to a string. now the problem is to format that string to IRS liking.
so why not do the following:
use remspecial to remove all tabs. now you have one long string without any spaces.
write an xbasic function that will take the string as an input and output the string the formatted way IRS wants using any of the string formatting function alpha provides, like word, substring etc.,.
then you will have the string formatted with one function and when IRS changes the format it is simple matter of changing the xbasic function.
so you are now basically delegating the boring job to the computer which is why they are there for.

this is only thinking, i am sure your way is probably way better and meets your needs.