Tn_Pink Posted September 20, 2014 Share Posted September 20, 2014 Good day. I am a first timer in using vectorscript. About dialog to database (excel); Question: How to input data from custom dialog to database(excel) and worksheet using vectorscript? Please kindly help me solve this. Thank you very much in advance. Quote Link to comment
Hippocode Posted September 20, 2014 Share Posted September 20, 2014 (edited) Good day. I am a first timer in using vectorscript. About dialog to database (excel); Question: How to input data from custom dialog to database(excel) and worksheet using vectorscript? Please kindly help me solve this. Thank you very much in advance. You can export DATA in vectorworks ; - delimited so excel can read it. You can do this from a worksheet within vectorworks or you could code your plugin to do so. In that case you basicly export a text file. You could also export to XML which I find more attractive.Excel can also read/write XML but sadly only static. This means that after importing the XML structure in EXCEL you can't change it(adding / removing columns) unless you delete your work in excel. This biggest difference between these 2 options is that in option 1 you completely rewrite the whole document each time you export. With XML you can just edit/add a specific element instead of doing it all over and over again. If its a database I'd go with XML. If its data you want to work with in EXCEL go text style. Edited September 20, 2014 by hippothamus Quote Link to comment
Miguel Barrera Posted September 22, 2014 Share Posted September 22, 2014 There is also 2 other alternatives that you may want to consider. The one that will give you more flexibility and two way communication is ODBC but it is also the most complicated to set up. The other one is creating a *.csv file, also a text file, that can be read directly by excel. This last one was the only way to set up databases some time ago and I still use this method to read from data tables. Quote Link to comment
Tn_Pink Posted September 22, 2014 Author Share Posted September 22, 2014 Thanks hippothamus. Yes, I need to make it into a code or script to be able to input the data to database. One more additional info, I need to use ODBC. But based on the function reference(ODBC) of vectorscript, I cannot connect the record field to database column. Can you please help on this? Thanks in advance. Quote Link to comment
Vectorworks, Inc Employee Alexandra Beshevlieva Posted September 26, 2014 Vectorworks, Inc Employee Share Posted September 26, 2014 Hi Tn_Pink, Do you use Vectorscript or Python? Quote Link to comment
Tn_Pink Posted September 27, 2014 Author Share Posted September 27, 2014 Hello Sasha, I'm using vectorscript. Quote Link to comment
Vectorworks, Inc Employee Alexandra Beshevlieva Posted September 30, 2014 Vectorworks, Inc Employee Share Posted September 30, 2014 Use can use the following script as example, but have in your mind the following: - There might be some problems with excel driver. For Excel driver the user interface is recommended; - Excel driver on Windows is read only; - If you want to update table you have to drop the table and insert it again. That's in theory, I haven't do that ever. See DBSQLExecute for more details. I used Filemaker database for the sample, feel free to use any DSN. So: DSN = "AccTest FM" username = "admin" password = "" In the database I have a table called "testrecord" which is: __________ | id | name | | ---|----------| | 1 | rect 1 | | 2 | rect 2 | | 3 | rect 3 | In my test file I created the following record: Record name = "TestRecord" [Field] "ID" of type Text [Field] "Name" of type Text I created 3 rectangles and attached the record. Now create the following script. The blocks are parts from one script, you can copy-paste them directly. I tested it, it works. I spitted it to make notes in the code. - declare the variables. {$DEBUG} is optional, the directive turns on the debug module. To use that turn on the Vectorworks option "Run script in developer mode" {$DEBUG} PROCEDURE Test; VAR databases, query : DYNARRAY [] OF CHAR; DSN, recordName : DYNARRAY [] OF CHAR; Tables : DYNARRAY [] OF CHAR; User : DYNARRAY [] OF CHAR; Pass : DYNARRAY [] OF CHAR; res, exist : BOOLEAN; i, cnt : LONGINT; hCurrentLayer, hCurrentObject, hRecord : HANDLE; fieldValue : DYNARRAY [] OF CHAR; - First we have to establish a connection to the DSN. Check if the DSN is already connected and add it as new connection only if necessary. AlertDialogs are optional, I use them for test purposes. BEGIN databases := ''; {1. Connect-------------------------------------------------------------------------------------------------} DSN := 'AccTest FM'; if DBDocGetDB(databases) then begin {check if the DSN exist} if Pos(DSN, databases) = 0 then exist := false else exist := true end else exist := false; if NOT exist then begin User := 'admin'; Pass := ''; res := DBDocAddConn( DSN, User, Pass ); end; res := DBDocGetTables( DSN, Tables ); {just check the connection} AlrtDialog( concat(DSN, Chr(13), 'succeeded = ', res, Chr(13), 'Tables: ', Tables ) ); - Second set the format connection. That could be verified from "Record Format Connection" dialog. {2. Set format connection-------------------------------------------------------------------------------------------------} recordName := 'TestRecord'; res := DBSetFormatConn(recordName, DSN, 'testrecord'); - Third set the format fields connections. That links each field to columns. The integer value in the end is the Link type of the connection. See here for more details. {3. Set format field connection-------------------------------------------------------------------------------------------------} res := DBSetFormatFieldConn(recordName , 'ID', 'id', 1); res := DBSetFormatFieldConn(recordName , 'Name', 'name', 0); - Select the objects which I want to connect. {4. Select only objects with record attached---------------------------------------------------------------------- } DSelectAll; SelectObj(INSYMBOL & INVIEWPORT & (R IN [recordName ])); - Finally connect the objects to particular row. Queries are written manually. {5.Link each object to a table row} fieldValue := ''; hCurrentLayer := FLayer; { first layer } while (hCurrentLayer <> NIL) do { loop through all the layers } begin hCurrentObject := FSObject(hCurrentLayer); { first selected object in layer } while hCurrentObject <> NIL do { loop through all the objects } begin {find the record} cnt := NumRecords(hCurrentObject ); for i := 1 to cnt do begin hRecord := GetRecord(hCurrentObject , i); if hRecord <> NIL then begin if GetName(hRecord) = recordName then begin {prepare queries} - Get field values from the record format. fieldValue := GetRField(hCurrentObject, recordName, 'id'); query := Concat('SELECT "id", "name" FROM "testrecord" WHERE "id"=', fieldValue, ''); res := DBObjSQLSetRead(hRecord, query); res := DBObjSQLGetRead(hRecord, query); AlrtDialog(query); - Get field values from the record format. %%Name%% means that Vectorworks will replace that with the value from the record. You can use it in WHERE clause also. query := Concat('UPDATE "testrecord" SET "name"=%%Name%% WHERE "id"=', fieldValue, ''); res := DBObjSQLSetWrite(hRecord, query); res := DBObjSQLGetWrite(hRecord, query); AlrtDialog(query); end; end; end; hCurrentObject := NextSObj(hCurrentObject); { next selected object } end; hCurrentLayer := NextLayer(hCurrentLayer); { next layer } end; END; RUN(Test); 1 Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.