Lan Version: 7.5.1

When it comes to writing new code from scratch I am a novice in SalesLogix.

Ultimately what I export will be quite different than the example I am trying but for now I am trying to get all records from the acount table and write them to a file. I have already created an object on a new toolbar, I just need help with the code.

 I first created a New Plugin (vbscript) and the family I used was system.

In the following code if I use sub Main & don't attempt to get all account records I can succesfully write to a file if using something like ts.Writeline "Test". However, if I do attempt to get all records then I get an error stating that SLX_DB is undefined.

In the same code if I use sub SLX_Export_G3_Tables & attempt to get all records I do not get this error, but I am not able to write anything to a file and the msgbox statements will not work.

Help.

The code:

<p>
option explicit

'sub Main

sub SLX_Export_G3_Tables

Dim objSLXDB

Dim objAccountRS

Dim strSQL

 

Set objSLXDB = New SLX_DB

Set objAccountRS = objSLXDB.GetNewRecordset()

strSQL = "SELECT * FROM ACCOUNT"

objAccountRS.Open strSQL, objSLXDB.Connection

'msgbox strSQL

'msgbox "G3 Test"

Dim sFileName

Dim fso, ts

sFileName = "C:\G3\G3testfile.txt"

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.CreateTextFile(sFileName, True)

ts.WriteLine strSQL
 'ts Writeline "Test"

ts.close

Set ts = Nothing

Set fso = Nothing

 

end sub

 

 

Scott,

I don't know the nature of your export, but wouldn't it be easier to click the "Export to Excel" button that already exists in the toolbar?  If the contents of the group tab is missing or has extra field fields you can create a custom group tab with all your required fields.  I find this much easier for most requests like this.  The nice part is it's repeatable and anyone or no one can be given access to the tab.

John

Thanks much for your reply.

Yes. That would be easier.

However, in the end we are trying to automate this so that a specific SDF formatted file, from the account table & some other custom tables, will automatically dump to a specific directory for another user to grab and import into another database. At times this dump may need to be done very frequently. We are trying to save time by eliminating a couple of steps. Plus it is very possible that we may need to manipulate some of the exported data at a later time.

Again, I don't know what is trying to be accomplished or what tools or languages are in use.  What you describe sounds very cumbersome.  There are probably better ways to accomplish this using different tools.  Maybe SSIS, Scribe or some other data transformation tool?  What are you using now after the export?

John

Actually I was hoping to do all the formatting in the SQL statement in the vbscript plugin so that once I pressed the button it does the export and is fomatted the way it needs to be so it is immediately ready for import into the other database. I don't think I will have a problem with this part of it since I have changed other pre-written code. I just need to get this started code working properly.

However, I am open to another way of doing this if it is easier and if I have experience using the other said tool(s).

Thanks.

1. Most of this stuff is Non SLX specific and is readily available on the web.

2. Anytime you talk ADO you are speaking about Sussman's ADO book v 2.60. Invaluable, indispensable, and a MUST HAVE.

3. Google "ADO Export to a text file" and see what comes up:

Open "c:\authors.dat" For Output As #1
' the last argument tells how Null should be rendered
Print #1, rs.GetString(, , ",", vbCrLf, "");
Close #1

The problem with this simplicistic approach, however, is that very large Recordset - with thousands of rows or more - generate very large strings, which impact on performance. A better way to create the file is to split the Recordset in smaller blocks of, say, one hundred rows:

Dim rs As New ADODB.Recordset' open the recordset (use a valid connection string for your system) rs.Open 

Open "c:\authors.dat" For Output As #1 

Do Until rs.EOF 

Print #1, rs.GetString(, 100, ",", vbCrLf, "")

LoopClose #1

    Print #1, rs.GetString(, 100, vbTab, vbCrLf, "");

OR

r.Open "Select * from jobs", j, 1 'open recordset

Open App.Path & "\test.txt" For Output As #1 'open text file

Do While Not r.EOF 'run the loop till end of file

For i = 0 To r.Fields.Count - 1
s = s & "," & r(i) 'concatenate the field values into string
Next
Print #1, s 'write to text file

r.movenext 'go to next record
Loop

Close #1 'close text file
End Sub

Scott, as others have mentioned, your method is not the best approach. Not knowing whether your database contains 1 row of 1M could ultimately doom this to failure! What you are suggesting is essentially "take EVERYTHING and throw it into a file" as a datastream. Even if this does work - you've got almost zero chance of having anything the other side understand what you did!

However, to answer your question directly - I wish you all the best with the rest of it !

1) Ensure you include the SLX script "System:SLX Database Support" - this is the cause of the 1st issue - SLX DB is undefined (it's defined in this script, and when included - it sets up many of the functions you'll need)
2) You do not have a loop
3) ts.WriteLine strSQL - will do exactly that - your file will end up with "SELECT * FROM ACCOUNT" in it (which isn't what you were expecting!)
4) You're not handling any form of formatting (but, I guess you'll do that later right?)
5) Beware BLOBS. The Account table contains a column called NOTES. This is a BLOB/MEMO field - and contain upto 2GB of data ! XL will just panic with that
6) If you have more than XXX rows - beware XL97 will not handle 32,767 rows (vastly increased in later versions - but still limited)

As for your general code, you'll need to alter this as follows:

#Include: SLX Database Support, SLX Error Support

    Dim objSLXDB, objRS, strSQL, i
    Set objSLXDB = New SLX_DB

    Set objRS = objSLXDB.GetNewRecordSet                ' Create an object (objRS) containing result set of sql supplied

    strSQL = "Select ACCOUNT From ACCOUNT Where TYPE = 'Active'"        ' sql you want to execute

    objRS.Open strSQL, objSLXDB.Connection                ' Open a connection, execute SQL and name it objRS

    With objRS                                                ' Work with object - means you can just use "." instead of entire name
         For i = 0 To .RecordCount - 1                        ' Loop
             If Not (.BOF And .EOF) Then                ' Check not at end/beginning

                msgbox .Fields("ACCOUNT").Value                ' do something here e.g.

                  ts.WriteLine .Fields("ACCOUNT").Value

                .MoveNext                                ' Move to next record
             End If
            Next
    .Close
    End With

    Set objRS = Nothing                                        ' Clean up 

The above executes a SQL command, gets a recordset, uses a loop to work through the returned RS and then does something of your bidding. If it were me, I'd specify the cols required and then perhaps loop through those columns, using an index (instead of a name) e.g. ts.WriteLine .Fields(ix).Value where ix is the index of the column.

I think you have a way to go before this'll begin to look usable. But, the above will at least get you started !

Regards
Mike

This is exactly what I needed. Now I can change the SQL statement to grab the accounts and other tables I need & to change it to the format that is needed by the other DB.

Thanks a lot.

Thanks for the book reference I will check into it.

Last time I was in a Fry's in Lombard, IL it was there......an oldie but a GOODIE!

I'd be tempted to do all of this in SSIS rather than code it in the SLX client - then you can easily just schedule the server to run it automatically rather than having to kick it off manually. One consequence of doing that would be an increase in performance.

Briefly, Where do I exactly go & how would I go about setting an automated schedule to do somethng like this?

If you were to do this in SSIS you can create a schedule to run the package at regular times.

There is nothing in SLX out-of-the-box that allows you to schedule a process like this to happen.  An add-on like Scribe Migration or similar gives you the ability to migrate data like you want.

John

Quote: Originally posted by Scott Newton Briefly, Where do I exactly go & how would I go about setting an automated schedule to do somethng like this?

If you haven't used SSIS before, I'm afraid that the word 'briefly' is not applicable here It's a fully featured ETL tool that comes with SQL Server that can move (and manipulate) almost any type of data to most likely destinations (including to an SLX database, with sync), on a scheduled basis.

That flexibility comes at a price: there's a bit of a learning curve - well worth it, in my opinion.