Error: name longer than db col size

English
#1

Hi!
I’ve just read this topic:" Sql INSERT error: name longer than db col size", which is almost the same as I have, but in my case is not when I try to insert data by SQL directly. In my case is tryin to copy the structure of a table (a calc sheet in this case) by driver, and the message is the same.
I supose the case is the same, but I don’t know how to manipulate the driver (the code) to work with simple quotes.
The error message apears when the code raises the last row I’ve wrote down

' CREATE DESCRIPTOR
' Modificado por SLV-es
Dim n As Integer, c As String
n = 1
c = ""
Do While True
    If Tables.hasByName(TableName & c) Then
        c = "_" & n
        n=n+1
    Else
        TableName = TableName & c
        Exit Do
    End If
Loop
' Fin de modificación

TableDescriptor=Tables.createDataDescriptor()
TableDescriptor.Name=TableName
Columns=TableDescriptor.Columns

If IsMissing(PKColumn) Then
	PKColumn=-1
End if
If PKColumn<0 OR PKColumn>=ResultSet.MetaData.getColumnCount() Then
	pkColumn=-1
End if

DataTypes=com.sun.star.sdbc.DataType
ColumnValues=com.sun.star.sdbc.ColumnValue
KeyTypes=com.sun.star.sdbcx.KeyType

If PKColumn<0 Then
    ColumnDescriptor=Columns.createDataDescriptor()
    PKName=TableName & "_PK"
    With ColumnDescriptor
    .Name=PKName
    .Type=DataTypes.INTEGER
    .IsNullable=ColumnValues.NO_NULLS
    .IsAutoIncrement=True
End With
Columns.appendByDescriptor(ColumnDescriptor)
Else
    PKName=ResultSet.MetaData.getColumnName(PKColumn)
End If

Dim tt,nn

For i=1 To ResultSet.MetaData.getColumnCount()

    ColumnDescriptor=Columns.createDataDescriptor() ' Just reset every time
    With ColumnDescriptor
    .Name=ResultSet.MetaData.getColumnlabel(i)
    .Type=ResultSet.MetaData.getColumnType(i)

    ' modificado SLV-es
    If .Type = 3 Then .Type = 8 ' forzar números tipo decimal a double
    	' http://docs.oracle.com/javase/1.4.2/docs/api/constant-values.html#java.sql.Types.DOUBLE
    	' fin modificación

    .IsNullable=ResultSet.MetaData.isNullable(i)
    .Description=ResultSet.MetaData.getColumnLabel(i)
    .IsAutoIncrement=ResultSet.MetaData.isAutoIncrement(i)
    .IsCurrency=ResultSet.MetaData.isCurrency(i)
    Precision=ResultSet.MetaData.getPrecision(i)
    If precision<1 Then
       	Precision=5
    End If

    ' modificado SLV-es
    If .type = 1 OR .type = 12 Then Precision = 400 ' forzar tipo texto a 400 de longitud
    ' fin modificación

    .Precision=Precision
    .Scale=ResultSet.MetaData.getScale(i)
    .Width=ResultSet.MetaData.getColumnDisplaySize(i)
	End With
	Columns.appendByDescriptor(ColumnDescriptor)
Next i

' CREATE KEY
Keys=TableDescriptor.Keys
KeyDescriptor=Keys.createDataDescriptor()
KeyDescriptor.Type=KeyTypes.PRIMARY
KeyDescriptor.Name=PKName
KeyColumns=KeyDescriptor.Columns
ColumnDescriptor=KeyColumns.createDataDescriptor()
ColumnDescriptor.Name=PKName
KeyColumns.appendByDescriptor(ColumnDescriptor)
Keys.AppendByDescriptor(KeyDescriptor)

' Finally, append table to table supplier
Tables.appendByDescriptor(TableDescriptor)
#2

Hello,
The link you refer to has nothing to do with your issue. That problem had Data surrounded with quote marks and should have been with apostrophes. You have no data - not the same problem.
.
Have done something similar but with much different code. Your code is not complete.
.
You also have no information on database used, connection type, OS, specific LO version used, the actual error received or what is being copied.
.
With this, a redacted sample will be of great help.

1 Like
#3

Sorry! You are right.
OS: Windows 10, LibreOffice Version 7.3.2.2 (x64), Database Engine Firebird.
The problem cames from other previous versions.
I’ll try to make a simple but complete example.
I imagined that the problem is the internal way of working LibreOffice at the moment of integrate the columns metadata inside the structure of a table (and I thought taht it is made by SQL, but I am a completly iliterate person in this field)

#4

The example is done!
The important tables are Transportistas, Transportistas2 and Hoja0 (when it appears).
Also say that the Transportistas table does not have a primary key and everything is String. It is something sought after, since a normal import from a spreadsheet to a new table incorporates only Strings (I think), then, with SQL I translate (especially for the dates) and incorporate it into Transportistas2 (which is my work table)
I have done three examples:
1. With embedded HSQLDB 1.8
2. With HSQLDB 2.3 split
3. With Firebird
When opening any of them, a form appears with 3 buttons, which correspond to 3 different import methods.
1. Button 1 imports from the code-launched com.sun.star.sdb.application.CopyTableWizard and in both HSQLDBs I use WscripShell to simulate a shortcut click to work automatically. Not in Firebird, but that’s when the error appears.
2. The second button opens the calc sheet in the background, selects data, closes the sheet, goes to tables and pastes. Then the import wizard comes into operation. The same thing, with WScriptShell I make it work and close automatically, but the shortcuts will depend on the language and version of Libreoffice, so I left it only for LO 7 and for Spanish and Galician…, in the rest of the cases you have to choose by hand to launch the wizard job.
In this case it works on all three bases
3. The third button launches a macro by Roberto Beneitez tuned by SLV and altered by me. It generates a new table and incorporates formatted data. He does it using the Driver. The defect it has is that it checks the format in each field of each record and that slows it down a lot, but it works……, but it only works for me in HSQLDB, not in Firebird It seems that the Driver does not work for Firebird, but for HSQLDB.
I apologize for the code, but I’m not a professional (not even a hobbyist, I just copy and paste what others did previously)
All the best!
Windows 10, LibreOffice 7.3.2.2 (x64) 49f2b1bff42cfccbd8f788c8dc32c1c309559be0

Examples

#5

I get errors on almost everything. Will need some time to figure out what is going on.

#6

Sorry!
I made several trials, and maybe the version I uploaded is not the better.
In the splitted HSQLDB, in the module 3 I have two lines with error:
Line 402: oStat= ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement
Line410:ThisComponent.Drawpage.Forms.getByName(“Formulario”).getByName(“Transportistas_Afectados”).Reload.
In order to get more fluency you can cut the calc sheet to 300 records for instance. I did it and checked all examples and they worked, except the third system in splitted HSQLDB (I hope it is corrected by those two code lines above) and the first and third procedures in the Firebird example (which were the initial problem I asked for solution. If they have!).
In Firebird the problem I detected is when the code gets the 172 line in module 1, which is the point to show the table importation wizar (I think that the driver don’t show any table to the wizard, so the problem is previous to this line)
In the thirth method the problematic line is the 316, module 3, and is the line in which the driver put the data table definition to make a database table.
I sorry to send you problems instead explanations or examples which work properly.
And also sorry because of my English!

#7

Hello,
There are many issues with your problem.
As for Creating table from Calc, I would not go through the dialog. Have seen issues with this. Need other code. (Note: another test shows this may be due to field size too large - 65K+. Manual change to 200 allowed it to process).
.
For the initial problems I was having, much had to do with you using spaces in naming conventions. Avoid spaces. Instead use underscores or camel case.
.
You have incorrect code. A bit of what I was running into in Sub EscribeDatosTablaParche2 (Firebird):

oForm=ThisComponent.Drawpage.Forms.getByName("Formulario")

line was missing but in HSQLDB.

oStat= ThisDatabaseDocumet.CurrentController.ActiveConnection.CreateStatement|

incorrect spelling.

Have tested result set in Firebird and it works. Where you are getting the error is not a true representation of where the error is actually being generated. It is in the creation of the Table definition and not in the movement of the data - Sub CreaTablaParche2(...
.
Some of the issue here is the amount of code you are trying to deal with. Smaller sections are preferable like just work with one routing to actually create the definition. Once done, now you can add data.
.
There may be other methods to create/copy a definition. See the following:

https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref-appx04-fields.html

https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref-appx04-relfields.html

I have done some initial testing with this and it appears to be feasible.
.
Edit:

Found length issue. In Calc sheet a column heading has:

Autorizado para o transporte de animais de compañía

which is 52 char and this exceeds current limit. See > Firebird 3 doesn’t currently support table names of more than 30 characters
.
PK alter statement also has problems.

#8

Now only one of the methods is not working in Firebird.
The soluction was cutting the length of the name in the calc sheet:

.Name=Left(ResultSet.MetaData.getColumnName(i),30)

It seems not to be a good solution, but for now it works!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.