Jump to content

Alexandra Beshevlieva

Vectorworks, Inc Employee
 View Profile  See their activity

  • Posts

    41

  • Joined

  • Last visited

 Content Type 

Posts posted by Alexandra Beshevlieva

    Data Manager Worksheet Issues

    in Troubleshooting

    Posted

    On 11/6/2019 at 6:49 PM, David Poiron said:

    2. In the OIP where we are using a field set to "pop-up", a "-" shows as a separator line which works well. But in a worksheet the pop-up at the top of the window and at the cell level only show the dash and not the separator line. See attached.

    Yes, in OIP the "-" is treated as separator, but in worksheets it is just a value.

     

     

     

     

    Dialogue Value to Record: Possible?

    in Vectorscript

    Posted

    Hello Mikaymikz!

    DBObjSQLSetWrite sets the SQL sentence used to update data from object to database. This is not your case.

    DBObjSQLGetWrite gets the SQL sencente used to update data from object to databsse. This is not your case either.

    DBSQLExecuteDSN executes a query directly to the specified datasource name. You send the DSN name, username, password and query as input parameters. That is your case :)Here is and example how to use it.

    DBSQLExecute is like the function above but you pass as parameter only the datasource name and it must be registered to the ODBC manager earlier.

    • Like 1

    Manage Database Column Names

    in General Discussion

    Posted · Edited by Sasha

    Sure! :)

    The tricky part was to find out which Psets do I need. The judgment should be made depending on the data in the datable. For my case I used furniture elements.

    I wrote a script in Python. It works over selected objects in a document. It traverses the selected objects and creates a IFC and COBie psets for the current object. Fills the values from database table and continues with next selected object. If you select 100 objects you need at least 100 records in the database. If there are less records the script will complete successfully - the IFC records will be created, but the values will be empty.

    Why Python? Because it is object oriented (it is easier to write on it), I am able to debug it and has intelli sence option in the most environments. I used Aptana studio ( here and here are articles how to configure the environment. All you need is to debug and write a plugin for Vectorworks is to add these two lines on the top of the page:

    import pydevd; pydevd.settrace(suspend=False)
import vs

    In Aptana if you write vs. the list of all available functions are listed:

    view?usp=sharing

    If you create a plugin (e.g. I created a menu command) it should be in the Plugins folder of Vectorworks. Both *.vsm and the *.py files.

    In *.vsm file you have to include the Python file:

    import menuCommand
menuCommand.execute()

    where menuCommand.py is the name of the script file.

    Here is described how to create a custom menu command.

    The entry point of the script is the function:

    def execute():
cntObjs	= vs.NumSelectedObjects()
vs.ProgressDlgOpen( 'Create IFC record with data from "' + Data.dsn + '".', False )
vs.ProgressDlgSetMeter( 'Processing ' + str(cntObjs) + ' objects...' )
vs.ProgressDlgStart( 100.0, cntObjs )

# 1. Connect
Data.ok  = vs.DBDocAddConn(Data.dsn, Data.user, Data.password)

# 2. Get CData from DB
query	= 'SELECT * FROM "' + Data.tableName + '"'
Data.ok, Data.cntCols, Data.resultSet = vs.DBSQLExecuteDSN(Data.dsn, Data.user, Data.password, query)
if Data.ok:
	# 2. Generate records
	vs.ForEachObjectInLayer(ProcessObject, 2, 2, 1)

	# Clear values
	vs.DBSQLExecuteDelete( Data.resultSet )
	vs.ProgressDlgEnd

	vs.AlrtDialog('Ready.')
else:
	vs.AlrtDialog('Failed.')

    I used the following class as global class to represents the data, required for the database connection:

    class Data:
# CUSTOMIZE THESE: database and table data 
dsn			= 'FM'
user			= 'admin'
password		= ''		
tableName		= 'Stockpile'

# DO NOT CHANGE!!! IFC record data
recordName		= 'IfcFurnishingElement'

# DO NOT CHANGE!!! traverse variables
row			= 0
ok			= False
cntCols			= 0
resultSet		= 0
cntObjs			= 0
dbNext			= True

    Here is the last function who does the main part of the job.

    def ProcessObject(h):
Data.row	= Data.row + 1
if Data.row > 1:
	Data.dbNext = vs.DBSQLExecuteNext( Data.resultSet )

#	1. Create IFC records
Data.ok	= vs.IFC_SetIFCEntity(h, Data.recordName)
if Data.ok :
	Data.ok	= vs.IFC_AttachPset(h,	'Pset_ManufacturerTypeInformation')
	Data.ok	= vs.IFC_AttachPset(h,	'Pset_ManufacturerOccurrence')
	Data.ok	= vs.IFC_AttachPset(h,	'COBie_Asset')
	Data.ok	= vs.IFC_SetPsetProp(h,	'COBie_Asset',	'AssetType',	'Movable')
	Data.ok	= vs.IFC_AttachPset(h,	'COBie_Specification')

	for col in range(1, Data.cntCols):
		Data.ok, colName, colValue	= vs.DBSQLExecuteGet( Data.resultSet, col )

		if colName == 'Item':
			Data.ok	= vs.IFC_SetEntityProp(h,	'Name', 		colValue)
			Data.ok	= vs.IFC_SetEntityProp(h,	'ObjectType',	colValue)
			Data.ok	= vs.IFC_SetPsetProp(h,		'Pset_ManufacturerTypeInformation',	'ModelLabel',	colValue)

		elif colName == 'Part Number':
			Data.ok	= vs.IFC_SetEntityProp(h,	'Tag',			colValue)
			Data.ok	= vs.IFC_SetPsetProp(h,		'Pset_ManufacturerTypeInformation',	'ArticleNumber',	colValue) 

		elif colName == 'Model Year':
			 Data.ok	= vs.IFC_SetPsetProp(h,	'Pset_ManufacturerTypeInformation',	'ProductionYear',	colValue)

		elif colName == 'Manufacturer':
			 Data.ok	= vs.IFC_SetPsetProp(h,	'Pset_ManufacturerTypeInformation',	'Manufacturer',	colValue)

		elif colName == 'Bar Code':
			Data.ok	= vs.IFC_SetPsetProp(h,	'Pset_ManufacturerOccurrence',	'BarCode',	colValue)

		elif colName == 'Dimensions':
			Data.ok	= vs.IFC_SetPsetProp(h,	'COBie_Specification',	'Size',	colValue)
	vs.ProgressDlgYeld( 1 )

    Mine test database is Filemaker database and the DSN name I used is called "FM". My table is called "Stockpile" and here is the structure: view?usp=sharing.

    And that's all. Enjoy! :)

    How to use DBSQLExecuteError?

    in Vectorscript

    Posted

    Here is a sample how to use it.

    PROCEDURE Test;
VAR
ok : BOOLEAN;
message, state, internalDesc : DYNARRAY[] OF CHAR;
code, colCnt, resSetInst : LONGINT;
BEGIN
ok := DBDocAddConn( 'MyTestODBCDatabase', '', '' );
ok := DBSQLExecuteDSN( 'MyTestODBCDatabase', '', '', 'SELECT * FROM Spaces', colCnt, resSetInst );
IF NOT ok THEN
BEGIN
	ok := DBSQLExecuteError(message, state, code, internalDesc);
	AlrtDialog(Concat('state: ', state, Chr(13), 'message: ', message, Chr(13), 'code: ', code, Chr(13), 'intDesc: ', internalDesc));
END;
END;

RUN(Test);

    Manage Database Column Names

    in General Discussion

    Posted

    Speaking in general they could be connected. IFC data is a record, attached to the object So there shouldn't be a problem to connect them. It is not visible in the "Record Format Connection" dialog because it is hidden record. But with script you can reach each record.

    The thing is that you have to know which field should be used as key column.

    How to input data from custom dialog to database(excel) and worksheet using

    in Vectorscript

    Posted

    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);

    • Like 1

    object database connection

    in General Discussion

    Posted

    Hi there!

    Check this article. There is a sample script.

    You wrote:

    1) How can I connect the specific database table in manage databases dialog?

    User:='';

    Pass:='';

    ConnectionResult:=DBDocAddConn('AccessName01',User,Pass);

    ConnectionResult:=DBSetFormatConn('RecordName01','DBName01','TableName01');

    ConnectionResult:=DBDocSetColKey('DBName01','TableName01','ColumnName01',TRUE);

    * But still not connected

    How do you check the connection at this point?

    Manage Database Column Names

    in General Discussion

    Posted

    That's strange, there is no limitation of column name length in Vectorworks. Vectorworks uses the driver limitations. Filemaker does have limitations, but what I see from the screen shots this is not the case. The maximum column length of text is 1 million characters, unless you specify a smaller Maximum number of characters for the text field in FileMaker. FileMaker returns empty strings as NULL. For more details see here.

    Are there any limits for text length?

×
×
  • Create New...